🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa107)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/guides/developer/ttl\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/guides/developer/ttl\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl","crawl_time":1775488413,"first_indexed_time":1740096099,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/guides\/developer\/ttl s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"Manage data with TTL (Time-to-live) | ClickHouse Docs","meta_descriptions":["TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed."],"attrs_boilerpipe_text":"Overview of TTL\n\nTTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression \"time-to-live\" sounds like it only applies to deleting old data, TTL has several use cases:\nRemoving old data: no surprise, you can delete rows or columns after a specified time interval\nMoving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot\/warm\/cold architecture\nData rollup: rollup your older data into various useful aggregations and computations before deleting it\nNote\nTTL can be applied to entire tables or specific columns.\nTTL syntax\n\nThe\nTTL\nclause can appear after a column definition and\/or at the end of the table definition. Use the\nINTERVAL\nclause to define a length of time (which needs to be a\nDate\nor\nDateTime\ndata type). For example, the following table has two columns\nwith\nTTL\nclauses:\nCREATE\nTABLE\nexample1\n(\ntimestamp\nDateTime\n,\nx UInt32 TTL\ntimestamp\n+\nINTERVAL\n1\nMONTH\n,\ny String TTL\ntimestamp\n+\nINTERVAL\n1\nDAY\n,\nz String\n)\nENGINE\n=\nMergeTree\nORDER\nBY\ntuple\n(\n)\nThe x column has a time to live of 1 month from the timestamp column\nThe y column has a time to live of 1 day from the timestamp column\nWhen the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem.\nBest practice\nWhen using table-level TTL to remove old rows, we recommend to\npartition your table by the date or month\nof the same time field used in your TTL expression.\nClickHouse can drop entire partitions much more efficiently than deleting individual rows.\nWhen your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows.\nChoose your partition granularity based on your TTL period:\nFor TTL of days\/weeks: partition by day using\ntoYYYYMMDD(date_field)\nFor TTL of months\/years: partition by month using\ntoYYYYMM(date_field)\nor\ntoStartOfMonth(date_field)\nTriggering TTL events\n\nThe deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events:\nmerge_with_ttl_timeout\n: the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours).\nmerge_with_recompression_ttl_timeout\n: the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours).\nSo by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently.\nNote\nNot a great solution (or one that we recommend you use frequently), but you can also force a merge using\nOPTIMIZE\n:\nOPTIMIZE\nTABLE\nexample1 FINAL\nOPTIMIZE\ninitializes an unscheduled merge of the parts of your table, and\nFINAL\nforces a reoptimization if your table is already a single part.\nRemoving rows\n\nTo remove entire rows from a table after a certain amount of time, define the TTL rule at the table level:\nCREATE\nTABLE\ncustomers\n(\ntimestamp\nDateTime\n,\nname String\n,\nbalance Int32\n,\naddress String\n)\nENGINE\n=\nMergeTree\nORDER\nBY\ntimestamp\nTTL\ntimestamp\n+\nINTERVAL\n12\nHOUR\nAdditionally, it is possible to define a TTL rule based on the record's value.\nThis is easily implemented by specifying a where condition.\nMultiple conditions are allowed:\nCREATE\nTABLE\nevents\n(\n`\nevent\n`\nString\n,\n`\ntime\n`\nDateTime\n,\n`\nvalue\n`\nUInt64\n)\nENGINE\n=\nMergeTree\nORDER\nBY\n(\nevent\n,\ntime\n)\nTTL\ntime\n+\nINTERVAL\n1\nMONTH\nDELETE\nWHERE\nevent\n!=\n'error'\n,\ntime\n+\nINTERVAL\n6\nMONTH\nDELETE\nWHERE\nevent\n=\n'error'\nRemoving columns\n\nInstead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the\ncustomers\ntable and add a TTL for both columns to be 2 hours:\nALTER\nTABLE\ncustomers\nMODIFY\nCOLUMN\nbalance Int32 TTL\ntimestamp\n+\nINTERVAL\n2\nHOUR\n,\nMODIFY\nCOLUMN\naddress String TTL\ntimestamp\n+\nINTERVAL\n2\nHOUR\nImplementing a rollup\n\nSuppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a\nGROUP BY\nclause to your\nTTL\nexpression, along with some columns in your table to store the aggregated results.\nSuppose in the following\nhits\ntable we want to delete old rows, but hang on to the sum and maximum of the\nhits\ncolumns before removing the rows. We will need a field to store those values in, and we will need to add a\nGROUP BY\nclause to the\nTTL\nclause that rolls up the sum and maximum:\nCREATE\nTABLE\nhits\n(\ntimestamp\nDateTime\n,\nid String\n,\nhits Int32\n,\nmax_hits Int32\nDEFAULT\nhits\n,\nsum_hits Int64\nDEFAULT\nhits\n)\nENGINE\n=\nMergeTree\nPRIMARY\nKEY\n(\nid\n,\ntoStartOfDay\n(\ntimestamp\n)\n,\ntimestamp\n)\nTTL\ntimestamp\n+\nINTERVAL\n1\nDAY\nGROUP\nBY\nid\n,\ntoStartOfDay\n(\ntimestamp\n)\nSET\nmax_hits\n=\nmax\n(\nmax_hits\n)\n,\nsum_hits\n=\nsum\n(\nsum_hits\n)\n;\nSome notes on the\nhits\ntable:\nThe\nGROUP BY\ncolumns in the\nTTL\nclause must be a prefix of the\nPRIMARY KEY\n, and we want to group our results by the start of the day. Therefore,\ntoStartOfDay(timestamp)\nwas added to the primary key\nWe added two fields to store the aggregated results:\nmax_hits\nand\nsum_hits\nSetting the default value of\nmax_hits\nand\nsum_hits\nto\nhits\nis necessary for our logic to work, based on how the\nSET\nclause is defined\nImplementing a hot\/warm\/cold architecture\n\nNot supported in ClickHouse Cloud\nNote\nIf you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud.\nA common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot\/warm\/cold architecture in ClickHouse using the\nTO DISK\nand\nTO VOLUME\nclauses of the\nTTL\ncommand. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.)\nThe\nTO DISK\nand\nTO VOLUME\noptions refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named\nmy_system.xml\n(or any file name) that defines your disks, then define volumes that use your disks.  Place the XML file in\n\/etc\/clickhouse-server\/config.d\/\nto have the configuration applied to your system:\n<\nclickhouse\n>\n<\nstorage_configuration\n>\n<\ndisks\n>\n<\ndefault\n>\n<\/\ndefault\n>\n<\nhot_disk\n>\n<\npath\n>\n.\/hot\/\n<\/\npath\n>\n<\/\nhot_disk\n>\n<\nwarm_disk\n>\n<\npath\n>\n.\/warm\/\n<\/\npath\n>\n<\/\nwarm_disk\n>\n<\ncold_disk\n>\n<\npath\n>\n.\/cold\/\n<\/\npath\n>\n<\/\ncold_disk\n>\n<\/\ndisks\n>\n<\npolicies\n>\n<\ndefault\n>\n<\nvolumes\n>\n<\ndefault\n>\n<\ndisk\n>\ndefault\n<\/\ndisk\n>\n<\/\ndefault\n>\n<\nhot_volume\n>\n<\ndisk\n>\nhot_disk\n<\/\ndisk\n>\n<\/\nhot_volume\n>\n<\nwarm_volume\n>\n<\ndisk\n>\nwarm_disk\n<\/\ndisk\n>\n<\/\nwarm_volume\n>\n<\ncold_volume\n>\n<\ndisk\n>\ncold_disk\n<\/\ndisk\n>\n<\/\ncold_volume\n>\n<\/\nvolumes\n>\n<\/\ndefault\n>\n<\/\npolicies\n>\n<\/\nstorage_configuration\n>\n<\/\nclickhouse\n>\nThe configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks:\nSELECT\nname\n,\npath\n,\nfree_space\n,\ntotal_space\nFROM\nsystem\n.\ndisks\n┌─name────────┬─path───────────┬───free_space─┬──total_space─┐\n│ cold_disk   │ .\/data\/cold\/   │ 179143311360 │ 494384795648 │\n│ default     │ .\/             │ 179143311360 │ 494384795648 │\n│ hot_disk    │ .\/data\/hot\/    │ 179143311360 │ 494384795648 │\n│ warm_disk   │ .\/data\/warm\/   │ 179143311360 │ 494384795648 │\n└─────────────┴────────────────┴──────────────┴──────────────┘\nAnd...let's verify the volumes:\nSELECT\nvolume_name\n,\ndisks\nFROM\nsystem\n.\nstorage_policies\n┌─volume_name─┬─disks─────────┐\n│ default     │ ['default']   │\n│ hot_volume  │ ['hot_disk']  │\n│ warm_volume │ ['warm_disk'] │\n│ cold_volume │ ['cold_disk'] │\n└─────────────┴───────────────┘\nNow we will add a\nTTL\nrule that moves the data between the hot, warm and cold volumes:\nALTER\nTABLE\nmy_table\nMODIFY\nTTL\ntrade_date\nTO\nVOLUME\n'hot_volume'\n,\ntrade_date\n+\nINTERVAL\n2\nYEAR\nTO\nVOLUME\n'warm_volume'\n,\ntrade_date\n+\nINTERVAL\n4\nYEAR\nTO\nVOLUME\n'cold_volume'\n;\nThe new\nTTL\nrule should materialize, but you can force it to make sure:\nALTER\nTABLE\nmy_table\nMATERIALIZE TTL\nVerify your data has moved to its expected disks using the\nsystem.parts\ntable:\nUsing\nthe system\n.\nparts\ntable\n,\nview\nwhich disks the parts are\non\nfor\nthe crypto_prices\ntable\n:\nSELECT\nname\n,\ndisk_name\nFROM\nsystem\n.\nparts\nWHERE\n(\ntable\n=\n'my_table'\n)\nAND\n(\nactive\n=\n1\n)\nThe response will look like:\n┌─name────────┬─disk_name─┐\n│ all_1_3_1_5 │ warm_disk │\n│ all_2_2_0   │ hot_disk  │\n└─────────────┴───────────┘","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n\n- [English](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/guides\/developer\/ttl)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/guides\/developer\/ttl)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/guides\/developer\/ttl)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/guides\/developer\/ttl)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n\n- [English](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/guides\/developer\/ttl)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/guides\/developer\/ttl)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/guides\/developer\/ttl)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/guides\/developer\/ttl)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Core concepts](https:\/\/clickhouse.com\/docs\/managing-data\/core-concepts)\n  - [Table parts](https:\/\/clickhouse.com\/docs\/parts)\n  - [Table partitions](https:\/\/clickhouse.com\/docs\/partitions)\n  - [Part merges](https:\/\/clickhouse.com\/docs\/merges)\n  - [Table shards and replicas](https:\/\/clickhouse.com\/docs\/shards)\n  - [Primary indexes](https:\/\/clickhouse.com\/docs\/primary-indexes)\n  - [Architecture overview](https:\/\/clickhouse.com\/docs\/academic_overview)\n- [Updating data](https:\/\/clickhouse.com\/docs\/updating-data)\n  - [Working with updates](https:\/\/clickhouse.com\/docs\/updating-data\/overview)\n  - [Working with the ReplacingMergeTree engine](https:\/\/clickhouse.com\/docs\/guides\/replacing-merge-tree)\n- [Deleting data](https:\/\/clickhouse.com\/docs\/managing-data\/deleting-data\/overview)\n  - [Delete overview](https:\/\/clickhouse.com\/docs\/deletes\/overview)\n  - [Lightweight deletes](https:\/\/clickhouse.com\/docs\/guides\/developer\/lightweight-delete)\n  - [Delete mutations](https:\/\/clickhouse.com\/docs\/managing-data\/delete_mutations)\n  - [Truncate table](https:\/\/clickhouse.com\/docs\/managing-data\/truncate)\n  - [Drop partition](https:\/\/clickhouse.com\/docs\/managing-data\/drop_partition)\n- [Data modeling](https:\/\/clickhouse.com\/docs\/data-modeling\/overview)\n  - [Schema design](https:\/\/clickhouse.com\/docs\/data-modeling\/schema-design)\n  - [Dictionary](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  - [Materialized views](https:\/\/clickhouse.com\/docs\/materialized-views)\n  - [Projections](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  - [Data compression](https:\/\/clickhouse.com\/docs\/data-compression\/compression-in-clickhouse)\n  - [Denormalizing Data](https:\/\/clickhouse.com\/docs\/data-modeling\/denormalization)\n  - [Backfilling Data](https:\/\/clickhouse.com\/docs\/data-modeling\/backfilling)\n- [Advanced guides](https:\/\/clickhouse.com\/docs\/guides\/developer\/overview)\n  - [Dynamic column selection](https:\/\/clickhouse.com\/docs\/guides\/developer\/dynamic-column-selection)\n  - [Merge table function](https:\/\/clickhouse.com\/docs\/guides\/developer\/merge-table-function)\n  - [Alternative query languages](https:\/\/clickhouse.com\/docs\/guides\/developer\/alternative-query-languages)\n  - [Cascading materialized views](https:\/\/clickhouse.com\/docs\/guides\/developer\/cascading-materialized-views)\n  - [Debugging memory issues](https:\/\/clickhouse.com\/docs\/guides\/developer\/debugging-memory-issues)\n  - [Deduplicating inserts on retries](https:\/\/clickhouse.com\/docs\/guides\/developer\/deduplicating-inserts-on-retries)\n  - [Deduplication strategies](https:\/\/clickhouse.com\/docs\/guides\/developer\/deduplication)\n  - [Time series - gap fill](https:\/\/clickhouse.com\/docs\/guides\/developer\/time-series-filling-gaps)\n  - [Transactional (ACID) support](https:\/\/clickhouse.com\/docs\/guides\/developer\/transactional)\n  - [TTL (Time to Live)](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  - [Understanding query execution with the analyzer](https:\/\/clickhouse.com\/docs\/guides\/developer\/understanding-query-execution-with-the-analyzer)\n  - [Using JOINs in ClickHouse](https:\/\/clickhouse.com\/docs\/guides\/joining-tables)\n  - [Stored procedures & query parameters](https:\/\/clickhouse.com\/docs\/guides\/developer\/stored-procedures-and-prepared-statements)\n- [Performance and optimizations](https:\/\/clickhouse.com\/docs\/operations\/overview)\n  - [Query optimization](https:\/\/clickhouse.com\/docs\/optimize\/query-optimization)\n  - [Primary indexes](https:\/\/clickhouse.com\/docs\/guides\/best-practices\/sparse-primary-indexes)\n  - [Query parallelism](https:\/\/clickhouse.com\/docs\/optimize\/query-parallelism)\n  - [Partitioning key](https:\/\/clickhouse.com\/docs\/optimize\/partitioning-key)\n  - [Data skipping indexes](https:\/\/clickhouse.com\/docs\/optimize\/skipping-indexes)\n  - [PREWHERE optimization](https:\/\/clickhouse.com\/docs\/optimize\/prewhere)\n  - [Lazy materialization](https:\/\/clickhouse.com\/docs\/optimize\/lazy-materialization)\n  - [Bulk inserts](https:\/\/clickhouse.com\/docs\/optimize\/bulk-inserts)\n  - [Asynchronous inserts](https:\/\/clickhouse.com\/docs\/optimize\/asynchronous-inserts)\n  - [Avoid mutations](https:\/\/clickhouse.com\/docs\/optimize\/avoid-mutations)\n  - [Avoid nullable columns](https:\/\/clickhouse.com\/docs\/optimize\/avoid-nullable-columns)\n  - [Avoid optimize final](https:\/\/clickhouse.com\/docs\/optimize\/avoidoptimizefinal)\n  - [Analyzer](https:\/\/clickhouse.com\/docs\/operations\/analyzer)\n  - [Query Profiling](https:\/\/clickhouse.com\/docs\/operations\/optimizing-performance\/sampling-query-profiler)\n  - [Query cache](https:\/\/clickhouse.com\/docs\/operations\/query-cache)\n  - [Query condition cache](https:\/\/clickhouse.com\/docs\/operations\/query-condition-cache)\n  - [Userspace page cache](https:\/\/clickhouse.com\/docs\/operations\/userspace-page-cache)\n  - [Testing Hardware](https:\/\/clickhouse.com\/docs\/operations\/performance-test)\n\n- [Advanced guides](https:\/\/clickhouse.com\/docs\/guides\/developer\/overview)\n- TTL (Time to Live)\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/clickhouse-docs\/blob\/main\/docs\/guides\/developer\/ttl.md)\n\n# Manage data with TTL (time-to-live)\n## Overview of TTL[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#overview-of-ttl \"Direct link to Overview of TTL\")\nTTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression \"time-to-live\" sounds like it only applies to deleting old data, TTL has several use cases:\n\n- Removing old data: no surprise, you can delete rows or columns after a specified time interval\n- Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot\/warm\/cold architecture\n- Data rollup: rollup your older data into various useful aggregations and computations before deleting it\n\nNote\n\nTTL can be applied to entire tables or specific columns.\n## TTL syntax[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#ttl-syntax \"Direct link to TTL syntax\")\nThe `TTL` clause can appear after a column definition and\/or at the end of the table definition. Use the `INTERVAL` clause to define a length of time (which needs to be a `Date` or `DateTime` data type). For example, the following table has two columns with `TTL` clauses:\n```\nCREATE TABLE example1 (\n   timestamp DateTime,\n   x UInt32 TTL timestamp + INTERVAL 1 MONTH,\n   y String TTL timestamp + INTERVAL 1 DAY,\n   z String\n)\nENGINE = MergeTree\nORDER BY tuple()\n```\n- The x column has a time to live of 1 month from the timestamp column\n- The y column has a time to live of 1 day from the timestamp column\n- When the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem.\n\nNote\n\nTTL rules can be altered or deleted. See the [Manipulations with Table TTL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/alter\/ttl) page for more details.\n\nBest practice\n\nWhen using table-level TTL to remove old rows, we recommend to **partition your table by the date or month** of the same time field used in your TTL expression.\n\nClickHouse can drop entire partitions much more efficiently than deleting individual rows. When your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows.\n\nChoose your partition granularity based on your TTL period:\n\n- For TTL of days\/weeks: partition by day using `toYYYYMMDD(date_field)`\n- For TTL of months\/years: partition by month using `toYYYYMM(date_field)` or `toStartOfMonth(date_field)`\n## Triggering TTL events[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#triggering-ttl-events \"Direct link to Triggering TTL events\")\nThe deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events:\n\n- `merge_with_ttl_timeout`: the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours).\n- `merge_with_recompression_ttl_timeout`: the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours).\n\nSo by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently.\n\nNote\n\nNot a great solution (or one that we recommend you use frequently), but you can also force a merge using `OPTIMIZE`:\n```\nOPTIMIZE TABLE example1 FINAL\n```\n`OPTIMIZE` initializes an unscheduled merge of the parts of your table, and `FINAL` forces a reoptimization if your table is already a single part.\n## Removing rows[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-rows \"Direct link to Removing rows\")\nTo remove entire rows from a table after a certain amount of time, define the TTL rule at the table level:\n```\nCREATE TABLE customers (\ntimestamp DateTime,\nname String,\nbalance Int32,\naddress String\n)\nENGINE = MergeTree\nORDER BY timestamp\nTTL timestamp + INTERVAL 12 HOUR\n```\nAdditionally, it is possible to define a TTL rule based on the record's value. This is easily implemented by specifying a where condition. Multiple conditions are allowed:\n```\nCREATE TABLE events\n(\n    `event` String,\n    `time` DateTime,\n    `value` UInt64\n)\nENGINE = MergeTree\nORDER BY (event, time)\nTTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error',\n    time + INTERVAL 6 MONTH DELETE WHERE event = 'error'\n```\n## Removing columns[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-columns \"Direct link to Removing columns\")\nInstead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the `customers` table and add a TTL for both columns to be 2 hours:\n```\nALTER TABLE customers\nMODIFY COLUMN balance Int32 TTL timestamp + INTERVAL 2 HOUR,\nMODIFY COLUMN address String TTL timestamp + INTERVAL 2 HOUR\n```\n## Implementing a rollup[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-rollup \"Direct link to Implementing a rollup\")\nSuppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a `GROUP BY` clause to your `TTL` expression, along with some columns in your table to store the aggregated results.\n\nSuppose in the following `hits` table we want to delete old rows, but hang on to the sum and maximum of the `hits` columns before removing the rows. We will need a field to store those values in, and we will need to add a `GROUP BY` clause to the `TTL` clause that rolls up the sum and maximum:\n```\nCREATE TABLE hits (\n   timestamp DateTime,\n   id String,\n   hits Int32,\n   max_hits Int32 DEFAULT hits,\n   sum_hits Int64 DEFAULT hits\n)\nENGINE = MergeTree\nPRIMARY KEY (id, toStartOfDay(timestamp), timestamp)\nTTL timestamp + INTERVAL 1 DAY\n    GROUP BY id, toStartOfDay(timestamp)\n    SET\n        max_hits = max(max_hits),\n        sum_hits = sum(sum_hits);\n```\nSome notes on the `hits` table:\n\n- The `GROUP BY` columns in the `TTL` clause must be a prefix of the `PRIMARY KEY`, and we want to group our results by the start of the day. Therefore, `toStartOfDay(timestamp)` was added to the primary key\n- We added two fields to store the aggregated results: `max_hits` and `sum_hits`\n- Setting the default value of `max_hits` and `sum_hits` to `hits` is necessary for our logic to work, based on how the `SET` clause is defined\n## Implementing a hot\/warm\/cold architecture[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-hotwarmcold-architecture \"Direct link to Implementing a hot\/warm\/cold architecture\")\nNot supported in ClickHouse Cloud\n\nNote\n\nIf you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud.\n\nA common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot\/warm\/cold architecture in ClickHouse using the `TO DISK` and `TO VOLUME` clauses of the `TTL` command. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.)\n\n1. The `TO DISK` and `TO VOLUME` options refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named `my_system.xml` (or any file name) that defines your disks, then define volumes that use your disks. Place the XML file in `\/etc\/clickhouse-server\/config.d\/` to have the configuration applied to your system:\n```\n<clickhouse>\n    <storage_configuration>\n        <disks>\n            <default>\n            <\/default>\n           <hot_disk>\n              <path>.\/hot\/<\/path>\n           <\/hot_disk>\n           <warm_disk>\n              <path>.\/warm\/<\/path>\n           <\/warm_disk>\n           <cold_disk>\n              <path>.\/cold\/<\/path>\n           <\/cold_disk>\n        <\/disks>\n        <policies>\n            <default>\n                <volumes>\n                    <default>\n                        <disk>default<\/disk>\n                    <\/default>\n                    <hot_volume>\n                        <disk>hot_disk<\/disk>\n                    <\/hot_volume>\n                    <warm_volume>\n                        <disk>warm_disk<\/disk>\n                    <\/warm_volume>\n                    <cold_volume>\n                        <disk>cold_disk<\/disk>\n                    <\/cold_volume>\n                <\/volumes>\n            <\/default>\n        <\/policies>\n    <\/storage_configuration>\n<\/clickhouse>\n```\n1. The configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks:\n```\nSELECT name, path, free_space, total_space\nFROM system.disks\n```\n```\n┌─name────────┬─path───────────┬───free_space─┬──total_space─┐\n│ cold_disk   │ .\/data\/cold\/   │ 179143311360 │ 494384795648 │\n│ default     │ .\/             │ 179143311360 │ 494384795648 │\n│ hot_disk    │ .\/data\/hot\/    │ 179143311360 │ 494384795648 │\n│ warm_disk   │ .\/data\/warm\/   │ 179143311360 │ 494384795648 │\n└─────────────┴────────────────┴──────────────┴──────────────┘\n```\n1. And...let's verify the volumes:\n```\nSELECT\n    volume_name,\n    disks\nFROM system.storage_policies\n```\n```\n┌─volume_name─┬─disks─────────┐\n│ default     │ ['default']   │\n│ hot_volume  │ ['hot_disk']  │\n│ warm_volume │ ['warm_disk'] │\n│ cold_volume │ ['cold_disk'] │\n└─────────────┴───────────────┘\n```\n1. Now we will add a `TTL` rule that moves the data between the hot, warm and cold volumes:\n```\nALTER TABLE my_table\n   MODIFY TTL\n      trade_date TO VOLUME 'hot_volume',\n      trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume',\n      trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume';\n```\n1. The new `TTL` rule should materialize, but you can force it to make sure:\n```\nALTER TABLE my_table\n    MATERIALIZE TTL\n```\n1. Verify your data has moved to its expected disks using the `system.parts` table:\n```\nUsing the system.parts table, view which disks the parts are on for the crypto_prices table:\n\nSELECT\n    name,\n    disk_name\nFROM system.parts\nWHERE (table = 'my_table') AND (active = 1)\n```\nThe response will look like:\n```\n┌─name────────┬─disk_name─┐\n│ all_1_3_1_5 │ warm_disk │\n│ all_2_2_0   │ hot_disk  │\n└─────────────┴───────────┘\n```\n\n[Previous Transactional (ACID) support](https:\/\/clickhouse.com\/docs\/guides\/developer\/transactional)\n\n[Next Understanding query execution with the analyzer](https:\/\/clickhouse.com\/docs\/guides\/developer\/understanding-query-execution-with-the-analyzer)\n\n- [Overview of TTL](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#overview-of-ttl)\n- [TTL syntax](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#ttl-syntax)\n- [Triggering TTL events](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#triggering-ttl-events)\n- [Removing rows](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-rows)\n- [Removing columns](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-columns)\n- [Implementing a rollup](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-rollup)\n- [Implementing a hot\/warm\/cold architecture](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-hotwarmcold-architecture)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Core concepts▼\n- Updating data▼\n- Deleting data▼\n- Data modeling▼\n- Advanced guides▼\n  - [Dynamic column selection](https:\/\/clickhouse.com\/docs\/guides\/developer\/dynamic-column-selection)\n  - [Merge table function](https:\/\/clickhouse.com\/docs\/guides\/developer\/merge-table-function)\n  - [Alternative query languages](https:\/\/clickhouse.com\/docs\/guides\/developer\/alternative-query-languages)\n  - [Cascading materialized views](https:\/\/clickhouse.com\/docs\/guides\/developer\/cascading-materialized-views)\n  - [Debugging memory issues](https:\/\/clickhouse.com\/docs\/guides\/developer\/debugging-memory-issues)\n  - [Deduplicating inserts on retries](https:\/\/clickhouse.com\/docs\/guides\/developer\/deduplicating-inserts-on-retries)\n  - [Deduplication strategies](https:\/\/clickhouse.com\/docs\/guides\/developer\/deduplication)\n  - [Time series - gap fill](https:\/\/clickhouse.com\/docs\/guides\/developer\/time-series-filling-gaps)\n  - [Transactional (ACID) support](https:\/\/clickhouse.com\/docs\/guides\/developer\/transactional)\n  - [TTL (Time to Live)](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl)\n  - [Understanding query execution with the analyzer](https:\/\/clickhouse.com\/docs\/guides\/developer\/understanding-query-execution-with-the-analyzer)\n  - [Using JOINs in ClickHouse](https:\/\/clickhouse.com\/docs\/guides\/joining-tables)\n  - [Stored procedures & query parameters](https:\/\/clickhouse.com\/docs\/guides\/developer\/stored-procedures-and-prepared-statements)\n- Performance and optimizations▼","attrs_readable_markdown":"## Overview of TTL[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#overview-of-ttl \"Direct link to Overview of TTL\")\nTTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression \"time-to-live\" sounds like it only applies to deleting old data, TTL has several use cases:\n\n- Removing old data: no surprise, you can delete rows or columns after a specified time interval\n- Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot\/warm\/cold architecture\n- Data rollup: rollup your older data into various useful aggregations and computations before deleting it\n\nNote\n\nTTL can be applied to entire tables or specific columns.\n## TTL syntax[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#ttl-syntax \"Direct link to TTL syntax\")\nThe `TTL` clause can appear after a column definition and\/or at the end of the table definition. Use the `INTERVAL` clause to define a length of time (which needs to be a `Date` or `DateTime` data type). For example, the following table has two columns with `TTL` clauses:\n```\nCREATE TABLE example1 (\n   timestamp DateTime,\n   x UInt32 TTL timestamp + INTERVAL 1 MONTH,\n   y String TTL timestamp + INTERVAL 1 DAY,\n   z String\n)\nENGINE = MergeTree\nORDER BY tuple()\n```\n- The x column has a time to live of 1 month from the timestamp column\n- The y column has a time to live of 1 day from the timestamp column\n- When the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem.\n\nBest practice\n\nWhen using table-level TTL to remove old rows, we recommend to **partition your table by the date or month** of the same time field used in your TTL expression.\n\nClickHouse can drop entire partitions much more efficiently than deleting individual rows. When your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows.\n\nChoose your partition granularity based on your TTL period:\n\n- For TTL of days\/weeks: partition by day using `toYYYYMMDD(date_field)`\n- For TTL of months\/years: partition by month using `toYYYYMM(date_field)` or `toStartOfMonth(date_field)`\n## Triggering TTL events[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#triggering-ttl-events \"Direct link to Triggering TTL events\")\nThe deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events:\n\n- `merge_with_ttl_timeout`: the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours).\n- `merge_with_recompression_ttl_timeout`: the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours).\n\nSo by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently.\n\nNote\n\nNot a great solution (or one that we recommend you use frequently), but you can also force a merge using `OPTIMIZE`:\n```\nOPTIMIZE TABLE example1 FINAL\n```\n`OPTIMIZE` initializes an unscheduled merge of the parts of your table, and `FINAL` forces a reoptimization if your table is already a single part.\n## Removing rows[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-rows \"Direct link to Removing rows\")\nTo remove entire rows from a table after a certain amount of time, define the TTL rule at the table level:\n```\nCREATE TABLE customers (\ntimestamp DateTime,\nname String,\nbalance Int32,\naddress String\n)\nENGINE = MergeTree\nORDER BY timestamp\nTTL timestamp + INTERVAL 12 HOUR\n```\nAdditionally, it is possible to define a TTL rule based on the record's value. This is easily implemented by specifying a where condition. Multiple conditions are allowed:\n```\nCREATE TABLE events\n(\n    `event` String,\n    `time` DateTime,\n    `value` UInt64\n)\nENGINE = MergeTree\nORDER BY (event, time)\nTTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error',\n    time + INTERVAL 6 MONTH DELETE WHERE event = 'error'\n```\n## Removing columns[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#removing-columns \"Direct link to Removing columns\")\nInstead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the `customers` table and add a TTL for both columns to be 2 hours:\n```\nALTER TABLE customers\nMODIFY COLUMN balance Int32 TTL timestamp + INTERVAL 2 HOUR,\nMODIFY COLUMN address String TTL timestamp + INTERVAL 2 HOUR\n```\n## Implementing a rollup[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-rollup \"Direct link to Implementing a rollup\")\nSuppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a `GROUP BY` clause to your `TTL` expression, along with some columns in your table to store the aggregated results.\n\nSuppose in the following `hits` table we want to delete old rows, but hang on to the sum and maximum of the `hits` columns before removing the rows. We will need a field to store those values in, and we will need to add a `GROUP BY` clause to the `TTL` clause that rolls up the sum and maximum:\n```\nCREATE TABLE hits (\n   timestamp DateTime,\n   id String,\n   hits Int32,\n   max_hits Int32 DEFAULT hits,\n   sum_hits Int64 DEFAULT hits\n)\nENGINE = MergeTree\nPRIMARY KEY (id, toStartOfDay(timestamp), timestamp)\nTTL timestamp + INTERVAL 1 DAY\n    GROUP BY id, toStartOfDay(timestamp)\n    SET\n        max_hits = max(max_hits),\n        sum_hits = sum(sum_hits);\n```\nSome notes on the `hits` table:\n\n- The `GROUP BY` columns in the `TTL` clause must be a prefix of the `PRIMARY KEY`, and we want to group our results by the start of the day. Therefore, `toStartOfDay(timestamp)` was added to the primary key\n- We added two fields to store the aggregated results: `max_hits` and `sum_hits`\n- Setting the default value of `max_hits` and `sum_hits` to `hits` is necessary for our logic to work, based on how the `SET` clause is defined\n## Implementing a hot\/warm\/cold architecture[](https:\/\/clickhouse.com\/docs\/guides\/developer\/ttl#implementing-a-hotwarmcold-architecture \"Direct link to Implementing a hot\/warm\/cold architecture\")\nNot supported in ClickHouse Cloud\n\nNote\n\nIf you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud.\n\nA common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot\/warm\/cold architecture in ClickHouse using the `TO DISK` and `TO VOLUME` clauses of the `TTL` command. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.)\n\n1. The `TO DISK` and `TO VOLUME` options refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named `my_system.xml` (or any file name) that defines your disks, then define volumes that use your disks. Place the XML file in `\/etc\/clickhouse-server\/config.d\/` to have the configuration applied to your system:\n```\n<clickhouse>\n    <storage_configuration>\n        <disks>\n            <default>\n            <\/default>\n           <hot_disk>\n              <path>.\/hot\/<\/path>\n           <\/hot_disk>\n           <warm_disk>\n              <path>.\/warm\/<\/path>\n           <\/warm_disk>\n           <cold_disk>\n              <path>.\/cold\/<\/path>\n           <\/cold_disk>\n        <\/disks>\n        <policies>\n            <default>\n                <volumes>\n                    <default>\n                        <disk>default<\/disk>\n                    <\/default>\n                    <hot_volume>\n                        <disk>hot_disk<\/disk>\n                    <\/hot_volume>\n                    <warm_volume>\n                        <disk>warm_disk<\/disk>\n                    <\/warm_volume>\n                    <cold_volume>\n                        <disk>cold_disk<\/disk>\n                    <\/cold_volume>\n                <\/volumes>\n            <\/default>\n        <\/policies>\n    <\/storage_configuration>\n<\/clickhouse>\n```\n1. The configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks:\n```\nSELECT name, path, free_space, total_space\nFROM system.disks\n```\n```\n┌─name────────┬─path───────────┬───free_space─┬──total_space─┐\n│ cold_disk   │ .\/data\/cold\/   │ 179143311360 │ 494384795648 │\n│ default     │ .\/             │ 179143311360 │ 494384795648 │\n│ hot_disk    │ .\/data\/hot\/    │ 179143311360 │ 494384795648 │\n│ warm_disk   │ .\/data\/warm\/   │ 179143311360 │ 494384795648 │\n└─────────────┴────────────────┴──────────────┴──────────────┘\n```\n1. And...let's verify the volumes:\n```\nSELECT\n    volume_name,\n    disks\nFROM system.storage_policies\n```\n```\n┌─volume_name─┬─disks─────────┐\n│ default     │ ['default']   │\n│ hot_volume  │ ['hot_disk']  │\n│ warm_volume │ ['warm_disk'] │\n│ cold_volume │ ['cold_disk'] │\n└─────────────┴───────────────┘\n```\n1. Now we will add a `TTL` rule that moves the data between the hot, warm and cold volumes:\n```\nALTER TABLE my_table\n   MODIFY TTL\n      trade_date TO VOLUME 'hot_volume',\n      trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume',\n      trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume';\n```\n1. The new `TTL` rule should materialize, but you can force it to make sure:\n```\nALTER TABLE my_table\n    MATERIALIZE TTL\n```\n1. Verify your data has moved to its expected disks using the `system.parts` table:\n```\nUsing the system.parts table, view which disks the parts are on for the crypto_prices table:\n\nSELECT\n    name,\n    disk_name\nFROM system.parts\nWHERE (table = 'my_table') AND (active = 1)\n```\nThe response will look like:\n```\n┌─name────────┬─disk_name─┐\n│ all_1_3_1_5 │ warm_disk │\n│ all_2_2_0   │ hot_disk  │\n└─────────────┴───────────┘\n```","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

