HOT updates and isolation levels: what you gain and what you pay

Every UPDATE in Postgres writes a new tuple. If the table has five indexes, each UPDATE writes five new index entries. HOT updates eliminate that cost — but only under two specific conditions. And the isolation level you pick determines which correctness bugs the database prevents vs which it leaves to your application code.

HOT updates: an optimization with a famous cost model

If an UPDATE touches no indexed column and the new tuple fits on the same heap page as the old, Postgres can perform a heap-only tuple (HOT) update:

• The old tuple gets flag HEAP_HOT_UPDATED — “follow the t_ctid chain to find me”
• The new tuple gets flag HEAP_ONLY_TUPLE — “no index entry points at me directly”
• The index continues pointing at the original tuple; the heap chain carries the lookup the rest of the way
• Result: two index writes become zero

On UPDATE-heavy workloads where most columns are not indexed, this is the difference between WAL doubling and barely growing — a 30–70% WAL savings.

HOT chains break when the new version cannot fit on the original page; once broken, every subsequent UPDATE pays the index-write cost again. The fillfactor storage parameter (ALTER TABLE t SET (fillfactor=85)) reserves free space on each page precisely so HOT chains have room to grow.

The four isolation levels Postgres exposes

SQL standard defines four levels by listing anomalies they allow or prohibit: dirty reads, non-repeatable reads, phantom reads, and serialization anomalies.

Postgres specifics:

• Read Uncommitted is silently treated as Read Committed; Postgres never allows dirty reads at any level.
• Read Committed (the default) gives each statement a fresh snapshot.
• Repeatable Read gives the whole transaction one snapshot taken at the first statement; Postgres implements RR as snapshot isolation — stronger than the SQL standard’s RR (no phantom reads) but weaker than true serializable (write skew is still possible).
• Serializable (since 9.1) layers Serializable Snapshot Isolation (SSI) on top of RR: Postgres tracks read–write dependencies and aborts transactions whose commit order cannot correspond to any serial schedule, throwing SQLSTATE 40001.

What the application must do

Under READ COMMITTED: the application is responsible for avoiding lost updates via:

• SELECT ... FOR UPDATE (pessimistic locking on the row), or
• UPDATE ... WHERE version = ? with optimistic concurrency, or
• RETURNING to confirm the row had the expected value.

Under REPEATABLE READ and SERIALIZABLE: the database raises SQLSTATE 40001 serialization_failure when a conflict is detected. The application’s only correct response is to retry the entire transaction. Frameworks like Django, ActiveRecord, and Hibernate ship retry middlewares.

When to pick which level

• READ COMMITTED: 90% of business logic — maximum throughput, fresh snapshot per statement.
• REPEATABLE READ: when the transaction reads a set of rows multiple times and needs consistency between those reads (e.g., a report that sums fields then cross-checks the aggregate version).
• SERIALIZABLE: when the business logic has an invariant only preserved by absence of write skew (e.g., “at least one doctor on call”, “no account goes below zero”). Adds up to 5x cost and requires the application to handle retries.

Write skew under Repeatable Read

Write skew is the canonical Repeatable Read anomaly Postgres does not prevent. Two transactions read the same set, decide based on the set, and each modifies a different row in that set. Both commit; the invariant is broken.