{"id":25353,"date":"2025-02-26T15:39:05","date_gmt":"2025-02-26T23:39:05","guid":{"rendered":"https:\/\/www.pingcap.com\/?p=25353"},"modified":"2025-02-27T09:37:15","modified_gmt":"2025-02-27T17:37:15","slug":"how-tidb-ttl-efficiently-handles-expired-data","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/","title":{"rendered":"Time\u2019s Up! How TiDB Efficiently Handles Expired Data"},"content":{"rendered":"\n<p>Managing large-scale data efficiently is a critical challenge for modern databases, especially when dealing with time-sensitive data that can quickly become outdated. Starting from <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/release-6.5.0\">TiDB 6.5<\/a> and becoming generally available in <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/release-7.0.0\">TiDB 7.0<\/a>, <strong><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/time-to-live\">TTL (Time To Live)<\/a><\/strong> automates the deletion of expired data, offering a powerful, customizable solution for maintaining data freshness while minimizing operational overhead.<\/p>\n\n\n\n<p>By allowing users to configure expiration rules and run periodic deletion jobs, TiDB TTL not only simplifies compliance with data retention policies but also optimizes storage usage and enhances system performance. By configuring TTL attributes on a table, <a href=\"https:\/\/www.pingcap.com\/tidb-self-managed\/\">TiDB<\/a> periodically checks for expired records and removes them without manual intervention. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE t (\n  id INT UNSIGNED PRIMARY KEY, \n  created_at DATETIME\n) \nTTL = created_at + INTERVAL 10 HOUR \nTTL_JOB_INTERVAL = '1h';<\/code><\/pre>\n\n\n\n<p>This configuration ensures that records are deleted every hour when <code>created_at + INTERVAL 10 HOUR<\/code> is less than the current time, helping to maintain data freshness and optimize storage usage.<\/p>\n\n\n\n<p>In this blog, we&#8217;ll explore the journey behind the design and implementation of TiDB&#8217;s TTL capability. We&#8217;ll dive into:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>How to configure TTL-related system variables for better performance and reduced impact on online workloads.<\/li>\n\n\n\n<li>The thought process and trade-offs behind implementing TTL functionality in a <a href=\"https:\/\/www.pingcap.com\/blog\/why-distributed-sql-databases-elevate-modern-app-dev\/\">distributed SQL database<\/a>.<\/li>\n\n\n\n<li>Key technical insights to help users fully leverage the capabilities of TTL for managing expired data efficiently.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Designing_TiDB_TTL_Evaluating_Methods_and_Trade-offs\"><\/span>Designing TiDB TTL: Evaluating Methods and Trade-offs<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>When implementing the TTL feature in TiDB, the team evaluated different methods for automatically cleaning up expired data. Here\u2019s a breakdown of the two main approaches considered, and why TiDB ultimately chose the <strong>periodic SQL-based deletion method<\/strong>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Option 1: Using RocksDB Compaction Filter<\/h3>\n\n\n\n<p>This method deletes expired data during LSM Tree compaction.  <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/tikv-overview\">TiKV\u2019s<\/a> RawKV TTL already uses this functionality. However, applying this approach to TiDB presented several challenges:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Data Format Changes: <\/strong>Each key-value pair would need an expiration time, requiring extensive read\/write operations during schema updates (DDL).<\/li>\n\n\n\n<li><strong>Consistency Issues: <\/strong>Keeping table data and indexes consistent is complex in a distributed system, especially when indexes and data might reside on different TiKV nodes.<\/li>\n\n\n\n<li><strong>Conflicts with Transactions: <\/strong>TiDB\u2019s transaction model (MVCC) and locking mechanisms would be hard to maintain. For instance:\n<ul class=\"wp-block-list\">\n<li>Removing newer versions could inadvertently expose older, outdated versions.<\/li>\n\n\n\n<li>Prematurely deleting locked data, causing errors.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>While it\u2019s possible to mitigate some of these issues (e.g., delaying deletion or filtering expired data during reads), this approach introduces too many risks and would require invasive changes to TiDB\u2019s architecture.<\/p>\n\n\n\n<p><strong>Verdict:<\/strong> This method contains high complexity, risk of breaking correctness, and significant impact on other features.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Option 2: Periodic Deletion with SQL Statements<\/h3>\n\n\n\n<p>This approach uses SQL queries to periodically identify and delete expired data. While simpler and safer, it also comes with its own trade-offs:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Advantages:<\/strong>\n<ul class=\"wp-block-list\">\n<li><strong>Preserves Correctness:<\/strong> SQL-based deletion ensures table data and indexes remain consistent, aligning naturally with TiDB\u2019s transaction model.<\/li>\n\n\n\n<li><strong>Isolated Design:<\/strong> This method is less invasive, keeping TTL functionality separate from other features.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Challenges:<\/strong>\n<ul class=\"wp-block-list\">\n<li><strong>Resource Usage:<\/strong> Full table scans are required if no indexes are present, which can be resource-intensive.<\/li>\n\n\n\n<li><strong>Indexing Limitations:<\/strong> Adding indexes on time-based columns (e.g., <code>created_at<\/code>) is generally discouraged in distributed databases, as it can create write hotspots and degrade performance.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>Despite these challenges, TiDB focused on optimizing for scenarios without indexes, using full table scans for simplicity and compatibility.<\/p>\n\n\n\n<p><strong>Verdict:<\/strong> This method balances correctness, simplicity, and compatibility with TiDB\u2019s architecture.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Why TiDB Chose Periodic Deletion<\/h3>\n\n\n\n<p>The periodic SQL-based deletion approach offered the best trade-off between feasibility, correctness, and minimal disruption to existing features. It avoids the complexity of deeply modifying TiDB\u2019s architecture while ensuring reliable cleanup of expired data. Though resource-intensive in some scenarios, the design prioritizes safety and maintainability, making it the ideal solution for TiDB\u2019s TTL feature.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_TiDB_TTL_Works\"><\/span>How TiDB TTL Works<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>TiDB\u2019s TTL feature automates the deletion of expired data by periodically triggering cleanup jobs and executing them efficiently. This functionality involves two key processes: scheduling TTL jobs and running them within defined operational constraints.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Scheduling TiDB TTL Jobs<\/h3>\n\n\n\n<p>TTL jobs are triggered at regular intervals, which can be configured to balance resource consumption and the need for timely cleanup.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Default Behavior: TTL jobs run every 24 hours for each table.<\/li>\n\n\n\n<li>Customization: The <code>TTL_JOB_INTERVAL<\/code> property allows adjustments:\n<ul class=\"wp-block-list\">\n<li>Longer intervals reduce resource usage for non-critical cleanup.<\/li>\n\n\n\n<li>Shorter intervals ensure quicker removal of expired data.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>TiDB tracks the status of TTL jobs in the <code>mysql.tidb_ttl_table_status<\/code> system table, which includes key columns such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>last_job_start_time<\/code>: Records the start time of the most recent TTL job.<\/li>\n\n\n\n<li><code>current_job_id<\/code>: Indicates whether a TTL job is currently running (empty if no job is active).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Cron-Like Triggering:<\/h4>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Every 10 seconds, TiDB nodes check <code>mysql.tidb_ttl_table_status <\/code>to determine if any tables require a new TTL job.\n<ol class=\"wp-block-list\">\n<li>A new job is triggered if:\n<ul class=\"wp-block-list\">\n<li>No current TTL job is running (<code>current_job_id<\/code> is empty).<\/li>\n\n\n\n<li>The interval since the last job exceeds the configured value (<code>last_job_start_time + TTL_JOB_INTERVAL &lt; NOW()<\/code>).<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<\/li>\n\n\n\n<li>Once triggered, the TiDB node updates the <code>current_* <\/code>fields in a transaction, ensuring that only one node starts the job and preventing redundant executions.<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Understanding the Status Table<\/h4>\n\n\n\n<p>TiDB records metadata about TTL jobs in the <code>mysql.tidb_ttl_table_status<\/code> table. Here\u2019s what some key fields represent:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Column<\/strong><\/td><td><strong>Description<\/strong><\/td><\/tr><tr><td><code>table_id<\/code><\/td><td>Unique identifier for the table the TTL job applies to.<\/td><\/tr><tr><td><code>last_job_id<\/code><\/td><td>UUID of the last completed TTL job for this table.<\/td><\/tr><tr><td><code>last_job_start_time<\/code><\/td><td>Timestamp when the most recent TTL job started.<\/td><\/tr><tr><td><code>last_job_finish_time<\/code><\/td><td>Timestamp when the most recent TTL job finished.<\/td><\/tr><tr><td><code>last_job_summary<\/code><\/td><td>JSON summary of the results, including rows scanned, successfully deleted, and errors (if any).<\/td><\/tr><tr><td><code>current_job_id<\/code><\/td><td>UUID of the currently running TTL job (empty if no job is active).<\/td><\/tr><tr><td><code>current_job_owner_id<\/code><\/td><td>Identifier of the TiDB node currently handling this TTL job.<\/td><\/tr><tr><td><code>current_job_owner_addr<\/code><\/td><td>Address of the TiDB node managing the current job.<\/td><\/tr><tr><td><code>current_job_start_time<\/code><\/td><td>Timestamp when the current TTL job began.<\/td><\/tr><tr><td><code>current_job_state<\/code><\/td><td>State of the active TTL job, if any (e.g., running, paused).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Example Output:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>TABLE mysql.tidb_ttl_table_status LIMIT 1\\G\n\n*************************** 1. row ***************************\n                      table_id: 85\n               parent_table_id: 85\n              table_statistics: NULL\n                   last_job_id: 0b4a6d50-3041-4664-9516-5525ee6d9f90\n           last_job_start_time: 2023-02-15 20:43:46\n          last_job_finish_time: 2023-02-15 20:44:46\n           last_job_ttl_expire: 2023-02-15 19:43:46\n              last_job_summary: {\"total_rows\":4369519,\"success_rows\":4369519,\"error_rows\":0,\"total_scan_task\":64,\"scheduled_scan_task\":64,\"finished_scan_task\":64}\n                current_job_id: NULL\n          current_job_owner_id: NULL\n        current_job_owner_addr: NULL\n     current_job_owner_hb_time: NULL\n        current_job_start_time: NULL\n        current_job_ttl_expire: NULL\n             current_job_state: NULL\n            current_job_status: NULL\ncurrent_job_status_update_time: NULL<\/code><\/pre>\n\n\n\n<p>This table helps coordinate TTL jobs across TiDB nodes and ensures that only one node is managing a given job at a time.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Running TiDB TTL Jobs During Low-Traffic Hours<\/h3>\n\n\n\n<p>Database workloads often have off-peak times when system load is lower (e.g., nighttime). To take advantage of this, TiDB lets you define time windows during which these jobs can run.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Time Window Settings:<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>tidb_ttl_job_schedule_window_start_time<\/code>: Defines the earliest time TTL jobs can run each day.<\/li>\n\n\n\n<li><code>tidb_ttl_job_schedule_window_end_time<\/code>: Sets the latest time TTL jobs can run.<\/li>\n<\/ul>\n\n\n\n<p>By default, TTL jobs run between 00:00 UTC and 23:59 UTC. Outside this window:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ongoing TTL jobs are canceled.<\/li>\n\n\n\n<li>New jobs will not be triggered until the window reopens.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">How It Works:<\/h4>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Before initiating a TTL job, TiDB checks if the current time falls within the configured window.<\/li>\n\n\n\n<li>If outside the window, all ongoing TTL jobs stop, and no new jobs begin.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Summary<\/h3>\n\n\n\n<p>By combining cron-like scheduling and configurable time windows, TiDB\u2019s TTL feature ensures that expired data cleanup is both efficient and minimally disruptive. These mechanisms provide flexibility for balancing timely deletion with operational stability.<\/p>\n\n\n\n<p>Next, we will examine the process of identifying and deleting expired data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Deleting_Expired_Data\"><\/span>Deleting Expired Data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Removing outdated data from a table in a database can be as simple as executing a single <code>DELETE<\/code> statement. For instance, consider the following table configuration:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE t (\n  id INT UNSIGNED PRIMARY KEY,\n  created_at DATETIME\n) TTL = created_at + INTERVAL 10 HOUR TTL_JOB_INTERVAL = '1h';<\/code><\/pre>\n\n\n\n<p>To delete records where <code>created_at + INTERVAL 10 HOUR<\/code> is earlier than a specific timestamp, you could run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELETE FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456';<\/code><\/pre>\n\n\n\n<p>However, as the size of the table grows, this direct approach presents several challenges:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Transaction Size<\/strong>: A single DELETE statement might create a transaction that is too large or takes too long to execute. If it fails, the entire operation rolls back.<\/li>\n\n\n\n<li><strong>Resource Impact<\/strong>: Deletion operations consume resources, and their speed can be hard to limit, potentially impacting other online services.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Optimizing Deletion with Batching<\/h3>\n\n\n\n<p>To address these issues, TiDB splits the deletion process into two steps:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Querying Expired Data:<\/strong> Identify the records that meet the expiration criteria.<\/li>\n\n\n\n<li><strong>Executing Deletions:<\/strong> Delete the expired records in smaller, manageable batches.<\/li>\n<\/ol>\n\n\n\n<p>Here\u2019s how this works in practice:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Step 1: Query in Batches<\/h4>\n\n\n\n<p>Instead of scanning the entire table in one operation, <code>SELECT<\/code> queries retrieve records in smaller chunks using a <code>LIMIT<\/code> clause. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' ORDER BY id ASC LIMIT 500;\nSELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' AND id &gt; x ORDER BY id ASC LIMIT 500;\nSELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' AND id &gt; y ORDER BY id ASC LIMIT 500;<\/code><\/pre>\n\n\n\n<p>The batch size (e.g., 500) can be adjusted using the <code>tidb_ttl_scan_batch_size<\/code> variable. This approach ensures only a small portion of data is queried at a time, reducing the load on the system.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Step 2: Delete in Batches<\/h4>\n\n\n\n<p>Similarly, deletions are performed in chunks using <code>IN<\/code> clauses to limit the number of rows affected in each operation. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELETE FROM t WHERE id IN (id1, id2, ..., id100);\nDELETE FROM t WHERE id IN (id101, id102, ..., id200);<\/code><\/pre>\n\n\n\n<p>The size of these batches can be controlled through the <code>tidb_ttl_delete_batch_size<\/code> variable.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Why Batching Works<\/h4>\n\n\n\n<p>By splitting the <code>SELECT<\/code> and <code>DELETE<\/code> operations into batches:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Each transaction is smaller and less prone to failure.<\/li>\n\n\n\n<li>System resources are used more efficiently, reducing the risk of performance degradation for other workloads.<\/li>\n\n\n\n<li>Fine-tuned control over query and deletion speed is possible through configuration.<\/li>\n<\/ul>\n\n\n\n<p>This method ensures that even large tables with millions of records process effectively without overwhelming the database. Further details on managing flow control and resource usage will be discussed later.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Local_TiDB_TTL_Task_Scheduling\"><\/span>Local TiDB TTL Task Scheduling<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To efficiently process expired data, TiDB breaks TTL jobs into smaller subtasks that can run in parallel across the cluster. This approach ensures balanced workloads and smooth execution, even in large-scale deployments.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Breaking Down the Tasks into Subtasks<\/h3>\n\n\n\n<p>TiDB divides a table into regions, assigning each region to a subtask. This splitting ensures the workload distributes evenly across the system. For example, if a table has <code>id<\/code> values ranging from 1 to 10,000, a subtask might process only values between 128 and 999. The query for this subtask includes conditions to focus on that range:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT id \nFROM t \nWHERE created_at + INTERVAL 10 HOUR &lt; CURRENT_TIMESTAMP \nAND id &gt;= 128 AND id &lt; 999 \nORDER BY id ASC LIMIT 500;<\/code><\/pre>\n\n\n\n<p>This targeted approach prevents redundant scanning and keeps processing efficient.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Running Subtasks in Parallel<\/h3>\n\n\n\n<p>TiDB uses two types of workers to process subtasks:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Scan Workers<\/strong> query expired data in batches.<\/li>\n\n\n\n<li><strong>Delete Workers<\/strong> remove the expired records based on the scan results.<\/li>\n<\/ul>\n\n\n\n<p>These workers operate in parallel, with Scan Workers feeding results to Delete Workers. Multiple subtasks can run simultaneously across nodes, ensuring optimal resource usage.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Distributed Execution and Resilience<\/h3>\n\n\n\n<p>To maximize cluster resources, TiDB distributes subtasks across nodes. Subtasks are recorded in the <code>mysql.tidb_ttl_task<\/code> table, and idle nodes claim tasks to execute them.<\/p>\n\n\n\n<p>If a node goes offline, other nodes detect the issue and take over the unfinished work, ensuring all subtasks finish reliably. This fault-tolerant design keeps the TTL process resilient and efficient.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Key Benefits<\/h3>\n\n\n\n<p>By splitting and distributing TTL tasks, TiDB achieves:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Improved Performance:<\/strong> Subtasks run in parallel, reducing execution time.<\/li>\n\n\n\n<li><strong>Reliable Execution:<\/strong> Built-in fault tolerance ensures no unfinished tasks.<\/li>\n\n\n\n<li><strong>Efficient Resource Use:<\/strong>  Nodes balance tasks, preventing bottlenecks.<\/li>\n<\/ul>\n\n\n\n<p>This streamlined approach ensures TTL jobs process efficiently with minimal disruption to other operations.<\/p>\n\n\n\n<p>Although such <code>SELECT<\/code> query statements are already running in batches, later ones depend on the results of previous ones and cannot be executed simultaneously. Since deletion tasks themselves don&#8217;t depend on the order of <code>id<\/code>, we might as well shard the primary keys.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Splitting Subtasks<\/h3>\n\n\n\n<p>To distribute query data volume as evenly as possible, TiDB divides the table&#8217;s regions into <code>min(max(64, tikv count), region count)<\/code> parts. Assuming each region has similar data volume, each subtask&#8217;s data volume would also be similar. The <code>min(..., region count)<\/code> part is easy to understand, as too few regions wouldn&#8217;t be enough to divide, with each subtask only getting one. <code>max(128, tikv count)<\/code> is to further improve TTL performance when the cluster is large enough.<\/p>\n\n\n\n<p>Each subtask will contain one or more continuous regions, and the beginning and end of these regions become the scanning range for this subtask. Still using table t from above as an example, if we get a subtask with id range <code>[128, 999)<\/code>, this subtask will add the condition <code>id &gt;= 128 AND id &lt; 999<\/code> when running the SELECT statements described above. It becomes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' AND id &gt;= 128 AND id &lt; 999 ORDER BY id ASC LIMIT 500\nSELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' AND id &gt; x AND id &gt;= 128 AND id &lt; 999 ORDER BY id ASC LIMIT 500\nSELECT id FROM t WHERE created_at + INTERVAL 10 HOUR &lt; '2024-12-13 12:52:01.123456' AND id &gt; y AND id &gt;= 128 AND id &lt; 999 ORDER BY id ASC LIMIT 500\n...<\/code><\/pre>\n\n\n\n<p>Multiple batches within a subtask still execute sequentially, but multiple subtasks can execute simultaneously without interfering with each other.<\/p>\n\n\n\n<p>Mapping region beginnings and endings to SQL syntax elements (like integers, strings) isn&#8217;t easy. For example, region boundaries might not be valid utf8 strings. Currently in TiDB, there are still many limitations on this kind of splitting, so please refer to the <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/time-to-live#limitations\">documentation<\/a> for usage restrictions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Scan Worker and Delete Worker<\/h3>\n\n\n\n<p>Since multiple subtasks execute simultaneously, TiDB abstracts Scan Worker and Delete Worker to run these TTL subtasks. Each subtask needs to occupy a Scan Worker to run SELECT statements, sending query results to Delete Worker through a channel.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104946\/Screenshot-2025-02-21-at-1.49.26%E2%80%AFPM.png\" alt=\"How TiDB abstracts Scan Worker and Delete Worker to run TTL subtasks\" class=\"wp-image-25361\"\/><\/figure>\n<\/div>\n\n\n<p>This way, TTL execution speed might be limited by several factors:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Too few Scan Workers, with many subtasks waiting for execution.<\/li>\n\n\n\n<li>Too few Delete Workers, with lots of scanned expired data waiting to be deleted. This will also block Scan Workers.<\/li>\n\n\n\n<li>Very few subtasks, can only be deleted sequentially and slowly.<\/li>\n<\/ol>\n\n\n\n<p>TiDB adjusts the number of Scan Workers and Delete Workers through variables <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code> and <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_worker_count-new-in-v650\">tidb_ttl_delete_worker_count<\/a><\/code>. They decrease to limit resources consumed by TTL functionality, or increase to speed up TTL querying and deleting expired data.<\/p>\n\n\n\n<p>Maintaining them at reasonable values isn&#8217;t easy. Because the distribution of expired data and table width differs for each table, these two variables need to be flexibly adjusted based on actual conditions. If aiming to improve TTL speed, we can gradually adjust these two variables according to the following principles.<\/p>\n\n\n\n<p>Observe the TTL section in the below TiDB Grafana monitoring panel. This records the proportion of Scan Workers and Delete Workers in different stages. When we find Scan Workers mostly in dispatch state (sending scanned expired data to Delete Workers) while Delete Workers rarely in idle state, as shown below, we can increase <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_worker_count-new-in-v650\">tidb_ttl_delete_worker_count<\/a><\/code>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"229\" src=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21105022\/image-9-1024x229.png\" alt=\"TiDB TTL showing the proportion of Scan Workers and Delete Workers in different stages.\" class=\"wp-image-25362\" srcset=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21105022\/image-9-1024x229.png 1024w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105022\/image-9-300x67.png 300w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105022\/image-9-768x172.png 768w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105022\/image-9.png 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>When Scan Workers are rarely in dispatch states while Delete Workers are frequently in idle states, as shown below, it indicates Scan Workers are quite busy, and we can increase <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"114\" src=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21105036\/image-10-1024x114.png\" alt=\"\" class=\"wp-image-25363\" srcset=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21105036\/image-10-1024x114.png 1024w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105036\/image-10-300x33.png 300w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105036\/image-10-768x85.png 768w, https:\/\/static.pingcap.com\/files\/2025\/02\/21105036\/image-10.png 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>If both Scan Workers and Delete Workers are in <code>idle<\/code> state while TTL tasks run slowly, check if the corresponding TTL table meets the conditions described in the documentation for splitting into multiple subtasks.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Flow Control for <code>DELETE<\/code> Statements<\/h3>\n\n\n\n<p>In TTL, deletion (as a special type of write) consumes resources particularly noticeably. So a mechanism limits TTL functionality&#8217;s deletion speed to avoid affecting other business operations. TiDB provides a system variable <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_rate_limit-new-in-v650\">tidb_ttl_delete_rate_limit<\/a><\/code> to limit the number of DELETE statements executed per second by TTL functionality on a TiDB node.<\/p>\n\n\n\n<p>This is implemented through <code><a href=\"https:\/\/pkg.go.dev\/golang.org\/x\/time\/rate#Limiter\">rate.Limiter<\/a><\/code> . When <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_rate_limit-new-in-v650\">tidb_ttl_delete_rate_limit<\/a><\/code> is non-zero, the TTL module calls <code><a href=\"https:\/\/pkg.go.dev\/golang.org\/x\/time\/rate#Limiter.Wait\">rate.Limiter.Wait()<\/a><\/code> before executing each DELETE statement to ensure current execution rate won&#8217;t exceed <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_rate_limit-new-in-v650\">tidb_ttl_delete_rate_limit<\/a><\/code><code>\/s<\/code>.<\/p>\n\n\n\n<p>This limit is per TiDB node, regardless of multiple or single TTL tasks. If multiple TTL tasks need to run on a TiDB node, their combined deletion rate won&#8217;t exceed <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_rate_limit-new-in-v650\">tidb_ttl_delete_rate_limit<\/a><\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Multi-Node_TiDB_TTL_Task_Scheduling\"><\/span>Multi-Node TiDB TTL Task Scheduling<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The previous content already covers most of TTL implementation in TiDB 6.5.<\/p>\n\n\n\n<p>Ideally, a TTL job can split into multiple subtasks running simultaneously. If all 64 subtasks execute on the same TiDB, it won&#8217;t fully utilize the entire cluster&#8217;s resources. So some improvements were made in TiDB 6.6 to distribute TTL subtasks across different TiDB nodes, getting Scan Workers and Delete Workers on each TiDB node working. This section will introduce the design of this improvement.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Subtask Scheduling<\/h3>\n\n\n\n<p>TiDB uses the <code>mysql.tidb_ttl_task<\/code> table to record subtasks split from TTL jobs. Its table structure is:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE tidb_ttl_task (\n  job_id <strong>varchar<\/strong>(64) NOT NULL,\n  table_id <strong>bigint<\/strong>(64) NOT NULL,\n  scan_id <strong>int<\/strong>(11) NOT NULL,\n  scan_range_start blob <strong>DEFAULT<\/strong> NULL,\n  scan_range_end blob <strong>DEFAULT<\/strong> NULL,\n  expire_time <strong>timestamp<\/strong> NOT NULL,\n  owner_id <strong>varchar<\/strong>(64) <strong>DEFAULT<\/strong> NULL,\n  owner_addr <strong>varchar<\/strong>(64) <strong>DEFAULT<\/strong> NULL,\n  owner_hb_time <strong>timestamp<\/strong> NULL <strong>DEFAULT<\/strong> NULL,\n  status <strong>varchar<\/strong>(64) <strong>DEFAULT<\/strong> 'waiting',\n  status_update_time <strong>timestamp<\/strong> NULL <strong>DEFAULT<\/strong> NULL,\n  state <strong>text<\/strong><strong>DEFAULT<\/strong> NULL,\n  created_time <strong>timestamp<\/strong> NOT NULL,\n  <strong>PRIMARY<\/strong><strong> KEY<\/strong> (job_id,scan_id) <em>\/*T!&#91;clustered_index] NONCLUSTERED *\/<\/em>,\n  KEY created_time (created_time) \n) ENGINE=InnoDB <strong>DEFAULT<\/strong> CHARSET=utf8mb4 <strong>COLLATE<\/strong>=utf8mb4_bin<\/code><\/pre>\n\n\n\n<p>After a TiDB node starts a TTL job, instead of directly starting local execution, it splits into multiple subtasks and fills them into the <code>tidb_ttl_task<\/code> table.<\/p>\n\n\n\n<p>Similar to TTL job scheduling, each TiDB node polls this table every minute. If the current node has idle Scan Workers and there exist subtasks with empty <code>owner_id<\/code>, it will try to update that subtask&#8217;s <code>owner_id<\/code> and let the idle Scan Worker run this subtask. This way, subtasks published by any node might be discovered and run by any node. Upon completion, the Owner updates the subtask&#8217;s status to &#8216;finished&#8217; to indicate a completed current subtask.<\/p>\n\n\n\n<p>After not needing to care about TTL&#8217;s specific execution, a TTL job&#8217;s Owner has very little to do. It just needs to check its managed TTL jobs every 10 seconds to see if all corresponding subtasks are completed. If all are completed, then this TTL job successfully ends.<\/p>\n\n\n\n<p>Note that since variable <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code> controls the number of Scan Workers on each node, the cluster&#8217;s total Scan Worker count will be <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code><code> * TiDB Count<\/code>. This means if the cluster has many TiDB nodes, the number of simultaneously running TTL subtasks could be very large, consuming many resources.<\/p>\n\n\n\n<p>Since <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code> can only limit simultaneously running subtasks to integer multiples of TiDB Count, it&#8217;s inconvenient for fine-tuning. TiDB provides variable <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_running_tasks-new-in-v700\">tidb_ttl_running_tasks<\/a><\/code> to further limit the number of simultaneously running subtasks. It limits how many TTL subtasks can run simultaneously across the entire cluster. The range is <code>-1<\/code> or <code>[1,256]<\/code>. -1 means the same as cluster&#8217;s TiKV node count.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Fault Tolerance<\/h3>\n\n\n\n<p>According to above design, each TTL job and subtask clearly belongs to a specific TiDB node. So if a TiDB node goes offline for various reasons, jobs and subtasks belonging to it would be unable to progress. To avoid this situation, TiDB introduced heartbeat mechanism for TTL jobs and subtasks.<\/p>\n\n\n\n<p>Observant readers might have noticed that both <code>mysql.tidb_ttl_table_status<\/code> and <code>mysql.tidb_ttl_task<\/code> tables have a column for updating heartbeat time. For <code>mysql.tidb_ttl_table_status<\/code> this column is <code>current_job_owner_hb_time<\/code>, while for <code>mysql.tidb_ttl_task<\/code> it&#8217;s <code>owner_hb_time<\/code>.<\/p>\n\n\n\n<p>Every 10 seconds, each TiDB node updates heartbeat time for TTL jobs it&#8217;s responsible for, and every 1 minute updates heartbeat time for TTL subtasks it&#8217;s responsible for. If the current node goes offline, heartbeat time for corresponding rows will stop updating. When other nodes find twice the specified interval has passed since heartbeat time, they can know the corresponding TTL task and subtask&#8217;s owner is in abnormal state.<\/p>\n\n\n\n<p>TiDB nodes discovering this will try to set themselves as the job&#8217;s owner and start regularly checking for all completed subtasks; TiDB nodes discovering this and having idle Scan Workers will try to set themselves as the subtask&#8217;s owner and start querying and deleting.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"TiDB_TTL_Configuration_Items\"><\/span>TiDB TTL Configuration Items<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The previous section introduced TiDB TTL&#8217;s overall design and implementation from scratch, covering all TTL-related configuration items along the way. Readers should now have a clear understanding of these variables&#8217; origins and functions. But numerous configuration items designed to adapt to different needs can still be overwhelming. Here&#8217;s a summary:<\/p>\n\n\n\n<p>Per-table Configuration:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TTL_JOB_INTERVAL: Controls interval between start times of two TTL job triggers for a single table.<\/li>\n<\/ul>\n\n\n\n<p>TTL-related Global Variables:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_batch_size-new-in-v650\">tidb_ttl_delete_batch_size<\/a><\/code> and <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_batch_size-new-in-v650\">tidb_ttl_scan_batch_size<\/a><\/code>: Adjust data volume for batch running SELECT and DELETE statements. Refer to &#8220;Deleting Expired Data&#8221; section above. Usually there is no need to adjust.<\/li>\n\n\n\n<li><code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_scan_worker_count-new-in-v650\">tidb_ttl_scan_worker_count<\/a><\/code> and <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_worker_count-new-in-v650\">tidb_ttl_delete_worker_count<\/a><\/code>: Adjust the number of Scan Workers and Delete Workers on each TiDB node. Adjust according to &#8220;Scan Worker and Delete Worker&#8221; section above or documentation.<\/li>\n\n\n\n<li><code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_running_tasks-new-in-v700\">tidb_ttl_running_tasks<\/a><\/code>: Limits the number of simultaneously running TTL subtasks.  Decreases when limiting TTL resource usage; increases when improving TTL performance and many restricted subtasks from scheduling to Scan Workers.<\/li>\n\n\n\n<li><code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_delete_rate_limit-new-in-v650\">tidb_ttl_delete_rate_limit<\/a><\/code>: Limits total TTL deletion rate on a single TiDB node.<\/li>\n\n\n\n<li><code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_job_schedule_window_start_time-new-in-v650\">tidb_ttl_job_schedule_window_start_time<\/a><\/code> and <code><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/system-variables#tidb_ttl_job_schedule_window_end_time-new-in-v650\">tidb_ttl_job_schedule_window_end_time<\/a><\/code>: Limit the time window during which TTL can run in a day.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"TiDB_TTL_Best_Practices\"><\/span>TiDB TTL Best Practices<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>After understanding how TiDB TTL functionality works, we can naturally summarize these best practices. Following these rules will enable better use of TTL functionality:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>For tables using clustered index, try to use integer types or strings with collation as binary \/ utf8mb4_bin \/ utf8mb4_0900_bin. Otherwise, subtasks cannot be split.<\/li>\n\n\n\n<li>Set reasonable <code>TTL_JOB_INTERVAL<\/code> based on data expiration time. The ratio between the data expiration time and <code>TTL_JOB_INTERVAL<\/code> shouldn&#8217;t be too large, otherwise expired data proportion in each TTL task would be very small but still needs full table scan wasting resources.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The current TiDB implementation of TTL still has some shortcomings, like single <code>SELECT<\/code> statement execution time quickly increasing when expired data proportion is extremely low, as it needs to scan more data (even full table) to gather 500 expired records; only tables meeting specific conditions can split subtasks (you can refer to the documentation for <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/time-to-live#limitations\">limitations<\/a>). <\/p>\n\n\n\n<p>As TiDB&#8217;s TTL functionality continuously improves, we look forward to hearing from users like you how we can make it even better. Please feel free to drop us a line with your feedback on\u00a0<a href=\"https:\/\/twitter.com\/PingCAP\" target=\"_blank\" rel=\"noreferrer noopener\">Twitter<\/a>,\u00a0<a href=\"https:\/\/www.linkedin.com\/company\/pingcap\/mycompany\/\" target=\"_blank\" rel=\"noreferrer noopener\">LinkedIn<\/a>, or through our\u00a0<a href=\"https:\/\/slack.tidb.io\/invite?team=tidb-community&amp;channel=everyone&amp;ref=pingcap&amp;__hstc=86493575.58dcb926dd62ed60a20a8d0550a5a38f.1721653672633.1740604540169.1740611387994.671&amp;__hssc=86493575.2.1740611387994&amp;__hsfp=3850742519\" target=\"_blank\" rel=\"noreferrer noopener\">Slack Channel<\/a>.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Managing large-scale data efficiently is a critical challenge for modern databases, especially when dealing with time-sensitive data that can quickly become outdated. Starting from TiDB 6.5 and becoming generally available in TiDB 7.0, TTL (Time To Live) automates the deletion of expired data, offering a powerful, customizable solution for maintaining data freshness while minimizing operational [&hellip;]<\/p>\n","protected":false},"author":296,"featured_media":25355,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ub_ctt_via":"","footnotes":""},"categories":[6],"tags":[378,147,377,111,376],"class_list":["post-25353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-engineering","tag-data-freshness","tag-distributed-sql","tag-distributed-systems","tag-tidb","tag-ttl"],"acf":[],"featured_image_src":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png","author_info":{"display_name":"Keao Yang","author_link":"https:\/\/www.pingcap.com\/ko\/blog\/author\/kyang\/"},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>TiDB TTL: How to Efficiently Manage Expired Data<\/title>\n<meta name=\"description\" content=\"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TiDB TTL: How to Efficiently Manage Expired Data\" \/>\n<meta property=\"og:description\" content=\"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:published_time\" content=\"2025-02-26T23:39:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-02-27T17:37:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104151\/tidb_1200x627-3.png\" \/>\n\t<meta property=\"og:image:width\" content=\"2400\" \/>\n\t<meta property=\"og:image:height\" content=\"1254\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Keao Yang\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:image\" content=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104208\/tidb_twitter_1600x900-4.png\" \/>\n<meta name=\"twitter:creator\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Keao Yang\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"20\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\"},\"author\":{\"name\":\"Keao Yang\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/fb8136b7ef0082a5b476b39bf20a6fc2\"},\"headline\":\"Time\u2019s Up! How TiDB Efficiently Handles Expired Data\",\"datePublished\":\"2025-02-26T23:39:05+00:00\",\"dateModified\":\"2025-02-27T17:37:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\"},\"wordCount\":3250,\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png\",\"keywords\":[\"Data Freshness\",\"Distributed SQL\",\"Distributed Systems\",\"TiDB\",\"TTL\"],\"articleSection\":[\"Engineering\"],\"inLanguage\":\"ko-KR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\",\"url\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\",\"name\":\"TiDB TTL: How to Efficiently Manage Expired Data\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png\",\"datePublished\":\"2025-02-26T23:39:05+00:00\",\"dateModified\":\"2025-02-27T17:37:15+00:00\",\"description\":\"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage\",\"url\":\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png\",\"width\":3600,\"height\":1200},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Time\u2019s Up! How TiDB Efficiently Handles Expired Data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/fb8136b7ef0082a5b476b39bf20a6fc2\",\"name\":\"Keao Yang\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"caption\":\"Keao Yang\"},\"description\":\"TiDB Compute Engineer\",\"url\":\"https:\/\/www.pingcap.com\/ko\/blog\/author\/kyang\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"TiDB TTL: How to Efficiently Manage Expired Data","description":"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/","og_locale":"ko_KR","og_type":"article","og_title":"TiDB TTL: How to Efficiently Manage Expired Data","og_description":"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.","og_url":"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_published_time":"2025-02-26T23:39:05+00:00","article_modified_time":"2025-02-27T17:37:15+00:00","og_image":[{"width":2400,"height":1254,"url":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104151\/tidb_1200x627-3.png","type":"image\/png"}],"author":"Keao Yang","twitter_card":"summary_large_image","twitter_image":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104208\/tidb_twitter_1600x900-4.png","twitter_creator":"@PingCAP","twitter_site":"@PingCAP","twitter_misc":{"Written by":"Keao Yang","Est. reading time":"20\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#article","isPartOf":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/"},"author":{"name":"Keao Yang","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/fb8136b7ef0082a5b476b39bf20a6fc2"},"headline":"Time\u2019s Up! How TiDB Efficiently Handles Expired Data","datePublished":"2025-02-26T23:39:05+00:00","dateModified":"2025-02-27T17:37:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/"},"wordCount":3250,"publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png","keywords":["Data Freshness","Distributed SQL","Distributed Systems","TiDB","TTL"],"articleSection":["Engineering"],"inLanguage":"ko-KR"},{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/","url":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/","name":"TiDB TTL: How to Efficiently Manage Expired Data","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png","datePublished":"2025-02-26T23:39:05+00:00","dateModified":"2025-02-27T17:37:15+00:00","description":"Explore how TiDB TTL (Time to Live) works so you can achieve better performance while reducing the impact on online workloads.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/"]}]},{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#primaryimage","url":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png","width":3600,"height":1200},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Time\u2019s Up! How TiDB Efficiently Handles Expired Data"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]},{"@type":"Person","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/fb8136b7ef0082a5b476b39bf20a6fc2","name":"Keao Yang","image":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/","url":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","contentUrl":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","caption":"Keao Yang"},"description":"TiDB Compute Engineer","url":"https:\/\/www.pingcap.com\/ko\/blog\/author\/kyang\/"}]}},"grav_blocks":false,"card_markup":"<a class=\"card-resource bg-white\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/how-tidb-ttl-efficiently-handles-expired-data\/\"><div class=\"card-resource__image-container\"><img class=\"card-resource__image\" alt=\"tidb_feature_1800x600 (1)\" src=\"https:\/\/static.pingcap.com\/files\/2025\/02\/21104121\/tidb_feature_1800x600-1-3.png\" loading=\"lazy\" width=3600 height=1200 \/><\/div><div class=\"card-resource__content-container\"><div class=\"card-resource__content-head\"><div class=\"card-resource__category\">Engineering<\/div><\/div><h5 class=\"card-resource__title\">Time\u2019s Up! How TiDB Efficiently Handles Expired Data<\/h5><\/div><\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/25353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/296"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/comments?post=25353"}],"version-history":[{"count":24,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/25353\/revisions"}],"predecessor-version":[{"id":25414,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/25353\/revisions\/25414"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media\/25355"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=25353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/categories?post=25353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/tags?post=25353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}