How to Clean Up Data After Using TRUNCATE or DELETE to Delete a Table

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

Original topic: 使用truncate或者delete删除表后数据怎么清理

| username: TiDBer_Xy7fsN7j

After using truncate or delete to remove a table, I found that the system file data is still there. How can I configure it to delete the data files as well?

| username: 逍遥_猫 | Original post link

When the GC time arrives, the GC worker will perform the cleanup.

| username: 普罗米修斯 | Original post link

The default GC is 10 minutes, and the cluster will uniformly clean up and delete after 10 minutes.

| username: 像风一样的男子 | Original post link

After truncate and GC, the space will be automatically released, but the space occupied by data deleted using delete will not be released.

| username: xingzhenxiang | Original post link

Mark, waiting for GC to reclaim and clean up the marked data.

| username: 春风十里 | Original post link

Do we have to passively wait for GC? Is there any proactive manual command similar to Oracle’s move or a manual shrink command to take the initiative?

| username: TiDBer_小阿飞 | Original post link

MVCC + GC + SST probably can’t manually delete underlying data completely.

| username: zhanggame1 | Original post link

After truncating, disk space is released. With delete, space is not released. Cleanup is executed after the GC time has passed.

| username: zhanggame1 | Original post link

No, the shortest GC time can be set to 10 minutes, and the default is also 10 minutes.

| username: 路在何chu | Original post link

Setting such a short time is a bit risky.

| username: zhanggame1 | Original post link

TiDB puts MVCC data and normal data together, and setting the time too long affects performance.

| username: TiDBer_小阿飞 | Original post link

Experts, both transaction deletion and SQL deletion should go through GC, right?

| username: dba远航 | Original post link

The database will automatically clean up.

| username: tidb菜鸟一只 | Original post link

Use shell to compact only the oltp.sbtest8 table (replace with your table name), adding -c write -d kv

mysql -uroot -pXXX -hxxx -PXXX information_schema -e "select region_id from tikv_region_status where db_name='oltp' and table_name='sbtest8'" > region_list
cat region_list | while read line
  tiup ctl:v6.5.1 tikv --host xxxx:20160 compact -r $line -d kv -c write --threads 1 --bottommost force
  tiup ctl:v6.5.1 tikv --host xxx:20160 compact -r $line -d kv -c default --threads 1 --bottommost force
| username: 春风十里 | Original post link

Okay, thank you, this is what I wanted to see.

| username: 春风十里 | Original post link

I checked the documentation again and saw that Tiflash can be simply operated with ALTER TABLE … COMPACT, but it is not yet supported for TiKV.


TiDB storage nodes automatically initiate data compaction in the background. During data compaction, the physical data in the table is rewritten, such as cleaning up deleted data, merging multiple versions of data, etc., to achieve higher access performance and reduce disk space usage. The ALTER TABLE ... COMPACT statement can be used to immediately compact the specified table without waiting for the background trigger.

Executing this statement will not block the execution of existing SQL statements or the use of TiDB features, including transactions, DDL, GC, etc., nor will it change the data content accessed through SQL statements. Executing this statement will consume a certain amount of IO and CPU resources, so please choose an appropriate time to execute it, such as during idle resource periods, to avoid negative impacts on business.

The statement will wait for all replicas in the table to complete data compaction before finishing and returning. During execution, you can safely interrupt the data compaction process of this table through the KILL statement. Interrupting will not compromise data consistency or cause data loss, nor will it affect subsequent re-initiations or automatic background data compaction triggers.

Currently, this statement only supports data compaction for TiFlash and does not support data compaction for TiKV.

| username: tidb菜鸟一只 | Original post link

Correct, this statement currently only applies to TiFlash.

| username: kelvin | Original post link

The database will automatically clean up, and the parameters can be set.