How is the internal update implemented in TiDB?

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

Original topic: tidb内部update是如何实现的?

| username: neo5j

In a row-based storage engine, if a field needs to be updated, does TiDB append with key + updated field, or insert a new complete record with key + updated field + other fields?

| username: Jellybean | Original post link

My impression is that in this way, a new record is appended to the end of the SST file, along with the latest timestamp TSO, indicating that it is the latest available MVCC version. The old data will be cleaned up during the next GC.

| username: neo5j | Original post link

Thank you for your response.
However, if it involves updating a field and appending it with other fields, and if the other fields are large, it should also impact performance, right? How does TiDB address this?

| username: TiDBer_pkQ5q1l0 | Original post link

The KV update is appended to memory, then swapped to immutable, then flushed to level 0 as is, and finally compacted in the background.

| username: Jellybean | Original post link

Yes, for rows with very large fields, write performance will be affected, so the official recommendation is that fields which are frequently updated should not be too large.

| username: neo5j | Original post link

I have an idea. Can we just write the key + updated fields, set an update status for this record, and append it to the underlying storage? When querying, we can merge other fields based on the LSN and update status?

| username: Jellybean | Original post link

In this way, it is necessary to maintain the status of different fields. In extreme cases, almost all fields of a table have been updated, and these fields have been updated many times. Imagine how troublesome it would be to maintain this. Moreover, MVCC multi-version control needs to be considered, which makes the design of the entire basic data row not simple and elegant enough, and is likely to cause other unexpected performance and management issues.

| username: neo5j | Original post link

Does TiDB provide a way for bulk updates? Suppose there is a scenario where many rows need to be updated for a certain field. Can it only be done by updating one row at a time with an update statement?

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

TiKV stores key:value pairs, with each row being a key-value pair. It is not possible to mark a single field within the value as updated, so updating a single field will result in a new complete value.

| username: Hacker_ufuLjDKs | Original post link

There are two main schools of MVCC implementation:
The undo school, represented by Oracle, MySQL, DB2, and DM, which is based on undo logs;
The row-copy school, represented by PostgreSQL, Kingbase, openGauss, and TiDB, which is based on row copying.

The undo school has broader adaptability, while the row-copy school can be more efficient in specific scenarios (though its general applicability is slightly lower). In TiDB, not only updates but any DML operations will write new rows, essentially trading space for efficiency. This results in a lot of garbage (expired) data. Efficiently reclaiming this garbage and avoiding conflicts with application demands during the reclamation process is an ongoing iterative optimization process.

Currently, some databases offer both MVCC implementation methods for selection. For example, openGauss, which originally used the row-copy implementation from the PostgreSQL system, has added a new implementation similar to Oracle’s undo method.

| username: xingzhenxiang | Original post link

insert a new piece of data with a higher version number

| username: system | Original post link

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