MVCC mental model in PostgreSQL

Introduction

MVCC helps PostgreSQL handle many users working with the database at the same time without conflicts.

When multiple people read and write data at the same time in a database.

When you want to avoid delays caused by waiting for others to finish their work.

When you want to keep data consistent even if many changes happen quickly.

When you want to make sure your queries see a stable view of the data.

Syntax

PostgreSQL

-- MVCC is a concept, not a command. It works automatically in PostgreSQL.

MVCC means each transaction sees a snapshot of the data as it was when the transaction started.

PostgreSQL keeps old versions of rows to support this snapshot view.

Examples

This transaction reads and updates a product. Other transactions see old or new data depending on timing.

PostgreSQL

BEGIN;
SELECT * FROM products WHERE id = 1;
-- Transaction sees data as it was at BEGIN
UPDATE products SET price = 20 WHERE id = 1;
COMMIT;

This transaction sees the product data as it was at the start, even if others changed it later.

PostgreSQL

BEGIN;
SELECT * FROM products WHERE id = 1;
-- Another transaction updates the same row here
COMMIT;

Sample Program

Transaction 1 starts and reads price 10. Transaction 2 updates price to 15 and commits. Transaction 1 still sees price 10 until it ends.

PostgreSQL

-- Setup table and data
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price INT);
INSERT INTO products (name, price) VALUES ('Apple', 10);

-- Transaction 1
BEGIN;
SELECT price FROM products WHERE name = 'Apple';
-- Returns 10

-- Transaction 2 (in another session)
BEGIN;
UPDATE products SET price = 15 WHERE name = 'Apple';
COMMIT;

-- Back to Transaction 1
SELECT price FROM products WHERE name = 'Apple';
COMMIT;

Important Notes

MVCC avoids locking delays by letting readers see old data versions.

Writers create new row versions; old versions are cleaned later.

This helps keep the database fast and consistent for many users.

Summary

MVCC lets many users work with data at the same time without waiting.

Each transaction sees a snapshot of data as it was when it started.

PostgreSQL keeps old row versions to support this snapshot view.