Can TiDB release disk space by deleting data conditionally, such as DELETE FROM table_x WHERE id IN (xxxx,xx)?

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

Original topic: Tidb按条件删除数据能释放磁盘空间吗,如DELETE FROM table_x WHERE id IN (xxxx,xx)

| username: TiDBer_yZ7iRfl0

Can deleting data based on conditions in TiDB release disk space, such as DELETE FROM table_x WHERE id IN (xxxx,xx)?

| username: yiduoyunQ | Original post link

| username: Miracle | Original post link

Delete will not release disk space; you can manually compact to release it.

| username: TiDBer_yZ7iRfl0 | Original post link

Will this operation block? The data volume is quite large.

| username: Miracle | Original post link

Manual compaction will occupy a large amount of disk space and is quite slow, so it is recommended to perform it during off-peak business hours.

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

In TiDB, using DELETE, TRUNCATE, and DROP statements to delete data will not immediately release space. For TRUNCATE and DROP operations, after reaching TiDB’s GC (garbage collection) time (default is 10 minutes), TiDB’s GC mechanism will delete the data and release the space. For DELETE operations, TiDB’s GC mechanism will delete the data but will not immediately release the space; instead, the space will be released during subsequent compaction.

| username: zhanggame1 | Original post link

Yes, after deleting, it will first go through GC, and then automatically compact after a day or two. Manual compaction is very effective but consumes a lot of resources, so use it cautiously in production environments.

| username: zhanggame1 | Original post link

Based on my testing experience, do not manually compact the database running the business; both CPU and IO can be fully utilized. After deleting the data, let the database compact slowly over a few days.

| username: 这里介绍不了我 | Original post link

The image you provided is not visible. Please provide the text you need translated.

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

If space is not urgent, wait for the GC to complete and then automatically compact…

| username: TiDBer_yZ7iRfl0 | Original post link

Okay, thank you. So it means I can delete the data first and let it release itself, right?

| username: TiDBer_yZ7iRfl0 | Original post link

There is still 20% left, but there is too much useless data. I don’t want to expand anymore, I want to delete it directly.

| username: zhanggame1 | Original post link

When deleting the remaining 20%, be careful. Deleting in TiDB involves writing data, so don’t delete too much in a short period of time to avoid filling up the hard drive. After deleting, observe the disk space for a few days to see the release situation. If you want to know the space usage of a specific table in detail, you can check the INFORMATION_SCHEMA.TIKV_REGION_STATUS view, which contains t.APPROXIMATE_SIZE and t.APPROXIMATE_KEYS.

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

If there’s still 20% left, it’s quite risky. It’s recommended to scale up first. Deleting data is slow, and you need to delete a lot of data before automatic compaction can free up some space.

| username: TiDBer_yZ7iRfl0 | Original post link

The table structure to be cleared occupies about 80% of the used space :rofl:

| username: TiDBer_yZ7iRfl0 | Original post link

To delete data from a table, the current data occupies about 80% of the used space :rofl:, which means it occupies about 70% of the entire disk.

| username: zhanggame1 | Original post link

Then take it slowly, delete a portion each day, and continue deleting for a few days.

| username: xfworld | Original post link

After deletion, if there is no GC, not only will the space not be released, but it may also affect query efficiency.

Higher versions have fixed issues related to MVCC versions. If you need to perform delete operations, you should consider this issue.

| username: TiDBer_yZ7iRfl0 | Original post link

It’s not easy to upgrade the version now, as it’s a 24-hour operation. The best approach is to first scale out the nodes, and then proceed with the deletion after the expansion.

| username: TiDBer_yZ7iRfl0 | Original post link

I’ll try it first, delete a part and see, while getting ready to apply for resource expansion.