Tejas GPBrowse

Home/Postgres Internals/Episode 3

UPDATE doesn't update

In Postgres, an UPDATE writes a whole new copy of the row. In 2016 Uber put the cost of that, write amplification, first on its list of reasons for moving from Postgres to MySQL.

9 Nov 2026 · 4 min read

postgres=# UPDATE accounts SET balance = 800 WHERE id = 7;UPDATE 1 -- one row changed, two versions on disk

The reel covered this in two minutes. This is the slower version: what actually happens on disk when you run an UPDATE, why Postgres does it that way, what it cost Uber, and how to check your own tables.

In 60 seconds

  • An UPDATE in Postgres does not change the row in place. It writes a new version of the row and leaves the old one where it was.
  • The old version stays because other transactions may still need to see it. This is MVCC: Multi-Version Concurrency Control.
  • Once nobody can see the old version, it becomes a dead row, and VACUUM cleans it up later.
  • The new version lives somewhere else, so normally every index on the table gets a new entry pointing to it. More indexes means more writes per UPDATE.
  • HOT updates skip the index writes when no indexed column changed and the page has room. You can measure how often that happens.

What happens on disk

Take one row: id = 7, balance = 500. You run:

sql
UPDATE accounts SET balance = 800 WHERE id = 7;

Postgres doesn't touch the ₹500 row. It writes a second row, id = 7, balance = 800, and marks the first one as replaced. On disk there are now two versions of the same row.

The docs say it plainly: "an UPDATE or DELETE of a row does not immediately remove the old version of the row."

Why keep the old version?

Because at the same moment, another transaction might be reading that row.

Say transaction A started before your UPDATE committed. A works from a snapshot of the database as it was when it started, so it must still see ₹500. Transaction B starts after the UPDATE and sees ₹800. Both are correct, at the same time, and neither had to wait for the other.

Dead rows and VACUUM

Once no running transaction can see the ₹500 version anymore, it's a dead row. It still takes space on the page until VACUUM marks that space as reusable. Autovacuum does this in the background.

If dead rows pile up faster than VACUUM clears them, tables and indexes grow. That's bloat, and it's the topic of the next episode.

The cost: every index gets a write

An index entry points at a physical location of a row version. The new version sits in a new location, so in the general case each index on the table needs a new entry pointing to it, even indexes on columns you didn't change.

This is the problem Uber described.

Uber, July 2016

On 26 July 2016, Uber Engineering published Why Uber Engineering Switched from Postgres to MySQL. The first problem on their list was write amplification.

Their example is a users table with three indexes: the primary key, (first, last), and birth_year. They change one person's birth_year from 780 to 770. Only one column changed, but Postgres does four physical writes:

  1. the new row version
  2. the primary key index
  3. the (first, last) index
  4. the birth_year index

And the WAL (write-ahead log) records all of it, on top.

They then scale it up: on a table with a dozen indexes, an update to a column covered by just one index still has to be written into all twelve.

The WAL is what replicas consume, and Postgres's WAL describes physical changes, so it's verbose. Uber's master was on the West Coast and replicas on the East Coast, and they write that the WAL volume overwhelmed the bandwidth between them.

One column changed. Every index paid for it.

Write amplification wasn't their only reason. The post also covers replication bugs, upgrades and connection handling. But it was first.

The part the post didn't mention: HOT

Postgres has an optimisation for exactly this case: Heap-Only Tuples (HOT). Per the docs, an UPDATE can be HOT when:

  • it doesn't modify any column referenced by the table's indexes (summarizing indexes like BRIN don't count), and
  • there's enough free space on the same page for the new version.

When both are true, the new version goes on the same page, the old one points to it, and no new index entries are needed. The dead version can also be cleaned up during normal page access, without waiting for VACUUM.

Uber's post doesn't mention HOT at all. In their own example, birth_year is indexed, so that update couldn't be HOT anyway. But many real updates are: think last_seen_at, status, or counters that aren't indexed.

Check your tables today

1. How many of your updates are HOT?

sql
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

A busy table with a low hot_pct is doing an index write for almost every update.

2. If HOT is low, look at two things.

  • Indexes on frequently updated columns. An index on a column you update all the time makes those updates non-HOT. Ask whether the index is worth it.
  • Free space on the page. Tables are filled to 100% by default. Lowering fillfactor leaves room on each page for new versions:
sql
ALTER TABLE accounts SET (fillfactor = 90);

This only affects pages written from now on. Existing pages keep their current packing until the table is rewritten.

Every index is one more write on every UPDATE that can't be HOT.

Sources