What Actually Happens When You UPDATE a Row in PostgreSQL?

Most developers think of a PostgreSQL table like this: users id | age 1 | 25 2 | 31 3 | 42 Then we run: UPDATE users SET age = 30 WHERE id = 1; The obvious mental model is: 25 → 30 But PostgreSQL doesn't simply overwrite the old value. To understand what actually happens, we need to look at how PostgreSQL stores rows and how MVCC (Multi-Version Concurrency Control) works. - Tables are stored as pages PostgreSQL stores table data in fixed-size pages. The usual page size is 8 KB. You can roughly imagine a table as: users ┌─────────────────┐ │ Page 0 - 8 KB │ ├─────────────────┤ │ Page 1 - 8 KB │ ├─────────────────┤ │ Page 2 - 8 KB │ ├─────────────────┤ │ Page 3 - 8 KB │ ├─────────────────┤ │ ... │ └─────────────────┘ Each page can contain multiple rows. Internally, PostgreSQL calls a row a tuple. So the simplified model is: Table ↓ Pages ↓ Tuples But a page isn't simply: [row][row][row] It has a structure. A simplified page looks like: ┌──────────────────────────┐ │ Page Header │ ├──────────────────────────┤ │ Line Pointers │ │ 1 → offset, length │ │ 2 → offset, length │ │ 3 → offset, length │ ├──────────────────────────┤ │ Free Space │ ├──────────────────────────┤ │ Tuple │ │ Tuple │ │ Tuple │ └──────────────────────────┘ The line pointer tells PostgreSQL where a tuple is located inside the page. This becomes important when we talk about ctid. - CTID: How does PostgreSQL locate a tuple? PostgreSQL exposes a system column called ctid. For example: SELECT id, age, ctid FROM users; You might see: | id | age | ctid | |---|---|---| | 1 | 25 | (0,1) | | 2 | 31 | (0,2) | | 3 | 42 | (1,1) | A CTID is essentially: (block number, item identifier number) So: (0,1) │ │ │ └── line pointer #1 └──── page/block #0 Notice something important: (0,1) does NOT mean byte offset 1. The first value identifies the page/block. The second value identifies the line pointer. The line pointer then contains the actual byte offset and length of the tuple inside that page. So: CTID (0,1) │ ▼ Line pointer #1 │ ├── offset └── length │ ▼ Tuple This is the important distinction when thinking about file offsets. - Where does the index come in? Now suppose we create an index: CREATE INDEX users_id_idx ON users(id); PostgreSQL will maintain a B-tree index. A simplified view: B-Tree │ ▼ key = 1 │ ▼ TID (0,1) │ ▼ Heap tuple The index doesn't contain the entire row. It helps PostgreSQL locate the corresponding heap tuple. So when we run: SELECT * FROM users WHERE id = 1; the simplified path is: id = 1 ↓ B-tree index ↓ TID / CTID ↓ Heap page ↓ Line pointer ↓ Tuple Now we get to the interesting part. What happens when that tuple is updated? - UPDATE doesn't simply overwrite the old tuple Suppose we start with: CTID = (0,1) id = 1 age = 25 Now: UPDATE users SET age = 30 WHERE id = 1; A simplified mental model is: Before: (0,1) id = 1 age = 25 After: Old version New version (0,1) (0,2) id = 1 id = 1 age = 25 age = 30 PostgreSQL has created a new tuple version. Why? Because another transaction might still need to see the old version. This is the fundamental idea behind MVCC. Instead of thinking: UPDATE ↓ overwrite row think: UPDATE ↓ create new tuple version Now PostgreSQL can have: Old version age = 25 │ ▼ New version age = 30 and different transactions can potentially see different versions depending on their snapshots. - So which version does SELECT return? This is where xmin and xmax come in. Tuple headers contain transaction metadata. Two important fields are: xmin xmax Very roughly: xmin = transaction that created the tuple xmax = transaction that deleted/replaced the tuple Imagine transaction 8 performs the update: Old tuple id = 1 age = 25 xmin = 5 xmax = 8 and the new version: New tuple id = 1 age = 30 xmin = 8 The old version was created by transaction 5 and later replaced by transaction 8. Now another transaction runs: SELECT * FROM users WHERE id = 1; Which version should it get? Not necessarily the one with the newest CTID. PostgreSQL asks: Which tuple version is visible in my transaction snapshot? That's the key idea behind MVCC. A simplified view: SELECT │ ▼ Transaction snapshot │ ▼ Find candidate tuples │ ▼ Check visibility / \ / \ visible invisible │ │ ▼ ▼ return check another version version The exact visibility rules are more complicated than simply comparing xmin and xmax. PostgreSQL also considers transaction status and the snapshot's view of committed and in-progress transactions. That's why two concurrent transactions can see different versions of what logically looks like the same row. - What happens to the old version? Eventually, the old tuple may no longer be needed by any active transaction. It becomes a dead tuple. Page ┌─────────────────────┐ │ Old tuple │ │ age = 25 │ │ DEAD │ ├─────────────────────┤ │ New tuple │ │ age = 30 │ └─────────────────────┘ PostgreSQL can't immediately remove the old version because an older transaction might still need to see it. Once PostgreSQL knows that no active transaction needs it anymore, VACUUM can clean it up and make its space available for reuse. So the lifecycle is roughly: INSERT ↓ Tuple created ↓ UPDATE ↓ New tuple version created ↓ Old version becomes obsolete ↓ Dead tuple ↓ VACUUM ↓ Space can be reused And this is one of the reasons PostgreSQL needs autovacuum. One important optimization: HOT updates There's one more interesting detail. Suppose we have: CREATE INDEX users_id_idx ON users(id); and execute: UPDATE users SET age = 30 WHERE id = 1; We're changing age, but the index is on id. If there is enough free space on the same page, PostgreSQL can perform a HOT (Heap-Only Tuple) update. In that case, it can create the new tuple version without creating another index entry. Conceptually: Index │ ▼ (0,1) │ ▼ Old tuple age = 25 │ ▼ New tuple age = 30 This can reduce the amount of index maintenance required by an UPDATE. HOT is possible when the UPDATE doesn't modify columns referenced by indexes and the new tuple can be placed appropriately on the same page. The mental model If you remember nothing else, remember this: TABLE │ ▼ PAGES │ ▼ TUPLES │ ▼ LINE POINTER │ ▼ BYTE OFFSET │ ▼ TUPLE An index gives PostgreSQL a faster way to get there: Index │ ▼ TID │ ▼ Heap page │ ▼ Line pointer │ ▼ Tuple And an UPDATE looks more like: UPDATE │ ▼ Find old tuple │ ▼ Create new tuple version │ ▼ MVCC decides which version each transaction can see │ ▼ Old version eventually becomes dead │ ▼ VACUUM reclaims its space So PostgreSQL didn't simply do: 25 → 30 Under the hood, it did something closer to: 25 │ │ UPDATE ▼ old tuple + new tuple │ │ ├── xmin/xmax + snapshot │ ↓ │ visibility │ └── eventually ↓ VACUUM And that's the interesting part of PostgreSQL: a simple SQL statement like UPDATE hides a surprisingly sophisticated storage and concurrency system underneath. Thanks for reading! More backend engineering deep dives coming soon! Top comments (0)