Why PostgreSQL Keeps the Row You Updated

Suppose we change one value:

UPDATE users SET city = 'Bangalore' WHERE id = 42;

The old value was Pune. The new value is Bangalore. It is tempting to picture PostgreSQL finding the row and replacing those four letters with nine.

That is not what happens. The extra letters are not the reason: even a same-size change normally creates a new version of the row. PostgreSQL leaves the old version in place for a while.

Why keep data that the user just changed? To answer that, we need to follow this one update from the table page to the reader and, eventually, to cleanup.

First, find the row

We will use a small table:

CREATE TABLE users (
  id bigint PRIMARY KEY,
  city text NOT NULL
);

INSERT INTO users (id, city) VALUES (42, 'Pune');

PostgreSQL stores the table's rows in a heap. Here, “heap” means the table's main storage, not the heap data structure from an algorithms course. The heap is divided into fixed-size pages, usually 8 KiB each. A page can hold many rows.

PostgreSQL calls a stored row a tuple. Each tuple sits on a page, and an item pointer on that page tells PostgreSQL where to find it. The physical address of a tuple is its ctid: a page number plus an item-pointer number.

The primary-key index on id can help PostgreSQL find user 42. An index entry points toward the heap, where PostgreSQL can inspect the tuple and decide whether this transaction is allowed to see it. The index does not usually make that visibility decision by itself.

This distinction matters: id = 42 is the logical identity we gave the row. Its ctid is a physical location. An update can create a new version at a different location, so ctid is not a safe replacement for a primary key. PostgreSQL's page-layout documentation describes pages, item pointers, and tuple headers.

We have found the old tuple. Why not change its city in place?

Someone may still need “Pune”

Imagine two transactions. Transaction A reads user 42. Before A finishes, transaction B changes the city and commits.

If B simply overwrote Pune with Bangalore, what should A see on its next read? Depending on its isolation level, A may be entitled to keep seeing the old value. Overwriting it would throw that value away.

PostgreSQL solves this by keeping row versions. An UPDATE puts a new tuple in the heap. The old tuple remains until PostgreSQL knows no relevant reader can need it. A DELETE follows a related idea: the old tuple is marked as deleted, but is not necessarily removed at once.

This is multi-version concurrency control, or MVCC. The “multi-version” part is literal: one logical row can have several physical versions. Readers can often continue without blocking a writer because the version they need can remain available. That does not mean two writers changing the same row never wait for each other; they can still conflict. PostgreSQL's MVCC introduction explains that separation between ordinary reads and writes.

After our update, the heap may briefly contain something like this:

logical user id = 42

old tuple: city = Pune       created by an earlier transaction
new tuple: city = Bangalore  created by the updating transaction

The old tuple has not become another user. It is an older physical version of the same logical user.

How does PostgreSQL tell the versions apart?

Each heap tuple has a small header alongside its column values. Two important fields are:

• xmin: the transaction ID that created this tuple version.
• xmax: a transaction ID associated with deleting or replacing it; the field can also be used for row locking.

An XID, or transaction ID, is a number PostgreSQL assigns to transactions that need one. The tuple header also contains status flags and a link that can point toward a newer version.

For our example, the old version's xmin belongs to the transaction that inserted Pune. The update gives the new version its own xmin and marks the old version with information about the updating transaction.

Imagine the insert had XID 10 and the update had XID 20:

Pune       xmin = 10    xmax = 20
Bangalore  xmin = 20    not replaced or deleted yet

Those numbers help identify what happened; they do not settle what every reader sees. XID 20 might still be in progress, might commit, or might roll back.

Do not read xmax as “this row is deleted.” The transaction recorded there may still be running, may have rolled back, or may only have locked the row. PostgreSQL must consider transaction status and the reader's view of the database before deciding whether a version is visible. The tuple-header layout lists these fields; it is a description of storage, not a one-field visibility rule.

The missing piece is the reader's snapshot.

Two readers, two answers

A snapshot is PostgreSQL's view of which transactions had completed and which were still in progress when the snapshot was taken. It is not just a clock time or a copy of the whole database. PostgreSQL uses it, together with tuple information and transaction status, to decide which versions a statement may see.

Watch the same row in two sessions:

Under PostgreSQL's default Read Committed level, each statement starts with a fresh snapshot. A's second SELECT can therefore see Bangalore after B commits.

