how does PostgreSQL work?

Why PostgreSQL Handles Thousands of Concurrent Transactions Without Blocking: Understanding MVCC

One of the most powerful features of PostgreSQL is its ability to handle thousands of concurrent transactions seamlessly without them getting in each other's way. The golden rule in PostgreSQL is: "Readers never block writers, and writers never block readers."

To understand why this is possible—and how SELECT and UPDATE interact—we need to look under the hood at MVCC (Multi-Version Concurrency Control).

How MVCC Works During UPDATE and SELECT
Unlike some traditional databases that overwrite data in place or lock tables during updates, PostgreSQL takes a completely different approach:

No Overwriting: When you update a record in PostgreSQL, it does not overwrite the existing data on disk. Instead, it performs a new INSERT operation behind the scenes, creating a brand new version of the row.

Marking the Old Row (xmax): The old version of the row is not immediately deleted. Instead, PostgreSQL updates its internal metadata—specifically, the xmax (transaction ID) field of the old row is marked with the ID of the transaction that performed the update. This effectively makes the old row inactive.

What the SELECT Transaction Sees: If a SELECT transaction is running concurrently while an UPDATE is happening (and before the UPDATE is even committed), it won't be blocked. Because of MVCC snapshots, the SELECT query simply reads the latest valid version of the data that existed before the update, or respects transaction isolation levels. Readers never have to wait for writers to finish.

Summary
Thanks to MVCC, data updates do not destroy old records; they simply create new versions while hiding the inactive ones from upcoming queries. This ingenious mechanism allows PostgreSQL to scale efficiently, ensuring high performance and zero unnecessary blocking even under heavy concurrent loads.