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.

1. 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.

1. 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.

1. 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?

1. 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.

1. 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.

1. 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!