# 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. ```plaintext 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```plaintext 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.