PostgreSQL transactions behind the scenes - MVCC, locks, isolation levels
Content MVCC Xmin and Xmax VACUUM command. Locks Transaction isolation levels Used tools: PostgreSQL setup with Docker: https://hub.docker.com/_/postgres pgAdmin: https://www.pgadmin.org/ MVCC PostgreSQL uses multiversion concurrency control (MVCC) architecture to implement transactions with proper isolation levels. MVCC makes concurrent access possible and safe by maintaining several versions of a row. Row is enriched with additional metadata which is xmin and xmax. Lets take a look at the example: Xmin and xmax I will insert a new row into the table: We can do a standard select: With followig result: Nothing unexpected. But lets see the result of SELECT that explicitly mentions xmin and xmax columns: We can now see two new columns in the result: These columns were not added by me when creating the table. They are handled by MVCC mechanism in PostgreSQL. As the official documentation states: xmin - The identity (transaction ID) of t...