Under Repeatable Read, A keeps the snapshot established by its first query or data-changing statement. Its second SELECT still sees Pune, even after B commits. A would still see changes it made itself. PostgreSQL also has Serializable isolation, but its extra checks are outside this row-version story. PostgreSQL's isolation-level documentation describes the exact rules.

Now the reason for keeping the old tuple is concrete. It is not leftover garbage while A can still see it.

There is a cost, though. Every update needs room for a new tuple. What happens to the index?

An update can make the indexes work too

An index entry leads to a location in the heap. An ordinary update can require new index entries for the new tuple, even when the indexed value itself did not change. Later PostgreSQL must also clean up entries that point to versions nobody can see.

For a busy table with several indexes, that is a lot of work for changing one unindexed column.

PostgreSQL has an optimization called a Heap-Only Tuple update, or HOT update. It can use HOT when two conditions hold:

1. The update does not change columns used by the table's ordinary indexes.
2. The new tuple fits on the same heap page as the old one.

When both hold, PostgreSQL still creates a new tuple version. But the existing ordinary index entry can keep pointing to the original item pointer on that page. PostgreSQL follows the version chain there to find the appropriate tuple, so it does not need to add new ordinary index entries for this update.

The same-page condition is important. If the new version lives on another page, that original page's index pointer cannot serve as the stable entrance to a local HOT chain. PostgreSQL must use the ordinary update path instead. An update can also be non-HOT even when both versions fit on one page, if it changes an indexed column.

For our example, changing city may qualify for HOT because the primary-key index uses id, not city. But if we also create an ordinary index on city, a change from Pune to Bangalore changes an indexed value and cannot use HOT. Enough free space on the original page is still required either way.

There is a technical exception: BRIN is a summarizing index, and its indexed columns do not rule out HOT in the same way; the summary may still need an update. The main rule for this article is about ordinary indexes. PostgreSQL documents the full conditions and notes that leaving more page space with a lower fillfactor can make HOT more likely. PostgreSQL's HOT documentation

HOT is not a different visibility rule. It saves index work while using the same MVCC idea: old and new tuple versions coexist until cleanup is safe.

Did the update go straight to the disk file?

Not necessarily. PostgreSQL normally changes a copy of the heap page in shared memory. That page can be written to its table file later.

For crash recovery, PostgreSQL records the change in its write-ahead log, or WAL. The important ordering is: the relevant WAL must reach durable storage before the changed data page is allowed to reach disk. If the server crashes before that page is written, recovery can replay the WAL. This is why a committed update does not require every changed heap and index page to be flushed immediately. PostgreSQL's WAL introduction

This article follows what happens to the tuple, not every step of commit and disk flushing. The key point is that PostgreSQL's heap pages do change. That differs from the immutable SSTables in Why Databases Love Immutable Files: an LSM engine replaces sorted files through compaction, while PostgreSQL updates pages and later reuses space within them.

When does the old version become waste?

Once B has committed Bangalore, new Read Committed statements will not need the old Pune version. But A might still have an older snapshot.

Only after no relevant snapshot can still see Pune may PostgreSQL treat that version as dead and reclaim its space. This explains a real operational problem: a long-running transaction can keep an old snapshot alive, preventing cleanup from removing versions that newer transactions no longer need.

Picture repeated updates:

Pune → Bangalore → Mumbai → Delhi

The application sees one current city. The heap may temporarily hold several versions. If old snapshots stay open, versions pile up, and the table—and sometimes its indexes—can grow.

HOT can shorten some version chains during normal page access when intermediate versions are no longer needed. It does not make all cleanup free. We still need a way to reclaim dead tuples and stale index entries across the table.

Cleanup is not the same as shrinking the file

PostgreSQL performs some local page pruning when a page is visited. It can remove versions that are safe to discard, particularly within HOT chains.

VACUUM does broader maintenance. It finds dead tuple versions, cleans up index entries when needed, and records which space can be reused. PostgreSQL's autovacuum workers usually schedule that work without you issuing VACUUM by hand.

Ordinary VACUUM usually makes free space available inside the table for future tuples. It usually does not make the table file smaller. It can sometimes release empty pages at the end, but that is not the normal promise. VACUUM FULL is different: it writes a new, compact copy of the table and needs a stronger lock. PostgreSQL's routine-vacuuming documentation makes this distinction.

