Why UPDATE Is Secretly an INSERT: How Postgres MVCC, xmin/xmax, and Table Bloat Actually Work

When you run an UPDATE statement in PostgreSQL, your intuition tells you something simple happens: the database finds the row on disk, seeks to the modified column, and overwrites the old bytes with the new values.

UPDATE users SET status = 'active' WHERE id = 42;

If Postgres actually worked this way, concurrent reads would crash, transactions would read half-written dirty bytes, and rollbacks would require expensive undo operations.

Postgres never modifies a row in place during an UPDATE.

Instead, Postgres performs an INSERT followed by a soft DELETE. It writes a completely new physical copy of the row, leaves the old row untouched on disk, marks the old row as expired, and links them together.

Understanding this mechanism (Multi-Version Concurrency Control, or MVCC) is the key to understanding why write-heavy Postgres databases experience massive table bloat, why secondary indexes suddenly degrade, and how Postgres engineers designed Heap-Only Tuples (HOT) to survive it.

Let us look beneath the SQL layer into the physical 8KB page layout, tuple headers, and transaction visibility rules.

---

1. Physical Storage: Inside the 8KB Heap Page

To see what happens during an update, you first need to see how Postgres stores data on disk.

Postgres tables are divided into physical 8KB blocks called pages (or buffers in shared memory). Each page is organized from the outside in:

+-----------------------------------------------------------------------+
| PageHeaderData (24 bytes)                                             |
| pd_lsn | pd_flags | pd_lower | pd_upper | pd_special ...              |
+-----------------------------------------------------------------------+
| ItemId (Line Pointers array: 4 bytes each, grows DOWNWARDS)           |
| [Line Pointer 1] -> offset to Tuple 1                                 |
| [Line Pointer 2] -> offset to Tuple 2                                 |
|                    | (Free Space Gap) |                               |
|                    v                  v                               |
+-----------------------------------------------------------------------+
| Heap Tuples (Actual Row Data: grows UPWARDS from the bottom)          |
|                                                                       |
| [Tuple 2 Payload + Header (23 bytes)]                                 |
| [Tuple 1 Payload + Header (23 bytes)]                                 |
+-----------------------------------------------------------------------+

Every physical tuple on a page has a 23-byte header (HeapTupleHeaderData) containing hidden metadata columns:

• t_xmin: The Transaction ID (XID) of the transaction that created/inserted this row version.
• t_xmax: The Transaction ID of the transaction that deleted or updated this row version (0 if the row is still live and un-deleted).
• t_ctid: The physical location (page_number, line_pointer_index) of this tuple or the newer version tuple if updated.
• t_infomask / t_infomask2: Bit flags indicating transaction commit status and Heap-Only Tuple properties.

---

2. The Step-by-Step Trace of an UPDATE

Let us trace what happens under the hood when Transaction 105 updates row 1 on page 0.

Initial State: A Fresh Insert (Transaction 100)

When row 1 is originally inserted by Transaction 100 at page 0, line pointer 1:

Physical Address: ctid = (0, 1)
xmin: 100
xmax: 0
data: { id: 42, name: 'Alice', status: 'pending' }

Any transaction running concurrently checks xmin (100 is committed) and xmax (0 means not deleted), so it reads the row.

The UPDATE Executes (Transaction 105)

When Transaction 105 executes UPDATE users SET status = 'active' WHERE id = 42:

Step 1: Write New Tuple (Physical Address: ctid (0, 2))
------------------------------------------------------
xmin: 105
xmax: 0
data: { id: 42, name: 'Alice', status: 'active' }

Step 2: Expire Old Tuple (Physical Address: ctid (0, 1))
------------------------------------------------------
xmin: 100
xmax: 105               <-- Marked deleted by Tx 105!
t_ctid: (0, 2)          <-- Points forward to the replacement version!
data: { id: 42, name: 'Alice', status: 'pending' }

+-----------------------------------------------------------------------+
| PAGE 0                                                                |
|                                                                       |
| Line Pointer 1 ---------> [Tuple 1: xmin=100, xmax=105, ctid=(0,2)]   |
|                                   | (Forward Pointer)                 |
|                                   v                                   |
| Line Pointer 2 ---------> [Tuple 2: xmin=105, xmax=0,   ctid=(0,2)]   |
+-----------------------------------------------------------------------+

