CODEWITHSUNDEEP
postgresqlb-tree
Zeng
Zeng

Reputation: 107

How does the btree index of PostgreSQL achieve multi-version concurrency control?

PostgreSQL USES MVCC technology for database concurrency control, creating a new version of an item for each write and then accessing that version through visibility rules.The question is how does the btree index implement multiple version control?When new btree nodes are added and the tree is split, the original btree structure will be changed. At this time, how is PGSQL processed?Can someone tell me?

Upvotes: 1

Views: 588

Answers (1)

jjanes
jjanes

Reputation: 44257

In PostgreSQL, indexes don't implement MVCC. An index contains every row that might possibly be interesting to anyone, all the way from rows that were inserted but are not yet committed, to rows that are totally obsolete but not yet vacuumed away. You have to visit the table itself to see if the row is interesting to you.

There are some optimizations on this. In an index-only scan, you can sometimes consult the visibility map of the table, rather than the main part of table itself. Also if a query looks up a row in the index and then goes to the table and sees that the row is obsolete for all uses, when it gets back to the index it can mark it dead in the index so future queries don't need to visit the table.

When new btree nodes are added and the tree is split, the original btree structure will be changed. At this time, how is PGSQL processed?Can someone tell me?

I don't think that is really a stack-overflow type question. The best reference for all the details is the source code and source comments. Perhaps you were just wondering what happens if the transaction rolls back. The page split remains, and the inserted tuple stays there until vacuum removes it (at which point the page split still remains).

In the case of a crash, either the WAL record which describe the split gets played back, or it doesn't. Since the pages dirtied by the split cannot be written out until the WAL record describing the split has been flushed to disk (they are guarded in shared_buffers until then), the system will be in a consistent state either way.

Upvotes: 5

Related Questions