Discussion on Physical Deletion in TiDB Development Standards

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

Original topic: TiDB开发规范之物理删除讨论

| username: asd80703406

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.5.4
[Reproduction Path]
[Resource Configuration]
[Question] I saw an article about TiDB development guidelines, which mentioned not to perform physical deletion. Instead, it suggested using the method of setting update t1 set is_del = 1 + crontab to achieve deletion. So, there are some points I don’t understand.

  1. What exactly is physical deletion? My understanding is that after delete, the deletion is achieved through gc + compaction, which results in physical deletion.
  2. What is the principle behind adding is_del during table design to optimize the impact of delete on business? Is this conclusion correct? If there are indeed many delete business scenarios in my business, how should I design the table? Partitioned table is one option. For tables that cannot be partitioned, are there any other experiences to share?
| username: zhanggame1 | Original post link

This specification has no reference value.
What you need to consider is why you want to delete the data, whether deleting the data has no impact, and whether it has no value for future use.

| username: DBAER | Original post link

Logical deletion involves tagging a field in the table to indicate whether the row has been deleted.
Physical deletion involves using the delete statement.

It depends on the business design. For simplicity, physical deletion is used.

| username: zhaokede | Original post link

Directly deleting a file like this is physical deletion, right?

| username: forever | Original post link

Delete is also considered a physical deletion. The deletion you’re talking about is database destruction. :grin:

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

The meaning of this development specification is that, for example, if you have a user table, user deactivation only sets the is_del field to 1 instead of directly deleting the user. This has nothing to do with the type of database; it is purely a development specification.

| username: asd80703406 | Original post link

Is there any reference value in starting with the scan principles of reading and writing from TiDB?

| username: asd80703406 | Original post link

I remember the principle of “delete from t1 where xx;” is to load all the content to be deleted into the memory of the TiDB server. The principle of update in terms of memory is still unknown.
So from the perspective of memory consumption, is update is_del = 1 more lightweight than delete?
But in terms of MVCC scan keys, is there also theoretical support?

| username: zhanggame1 | Original post link

From the tests, delete should be faster than update. From the underlying implementation of TiDB, both delete and update involve inserting a KV data.

| username: asd80703406 | Original post link

What about resource consumption?
Delete should be more costly, right? Update should just be k_new-version = new_value. During the operation, it just takes the column into memory for updating.

For delete, I read an article before that said the entire row is taken into memory…

| username: zhanggame1 | Original post link

Think about it, doesn’t an update also need to fetch the data into memory, modify the content, and then insert the new data back in?

| username: YuchongXU | Original post link

Just delete it according to business requirements.

| username: chris-zhang | Original post link

Should it be physical deletion or physical deletion, based on actual business needs? Isn’t this specification taking things out of context without considering the scenario?

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

The underlying layer of TiDB is key-value. Deletion is essentially adding a new piece of data, which is the original key plus a deletion marker. Update is also adding a new piece of data, which is the original key plus the modified value (including all column values). At this point, since the corresponding key has an updated value, the original data becomes obsolete and will be garbage collected (GC) when your GC time is reached. From a memory consumption perspective, delete might be slightly better since the deletion marker is smaller, whereas the updated value is larger, but the difference is minimal. In terms of MVCC scan keys, it is essentially the same. Before the useless data is GC’d, you will scan the previous useless data and then filter it out.

| username: zhang_2023 | Original post link

The specification you are looking at is somewhat non-standard.

| username: TiDBer_rvITcue9 | Original post link

The specification you’re looking at is somewhat non-standard.

| username: asd80703406 | Original post link

Thank you, master.
I will take my time to understand this passage. I need to keep learning the principles!

| username: asd80703406 | Original post link

Let me follow up here, (including all column values), if I update one column, will it also load the other unchanged columns into memory, generate new kv, and write it in? Or can it just update the data of one column into memory?

I read a post before (专栏 - TiDB MVCC 版本堆积相关原理及排查手段 | TiDB 社区)
It says that all columns are taken out, a new row of kv is generated based on the new value, and then written into the underlying data. So the cost of update in terms of memory is still higher than delete. From this, it can also be inferred that using replace to optimize delete is actually not valid. Because replace is actually also update.

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

The value in TiDB is the combination of all columns, which you can imagine as a JSON. If you modify one of the fields, you still need to retrieve the entire value, modify the specific field, and then store it back. Using replace does not optimize delete; it is simply a delete+insert operation used to optimize application code. In fact, its consumption is not lower than update because it first adds a key with a delete marker, then creates a new key+value. Update retrieves the original key+value, modifies the value, and then stores it back.

| username: system | Original post link

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