Two small maps help PostgreSQL manage this work:

• The Free Space Map records where there may be room for another tuple.
• The Visibility Map records pages known to contain only versions visible to everyone, and pages whose versions are all frozen. This helps VACUUM skip some pages; it can also let certain index-only scans avoid visiting the heap.

These maps are helpers, not alternate homes for row data. Free Space Map · Visibility Map

At this point, VACUUM sounds like a cleanup crew. But it has another job that has nothing to do with deleting old versions.

Why would a live row need freezing?

Suppose a user record is inserted and never updated. It remains live for years. Its creating transaction ID, xmin, becomes very old.

To see the problem, first imagine a tiny transaction counter with only eight numbers, 0 through 7. After 7, it starts again at 0. This is a teaching example; PostgreSQL's real counter is much larger.

Suppose our row was created by transaction 6. A little later, the counter reaches 1:

6 (row created) → 7 → 0 → 1 (now)

We know 6 is three transactions behind 1. Notice that comparing the numbers normally would already fail: 6 is greater than 1, but it happened earlier. PostgreSQL must compare them as positions on a circle.

Now let more transactions happen, while that row stays untouched:

6 (row created) → 7 → 0 → 1 → 2 → 3 (now)

The row was really created five transactions ago. But if all we have are the numbers 6 and 3, then 6 is also three positions ahead of 3 on the circle. The numbers alone cannot tell us whether 6 was five steps in the past or three steps in the future.

PostgreSQL has to choose a side: positions in the previous half of the circle count as past, and positions in the next half count as future. At “now = 3” in our toy example, 6 has moved into the future half, even though the row was created five steps ago.

PostgreSQL's real XIDs use 32 bits, giving about 4.3 billion numbered positions. An unfrozen XID becomes unsafe after roughly 2 billion newer transactions, when it can cross that same halfway boundary. PostgreSQL's wraparound explanation describes this rule.

Freezing stops a long-lived row from depending forever on that comparison. Before its creating XID becomes too old, VACUUM marks the live tuple as frozen. PostgreSQL can then treat its creator as safely in the past regardless of where the counter moves. Modern PostgreSQL sets a tuple flag for this; it does not need to erase the original xmin. Freezing does not delete the row, reserve its XID, or stop the row from being updated later.

That gives VACUUM two jobs that are easy to mix up:

dead old version  → reclaim space when nobody can need it
live old version  → freeze it so its XID cannot age into ambiguity

Autovacuum is therefore not just a performance nicety. PostgreSQL also runs anti-wraparound vacuum work to keep transaction IDs safe. If that cannot make progress, PostgreSQL eventually refuses new transactions that need an XID rather than risk misreading ancient rows. PostgreSQL's vacuuming documentation

What did one UPDATE really do?

Our SQL statement looked like a replacement:

UPDATE users SET city = 'Bangalore' WHERE id = 42;

Underneath it, PostgreSQL found a heap tuple, created another version, recorded transaction information, and let each reader's snapshot decide which version it could see. HOT may have saved index entries if the change and page space allowed it. Later, pruning and VACUUM could reclaim obsolete versions. VACUUM also has to protect old live versions from transaction ID wraparound.

The useful trade is concurrency: readers can often use an older version while a writer creates a new one. The bill is extra space, index work, and ongoing maintenance. A long-running transaction can make that bill larger by keeping history alive.

That is why PostgreSQL does not immediately erase Pune. For a while, the old value is not waste at all. It is the answer a legitimate reader may still be owed.

Inspiration and references

This investigation was inspired by Hussein Nasser's video on PostgreSQL internals. I used PostgreSQL's documentation to check the implementation details and shape the examples here.

• Database page layout for heap pages, item pointers, tuple headers, and ctid.
• MVCC and transaction isolation for snapshots and visibility behavior.
• Heap-Only Tuples for HOT eligibility and index maintenance.
• Routine vacuuming for dead-version cleanup, freezing, and wraparound safeguards.
• Write-ahead logging for the durability boundary between WAL and data pages.

The diagrams are original teaching illustrations, not literal dumps of PostgreSQL page bytes. Implementation details were checked against PostgreSQL 18 documentation on 17 September 2026.