When using drop or truncate, is the table directly deleted or marked as an old version?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: drop、truncate时是直接删除表了还是标记成旧版本了？

| username: xiaohetao

When using drop or truncate, is the table directly deleted or is it similar to delete, marked as an old version?

2
| username: BraveChen | Original post link

In the old version, as long as it hasn’t been garbage collected (GC), you can retrieve the mistakenly deleted table.

3
| username: 啦啦啦啦啦 | Original post link

Both are marked for deletion, and can be recovered before GC.

4
| username: h5n1 | Original post link

Check the official documentation: FLASHBACK TABLE | PingCAP 文档中心

5
| username: xiaohetao | Original post link

Is truncate marking each piece of data in the table for deletion one by one?

6
| username: xiaohetao | Original post link

How is truncate marked for deletion? One by one?

7
| username: h5n1 | Original post link

Working Principle

When TiDB deletes a table, it actually only deletes the table’s metadata and writes a record to the mysql.gc_delete_range table for the data (row data and index data) that needs to be deleted. The GC Worker in the background of TiDB will periodically fetch keys from the mysql.gc_delete_range table that exceed the GC lifetime and delete them.

Therefore, FLASHBACK TABLE only needs to restore the table’s metadata and delete the corresponding row record in the mysql.gc_delete_range table before the GC Worker deletes the table data. The table’s metadata can be restored using TiDB’s snapshot read. For detailed information on snapshot read, refer to the Read Historical Data documentation. Below is the workflow of FLASHBACK TABLE t TO t1:

  1. Find the first DDL job of type drop table or truncate table for the table named t from the DDL History job. If not found, return an error.
  2. Check the start time of the DDL job to see if it is before tikv_gc_safe_point. If it is before tikv_gc_safe_point, it means the table deleted by DROP or TRUNCATE has already been cleaned up by GC, and an error is returned.
  3. Use the start time of the DDL job as a snapshot to read historical data and retrieve the table’s metadata.
  4. Delete the GC tasks related to table t from mysql.gc_delete_range.
  5. Modify the name in the table’s metadata to t1 and create a new table with this metadata. Note: Only the table name is changed here, but the table ID remains the same as the previously deleted table t.

It can be observed that from the deletion of table t to the restoration of table t to t1 via FLASHBACK, operations are always performed on the table’s metadata, and the user data of the table is never modified. The restored table t1 has the same table ID as the previously deleted table t, so table t1 can read the user data of table t.

8
| username: 啦啦啦啦啦 | Original post link

No, truncate is drop+create.

9
| username: kkpeter | Original post link

When TiDB deletes a table, it actually only deletes the table’s metadata and writes a record to the mysql.gc_delete_range table for the data that needs to be deleted (row data and index data). The GC Worker in the TiDB background will periodically retrieve keys from the mysql.gc_delete_range table that exceed the GC lifetime range for deletion.

10
| username: xiaohetao | Original post link

I see you are very familiar with the official documentation! :+1::+1::+1:

11
| username: xiaohetao | Original post link

Thank you, everyone, for answering my questions and clearing up my doubts. :handshake::+1:

12
| username: cs58_dba | Original post link

Reference Data Deletion Guidelines:
5.1 When deleting all data in a table, use TRUNCATE instead of DELETE.
5.2 If deleting a large amount of data, it can easily cause performance jitter across the entire cluster. It is recommended to split the SQL (delete less, submit more).

13
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.