Notice what just happened:

1. Tuple 1 was not overwritten. Its xmax was updated to 105, and its t_ctid was updated to point to (0, 2).
2. Tuple 2 was written as a fresh row at line pointer 2 with xmin = 105.
3. Both tuples now exist simultaneously on the physical disk page.

---

3. How MVCC Solves Concurrency Without Read Locks

Why go through this trouble instead of modifying bytes in place?

Because readers never block writers, and writers never block readers.

Consider two concurrent transactions:

1. Transaction 102 (Started earlier, long-running report):
   When Tx 102 queries users WHERE id = 42, its snapshot rule says: "Ignore changes from any transaction ID >= 102."
   It inspects Tuple 1:

   • xmin is 100 (committed before 102: visible).
   • xmax is 105 (committed after 102 started: invisible deletion). Tx 102 reads Tuple 1 (status: 'pending') cleanly without taking a single shared read lock on the row.

2. Transaction 108 (Started after 105 commits):
   Its snapshot rule says: "Transaction 105 is committed."
   It inspects Tuple 1:

   • xmax is 105 (committed: Tuple 1 is dead). It follows the forward pointer t_ctid = (0, 2) to Tuple 2:
   • xmin is 105 (committed: visible).
   • xmax is 0 (live). Tx 108 reads Tuple 2 (status: 'active').

No locks, no mutexes, no dirty reads, and no rollbacks. If Transaction 105 aborts, Postgres simply marks Tx 105 as aborted in the pg_xact commit log status map. Tuple 2 becomes instantly invisible to everyone, and Tuple 1 becomes visible again. Zero rollback log application needed.

---

4. The Hidden Tax: The Secondary Index Disaster

The MVCC design is brilliant for concurrency, but it introduces a severe architectural problem with database indexes.

In PostgreSQL, secondary indexes (B-Trees, GIN, GiST) do not point to a primary key or clustered table structure (unlike MySQL InnoDB). In Postgres, all indexes store direct physical pointers (ctid) to heap tuples.

If your users table has 5 indexes (e.g. on email, created_at, org_id, last_login, and status):

Before UPDATE:
Index on email       -------> ctid (0, 1)
Index on created_at  -------> ctid (0, 1)
Index on org_id      -------> ctid (0, 1)
Index on last_login  -------> ctid (0, 1)
Index on status      -------> ctid (0, 1)

When you update just one column (status):

• The new row is written to ctid (0, 2).
• If naive indexing applied, Postgres would have to insert a new index entry pointing to (0, 2) into all 5 indexes, even though email, created_at, org_id, and last_login never changed.

In a write-heavy database with 10 million rows and multiple indexes, updating a single column would trigger massive B-Tree write amplification, leaf page splits, and catastrophic disk I/O.

---

5. The Solution: Heap-Only Tuples (HOT)

To fix this index amplification, Postgres 8.3 introduced HOT (Heap-Only Tuples).

HOT allows Postgres to update a row without touching secondary indexes at all, provided two conditions are met:

1. No indexed column is modified: The UPDATE statement only changes columns that are not part of any index definition.
2. Space exists on the same page: The new tuple version fits inside the exact same 8KB physical page as the old tuple.

Index on email ------> Line Pointer 1 (LP_REDIRECT)
                             |
                             v
                       Line Pointer 2 ------> [Tuple 2 (status: 'active')]
                                              [Tuple 1 (Dead: status: 'pending')]

When HOT activates:

1. Postgres writes Tuple 2 to the same page.
2. Postgres turns Line Pointer 1 into an LP_REDIRECT stub pointing directly to Line Pointer 2 inside the page header.
3. Every secondary index continues pointing to Line Pointer 1 without any index modifications.
4. When an index scan traverses the B-Tree to Line Pointer 1, the engine follows the internal line pointer redirection directly to Tuple 2.