2 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.1 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/guides/developer/ttl
Last Crawled	2026-04-06 15:13:33 (2 days ago)
First Indexed	2025-02-21 00:01:39 (1 year ago)
HTTP Status Code	200
Meta Title	Manage data with TTL (Time-to-live) \| ClickHouse Docs
Meta Description	TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed.
Meta Canonical	null
Boilerpipe Text	Overview of TTL TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression "time-to-live" sounds like it only applies to deleting old data, TTL has several use cases: Removing old data: no surprise, you can delete rows or columns after a specified time interval Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot/warm/cold architecture Data rollup: rollup your older data into various useful aggregations and computations before deleting it Note TTL can be applied to entire tables or specific columns. TTL syntax The TTL clause can appear after a column definition and/or at the end of the table definition. Use the INTERVAL clause to define a length of time (which needs to be a Date or DateTime data type). For example, the following table has two columns with TTL clauses: CREATE TABLE example1 ( timestamp DateTime , x UInt32 TTL timestamp + INTERVAL 1 MONTH , y String TTL timestamp + INTERVAL 1 DAY , z String ) ENGINE = MergeTree ORDER BY tuple ( ) The x column has a time to live of 1 month from the timestamp column The y column has a time to live of 1 day from the timestamp column When the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem. Best practice When using table-level TTL to remove old rows, we recommend to partition your table by the date or month of the same time field used in your TTL expression. ClickHouse can drop entire partitions much more efficiently than deleting individual rows. When your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows. Choose your partition granularity based on your TTL period: For TTL of days/weeks: partition by day using toYYYYMMDD(date_field) For TTL of months/years: partition by month using toYYYYMM(date_field) or toStartOfMonth(date_field) Triggering TTL events The deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events: merge_with_ttl_timeout : the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours). merge_with_recompression_ttl_timeout : the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours). So by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently. Note Not a great solution (or one that we recommend you use frequently), but you can also force a merge using OPTIMIZE : OPTIMIZE TABLE example1 FINAL OPTIMIZE initializes an unscheduled merge of the parts of your table, and FINAL forces a reoptimization if your table is already a single part. Removing rows To remove entire rows from a table after a certain amount of time, define the TTL rule at the table level: CREATE TABLE customers ( timestamp DateTime , name String , balance Int32 , address String ) ENGINE = MergeTree ORDER BY timestamp TTL timestamp + INTERVAL 12 HOUR Additionally, it is possible to define a TTL rule based on the record's value. This is easily implemented by specifying a where condition. Multiple conditions are allowed: CREATE TABLE events ( ` event ` String , ` time ` DateTime , ` value ` UInt64 ) ENGINE = MergeTree ORDER BY ( event , time ) TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error' , time + INTERVAL 6 MONTH DELETE WHERE event = 'error' Removing columns Instead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the customers table and add a TTL for both columns to be 2 hours: ALTER TABLE customers MODIFY COLUMN balance Int32 TTL timestamp + INTERVAL 2 HOUR , MODIFY COLUMN address String TTL timestamp + INTERVAL 2 HOUR Implementing a rollup Suppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a GROUP BY clause to your TTL expression, along with some columns in your table to store the aggregated results. Suppose in the following hits table we want to delete old rows, but hang on to the sum and maximum of the hits columns before removing the rows. We will need a field to store those values in, and we will need to add a GROUP BY clause to the TTL clause that rolls up the sum and maximum: CREATE TABLE hits ( timestamp DateTime , id String , hits Int32 , max_hits Int32 DEFAULT hits , sum_hits Int64 DEFAULT hits ) ENGINE = MergeTree PRIMARY KEY ( id , toStartOfDay ( timestamp ) , timestamp ) TTL timestamp + INTERVAL 1 DAY GROUP BY id , toStartOfDay ( timestamp ) SET max_hits = max ( max_hits ) , sum_hits = sum ( sum_hits ) ; Some notes on the hits table: The GROUP BY columns in the TTL clause must be a prefix of the PRIMARY KEY , and we want to group our results by the start of the day. Therefore, toStartOfDay(timestamp) was added to the primary key We added two fields to store the aggregated results: max_hits and sum_hits Setting the default value of max_hits and sum_hits to hits is necessary for our logic to work, based on how the SET clause is defined Implementing a hot/warm/cold architecture Not supported in ClickHouse Cloud Note If you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud. A common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot/warm/cold architecture in ClickHouse using the TO DISK and TO VOLUME clauses of the TTL command. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.) The TO DISK and TO VOLUME options refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named my_system.xml (or any file name) that defines your disks, then define volumes that use your disks. Place the XML file in /etc/clickhouse-server/config.d/ to have the configuration applied to your system: < clickhouse > < storage_configuration > < disks > < default > </ default > < hot_disk > < path > ./hot/ </ path > </ hot_disk > < warm_disk > < path > ./warm/ </ path > </ warm_disk > < cold_disk > < path > ./cold/ </ path > </ cold_disk > </ disks > < policies > < default > < volumes > < default > < disk > default </ disk > </ default > < hot_volume > < disk > hot_disk </ disk > </ hot_volume > < warm_volume > < disk > warm_disk </ disk > </ warm_volume > < cold_volume > < disk > cold_disk </ disk > </ cold_volume > </ volumes > </ default > </ policies > </ storage_configuration > </ clickhouse > The configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks: SELECT name , path , free_space , total_space FROM system . disks ┌─name────────┬─path───────────┬───free_space─┬──total_space─┐ │ cold_disk │ ./data/cold/ │ 179143311360 │ 494384795648 │ │ default │ ./ │ 179143311360 │ 494384795648 │ │ hot_disk │ ./data/hot/ │ 179143311360 │ 494384795648 │ │ warm_disk │ ./data/warm/ │ 179143311360 │ 494384795648 │ └─────────────┴────────────────┴──────────────┴──────────────┘ And...let's verify the volumes: SELECT volume_name , disks FROM system . storage_policies ┌─volume_name─┬─disks─────────┐ │ default │ ['default'] │ │ hot_volume │ ['hot_disk'] │ │ warm_volume │ ['warm_disk'] │ │ cold_volume │ ['cold_disk'] │ └─────────────┴───────────────┘ Now we will add a TTL rule that moves the data between the hot, warm and cold volumes: ALTER TABLE my_table MODIFY TTL trade_date TO VOLUME 'hot_volume' , trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume' , trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume' ; The new TTL rule should materialize, but you can force it to make sure: ALTER TABLE my_table MATERIALIZE TTL Verify your data has moved to its expected disks using the system.parts table: Using the system . parts table , view which disks the parts are on for the crypto_prices table : SELECT name , disk_name FROM system . parts WHERE ( table = 'my_table' ) AND ( active = 1 ) The response will look like: ┌─name────────┬─disk_name─┐ │ all_1_3_1_5 │ warm_disk │ │ all_2_2_0 │ hot_disk │ └─────────────┴───────────┘
Markdown	[Skip to main content](https://clickhouse.com/docs/guides/developer/ttl#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/guides/developer/ttl) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/guides/developer/ttl) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/guides/developer/ttl) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/guides/developer/ttl) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/guides/developer/ttl) - [English](https://clickhouse.com/docs/guides/developer/ttl) - [日本語](https://clickhouse.com/docs/jp/guides/developer/ttl) - [中文](https://clickhouse.com/docs/zh/guides/developer/ttl) - [Русский](https://clickhouse.com/docs/ru/guides/developer/ttl) - [한국어](https://clickhouse.com/docs/ko/guides/developer/ttl) [Skip to main content](https://clickhouse.com/docs/guides/developer/ttl#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/guides/developer/ttl) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/guides/developer/ttl) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/guides/developer/ttl) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/guides/developer/ttl) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/guides/developer/ttl) - [English](https://clickhouse.com/docs/guides/developer/ttl) - [日本語](https://clickhouse.com/docs/jp/guides/developer/ttl) - [中文](https://clickhouse.com/docs/zh/guides/developer/ttl) - [Русский](https://clickhouse.com/docs/ru/guides/developer/ttl) - [한국어](https://clickhouse.com/docs/ko/guides/developer/ttl) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Core concepts](https://clickhouse.com/docs/managing-data/core-concepts) - [Table parts](https://clickhouse.com/docs/parts) - [Table partitions](https://clickhouse.com/docs/partitions) - [Part merges](https://clickhouse.com/docs/merges) - [Table shards and replicas](https://clickhouse.com/docs/shards) - [Primary indexes](https://clickhouse.com/docs/primary-indexes) - [Architecture overview](https://clickhouse.com/docs/academic_overview) - [Updating data](https://clickhouse.com/docs/updating-data) - [Working with updates](https://clickhouse.com/docs/updating-data/overview) - [Working with the ReplacingMergeTree engine](https://clickhouse.com/docs/guides/replacing-merge-tree) - [Deleting data](https://clickhouse.com/docs/managing-data/deleting-data/overview) - [Delete overview](https://clickhouse.com/docs/deletes/overview) - [Lightweight deletes](https://clickhouse.com/docs/guides/developer/lightweight-delete) - [Delete mutations](https://clickhouse.com/docs/managing-data/delete_mutations) - [Truncate table](https://clickhouse.com/docs/managing-data/truncate) - [Drop partition](https://clickhouse.com/docs/managing-data/drop_partition) - [Data modeling](https://clickhouse.com/docs/data-modeling/overview) - [Schema design](https://clickhouse.com/docs/data-modeling/schema-design) - [Dictionary](https://clickhouse.com/docs/guides/developer/ttl) - [Materialized views](https://clickhouse.com/docs/materialized-views) - [Projections](https://clickhouse.com/docs/guides/developer/ttl) - [Data compression](https://clickhouse.com/docs/data-compression/compression-in-clickhouse) - [Denormalizing Data](https://clickhouse.com/docs/data-modeling/denormalization) - [Backfilling Data](https://clickhouse.com/docs/data-modeling/backfilling) - [Advanced guides](https://clickhouse.com/docs/guides/developer/overview) - [Dynamic column selection](https://clickhouse.com/docs/guides/developer/dynamic-column-selection) - [Merge table function](https://clickhouse.com/docs/guides/developer/merge-table-function) - [Alternative query languages](https://clickhouse.com/docs/guides/developer/alternative-query-languages) - [Cascading materialized views](https://clickhouse.com/docs/guides/developer/cascading-materialized-views) - [Debugging memory issues](https://clickhouse.com/docs/guides/developer/debugging-memory-issues) - [Deduplicating inserts on retries](https://clickhouse.com/docs/guides/developer/deduplicating-inserts-on-retries) - [Deduplication strategies](https://clickhouse.com/docs/guides/developer/deduplication) - [Time series - gap fill](https://clickhouse.com/docs/guides/developer/time-series-filling-gaps) - [Transactional (ACID) support](https://clickhouse.com/docs/guides/developer/transactional) - [TTL (Time to Live)](https://clickhouse.com/docs/guides/developer/ttl) - [Understanding query execution with the analyzer](https://clickhouse.com/docs/guides/developer/understanding-query-execution-with-the-analyzer) - [Using JOINs in ClickHouse](https://clickhouse.com/docs/guides/joining-tables) - [Stored procedures & query parameters](https://clickhouse.com/docs/guides/developer/stored-procedures-and-prepared-statements) - [Performance and optimizations](https://clickhouse.com/docs/operations/overview) - [Query optimization](https://clickhouse.com/docs/optimize/query-optimization) - [Primary indexes](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes) - [Query parallelism](https://clickhouse.com/docs/optimize/query-parallelism) - [Partitioning key](https://clickhouse.com/docs/optimize/partitioning-key) - [Data skipping indexes](https://clickhouse.com/docs/optimize/skipping-indexes) - [PREWHERE optimization](https://clickhouse.com/docs/optimize/prewhere) - [Lazy materialization](https://clickhouse.com/docs/optimize/lazy-materialization) - [Bulk inserts](https://clickhouse.com/docs/optimize/bulk-inserts) - [Asynchronous inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts) - [Avoid mutations](https://clickhouse.com/docs/optimize/avoid-mutations) - [Avoid nullable columns](https://clickhouse.com/docs/optimize/avoid-nullable-columns) - [Avoid optimize final](https://clickhouse.com/docs/optimize/avoidoptimizefinal) - [Analyzer](https://clickhouse.com/docs/operations/analyzer) - [Query Profiling](https://clickhouse.com/docs/operations/optimizing-performance/sampling-query-profiler) - [Query cache](https://clickhouse.com/docs/operations/query-cache) - [Query condition cache](https://clickhouse.com/docs/operations/query-condition-cache) - [Userspace page cache](https://clickhouse.com/docs/operations/userspace-page-cache) - [Testing Hardware](https://clickhouse.com/docs/operations/performance-test) - [Advanced guides](https://clickhouse.com/docs/guides/developer/overview) - TTL (Time to Live) [Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/developer/ttl.md) # Manage data with TTL (time-to-live) ## Overview of TTL[](https://clickhouse.com/docs/guides/developer/ttl#overview-of-ttl "Direct link to Overview of TTL") TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression "time-to-live" sounds like it only applies to deleting old data, TTL has several use cases: - Removing old data: no surprise, you can delete rows or columns after a specified time interval - Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot/warm/cold architecture - Data rollup: rollup your older data into various useful aggregations and computations before deleting it Note TTL can be applied to entire tables or specific columns. ## TTL syntax[](https://clickhouse.com/docs/guides/developer/ttl#ttl-syntax "Direct link to TTL syntax") The `TTL` clause can appear after a column definition and/or at the end of the table definition. Use the `INTERVAL` clause to define a length of time (which needs to be a `Date` or `DateTime` data type). For example, the following table has two columns with `TTL` clauses: ``` CREATE TABLE example1 ( timestamp DateTime, x UInt32 TTL timestamp + INTERVAL 1 MONTH, y String TTL timestamp + INTERVAL 1 DAY, z String ) ENGINE = MergeTree ORDER BY tuple() ``` - The x column has a time to live of 1 month from the timestamp column - The y column has a time to live of 1 day from the timestamp column - When the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem. Note TTL rules can be altered or deleted. See the [Manipulations with Table TTL](https://clickhouse.com/docs/sql-reference/statements/alter/ttl) page for more details. Best practice When using table-level TTL to remove old rows, we recommend to partition your table by the date or month of the same time field used in your TTL expression. ClickHouse can drop entire partitions much more efficiently than deleting individual rows. When your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows. Choose your partition granularity based on your TTL period: - For TTL of days/weeks: partition by day using `toYYYYMMDD(date_field)` - For TTL of months/years: partition by month using `toYYYYMM(date_field)` or `toStartOfMonth(date_field)` ## Triggering TTL events[](https://clickhouse.com/docs/guides/developer/ttl#triggering-ttl-events "Direct link to Triggering TTL events") The deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events: - `merge_with_ttl_timeout`: the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours). - `merge_with_recompression_ttl_timeout`: the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours). So by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently. Note Not a great solution (or one that we recommend you use frequently), but you can also force a merge using `OPTIMIZE`: ``` OPTIMIZE TABLE example1 FINAL ``` `OPTIMIZE` initializes an unscheduled merge of the parts of your table, and `FINAL` forces a reoptimization if your table is already a single part. ## Removing rows[](https://clickhouse.com/docs/guides/developer/ttl#removing-rows "Direct link to Removing rows") To remove entire rows from a table after a certain amount of time, define the TTL rule at the table level: ``` CREATE TABLE customers ( timestamp DateTime, name String, balance Int32, address String ) ENGINE = MergeTree ORDER BY timestamp TTL timestamp + INTERVAL 12 HOUR ``` Additionally, it is possible to define a TTL rule based on the record's value. This is easily implemented by specifying a where condition. Multiple conditions are allowed: ``` CREATE TABLE events ( `event` String, `time` DateTime, `value` UInt64 ) ENGINE = MergeTree ORDER BY (event, time) TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error', time + INTERVAL 6 MONTH DELETE WHERE event = 'error' ``` ## Removing columns[](https://clickhouse.com/docs/guides/developer/ttl#removing-columns "Direct link to Removing columns") Instead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the `customers` table and add a TTL for both columns to be 2 hours: ``` ALTER TABLE customers MODIFY COLUMN balance Int32 TTL timestamp + INTERVAL 2 HOUR, MODIFY COLUMN address String TTL timestamp + INTERVAL 2 HOUR ``` ## Implementing a rollup[](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-rollup "Direct link to Implementing a rollup") Suppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a `GROUP BY` clause to your `TTL` expression, along with some columns in your table to store the aggregated results. Suppose in the following `hits` table we want to delete old rows, but hang on to the sum and maximum of the `hits` columns before removing the rows. We will need a field to store those values in, and we will need to add a `GROUP BY` clause to the `TTL` clause that rolls up the sum and maximum: ``` CREATE TABLE hits ( timestamp DateTime, id String, hits Int32, max_hits Int32 DEFAULT hits, sum_hits Int64 DEFAULT hits ) ENGINE = MergeTree PRIMARY KEY (id, toStartOfDay(timestamp), timestamp) TTL timestamp + INTERVAL 1 DAY GROUP BY id, toStartOfDay(timestamp) SET max_hits = max(max_hits), sum_hits = sum(sum_hits); ``` Some notes on the `hits` table: - The `GROUP BY` columns in the `TTL` clause must be a prefix of the `PRIMARY KEY`, and we want to group our results by the start of the day. Therefore, `toStartOfDay(timestamp)` was added to the primary key - We added two fields to store the aggregated results: `max_hits` and `sum_hits` - Setting the default value of `max_hits` and `sum_hits` to `hits` is necessary for our logic to work, based on how the `SET` clause is defined ## Implementing a hot/warm/cold architecture[](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-hotwarmcold-architecture "Direct link to Implementing a hot/warm/cold architecture") Not supported in ClickHouse Cloud Note If you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud. A common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot/warm/cold architecture in ClickHouse using the `TO DISK` and `TO VOLUME` clauses of the `TTL` command. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.) 1. The `TO DISK` and `TO VOLUME` options refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named `my_system.xml` (or any file name) that defines your disks, then define volumes that use your disks. Place the XML file in `/etc/clickhouse-server/config.d/` to have the configuration applied to your system: ``` <clickhouse> <storage_configuration> <disks> <default> </default> <hot_disk> <path>./hot/</path> </hot_disk> <warm_disk> <path>./warm/</path> </warm_disk> <cold_disk> <path>./cold/</path> </cold_disk> </disks> <policies> <default> <volumes> <default> <disk>default</disk> </default> <hot_volume> <disk>hot_disk</disk> </hot_volume> <warm_volume> <disk>warm_disk</disk> </warm_volume> <cold_volume> <disk>cold_disk</disk> </cold_volume> </volumes> </default> </policies> </storage_configuration> </clickhouse> ``` 1. The configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks: ``` SELECT name, path, free_space, total_space FROM system.disks ``` ``` ┌─name────────┬─path───────────┬───free_space─┬──total_space─┐ │ cold_disk │ ./data/cold/ │ 179143311360 │ 494384795648 │ │ default │ ./ │ 179143311360 │ 494384795648 │ │ hot_disk │ ./data/hot/ │ 179143311360 │ 494384795648 │ │ warm_disk │ ./data/warm/ │ 179143311360 │ 494384795648 │ └─────────────┴────────────────┴──────────────┴──────────────┘ ``` 1. And...let's verify the volumes: ``` SELECT volume_name, disks FROM system.storage_policies ``` ``` ┌─volume_name─┬─disks─────────┐ │ default │ ['default'] │ │ hot_volume │ ['hot_disk'] │ │ warm_volume │ ['warm_disk'] │ │ cold_volume │ ['cold_disk'] │ └─────────────┴───────────────┘ ``` 1. Now we will add a `TTL` rule that moves the data between the hot, warm and cold volumes: ``` ALTER TABLE my_table MODIFY TTL trade_date TO VOLUME 'hot_volume', trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume', trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume'; ``` 1. The new `TTL` rule should materialize, but you can force it to make sure: ``` ALTER TABLE my_table MATERIALIZE TTL ``` 1. Verify your data has moved to its expected disks using the `system.parts` table: ``` Using the system.parts table, view which disks the parts are on for the crypto_prices table: SELECT name, disk_name FROM system.parts WHERE (table = 'my_table') AND (active = 1) ``` The response will look like: ``` ┌─name────────┬─disk_name─┐ │ all_1_3_1_5 │ warm_disk │ │ all_2_2_0 │ hot_disk │ └─────────────┴───────────┘ ``` [Previous Transactional (ACID) support](https://clickhouse.com/docs/guides/developer/transactional) [Next Understanding query execution with the analyzer](https://clickhouse.com/docs/guides/developer/understanding-query-execution-with-the-analyzer) - [Overview of TTL](https://clickhouse.com/docs/guides/developer/ttl#overview-of-ttl) - [TTL syntax](https://clickhouse.com/docs/guides/developer/ttl#ttl-syntax) - [Triggering TTL events](https://clickhouse.com/docs/guides/developer/ttl#triggering-ttl-events) - [Removing rows](https://clickhouse.com/docs/guides/developer/ttl#removing-rows) - [Removing columns](https://clickhouse.com/docs/guides/developer/ttl#removing-columns) - [Implementing a rollup](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-rollup) - [Implementing a hot/warm/cold architecture](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-hotwarmcold-architecture) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=dfe9d7c8-e366-4bd8-a024-158376ba2b5f&pagePath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&origPath=%2Fdocs%2Fguides%2Fdeveloper%2Fttl&utm_ga=GA1.1.230363323.1775488415) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Core concepts▼ - Updating data▼ - Deleting data▼ - Data modeling▼ - Advanced guides▼ - [Dynamic column selection](https://clickhouse.com/docs/guides/developer/dynamic-column-selection) - [Merge table function](https://clickhouse.com/docs/guides/developer/merge-table-function) - [Alternative query languages](https://clickhouse.com/docs/guides/developer/alternative-query-languages) - [Cascading materialized views](https://clickhouse.com/docs/guides/developer/cascading-materialized-views) - [Debugging memory issues](https://clickhouse.com/docs/guides/developer/debugging-memory-issues) - [Deduplicating inserts on retries](https://clickhouse.com/docs/guides/developer/deduplicating-inserts-on-retries) - [Deduplication strategies](https://clickhouse.com/docs/guides/developer/deduplication) - [Time series - gap fill](https://clickhouse.com/docs/guides/developer/time-series-filling-gaps) - [Transactional (ACID) support](https://clickhouse.com/docs/guides/developer/transactional) - [TTL (Time to Live)](https://clickhouse.com/docs/guides/developer/ttl) - [Understanding query execution with the analyzer](https://clickhouse.com/docs/guides/developer/understanding-query-execution-with-the-analyzer) - [Using JOINs in ClickHouse](https://clickhouse.com/docs/guides/joining-tables) - [Stored procedures & query parameters](https://clickhouse.com/docs/guides/developer/stored-procedures-and-prepared-statements) - Performance and optimizations▼
Readable Markdown	## Overview of TTL[](https://clickhouse.com/docs/guides/developer/ttl#overview-of-ttl "Direct link to Overview of TTL") TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression "time-to-live" sounds like it only applies to deleting old data, TTL has several use cases: - Removing old data: no surprise, you can delete rows or columns after a specified time interval - Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot/warm/cold architecture - Data rollup: rollup your older data into various useful aggregations and computations before deleting it Note TTL can be applied to entire tables or specific columns. ## TTL syntax[](https://clickhouse.com/docs/guides/developer/ttl#ttl-syntax "Direct link to TTL syntax") The `TTL` clause can appear after a column definition and/or at the end of the table definition. Use the `INTERVAL` clause to define a length of time (which needs to be a `Date` or `DateTime` data type). For example, the following table has two columns with `TTL` clauses: ``` CREATE TABLE example1 ( timestamp DateTime, x UInt32 TTL timestamp + INTERVAL 1 MONTH, y String TTL timestamp + INTERVAL 1 DAY, z String ) ENGINE = MergeTree ORDER BY tuple() ``` - The x column has a time to live of 1 month from the timestamp column - The y column has a time to live of 1 day from the timestamp column - When the interval lapses, the column expires. ClickHouse replaces the column value with the default value of its data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in the filesystem. Best practice When using table-level TTL to remove old rows, we recommend to partition your table by the date or month of the same time field used in your TTL expression. ClickHouse can drop entire partitions much more efficiently than deleting individual rows. When your partition key aligns with your TTL expression, ClickHouse can drop whole partitions at once when they expire, rather than rewriting data parts to remove expired rows. Choose your partition granularity based on your TTL period: - For TTL of days/weeks: partition by day using `toYYYYMMDD(date_field)` - For TTL of months/years: partition by month using `toYYYYMM(date_field)` or `toStartOfMonth(date_field)` ## Triggering TTL events[](https://clickhouse.com/docs/guides/developer/ttl#triggering-ttl-events "Direct link to Triggering TTL events") The deleting or aggregating of expired rows isn't immediate - it only occurs during table merges. If you have a table that's not actively merging (for whatever reason), there are two settings that trigger TTL events: - `merge_with_ttl_timeout`: the minimum delay in seconds before repeating a merge with delete TTL. The default is 14400 seconds (4 hours). - `merge_with_recompression_ttl_timeout`: the minimum delay in seconds before repeating a merge with recompression TTL (rules that roll up data before deleting). Default value: 14400 seconds (4 hours). So by default, your TTL rules will be applied to your table at least once every 4 hours. Just modify the settings above if you need your TTL rules applied more frequently. Note Not a great solution (or one that we recommend you use frequently), but you can also force a merge using `OPTIMIZE`: ``` OPTIMIZE TABLE example1 FINAL ``` `OPTIMIZE` initializes an unscheduled merge of the parts of your table, and `FINAL` forces a reoptimization if your table is already a single part. ## Removing rows[](https://clickhouse.com/docs/guides/developer/ttl#removing-rows "Direct link to Removing rows") To remove entire rows from a table after a certain amount of time, define the TTL rule at the table level: ``` CREATE TABLE customers ( timestamp DateTime, name String, balance Int32, address String ) ENGINE = MergeTree ORDER BY timestamp TTL timestamp + INTERVAL 12 HOUR ``` Additionally, it is possible to define a TTL rule based on the record's value. This is easily implemented by specifying a where condition. Multiple conditions are allowed: ``` CREATE TABLE events ( `event` String, `time` DateTime, `value` UInt64 ) ENGINE = MergeTree ORDER BY (event, time) TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error', time + INTERVAL 6 MONTH DELETE WHERE event = 'error' ``` ## Removing columns[](https://clickhouse.com/docs/guides/developer/ttl#removing-columns "Direct link to Removing columns") Instead of deleting the entire row, suppose you want just the balance and address columns to expire. Let's modify the `customers` table and add a TTL for both columns to be 2 hours: ``` ALTER TABLE customers MODIFY COLUMN balance Int32 TTL timestamp + INTERVAL 2 HOUR, MODIFY COLUMN address String TTL timestamp + INTERVAL 2 HOUR ``` ## Implementing a rollup[](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-rollup "Direct link to Implementing a rollup") Suppose we want to delete rows after a certain amount of time but hang on to some of the data for reporting purposes. We don't want all the details - just a few aggregated results of historical data. This can be implemented by adding a `GROUP BY` clause to your `TTL` expression, along with some columns in your table to store the aggregated results. Suppose in the following `hits` table we want to delete old rows, but hang on to the sum and maximum of the `hits` columns before removing the rows. We will need a field to store those values in, and we will need to add a `GROUP BY` clause to the `TTL` clause that rolls up the sum and maximum: ``` CREATE TABLE hits ( timestamp DateTime, id String, hits Int32, max_hits Int32 DEFAULT hits, sum_hits Int64 DEFAULT hits ) ENGINE = MergeTree PRIMARY KEY (id, toStartOfDay(timestamp), timestamp) TTL timestamp + INTERVAL 1 DAY GROUP BY id, toStartOfDay(timestamp) SET max_hits = max(max_hits), sum_hits = sum(sum_hits); ``` Some notes on the `hits` table: - The `GROUP BY` columns in the `TTL` clause must be a prefix of the `PRIMARY KEY`, and we want to group our results by the start of the day. Therefore, `toStartOfDay(timestamp)` was added to the primary key - We added two fields to store the aggregated results: `max_hits` and `sum_hits` - Setting the default value of `max_hits` and `sum_hits` to `hits` is necessary for our logic to work, based on how the `SET` clause is defined ## Implementing a hot/warm/cold architecture[](https://clickhouse.com/docs/guides/developer/ttl#implementing-a-hotwarmcold-architecture "Direct link to Implementing a hot/warm/cold architecture") Not supported in ClickHouse Cloud Note If you're using ClickHouse Cloud, the steps in the lesson aren't applicable. You don't need to worry about moving old data around in ClickHouse Cloud. A common practice when working with large amounts of data is to move that data around as it gets older. Here are the steps for implementing a hot/warm/cold architecture in ClickHouse using the `TO DISK` and `TO VOLUME` clauses of the `TTL` command. (By the way, it doesn't have to be a hot and cold thing - you can use TTL to move data around for whatever use case you have.) 1. The `TO DISK` and `TO VOLUME` options refer to the names of disks or volumes defined in your ClickHouse configuration files. Create a new file named `my_system.xml` (or any file name) that defines your disks, then define volumes that use your disks. Place the XML file in `/etc/clickhouse-server/config.d/` to have the configuration applied to your system: ``` <clickhouse> <storage_configuration> <disks> <default> </default> <hot_disk> <path>./hot/</path> </hot_disk> <warm_disk> <path>./warm/</path> </warm_disk> <cold_disk> <path>./cold/</path> </cold_disk> </disks> <policies> <default> <volumes> <default> <disk>default</disk> </default> <hot_volume> <disk>hot_disk</disk> </hot_volume> <warm_volume> <disk>warm_disk</disk> </warm_volume> <cold_volume> <disk>cold_disk</disk> </cold_volume> </volumes> </default> </policies> </storage_configuration> </clickhouse> ``` 1. The configuration above refers to three disks that point to folders that ClickHouse can read from and write to. Volumes can contain one or more disks - we defined a volume for each of the three disks. Let's view the disks: ``` SELECT name, path, free_space, total_space FROM system.disks ``` ``` ┌─name────────┬─path───────────┬───free_space─┬──total_space─┐ │ cold_disk │ ./data/cold/ │ 179143311360 │ 494384795648 │ │ default │ ./ │ 179143311360 │ 494384795648 │ │ hot_disk │ ./data/hot/ │ 179143311360 │ 494384795648 │ │ warm_disk │ ./data/warm/ │ 179143311360 │ 494384795648 │ └─────────────┴────────────────┴──────────────┴──────────────┘ ``` 1. And...let's verify the volumes: ``` SELECT volume_name, disks FROM system.storage_policies ``` ``` ┌─volume_name─┬─disks─────────┐ │ default │ ['default'] │ │ hot_volume │ ['hot_disk'] │ │ warm_volume │ ['warm_disk'] │ │ cold_volume │ ['cold_disk'] │ └─────────────┴───────────────┘ ``` 1. Now we will add a `TTL` rule that moves the data between the hot, warm and cold volumes: ``` ALTER TABLE my_table MODIFY TTL trade_date TO VOLUME 'hot_volume', trade_date + INTERVAL 2 YEAR TO VOLUME 'warm_volume', trade_date + INTERVAL 4 YEAR TO VOLUME 'cold_volume'; ``` 1. The new `TTL` rule should materialize, but you can force it to make sure: ``` ALTER TABLE my_table MATERIALIZE TTL ``` 1. Verify your data has moved to its expected disks using the `system.parts` table: ``` Using the system.parts table, view which disks the parts are on for the crypto_prices table: SELECT name, disk_name FROM system.parts WHERE (table = 'my_table') AND (active = 1) ``` The response will look like: ``` ┌─name────────┬─disk_name─┐ │ all_1_3_1_5 │ warm_disk │ │ all_2_2_0 │ hot_disk │ └─────────────┴───────────┘ ```
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/guides/developer/ttl s443