In what way does btree index of PostgreSQL achieve multi-version concurrency control?

Question

PostgreSQL makes use of MVCC technology for database concurrency control, it creates the latest version of an item for each write and then accessing that version through visibility rules.I want to ask how does the btree index implement multiple version control?

PostgreSQL makes use of MVCC technology for database concurrency control, it creates the latest version of an item for each write and then accessing that version through visibility rules.I want to ask how does the btree index implement multiple version control?

Answer 1

Indexes never ever implement MVCC in PostgreSQL. An index has every single row, from rows that were inserted but not yet committed, to rows that are totally obsolete but not yet vacuumed away. You need to visit the table to see whether the row of your interest is available or not.

We have some optimizations available for this. In an index-only scan, you can easily consult the visibility map of the table if you want, instead of the main part of the table itself. If a query tries to find 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 be marked dead in the index so that no future query can visit that table again. 

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.