-- Check if your table is benefiting from HOT updates
SELECT 
    relname, 
    n_tup_upd AS total_updates, 
    n_tup_hot_upd AS hot_updates,
    ROUND((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS hot_ratio_pct
FROM pg_stat_user_tables
WHERE relname = 'users';

If your hot_ratio_pct is low (below 80% on update-heavy tables), you are paying a massive write penalty.

---

6. Inspecting the Physical Page with pageinspect

You can verify this physical behavior directly using the built-in Postgres pageinspect extension.

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Inspect line pointers and tuple headers on block 0 of users table
SELECT 
    lp, 
    lp_flags, 
    t_xmin, 
    t_xmax, 
    t_ctid, 
    t_infomask, 
    t_data 
FROM heap_page_items(get_raw_page('users', 0));

When you run this after an UPDATE, you will literally see two separate items on the page:

1. Item lp=1 with t_xmax = <txid> and t_ctid = (0, 2).
2. Item lp=2 with t_xmin = <txid> and t_xmax = 0.

---

7. Table Bloat and the Autovacuum Lifecycle

Because updates leave dead tuples behind, a table that processes 100,000 updates a day accumulates 100,000 dead rows.

The Role of VACUUM

When VACUUM runs:

1. It scans pages and identifies tuples whose xmax is older than the oldest active transaction snapshot.
2. It removes the dead tuple payload and marks the space on the 8KB page as available for future INSERT or UPDATE operations.
3. It updates the Free Space Map (_fsm fork) so new writes know where to find free slots.

The Bloat Trap: VACUUM Does Not Shrink File Size

Standard VACUUM frees space inside existing pages, but it never returns pages to the operating system filesystem.

If a table bloats from 1 GB to 20 GB during a bulk update batch, the disk file stays 20 GB forever unless:

• Future inserts fill the empty slots inside the existing pages.
• You run VACUUM FULL users or pg_repack, which creates a brand new copy of the table and swaps the physical relfilenode on disk.

+---------------------------------------------------------------+
| Disk File (20 GB on OS)                                       |
| [Page 0: 80% free] [Page 1: 90% free] ... [Page 2,500,000]    |
+---------------------------------------------------------------+

---

8. Senior Database Gotchas to Prevent MVCC Outages

1. The Long-Running Transaction Anchor

If a developer opens a BEGIN; session in psql or an analytics query runs for 4 hours, Postgres cannot vacuum any dead tuples created after that transaction started.
Autovacuum will skip dead tuples, leading to exponential table bloat and high disk consumption.

-- Find transactions holding back VACUUM cleanup
SELECT 
    pid, 
    usename, 
    state, 
    age(backend_xmin) as xmin_age, 
    query, 
    now() - xact_start as duration 
FROM pg_stat_activity 
WHERE backend_xmin IS NOT NULL 
ORDER BY age(backend_xmin) DESC;

2. Tuning Table fillfactor for HOT Updates

By default, Postgres packs 100% data into every 8KB page (fillfactor = 100). This leaves zero empty room for HOT updates on the same page, forcing every update to allocate a new page and update all secondary indexes.

For write-heavy tables with frequent updates, set fillfactor to 80-90%:

ALTER TABLE users SET (fillfactor = 80);
VACUUM FULL users; -- Rewrites the table with 20% free space per page

Now, 20% of every page is reserved specifically for in-page HOT updates, eliminating secondary index churn.

3. Autovacuum Cost Delay Throttling

Default PostgreSQL autovacuum settings are deliberately conservative (autovacuum_vacuum_cost_limit = 200). Under write load, dead tuples accumulate 10x faster than autovacuum can process them.
Tune autovacuum_vacuum_cost_limit to 1000 or 2000 on modern NVMe SSDs to allow vacuum workers to keep up with write streams.

---

Summary Mental Model

Operation	What Developers Assume	What Postgres Actually Does
UPDATE	Modifies bytes in place on disk	Writes a new row (xmin), marks old row dead (xmax), updates t_ctid pointer chain
DELETE	Erases bytes from disk	Sets xmax = current_txid in the tuple header (soft delete)
Concurrency	Read locks prevent dirty reads	Snapshot visibility check on xmin/xmax headers without read locks
Index Impact	No index changes if keys are untouched	All indexes get new entries unless HOT (Heap-Only Tuples) optimization triggers
VACUUM	Shrinks database file on disk	Reclaims internal page space for future writes; does not shrink OS file size

Understanding that an UPDATE is an INSERT changes how you design schema indexes, tune fillfactor, configure autovacuum, and manage transaction lifetimes in production.