How PostgreSQL Handles Millions of Queries Without Locking Up

Imagine it’s Friday afternoon. Your application traffic spikes, millions of users are writing data, and your analytics dashboard is hammering the database with massive SELECT queries. In a traditional database, this is a recipe for a deadlock disaster. Yet, PostgreSQL handles it without breaking a sweat. Reads don't block writes, and writes don't block reads. But how does it pull off this architectural magic trick without locking up? The answer lies in a system called Multi-Version Concurrency Control (MVCC).

To understand how it works we first need to understand what is transaction, xmin, xmax and what's a snapshot.

What is a "transaction" here?

Every time you do something to the database. An UPDATE, INSERT, DELETE, or even just a SELECT wrapped in BEGIN...COMMIT , Postgres stamps it with a unique, ever-increasing number called a transaction ID (XID).

Think of it like a ticket number at a counter. Transaction #100 walked in and got ticket 100. Transaction #101 got ticket 101. Numbers only go up.

Time →
XID 100: someone's transaction
XID 101: someone's transaction
XID 102: someone's transaction

A transaction is either:

• committed: it finished successfully, its changes are permanent

• aborted/rolled back: it failed or was cancelled, its changes should be ignored forever

• in progress: it's still running right now, hasn't finished either way

Now lets understand xmin and xmax

Every row version (tuple) in a Postgres heap has hidden system columns you can actually query:

SELECT xmin, xmax, ctid, * FROM your_table;

• xmin - the transaction ID (XID) that created this tuple version (via INSERT, or the "new" side of an UPDATE).

• xmax - the transaction ID that deleted or superseded this tuple version (via DELETE, or the "old" side of an UPDATE). If the row hasn't been deleted/updated, xmax = 0, meaning "not yet deleted."

As we know already every transaction gets a monotonically increasing XID when it first writes. So conceptually, a tuple's "lifetime" is the range [xmin, xmax).

Postgres never overwrites a row in place. An UPDATE is really:

• old tuple: xmax set to the updating transaction's XID (marks it as superseded)

• new tuple: xmin set to the same XID (marks it as the new current version)

Now let's build an example

Say there's one row, and here's what happens over time:

XID 50: INSERT row (id=1, balance=100) → COMMITTED 
XID 75: UPDATE row to balance=200 → COMMITTED 
XID 90: UPDATE row to balance=300 → still RUNNING (not committed yet)

Right now, on the heap page, there are actually three physical copies of this row sitting around (Postgres doesn't delete old versions immediately, remember):

tuple	xmin	xmax	balance
v1	50	75	100
v2	75	90	200
v3	90	0	300

Read that as:

• v1 was created by XID 50, and got superseded by XID 75.

• v2 was created by XID 75, superseded by XID 90.

• v3 was created by XID 90, not superseded by anyone yet (xmax=0).

Now lets understand what happens when you run Query

This is where "snapshot" comes in. forget the formal definition for a second. A snapshot is just Postgres asking one question on your behalf:

Of all the transactions that exist so far (50, 75, 90...), which ones are done-and-committed as of the moment I started looking?

Lets get into the anatomy of a snapshot. When your transaction starts (or per-statement, depending on isolation level), Postgres builds a small struct with exactly 3 things:

• snapshot.xmin: the oldest XID that was still running when I started

• snapshot.xmax: the next XID that hasn't been assigned yet (i.e., anything ≥ this doesn't exist yet)

• snapshot.xip_list: the actual list of XIDs that were in-progress (running) at that moment

Lets continue with the previous example. Say at the moment your query starts, these transactions exist:

XID 40-49: all finished long ago (committed or rolled back) 
XID 50: currently running 
XID 51: currently running 
XID 52-59: finished 
XID 60: not yet started (doesn't exist yet)

Your snapshot gets built as:

xmin = 50 (oldest one still running) 
xmax = 60 (next XID to be handed out) 
xip_list = [50, 51] (the actual in-progress ones between xmin and xmax)

Notice: XIDs 52-59 finished already, so they're not in xip_list even though they're numerically between 50 and 60. The list only contains ones that were actually still running.

The actual logic Postgres uses for visibility check

For a given tuple with some tuple.xmin (its creator XID), Postgres runs this logic:

if tuple.xmin >= snapshot.xmax: → didn't exist yet when I started. NOT VISIBLE.

else if tuple.xmin is in snapshot.xip_list: → it was still running when I started, so even if it has since committed, I don't get to see it. NOT VISIBLE.

else if tuple.xmin < snapshot.xmin: → guaranteed finished before I even started. Now just check: did it commit or abort? (This is the one real lookup — see below.)

else: → it's between xmin and xmax but not in xip_list, meaning it must have finished (committed or aborted) by the time I took my snapshot. Check commit status.

So the xip_list is what lets Postgres skip re-checking every single XID. it only needs special-case treatment for the ones that were mid-flight. Everything below snapshot.xmin is trivially "already decided," and everything at/above snapshot.xmax is trivially "doesn't exist yet."

But how does it know if an XID actually committed vs aborted?

This is the missing piece, the xip_list only tells you "was it running." It doesn't tell you the final outcome. For that, Postgres checks a separate structure: the commit log, called pg_xact (older versions called it pg_clog).

This is a super compact on-disk bitmap — literally 2 bits per transaction ID — recording one of four states:

00 = in progress 
01 = committed 
10 = aborted 
11 = sub-transaction (special case)

Look at my snapshot's xmin/xmax/xip_list → is it possibly visible based on timing? (the logic above)

If timing says "maybe" → look up XID 75 in pg_xact → did it actually commit, or did it abort?

Only if both checks pass (finished before/outside my snapshot's blind spot, AND actually committed) does Postgres consider the tuple's xmin "visible to me."

One optimization worth knowing: hint bits

Checking pg_xact on every single row scan would be slow, so once Postgres determines a tuple's commit status the first time, it writes a hint bit directly onto the tuple header on the heap page (e.g., "this tuple's xmin is definitely committed"). Future scans just read that bit instead of consulting pg_xact again. This is also why you sometimes see unexpected disk writes on a table you only just SELECTed from — Postgres is "painting" these hint bits in for the first time.

---

I hope this clears your understanding on how PostgreSQL deals with concurrency effectively there are more things to it but i tried to abstract things without losing much information.