8. Transactions and Concurrency

A database serves many clients at once, and each of them expects its work to be all-or-nothing and unaffected by the others. This chapter explains how PostgreSQL delivers that: transactions and their boundaries, the isolation levels and the anomalies each one allows, the multiversion concurrency control (MVCC) design that lets readers and writers work without blocking each other, the locks that serialize conflicting writes, and the vacuum process that cleans up after MVCC. Almost every production incident involving wrong data, stuck queries, or a database that keeps growing traces back to one of these topics.

<a id="81-transaction-basics"></a>

8.1 Transaction Basics

This subchapter covers how to start and end transactions, what happens when you do not, how to roll back part of a transaction, how errors affect an open transaction, and how to make transactions safe to retry.

BEGIN, COMMIT, and ROLLBACK

Real operations rarely consist of a single statement. Moving money between two accounts is two updates; placing an order inserts an order, several order lines, and decrements stock. If the server crashes or a constraint fails halfway through, the data must not be left in a half-finished state where the money has left one account but never arrived in the other. A transaction groups statements into one unit that either takes effect completely or not at all.

Think of a transaction as a shopping cart. You can add and remove items for as long as you like, and nothing is charged until you press "Buy" (COMMIT). If you abandon the cart (ROLLBACK), the store is exactly as it was.

The ACID guarantees

Property	Meaning	How PostgreSQL provides it
Atomicity	All statements take effect, or none do	Changes from an aborted transaction are never marked committed, so no reader ever sees them
Consistency	Each transaction moves the database from one valid state to another	Constraints (CHECK, UNIQUE, foreign keys) are checked; a violation aborts the transaction
Isolation	Concurrent transactions do not see each other's intermediate states	MVCC snapshots and locks (8.2, 8.3, 8.4)
Durability	Once COMMIT returns, the change survives a crash	The commit record is flushed to the write-ahead log (WAL) before COMMIT returns

Sample data for this chapter. Most examples use a small bank schema. Run it once; later topics add their own tables where needed:

DROP TABLE IF EXISTS transfers, accounts CASCADE;     -- replaces earlier accounts tables

CREATE TABLE accounts (
  id      int           PRIMARY KEY,
  owner   text          NOT NULL,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfers (
  id         bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_id    int           NOT NULL REFERENCES accounts,
  to_id      int           NOT NULL REFERENCES accounts,
  amount     numeric(12,2) NOT NULL CHECK (amount > 0),
  created_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50), (3, 'carol', 0);

The basic commands

BEGIN;                                                    -- start a transaction block
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 30);
COMMIT;                                                   -- make all three changes permanent and visible

BEGIN;
DELETE FROM transfers;                                    -- oops
ROLLBACK;                                                 -- undo everything since BEGIN
SELECT count(*) FROM transfers;                           -- 1: the delete never happened

PostgreSQL accepts several spellings of the same commands:

Standard / common	Equivalent PostgreSQL forms
START TRANSACTION	BEGIN, BEGIN WORK, BEGIN TRANSACTION
COMMIT	COMMIT WORK, END
ROLLBACK	ROLLBACK WORK, ABORT

Transaction modes. BEGIN accepts options that apply to the whole transaction. They can also be set with SET TRANSACTION as the first statement:

BEGIN ISOLATION LEVEL REPEATABLE READ, READ ONLY;      -- see 8.2 for isolation levels
SELECT sum(balance) FROM accounts;                      -- a consistent report
COMMIT;

BEGIN;
SET TRANSACTION READ ONLY;                              -- must come before any query
UPDATE accounts SET balance = 0;
-- ERROR:  cannot execute UPDATE in a read-only transaction
ROLLBACK;

READ ONLY is a safety net for reporting code, and it lets PostgreSQL apply optimizations at the SERIALIZABLE level (Serializable).

Visibility of uncommitted work. Until COMMIT, changes are visible only inside the transaction that made them:

 Session A                                     Session B
 ---------                                     ---------
 BEGIN;
 UPDATE accounts SET balance = 500 WHERE id=3;
 SELECT balance FROM accounts WHERE id=3;  -> 500   (A sees its own change)
                                               SELECT balance FROM accounts WHERE id=3;  -> 0    (A has not committed)
 COMMIT;
                                               SELECT balance FROM accounts WHERE id=3;  -> 500
 (afterwards: UPDATE accounts SET balance = 0 WHERE id = 3; to restore the sample data)

Chaining transactions. COMMIT AND CHAIN (PostgreSQL 12+) commits and immediately starts a new transaction with the same modes, which is handy for batch jobs that process data in chunks:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... process chunk 1 ...
COMMIT AND CHAIN;                     -- commit chunk 1, new transaction is also REPEATABLE READ
-- ... process chunk 2 ...
COMMIT;

What COMMIT costs. A commit must wait for its WAL record to reach durable storage, typically 0.1 to a few milliseconds depending on the disk. That is why one transaction around 10,000 inserts is far faster than 10,000 single-statement transactions (Batching and Round-Trip Reduction). The setting synchronous_commit = off makes COMMIT return before the flush, trading the last fraction of a second of commits on a crash for lower latency. It never causes corruption, only possible loss of recent commits, and it can be set per transaction:

BEGIN;
SET LOCAL synchronous_commit = off;   -- only for this transaction: acceptable for, say, click tracking
INSERT INTO page_views ...;
COMMIT;

Key takeaways

• A transaction makes a group of statements atomic, consistent, isolated, and durable (ACID).
• BEGIN starts a transaction block, COMMIT makes it permanent and visible, ROLLBACK discards it entirely.
• Uncommitted changes are visible only to the transaction that made them.
• Transaction modes (ISOLATION LEVEL, READ ONLY) are set on BEGIN or by SET TRANSACTION before the first query.
• Each commit waits for a WAL flush; group many small writes into fewer transactions.

🧪 Practice

1. (Beginner) Open two psql sessions. In the first, start a transaction and insert a new account. Check from the second session that the row is invisible, then commit and check again.
2. (Intermediate) Insert 10,000 rows into a scratch table, once as 10,000 separate statements in autocommit mode and once inside a single BEGIN ... COMMIT. Compare the timings with \timing and explain the difference.
3. (Interview) "Explain how a database guarantees that a committed transfer survives a power failure one millisecond after COMMIT returns." Hint: What has been written to disk at the moment COMMIT returns, and what has not? How is the rest reconstructed on restart?

Autocommit Behavior

If every interaction required an explicit BEGIN and COMMIT, simple one-off queries would be tedious. PostgreSQL therefore runs any statement sent outside a transaction block in its own implicit transaction, which commits automatically when the statement succeeds and rolls back when it fails. This is autocommit, and it is the server's only mode: there is no server setting to turn it off. What varies is how client tools and drivers behave, and that difference causes real bugs.

Server-side behavior. Each statement is atomic on its own:

UPDATE accounts SET balance = balance - 200;        -- runs in its own transaction
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
-- No row was changed, not even the accounts with enough balance: the statement is atomic.

A multi-statement string sent in one simple-query message (for example psql -c "stmt1; stmt2") runs as a single implicit transaction unless it contains explicit BEGIN/COMMIT commands.

Client-side autocommit. Some clients emulate "autocommit off" by sending BEGIN before the first statement for you:

Client	Default	How to change it
psql	Autocommit on	\set AUTOCOMMIT off: psql issues BEGIN implicitly; you must COMMIT
JDBC (pgJDBC)	Autocommit on	conn.setAutoCommit(false)
psycopg 3 (Python)	Autocommit off: a transaction starts on the first statement	psycopg.connect(..., autocommit=True) or with conn.transaction(): blocks
node-postgres	Autocommit on	Send BEGIN/COMMIT yourself on a single checked-out client
SQLAlchemy / Django ORM	Transaction per unit of work or per request (configurable)	Framework settings

The forgotten COMMIT. With psycopg's default, this script appears to work but changes nothing:

import psycopg

conn = psycopg.connect("dbname=bank")
conn.execute("UPDATE accounts SET balance = balance + 10 WHERE id = 3")
# psycopg sent BEGIN implicitly before the UPDATE
conn.close()            # closing without commit -> the server rolls the transaction back

The idiomatic fix is a context manager, which commits on success and rolls back on an exception:

with psycopg.connect("dbname=bank") as conn:          # commit on normal exit, rollback on exception
    conn.execute("UPDATE accounts SET balance = balance + 10 WHERE id = 3")

with psycopg.connect("dbname=bank", autocommit=True) as conn:
    with conn.transaction():                          # explicit block: BEGIN ... COMMIT
        conn.execute("UPDATE accounts SET balance = balance - 10 WHERE id = 1")
        conn.execute("UPDATE accounts SET balance = balance + 10 WHERE id = 3")
    conn.execute("VACUUM accounts")                   # VACUUM cannot run inside a transaction block

The opposite danger: idle in transaction. A driver that opens a transaction implicitly and an application that forgets to end it produce a session in the state idle in transaction. That open transaction holds its locks and its snapshot indefinitely, which blocks DDL and prevents vacuum from removing dead rows (Dead Tuples and Row Versions):

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age, left(query, 40) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;
--  pid  | usename | application_name |        state        |    xact_age     |     last_query
-- ------+---------+------------------+---------------------+-----------------+-------------------
--  4412 | app     | report-worker    | idle in transaction | 03:12:44.10812  | SELECT * FROM acc

Statements that refuse transaction blocks. A few commands manage their own transactions and fail inside BEGIN: VACUUM, CREATE DATABASE, DROP DATABASE, CREATE INDEX CONCURRENTLY, REINDEX ... CONCURRENTLY, ALTER SYSTEM, and CREATE TABLESPACE. Run them in autocommit mode:

BEGIN;
VACUUM accounts;
-- ERROR:  VACUUM cannot run inside a transaction block
ROLLBACK;

Key takeaways

• The server always autocommits statements sent outside a BEGIN block; each such statement is atomic on its own.
• "Autocommit off" is a client feature: the client sends BEGIN for you, and you must remember to COMMIT.
• psycopg 3 starts a transaction implicitly by default; JDBC, psql, and node-postgres autocommit by default.
• Forgotten transactions show up as idle in transaction and hold locks and snapshots.
• VACUUM, CREATE DATABASE, and the CONCURRENTLY commands cannot run in a transaction block.

🧪 Practice

1. (Beginner) In psql, run \set AUTOCOMMIT off, execute an UPDATE, quit with \q, reconnect, and check whether the update persisted.
2. (Intermediate) Write a Python script with psycopg 3 that updates a row without committing and then sleeps for 60 seconds. While it sleeps, find its session in pg_stat_activity and try ALTER TABLE accounts ADD COLUMN x int from another session with lock_timeout = '2s'. Explain the result.
3. (Interview) "Your ORM wraps every HTTP request in a transaction. A new endpoint calls an external payment API in the middle of the request. What problems might this cause in the database?" Hint: Consider how long the transaction stays open, which locks it holds, and what a slow third-party API does to vacuum and to concurrent requests.

Savepoints and Nested Rollbacks

Sometimes you want to undo part of a transaction without discarding all of it. A bulk import may want to skip a bad row and continue; a business operation may try an optional step and fall back if it fails. In PostgreSQL, any error aborts the entire transaction, so you need a way to mark a point you can return to. That is a savepoint: a named checkpoint inside a transaction, like a bookmark you can rewind to without closing the book.

The commands

BEGIN;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 5);

SAVEPOINT before_bonus;                                     -- mark a point
UPDATE accounts SET balance = balance - 1000 WHERE id = 3;  -- carol has 0
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
ROLLBACK TO SAVEPOINT before_bonus;                         -- undo back to the mark; transaction is usable again

UPDATE accounts SET balance = balance - 5 WHERE id = 1;     -- continue normally
RELEASE SAVEPOINT before_bonus;                             -- forget the mark (keeps the changes)
COMMIT;                                                     -- the first INSERT and the last UPDATE persist

Command	Effect
SAVEPOINT name	Starts a subtransaction and names this point
ROLLBACK TO [SAVEPOINT] name	Undoes everything after the savepoint, clears the error state; the savepoint itself remains and can be reused
RELEASE [SAVEPOINT] name	Merges the work since the savepoint into the parent; does not commit anything to disk

Nesting. Savepoints form a stack. Rolling back to an outer savepoint discards all inner ones, and releasing an outer one also releases the inner ones:

 BEGIN
  |-- INSERT a                               kept
  |-- SAVEPOINT s1
  |     |-- INSERT b                         discarded by ROLLBACK TO s1
  |     |-- SAVEPOINT s2
  |     |     |-- INSERT c                   discarded by ROLLBACK TO s1
  |-- ROLLBACK TO s1   <-- s2 is destroyed, s1 still exists
  |-- INSERT d                               kept
 COMMIT                                      result: a, d

Reusing a name is allowed: the newer savepoint shadows the older one until it is released.

Savepoints are not nested transactions. Nothing inside a savepoint is durable or visible to other sessions until the top-level transaction commits. If the outer transaction rolls back, every released savepoint goes with it.

psql's ON_ERROR_ROLLBACK. In interactive sessions, a typo inside a long transaction would normally force you to start over. psql can wrap each statement in an implicit savepoint:

\set ON_ERROR_ROLLBACK interactive        -- only in interactive sessions, not in scripts
BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELEC 1;                                  -- typo
-- ERROR:  syntax error at or near "SELEC"
UPDATE accounts SET balance = balance + 1 WHERE id = 2;   -- still works: psql rolled back only the typo
COMMIT;

Drivers offer the same idea: JDBC has autosave=always / conservative, and psycopg's nested with conn.transaction(): blocks create savepoints automatically:

with conn.transaction():                          # BEGIN
    for row in rows:
        try:
            with conn.transaction():              # SAVEPOINT _pg3_1
                conn.execute("INSERT INTO imports VALUES (%s, %s)", row)
        except psycopg.errors.UniqueViolation:    # ROLLBACK TO SAVEPOINT _pg3_1 already happened
            log.warning("duplicate %s skipped", row[0])
# COMMIT: all good rows persist

The cost of subtransactions. Each savepoint that writes data gets its own subtransaction ID. Every backend caches up to 64 subtransaction IDs in shared memory; beyond that, the cache overflows and other sessions must look up subtransaction parents in pg_subtrans on disk to check visibility. On busy systems, especially with replicas, this causes severe slowdowns (SubtransSLRU / SubtransBuffer wait events). A loop that creates a savepoint per row on a 100,000-row import is a common culprit:

-- Watch for backends with many subtransactions or an overflowed cache (PostgreSQL 16+)
SELECT pg_stat_get_backend_pid(b.id) AS pid, s.subxact_count, s.subxact_overflowed
FROM pg_stat_get_backend_idset() AS b(id),
     LATERAL pg_stat_get_backend_subxact(b.id) AS s     -- takes a backend id, not a pid
WHERE s.subxact_count > 0;

Prefer set-based alternatives when possible: INSERT ... ON CONFLICT DO NOTHING skips duplicates without any savepoint.

Key takeaways

• SAVEPOINT marks a point; ROLLBACK TO undoes work after it and clears the error state; RELEASE merges it into the parent.
• Savepoints nest as a stack and are never durable on their own: only the top-level COMMIT persists anything.
• \set ON_ERROR_ROLLBACK interactive protects long interactive transactions from typos.
• Drivers and PL/pgSQL EXCEPTION blocks create savepoints implicitly.
• More than 64 subtransactions in one transaction overflows a shared cache and can slow the whole cluster; prefer set-based error handling.

🧪 Practice

1. (Beginner) In one transaction, insert three transfers with a savepoint before each. Roll back to the second savepoint and commit. Predict which rows persist, then verify.
2. (Intermediate) Write a psycopg loop that imports 1,000 rows, 10 of which are duplicates, using one savepoint per row. Then rewrite it as a single INSERT ... SELECT ... ON CONFLICT DO NOTHING and compare runtime and the number of transaction IDs consumed (pg_current_xact_id() before and after).
3. (Interview) "A team reports that their read replicas become extremely slow whenever a certain nightly import runs on the primary. The import uses a savepoint per row. What could be the connection?" Hint: Look up subtransaction cache overflow and how replicas check the visibility of rows written by subtransactions.

Transaction Scope and Error Handling

Deciding where a transaction begins and ends is a design decision, not a detail. Too small, and related changes can be split by a crash or interleaved with other sessions; too large, and the transaction holds locks and an old snapshot for so long that it blocks other work and stops vacuum. Error handling interacts with this: in PostgreSQL, the first error inside a transaction poisons the whole transaction until you roll back.

The aborted state. After any error, the transaction refuses all further commands except ROLLBACK (or ROLLBACK TO SAVEPOINT):

BEGIN;
INSERT INTO accounts VALUES (1, 'dup', 0);
-- ERROR:  duplicate key value violates unique constraint "accounts_pkey"
SELECT 1;
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
-- ROLLBACK                       -- COMMIT on an aborted transaction silently becomes ROLLBACK

Note the last line: COMMIT reports ROLLBACK. Application code that ignores the command tag may believe it committed.

Transaction states

                 BEGIN
   (no txn) ----------------> IN TRANSACTION ---- COMMIT ----> (no txn)  changes persist
       ^                       |         ^
       |                  error|         | ROLLBACK TO SAVEPOINT
       |                       v         |
       +------- ROLLBACK ---- ABORTED ---+
                (or COMMIT, which acts as ROLLBACK)

psql shows the state in its prompt: bank=# (idle), bank=*# (in transaction), bank=!# (aborted).

DDL is transactional. Unlike Oracle or MySQL, PostgreSQL runs most DDL inside transactions, so a schema migration can be atomic:

BEGIN;
ALTER TABLE accounts ADD COLUMN currency char(3) NOT NULL DEFAULT 'EUR';
CREATE INDEX accounts_owner_idx ON accounts (owner);
UPDATE accounts SET currency = 'USD' WHERE owner = 'bob';
-- if anything fails here, the column and the index disappear too
COMMIT;

The catch: DDL takes strong locks, and those locks are held until the transaction ends (Table-Level Lock Modes).

Choosing the scope. Include in one transaction exactly the statements that must succeed or fail together, and nothing else:

Inside the transaction	Outside the transaction
Statements that must be atomic together (debit + credit + audit row)	Calls to external APIs, sending emails, file uploads
Reads whose results decide the writes (with suitable isolation or locks)	User think time ("confirm your order" screens)
	Expensive computation that does not need the database
	Large batch jobs: split into chunks, each its own transaction

Timeouts as guardrails. Set these per role or per application, not only globally:

Setting	Limits	Typical value
statement_timeout	Runtime of a single statement	5–30 s for OLTP roles
lock_timeout	Time spent waiting for one lock	1–5 s for migrations
idle_in_transaction_session_timeout	Time a session may sit idle inside an open transaction	30 s – 5 min
transaction_timeout (PostgreSQL 17+)	Total duration of a transaction	Minutes, for safety
idle_session_timeout (PostgreSQL 14+)	Idle time outside a transaction	Rarely needed with poolers

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
-- FATAL:  terminating connection due to idle-in-transaction timeout   (what the client sees)

Error handling in application code. The pattern is always the same: on any database error, roll back (or let the context manager do it), then decide whether to retry, report, or give up. The SQLSTATE code, not the message text, tells you what happened:

from psycopg import errors

def transfer(conn, src, dst, amount):
    try:
        with conn.transaction():                        # BEGIN / COMMIT, or ROLLBACK on exception
            conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
            conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
            conn.execute("INSERT INTO transfers (from_id, to_id, amount) VALUES (%s, %s, %s)",
                         (src, dst, amount))
    except errors.CheckViolation:                       # SQLSTATE 23514: balance would go negative
        raise InsufficientFunds(src)                    # business error: do not retry
    except errors.ForeignKeyViolation:                  # 23503: unknown account
        raise UnknownAccount(src, dst)
    # SerializationFailure (40001) and DeadlockDetected (40P01) are retryable: see the next topic

SQLSTATE class	Meaning	Typical reaction
23xxx	Integrity constraint violation	Business error, report to the user
40001, 40P01	Serialization failure, deadlock	Retry the whole transaction
57014	Query canceled (statement timeout)	Investigate; maybe retry once
55P03	Lock not available (NOWAIT, lock_timeout)	Retry later or skip
08xxx	Connection exception	Reconnect; the commit outcome may be unknown

Errors inside PL/pgSQL. A BEGIN ... EXCEPTION ... END block in a function creates an implicit savepoint, so an error inside it rolls back only that block:

DO $$
BEGIN
  BEGIN                                                        -- inner block = implicit savepoint
    UPDATE accounts SET balance = balance - 999 WHERE id = 3;
  EXCEPTION WHEN check_violation THEN
    RAISE NOTICE 'carol cannot pay, skipping';                 -- the UPDATE was rolled back
  END;
  UPDATE accounts SET balance = balance + 1 WHERE id = 3;      -- still runs
END $$;

Key takeaways

• After an error, a transaction accepts only ROLLBACK; a COMMIT silently rolls back.
• Most DDL is transactional in PostgreSQL, so migrations can be atomic, but their locks last until commit.
• Keep transactions short: no network calls, user interaction, or long computation inside them.
• Use statement_timeout, lock_timeout, idle_in_transaction_session_timeout, and transaction_timeout as guardrails.
• Handle errors by SQLSTATE: constraint violations are business errors, 40001/40P01 are retryable.

🧪 Practice

1. (Beginner) Trigger a unique violation inside a transaction, then run COMMIT. Observe the command tag and check that earlier statements in the transaction did not persist.
2. (Intermediate) Set idle_in_transaction_session_timeout = '5s' for your session, open a transaction, run one query, and wait 10 seconds. What happens to the session and the next query? Repeat with transaction_timeout on PostgreSQL 17+ while running SELECT pg_sleep(...) statements.
3. (Interview) "An order service charges a credit card through an external API and records the order in PostgreSQL. Where should the transaction boundary be, and what happens if the process crashes between the two steps?" Hint: Think about doing the external call outside the transaction, recording intent before and outcome after, and how an idempotency key lets you reconcile.

Idempotency and Retry Logic

Networks fail, servers restart, and PostgreSQL deliberately aborts some transactions to keep isolation guarantees (Serialization Failures and Retries). Robust applications therefore retry. But retrying is only safe if running the same operation twice has the same effect as running it once. That property is idempotency. A light switch labelled "turn on" is idempotent; one labelled "toggle" is not.

Which errors to retry

Situation	Did the transaction commit?	Safe to retry blindly?
40001 serialization failure, 40P01 deadlock	No, it was rolled back	Yes, rerun the whole transaction
55P03 lock not available	No	Yes, after a delay
Connection lost before sending COMMIT	No	Yes
Connection lost after sending COMMIT, before the reply	Unknown	Only if the operation is idempotent
Constraint violation (23xxx)	No	No: retrying gives the same error

The fourth row is the hard case. The server may have committed the transfer and crashed before replying. A blind retry would transfer the money twice.

Idempotency keys. The client generates a unique key for each logical operation (for example a UUID created when the user clicks "Pay") and sends it with every attempt. The database records the key in the same transaction as the effect, and a unique constraint turns duplicates into no-ops:

ALTER TABLE transfers ADD COLUMN idempotency_key uuid UNIQUE;   -- NULLs allowed for old rows

CREATE OR REPLACE FUNCTION do_transfer(p_key uuid, p_from int, p_to int, p_amount numeric)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  v_id bigint;
BEGIN
  INSERT INTO transfers (idempotency_key, from_id, to_id, amount)
  VALUES (p_key, p_from, p_to, p_amount)
  ON CONFLICT (idempotency_key) DO NOTHING                      -- second attempt inserts nothing
  RETURNING id INTO v_id;

  IF v_id IS NULL THEN                                          -- already processed earlier
    SELECT id INTO v_id FROM transfers WHERE idempotency_key = p_key;
    RETURN v_id;                                                -- return the original result
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
  RETURN v_id;
END $$;

SELECT do_transfer('5f0c...e1'::uuid, 1, 2, 10);   -- moves 10, returns id 7
SELECT do_transfer('5f0c...e1'::uuid, 1, 2, 10);   -- moves nothing, returns id 7 again

If two attempts with the same key run concurrently, the second INSERT waits for the first transaction's unique-index entry, then sees the conflict once the first commits, so exactly one succeeds.

Naturally idempotent statements. Some SQL is idempotent by construction, and preferring it removes the need for keys:

Not idempotent	Idempotent alternative
UPDATE accounts SET balance = balance + 10	Record the transfer with a key; derive balance from the ledger
INSERT INTO t VALUES (...)	INSERT ... ON CONFLICT DO NOTHING / DO UPDATE with a natural key
UPDATE orders SET status = 'shipped'	UPDATE orders SET status = 'shipped' WHERE id = 5 AND status = 'paid'
DELETE then INSERT	MERGE or upsert

A retry loop. Retry the whole transaction, never just the failed statement, because every earlier read in the transaction may now be stale. Use exponential backoff with jitter so that conflicting clients do not retry in lockstep:

import random, time
import psycopg
from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected, errors.LockNotAvailable)

def run_in_transaction(conn, work, max_attempts=5):
    """Run work(conn) in a transaction, retrying on transient errors."""
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.transaction():
                return work(conn)                       # all reads AND writes happen inside work()
        except RETRYABLE:
            if attempt == max_attempts:
                raise
            delay = min(2.0, 0.05 * 2 ** attempt)       # 0.1, 0.2, 0.4 ... capped at 2 s
            time.sleep(random.uniform(0, delay))        # full jitter
        # psycopg.OperationalError (connection lost) needs a new connection and an idempotent work()

Checking an unknown outcome. If the connection breaks during COMMIT, you can ask the server afterwards, provided you recorded the transaction ID before committing:

SELECT pg_current_xact_id();                 -- inside the transaction, before COMMIT: e.g. 10931
-- ... connection lost during COMMIT; reconnect ...
SELECT pg_xact_status('10931');              -- 'committed', 'aborted', or 'in progress'

An idempotency key is usually simpler and also covers the case where the client itself crashes.

Key takeaways

• Retry only transient errors (40001, 40P01, 55P03, lost connections), and always retry the entire transaction.
• A lost connection during COMMIT leaves the outcome unknown; only idempotent operations are safe to repeat.
• Idempotency keys stored under a unique constraint, in the same transaction as the effect, make any operation safe to repeat.
• Prefer naturally idempotent SQL: upserts, conditional updates, state transitions with guards.
• Use exponential backoff with jitter and a cap on attempts.

🧪 Practice

1. (Beginner) Call do_transfer twice with the same key and check that balances changed only once.
2. (Intermediate) Run do_transfer with the same key from two sessions at the same time (start a transaction in each and call the function before committing either). Describe what the second session waits for and what it returns after the first commits.
3. (Interview) "A mobile client retries a 'place order' request after a timeout, and some customers are charged twice. Design a fix spanning client, API, and database." Hint: Where is the key generated, how long is it stored, what does the API return on a duplicate, and what happens if the duplicate arrives while the first attempt is still running?

<a id="82-isolation-levels"></a>

8.2 Isolation Levels

Isolation decides what a transaction can see of other transactions that run at the same time. This subchapter names the anomalies that weak isolation allows, walks through PostgreSQL's three effective levels with two-session experiments, and ends with how to handle the errors stronger levels raise and how to choose a level.

Read Phenomena: Dirty, Non-Repeatable, Phantom

Perfect isolation would make every transaction behave as if it ran alone, one after another. That is easy to provide by actually running them one at a time, but then a single slow transaction would stop everyone. Databases instead let transactions overlap and offer isolation levels that trade some guarantees for concurrency. To compare those levels, we need names for the things that can go wrong. The SQL standard defines three read phenomena, and practice adds a few more.

Use these experiments with the sample accounts table from BEGIN, COMMIT, and ROLLBACK. Time flows downward; T1 and T2 are two sessions.

Dirty read. A transaction reads data written by another transaction that has not committed yet. If the writer rolls back, the reader acted on data that never existed.

 T1                                         T2
 BEGIN;
 UPDATE accounts SET balance = 1000
   WHERE id = 1;
                                            BEGIN;
                                            SELECT balance FROM accounts WHERE id = 1;
                                              -> 1000 under a level that allows dirty reads
                                              -> 100  in PostgreSQL (always)
 ROLLBACK;                                  -- the 1000 never existed

Non-repeatable read. A transaction reads the same row twice and gets different values, because another transaction committed a change in between.

 T1                                         T2
 BEGIN;
 SELECT balance FROM accounts WHERE id=1;
   -> 100
                                            UPDATE accounts SET balance = 70 WHERE id = 1;  (autocommit)
 SELECT balance FROM accounts WHERE id=1;
   -> 70   the row changed under T1
 COMMIT;

Phantom read. A transaction runs the same query with a condition twice and gets a different set of rows, because another transaction inserted or deleted rows that match the condition.

 T1                                         T2
 BEGIN;
 SELECT count(*) FROM accounts
   WHERE balance < 60;          -> 2
                                            INSERT INTO accounts VALUES (4, 'dan', 10);
 SELECT count(*) FROM accounts
   WHERE balance < 60;          -> 3   a "phantom" row appeared
 COMMIT;

Lost update. Two transactions read the same value, both compute a new value from it, and both write it back. The second write silently overwrites the first.

 T1                                         T2
 SELECT balance ... id=1  -> 100            SELECT balance ... id=1  -> 100
 (app computes 100 + 20)                    (app computes 100 - 30)
 UPDATE ... SET balance = 120 WHERE id=1;
                                            UPDATE ... SET balance = 70 WHERE id=1;
 final balance: 70   (should be 90: T1's +20 is lost)

Write skew. Two transactions each read an overlapping set of rows, check a rule, and then write different rows. Each write is fine on its own, but together they break the rule. The classic example: at least one doctor must stay on call; two doctors, each seeing the other still on call, both go off duty.

 T1 (Alice)                                 T2 (Bob)
 SELECT count(*) ... on_call -> 2           SELECT count(*) ... on_call -> 2
 "2 > 1, I may leave"                       "2 > 1, I may leave"
 UPDATE doctors SET on_call = false         UPDATE doctors SET on_call = false
   WHERE name = 'alice';                      WHERE name = 'bob';
 COMMIT;                                    COMMIT;
 result: nobody on call, although each transaction checked the rule

Serialization anomaly. The general case: the result of concurrently committed transactions cannot be produced by running them one at a time in any order. Lost updates and write skew are specific examples.

The standard levels and PostgreSQL

Phenomenon	Read Uncommitted (standard)	Read Committed	Repeatable Read (standard)	Repeatable Read (PostgreSQL)	Serializable
Dirty read	Possible	No	No	No	No
Non-repeatable read	Possible	Possible	No	No	No
Phantom read	Possible	Possible	Possible	No	No
Lost update	Possible	Possible	Possible	No (error)	No (error)
Write skew	Possible	Possible	Possible	Possible	No (error)

Two PostgreSQL details:

• READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED. MVCC never needs to show uncommitted data, so PostgreSQL never does.
• PostgreSQL's REPEATABLE READ is stronger than the standard requires: it uses a single snapshot, which rules out phantoms too. This level is often called snapshot isolation.

The standard defines levels by which phenomena they forbid, so an implementation may always be stricter than required.

Key takeaways

• Dirty read: seeing uncommitted data. Non-repeatable read: a row changes between reads. Phantom: the set of matching rows changes.
• Lost update and write skew are anomalies outside the original three, and they cause most real bugs.
• PostgreSQL never allows dirty reads; READ UNCOMMITTED is treated as READ COMMITTED.
• PostgreSQL's REPEATABLE READ also prevents phantoms, but still allows write skew; only SERIALIZABLE prevents every anomaly.

🧪 Practice

1. (Beginner) Reproduce the non-repeatable read and the phantom read in two psql sessions at the default isolation level.
2. (Intermediate) Reproduce the lost update with two sessions that each SELECT the balance, then UPDATE with a constant computed by hand. Then show that UPDATE ... SET balance = balance + 20 does not lose the update, and explain why.
3. (Interview) "Give a real-world example of write skew in an application you know, and explain why row locks on the rows being updated do not prevent it." Hint: The two transactions update different rows; what they conflict on is something they both read.

Read Committed

READ COMMITTED is PostgreSQL's default level and the one most applications run under, often without anyone choosing it. It gives a simple promise: you never see uncommitted data, and every statement sees data committed before that statement began. It is cheap and never raises serialization errors, but it lets the world change between your statements.

One snapshot per statement. Each statement takes a fresh snapshot of committed data. Within one statement, the view is consistent; between statements, it is not:

SHOW transaction_isolation;                 -- read committed

-- T1
BEGIN;
SELECT sum(balance) FROM accounts;          -- 150
                                            -- T2 (autocommit): INSERT INTO accounts VALUES (5, 'eve', 40);
SELECT sum(balance) FROM accounts;          -- 190: a different answer in the same transaction
COMMIT;

A report built from several queries in one READ COMMITTED transaction can therefore be internally inconsistent: totals in one query may not match details in the next.

How writes behave. Reads never block, but two transactions updating the same row must take turns. When an UPDATE, DELETE, or SELECT FOR UPDATE finds that its target row was changed by a transaction that is still running, it waits. When that transaction commits, PostgreSQL does not restart the statement; it re-reads the newest version of that row, re-checks the WHERE condition against it, and applies the update to it if the row still matches:

 T1                                               T2
 BEGIN;
 UPDATE accounts SET balance = balance + 20
   WHERE id = 1;          -- 100 -> 120
                                                  UPDATE accounts SET balance = balance - 30
                                                    WHERE id = 1;
                                                  -- waits for T1's row lock
 COMMIT;
                                                  -- wakes up, re-reads row: 120, still matches id = 1
                                                  -- writes 120 - 30 = 90. Correct.

This is why SET balance = balance + 20 is safe under READ COMMITTED: the arithmetic is evaluated against the latest committed version of the row.

Where it surprises. The re-check applies only to the rows the statement had already selected. Rows that start matching because of the concurrent change are not found:

CREATE TABLE tickets (id int PRIMARY KEY, priority int);
INSERT INTO tickets VALUES (1, 1), (2, 2);

-- T1
BEGIN;
UPDATE tickets SET priority = priority + 1;      -- 1 -> 2, 2 -> 3

                                                 -- T2
                                                 DELETE FROM tickets WHERE priority = 2;   -- waits
-- T1
COMMIT;
                                                 -- DELETE 0  (!)

T2's snapshot saw only ticket 2 with priority 2. After waiting, it re-checked ticket 2 (now priority 3, no longer matches) and skipped it. Ticket 1, which now has priority 2, was not in T2's original set, so it was never considered. T2 deletes nothing, a result no serial order would produce.

The lost update under Read Committed. The safe pattern above relies on doing the read and the write in the same statement. If the application reads first and writes a computed value later, updates are lost, because the second statement's re-check sees nothing wrong:

# Unsafe under READ COMMITTED: read-modify-write split across statements
bal = conn.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]
conn.execute("UPDATE accounts SET balance = %s WHERE id = 1", (bal + 20,))   # may overwrite a concurrent change

Three standard fixes:

-- 1. Atomic update: compute inside the statement
UPDATE accounts SET balance = balance + 20 WHERE id = 1;

-- 2. Pessimistic lock: lock the row when reading it (see 8.4)
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;     -- others wanting to write it now wait
UPDATE accounts SET balance = 120 WHERE id = 1;
COMMIT;

-- 3. Optimistic check: write only if the value is still what you read
UPDATE accounts SET balance = 120 WHERE id = 1 AND balance = 100;   -- UPDATE 0 -> someone changed it, reread and retry

A version column (version int, incremented on each write and compared in the WHERE) is the general form of option 3, and is what many ORMs call optimistic locking.

Key takeaways

• READ COMMITTED is the default: each statement sees data committed before it started.
• Different statements in one transaction can see different data; multi-query reports may be inconsistent.
• Concurrent updates to the same row wait, then re-check the WHERE clause against the newest row version.
• Single-statement read-modify-write (balance = balance + x) is safe; read-then-write across statements loses updates.
• Fix lost updates with atomic statements, SELECT ... FOR UPDATE, or optimistic version checks.

🧪 Practice

1. (Beginner) Run a transaction that executes SELECT now(), sum(balance) FROM accounts twice, while another session inserts a row in between. Explain why now() stays the same but the sum changes.
2. (Intermediate) Reproduce the tickets example. Then rerun it with T2 in REPEATABLE READ and describe the difference.
3. (Interview) "An inventory service does SELECT stock, checks stock >= qty in Java, then UPDATE products SET stock = ?. Under load, stock occasionally goes wrong. Explain the bug and propose two fixes with their trade-offs." Hint: Compare a conditional atomic UPDATE ... WHERE stock >= qty with SELECT ... FOR UPDATE, in terms of round trips and lock duration.

Repeatable Read

Many tasks need a stable view across several statements: a report whose numbers must add up, an export that must be consistent, a business rule that reads several tables before deciding. REPEATABLE READ provides this by giving the whole transaction one snapshot. Imagine taking a photograph of the database at the start and answering every question from the photograph, while the real world keeps moving.

One snapshot per transaction. The snapshot is taken by the first query in the transaction, not by BEGIN:

-- T1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;          -- 150: snapshot taken here
                                            -- T2: INSERT INTO accounts VALUES (6, 'frank', 40);
                                            -- T2: UPDATE accounts SET balance = 0 WHERE id = 2;
SELECT sum(balance) FROM accounts;          -- 150: same answer
SELECT count(*) FROM accounts;              -- does not see frank: no phantoms
COMMIT;
SELECT sum(balance) FROM accounts;          -- 140: new transaction, new snapshot

Because the snapshot is fixed at the first query, a BEGIN followed by a long pause does not "freeze" the earlier state. Start the transaction right before the queries that need consistency.

Concurrent updates cause errors, not waits-and-rechecks. If a REPEATABLE READ transaction tries to update or delete a row that another transaction changed after its snapshot, it cannot apply the change to a version it is not allowed to see. Instead of silently re-checking, it aborts:

 T1 (REPEATABLE READ)                         T2
 BEGIN ISOLATION LEVEL REPEATABLE READ;
 SELECT balance FROM accounts WHERE id=1;
   -> 100
                                              UPDATE accounts SET balance = balance + 20 WHERE id = 1;
                                              (autocommit: now 120)
 UPDATE accounts SET balance = balance - 30
   WHERE id = 1;
 ERROR:  could not serialize access due to concurrent update
 ROLLBACK;     -- retry the whole transaction

If T2 had not committed yet, T1 would wait first: if T2 then rolls back, T1 proceeds; if T2 commits, T1 gets the error. This is how REPEATABLE READ prevents lost updates: the loser must retry with fresh data (Serialization Failures and Retries).

What it does not prevent: write skew. The snapshot protects reads and same-row writes, but two transactions writing different rows never conflict:

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

-- T1 and T2 both run:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;          -- 2 in both sessions

-- T1                                                -- T2
UPDATE doctors SET on_call = false                   UPDATE doctors SET on_call = false
  WHERE name = 'alice';                                WHERE name = 'bob';
COMMIT;                                              COMMIT;   -- both succeed

SELECT * FROM doctors WHERE on_call;                 -- 0 rows: the rule is broken

The fix is either SERIALIZABLE (Serializable) or an explicit lock that makes the transactions conflict, such as SELECT ... FROM doctors WHERE on_call FOR UPDATE, which locks the rows both transactions read.

Typical uses

• Consistent reports and exports spanning several queries (pg_dump uses REPEATABLE READ internally, sharing one snapshot across all tables).
• Read-only analytics that must not see half-applied batches.
• Read-modify-write logic where a retry on conflict is acceptable and write skew is not a concern.

Long snapshots have a cost. As long as the transaction is open, its snapshot keeps every row version it might need alive, so vacuum cannot remove dead tuples newer than it, across all tables in the database (Dead Tuples and Row Versions). A multi-hour REPEATABLE READ export on a busy primary causes bloat; run it on a replica or in chunks if possible.

Key takeaways

• REPEATABLE READ uses one snapshot for the whole transaction, taken at its first query.
• It prevents non-repeatable reads and, in PostgreSQL, phantoms.
• Updating a row changed concurrently after the snapshot raises could not serialize access due to concurrent update; retry the transaction.
• It still allows write skew between transactions that write different rows.
• Long-lived snapshots hold back vacuum for the whole database.

🧪 Practice

1. (Beginner) Start a REPEATABLE READ transaction, run SELECT count(*) FROM accounts, insert a row from another session, and count again. Then commit and count once more.
2. (Intermediate) Show that the snapshot is taken at the first query, not at BEGIN: start the transaction, insert a row from a second session, and only then run the first query in the first session.
3. (Interview) "Why does pg_dump produce a consistent backup without locking out writers, and what happens to the database if a dump of a large, busy database takes six hours?" Hint: Consider which isolation level the dump uses, what a snapshot needs to keep, and what vacuum is prevented from doing.

Serializable

SERIALIZABLE is the strongest level and the simplest to reason about: whatever transactions run concurrently, the committed result is the same as if they had run one at a time in some order. If PostgreSQL cannot guarantee that, it aborts one of them. Developers can then write each transaction as if it were alone, without thinking about which interleavings might break a rule. The price is occasional aborts that must be retried, and some bookkeeping overhead.

How PostgreSQL implements it. A classic implementation would use strict two-phase locking, making readers block writers. PostgreSQL instead uses Serializable Snapshot Isolation (SSI): transactions run exactly as in REPEATABLE READ, with no extra blocking, while the server also tracks what each transaction read and detects dangerous patterns of dependencies. Details are in Serializable Snapshot Isolation.

Write skew is caught. The doctors example from the previous topic, at SERIALIZABLE:

UPDATE doctors SET on_call = true;                   -- reset

-- T1                                                -- T2
BEGIN ISOLATION LEVEL SERIALIZABLE;                  BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;  -- 2     SELECT count(*) FROM doctors WHERE on_call;  -- 2
UPDATE doctors SET on_call = false                   UPDATE doctors SET on_call = false
  WHERE name = 'alice';                                WHERE name = 'bob';
COMMIT;                                              COMMIT;
-- COMMIT (succeeds)                                 -- ERROR:  could not serialize access due to read/write dependencies among transactions
                                                     -- DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
                                                     -- HINT:  The transaction might succeed if retried.

When T2 retries, its new snapshot shows only one doctor on call, and the application logic refuses to let Bob leave. The rule holds without any explicit lock.

Where the error appears. A serialization failure may be raised by any statement, including a SELECT or the final COMMIT. Code must be prepared to retry the whole transaction from any point.

Rules for correct use

• All transactions that participate in the rule must run at SERIALIZABLE. A READ COMMITTED transaction mixing in is not tracked and can still break the invariant. Setting default_transaction_isolation = 'serializable' in postgresql.conf or per database makes this automatic.
• Keep transactions short; long serializable transactions accumulate dependencies and increase the abort rate.
• Declare read-only transactions as READ ONLY; PostgreSQL can then stop tracking them as soon as it proves they cannot be part of an anomaly.
• Use indexes on the columns in WHERE clauses. SSI tracks reads at the granularity of what was scanned: a sequential scan "reads" the whole table and conflicts with every write to it, while an index scan reads only a range.

Read-only, deferrable transactions. A long report at SERIALIZABLE could still be aborted. Declaring it READ ONLY DEFERRABLE makes PostgreSQL wait, once, at the start until it can take a snapshot that is guaranteed safe. After that the transaction runs with no tracking overhead and can never fail with a serialization error:

BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;   -- may pause briefly before the first query
SELECT owner, balance FROM accounts ORDER BY owner;          -- never raises 40001
COMMIT;

Limitations

• Not available on hot standby replicas (ERROR: cannot use serializable mode in a hot standby); use REPEATABLE READ there.
• Tracking uses shared memory (max_pred_locks_per_transaction, default 64, multiplied by max_connections). When it runs low, PostgreSQL coarsens locks from rows to pages to whole relations, which increases false-positive aborts.
• Some aborts are false positives: the transactions would have been serializable, but SSI could not prove it. The design accepts this to stay cheap.

Key takeaways

• SERIALIZABLE guarantees the result equals some one-at-a-time execution; otherwise a transaction fails with SQLSTATE 40001.
• PostgreSQL implements it with SSI: no extra blocking, but tracking of reads and possible aborts at any statement, including COMMIT.
• Every transaction involved in an invariant must use SERIALIZABLE.
• Good indexes, short transactions, and READ ONLY reduce false-positive aborts.
• READ ONLY DEFERRABLE gives long reports a serializable view that can never fail.

🧪 Practice

1. (Beginner) Reproduce the doctors write skew at REPEATABLE READ and at SERIALIZABLE, and record which session fails and at which statement.
2. (Intermediate) Repeat the experiment with a table of 100,000 doctors and no index on on_call, where each transaction updates a different doctor selected by name. Measure how often unrelated transactions fail, then add indexes and compare.
3. (Interview) "If SERIALIZABLE prevents all anomalies, why is it not the default in PostgreSQL or most other databases?" Hint: Consider retry logic in every client, false positives, overhead, behavior on replicas, and what existing applications assume.

Serialization Failures and Retries

Stronger isolation levels do not prevent conflicts; they detect them and make one transaction lose. That is a deliberate design: aborting and retrying is cheaper than blocking everyone. It only works if the application actually retries. An application that runs at REPEATABLE READ or SERIALIZABLE without a retry loop simply converts concurrency bugs into user-visible errors.

The errors to recognize

SQLSTATE	Condition name	Message	Raised by
40001	serialization_failure	could not serialize access due to concurrent update	REPEATABLE READ and SERIALIZABLE, same-row write conflict
40001	serialization_failure	could not serialize access due to read/write dependencies among transactions	SERIALIZABLE only, SSI detection
40P01	deadlock_detected	deadlock detected	Any level (Deadlock Detection and Prevention)
23505	unique_violation	duplicate key value violates unique constraint	Any level; under SERIALIZABLE, may also indicate a serialization conflict

Always match on the SQLSTATE, never on message text, which changes with the server's language setting.

Why retry the whole transaction. A serialization failure means the transaction's reads are no longer valid in any serial order. Re-executing only the failed statement would combine stale reads with new writes, the exact anomaly the level is meant to prevent. The application must repeat everything, including the reads and the decisions based on them:

 attempt 1:  BEGIN -> read A -> decide -> write B -> COMMIT  ERROR 40001 -> ROLLBACK
 attempt 2:  BEGIN -> read A (fresh) -> decide again -> write B -> COMMIT  OK
             ^ the decision may change: e.g. "Bob may not go off call after all"

A retry wrapper in Java (JDBC)

public <T> T inTransaction(DataSource ds, SqlWork<T> work) throws SQLException {
    final int maxAttempts = 5;
    for (int attempt = 1; ; attempt++) {
        try (Connection c = ds.getConnection()) {
            c.setAutoCommit(false);
            c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            try {
                T result = work.run(c);                 // all reads and writes of the transaction
                c.commit();                             // 40001 can also be thrown here
                return result;
            } catch (SQLException e) {
                c.rollback();
                String state = e.getSQLState();
                boolean retryable = "40001".equals(state) || "40P01".equals(state);
                if (!retryable || attempt == maxAttempts) throw e;
                long cap = Math.min(2000, 50L << attempt);            // exponential backoff, ms
                Thread.sleep(ThreadLocalRandom.current().nextLong(cap)); // full jitter
            }
        } catch (InterruptedException ie) {
            Thread.currentThread().interrupt();
            throw new SQLException("interrupted during retry", ie);
        }
    }
}

The Python equivalent is the run_in_transaction helper in Idempotency and Retry Logic.

Rules for the code inside the loop

• No side effects outside the database. Do not send emails or call payment APIs inside work; they would run once per attempt. Record the intent in a table (an "outbox") and act on it after commit.
• No caching of reads across attempts. Every value must be re-read inside the new attempt.
• Bounded attempts. Persistent failure signals a design problem (a hot row, a missing index); surface it rather than retry forever.

Measuring the abort rate. Count retries in the application, and watch the server-wide counters:

SELECT datname, xact_commit, xact_rollback,
       round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 2) AS rollback_pct,
       deadlocks, conflicts                                   -- conflicts = recovery conflicts on standbys
FROM pg_stat_database
WHERE datname = current_database();

xact_rollback includes all rollbacks, not only serialization failures; the application's own retry metrics are more precise. A serializable workload with a few percent retries is normal; tens of percent indicates hot spots.

Reducing failures

Cause	Remedy
Many transactions update the same "hot" row (a counter, a balance)	Shard the counter across rows, or use an append-only ledger summed later
Sequential scans make SSI track whole tables	Add indexes matching the WHERE clauses
Long transactions overlap with many others	Shorten them; move work outside
Reports aborted by concurrent writes	READ ONLY DEFERRABLE, or run them on a replica at REPEATABLE READ
Predicate locks promoted to relation level	Raise max_pred_locks_per_transaction / max_pred_locks_per_relation

Key takeaways

• 40001 and 40P01 mean "the transaction was rolled back, try again"; recognize them by SQLSTATE.
• Retry the entire transaction, including reads and decisions, with bounded attempts and jittered backoff.
• Keep external side effects out of the retried code; use an outbox table instead.
• Track retry rates; high rates point to hot rows, missing indexes, or long transactions.

🧪 Practice

1. (Beginner) Trigger a 40001 at REPEATABLE READ by updating the same row from two sessions, and read the full error with \errverbose in psql to see the SQLSTATE.
2. (Intermediate) Write a Python script that starts 20 threads, each running 100 serializable transactions that increment a single counter row through SELECT then UPDATE, using the retry helper. Report the total attempts versus successful commits, then rewrite the increment as a single UPDATE ... SET n = n + 1 and compare.
3. (Interview) "Your service runs at SERIALIZABLE and sends a confirmation email after placing an order. Customers occasionally receive two emails for one order. Why, and how do you fix it?" Hint: Where is the email sent relative to the retry loop and to COMMIT? Look up the transactional outbox pattern.

Choosing an Isolation Level

No level is best for every workload. A higher level removes classes of bugs but adds aborts and the need to retry; a lower level is faster and simpler for the client but leaves correctness to careful query design. The right choice depends on what the transaction does, and it can be made per transaction rather than once for the whole system.

Comparison

	Read Committed	Repeatable Read	Serializable
Snapshot	Per statement	Per transaction	Per transaction
Prevents	Dirty reads	+ non-repeatable reads, phantoms, lost updates	+ write skew, all anomalies
Blocks readers?	No	No	No
Raises 40001?	Never	On same-row write conflicts	On same-row conflicts and dangerous read/write patterns
Needs retry logic?	Only for deadlocks	Yes	Yes
Overhead	Lowest	Low	Moderate: predicate-lock tracking
On standbys	Yes	Yes	No

A decision guide

 Does the transaction only read?
 |-- yes: does it run several queries that must agree with each other?
 |        |-- no  -> READ COMMITTED
 |        |-- yes -> REPEATABLE READ  (or SERIALIZABLE READ ONLY DEFERRABLE on the primary)
 |
 |-- no: does it read data, decide, then write based on what it read?
          |-- no (single-statement writes, inserts, atomic updates) -> READ COMMITTED
          |-- yes: is the rule about the SAME rows it writes?
                   |-- yes -> READ COMMITTED + SELECT FOR UPDATE, or REPEATABLE READ + retry
                   |-- no (rule spans other rows: quotas, schedules, uniqueness across rows)
                            -> SERIALIZABLE + retry  (or an explicit lock / constraint)

Common workloads

Workload	Recommended	Why
Typical web CRUD with single-row updates	READ COMMITTED	Atomic statements are already safe; no retry plumbing needed
Counters, balances updated in place	READ COMMITTED with SET x = x + n	Same-row conflicts serialize through the row lock
Multi-query dashboards, exports	REPEATABLE READ	Consistent numbers across queries
Booking, scheduling, quotas, "at least one" rules	SERIALIZABLE, or a constraint (exclusion, unique)	Rules span multiple rows: write skew risk
Financial ledgers with complex checks	SERIALIZABLE	Simplest way to be provably correct
Queue workers	READ COMMITTED + FOR UPDATE SKIP LOCKED	Locks, not isolation, distribute the work (SKIP LOCKED and NOWAIT)

Prefer constraints when they fit. Many write-skew cases have a declarative fix that works at any level: a UNIQUE index for "only one active subscription per user", an exclusion constraint for "no overlapping bookings" (Exclusion Constraints). The database enforces those with locks on the index, so no isolation trick is needed.

Setting the level

BEGIN ISOLATION LEVEL SERIALIZABLE;                                  -- one transaction
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- rest of this session
ALTER ROLE reporting SET default_transaction_isolation = 'repeatable read'; -- every session of a role
ALTER DATABASE bank SET default_transaction_isolation = 'serializable';     -- every session in a database

Mixing levels is allowed, but remember: only transactions running at SERIALIZABLE are protected from each other by SSI.

Key takeaways

• READ COMMITTED is fine when every write is a self-contained statement or protected by row locks.
• REPEATABLE READ is for consistent multi-query reads and same-row read-modify-write with retries.
• SERIALIZABLE is for rules that span multiple rows; all participating transactions must use it.
• Prefer declarative constraints over isolation tricks when the rule can be expressed as one.
• The level can be set per transaction, session, role, or database.

🧪 Practice

1. (Beginner) For each of these, choose a level and justify it: a nightly CSV export of all orders; incrementing a page-view counter; checking that a meeting room is free before inserting a booking.
2. (Intermediate) Implement the meeting-room rule three ways: SERIALIZABLE with a check query, READ COMMITTED with a lock on the room row, and an exclusion constraint. Test each with two concurrent sessions booking overlapping slots.
3. (Interview) "A colleague proposes switching the whole database to SERIALIZABLE to fix a race condition. What questions would you ask before agreeing?" Hint: Retry logic in all clients, replicas, current abort and deadlock rates, whether a constraint or a targeted lock would solve the specific race.

<a id="83-multiversion-concurrency-control"></a>

8.3 Multiversion Concurrency Control

The isolation levels in 8.2 are promises; this subchapter explains the machinery that keeps them. It covers how PostgreSQL stores several versions of each row, how transaction IDs and snapshots decide which version a query sees, why old versions pile up, how serializable isolation is layered on top, and how this design compares with databases that rely on locks or undo logs.

MVCC Fundamentals

Suppose a long report is summing every account while a transfer moves money between two of them. If the report reads the debited account before the transfer and the credited account after it, the total is wrong. The simplest fix is locking: the report locks every row it reads, and the transfer waits. But then a one-hour report blocks all writes for an hour, and every write blocks every reader of that row. Multiversion concurrency control (MVCC) avoids this by keeping several versions of a row at the same time. Writers create new versions; readers keep reading the version that was current when they started.

A library analogy: instead of one copy of a book that only one person can hold, the library keeps every edition. A reader who started with the 2023 edition keeps reading it even after the 2024 edition is shelved. New readers get the latest edition. Old editions are thrown away (by vacuum) once nobody is reading them.

The core rule. In PostgreSQL:

Operation	What physically happens
INSERT	Writes a new tuple with xmin = the inserting transaction's ID
DELETE	Does not remove the tuple; sets its xmax = the deleting transaction's ID
UPDATE	A DELETE plus an INSERT: sets xmax on the old tuple and writes a new tuple, linked from the old one through ctid
SELECT	Reads tuples and keeps only those visible to its snapshot (Tuple Visibility Rules)

The tuple header fields involved were introduced in Tuple Headers and Visibility Info.

One row, several versions

 accounts, row id = 1                                         visible to
 -------------------------------------------------------      ---------------------------------
 (0,1)  xmin=900  xmax=950  balance=100  ctid->(0,4)          snapshots taken before 950 committed
 (0,4)  xmin=950  xmax=987  balance=70   ctid->(0,7)          snapshots between 950 and 987
 (0,7)  xmin=987  xmax=0    balance=90   ctid->(0,7)          snapshots after 987 committed (current)

 Txn 950: UPDATE ... balance = 70     Txn 987: UPDATE ... balance = 90

A report that started while transaction 950 was still running reads (0,1) and sees 100, regardless of how many updates happen afterward.

What MVCC gives you

• Readers never block writers, and writers never block readers. A SELECT takes no row locks and never waits for an UPDATE.
• Consistent snapshots for free. REPEATABLE READ simply keeps using one snapshot.
• Cheap rollback. An aborted transaction's tuples are simply never visible; nothing has to be undone at rollback time.

What it costs

• Writers still block writers on the same row: two updates of row 1 must take turns (Row-Level Locks).
• Old versions take space until vacuum removes them (Dead Tuples and Row Versions).
• Every update writes a whole new tuple and, unless it is a HOT update, new index entries (HOT Updates and Fillfactor).
• Visibility checks on every read, which is why index entries alone cannot answer a query without the visibility map (Index-Only Scans).

Seeing versions accumulate

CREATE EXTENSION IF NOT EXISTS pageinspect;

UPDATE accounts SET balance = balance WHERE id = 1;     -- a no-op change still writes a new version
UPDATE accounts SET balance = balance WHERE id = 1;

SELECT lp, t_xmin, t_xmax, t_ctid,
       CASE WHEN t_xmax = 0 THEN 'live' ELSE 'old version' END AS status
FROM heap_page_items(get_raw_page('accounts', 0))
ORDER BY lp;
--  lp | t_xmin | t_xmax | t_ctid |   status
-- ----+--------+--------+--------+-------------
--   1 |  10900 |  10944 | (0,4)  | old version     -- alice, original
--   2 |  10900 |      0 | (0,2)  | live            -- bob
--   3 |  10900 |      0 | (0,3)  | live            -- carol
--   4 |  10944 |  10945 | (0,5)  | old version     -- alice, first update
--   5 |  10945 |      0 | (0,5)  | live            -- alice, current

Key takeaways

• MVCC keeps multiple versions of each row so that readers and writers do not block each other.
• DELETE marks a tuple with xmax; UPDATE marks the old tuple and inserts a new one.
• Readers choose versions by comparing xmin/xmax with their snapshot.
• Writers of the same row still wait for each other.
• The price is extra space and write volume, reclaimed later by vacuum.

🧪 Practice

1. (Beginner) Update one row five times and use heap_page_items to list all its versions. Which one has t_xmax = 0?
2. (Intermediate) In session A, start a REPEATABLE READ transaction and read row 1. In session B, update row 1 three times. Back in A, read row 1 again, then show with heap_page_items which version A is reading.
3. (Interview) "Why can PostgreSQL roll back a transaction that modified a million rows almost instantly, while some other databases take as long to roll back as the transaction took to run?" Hint: Compare marking a transaction as aborted in a status log with applying undo records to restore old values.

Transaction IDs and Snapshots

To decide whether a version is visible, a reader needs two things: an identity for each writer and a record of which writers had finished when the reader started. PostgreSQL uses transaction IDs (XIDs) for the first and snapshots for the second.

Transaction IDs. Every transaction that writes gets a 32-bit XID from a global counter. IDs are assigned lazily: a read-only transaction never consumes one and uses only a cheap, local virtual transaction ID (vxid, shown as backendID/localNumber):

BEGIN;
SELECT pg_current_xact_id_if_assigned();   -- NULL: no XID yet, this transaction has only read
SELECT virtualtransaction FROM pg_locks WHERE pid = pg_backend_pid() LIMIT 1;   -- e.g. 5/112
UPDATE accounts SET balance = balance WHERE id = 2;
SELECT pg_current_xact_id_if_assigned();   -- 10946: the first write assigned an XID
COMMIT;

Function	Returns
pg_current_xact_id()	The current XID as xid8 (64-bit: epoch + 32-bit XID), assigning one if needed
pg_current_xact_id_if_assigned()	The XID, or NULL if the transaction has not written
pg_xact_status(xid8)	committed, aborted, or in progress
age(xid)	How many transactions ago a 32-bit XID was assigned
txid_current()	Older name for pg_current_xact_id(), still available

The tuple header stores only the 32-bit part. XIDs compare in a circular way (each XID sees 2 billion XIDs as "older" and 2 billion as "newer"), which is why very old tuples must be frozen before the counter wraps around (Transaction ID Wraparound and Freezing).

The commit status of each XID is stored in the commit log (pg_xact directory), 2 bits per transaction: in progress, committed, aborted, or sub-committed.

Snapshots. A snapshot records which transactions had finished at a point in time. It needs only three values:

Field	Meaning
xmin	The oldest XID that was still running; every XID below it has finished
xmax	One past the newest finished XID; every XID at or above it is treated as still running
xip	The list of XIDs between xmin and xmax that were still running

SELECT pg_current_snapshot();
--  pg_current_snapshot
-- ----------------------------
--  10940:10950:10940,10942,10947        -- xmin : xmax : in-progress list

 XID:     ... 10939 | 10940  10941  10942  10943 ... 10946  10947  10948  10949 | 10950  10951 ...
                    ^xmin                                                       ^xmax
 status:  finished  | run    fin    run    fin       fin    run    fin    fin   | not started yet
                     (xip)         (xip)                   (xip)                  (treated as running)

A transaction is "in the past" for this snapshot when its XID is below xmax and not in xip. Its changes are visible if it committed. Anything else is invisible, even if it commits a microsecond later.

When snapshots are taken

Isolation level	Snapshot taken
READ COMMITTED	At the start of each statement
REPEATABLE READ, SERIALIZABLE	At the first statement of the transaction, reused until the end

Command IDs. Inside one transaction, each statement gets a command ID (cid). A statement does not see rows it inserted itself during its own execution, which prevents, for example, INSERT INTO t SELECT * FROM t from looping forever. The tuple header's t_cid records the inserting or deleting command.

Sharing a snapshot. A transaction can export its snapshot so that other sessions see exactly the same data. Parallel pg_dump uses this to dump different tables from several connections consistently:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();             -- '00000005-0000002A-1'
-- keep A open while others use the snapshot

-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000005-0000002A-1';   -- must be the first statement
SELECT count(*) FROM accounts;           -- sees exactly what A sees

Key takeaways

• Writing transactions get a 32-bit XID; read-only transactions use only a virtual ID and consume no XIDs.
• The commit log (pg_xact) records whether each XID committed or aborted.
• A snapshot is xmin:xmax:xip; anything at or above xmax or listed in xip was still running.
• READ COMMITTED takes a snapshot per statement; REPEATABLE READ and SERIALIZABLE one per transaction.
• pg_export_snapshot and SET TRANSACTION SNAPSHOT let several sessions share one view.

🧪 Practice

1. (Beginner) Run SELECT pg_current_snapshot() in a session while another session holds an open transaction that has written a row. Identify the other transaction's XID in the output.
2. (Intermediate) Show that read-only transactions do not consume XIDs: record pg_current_xact_id() in one session, run 1,000 SELECT statements in autocommit mode from another, then 1,000 single-row UPDATE statements, checking the counter after each batch.
3. (Interview) "Why does a snapshot need a list of in-progress transactions, rather than just a single 'last committed XID' number?" Hint: XIDs are assigned at start but transactions commit in a different order. Construct a case where XID 101 commits before XID 100.

Tuple Visibility Rules

Every row a query touches passes a visibility test. The test combines the tuple's header (xmin, xmax, flags) with the reader's snapshot and the commit log. Understanding it explains almost every MVCC behavior: why an uncommitted insert is invisible, why a deleted row still appears in an old snapshot, and why a row locked with FOR UPDATE is not deleted.

The rules in words. A tuple is visible to a snapshot when both of these hold:

1. Its creator is visible: xmin is the reader's own transaction (and the inserting command came earlier), or xmin committed and is "in the past" for the snapshot.
2. Its deleter is not visible: xmax is empty, or aborted, or only a row lock, or still running from the snapshot's point of view, or it is the reader's own transaction but the deleting command comes later.

The rules as code. This simplified Python model leaves out subtransactions and multixacts but follows PostgreSQL's logic:

def in_past(xid, snap):
    """True if xid had finished when the snapshot was taken."""
    return xid < snap.xmax and xid not in snap.xip

def visible(t, snap, me, cur_cid, clog):
    # --- rule 1: was the inserting transaction visible? ---
    if t.xmin == me:
        if t.cmin >= cur_cid:                 # inserted by this or a later command of my transaction
            return False
    elif not (in_past(t.xmin, snap) and clog[t.xmin] == "committed"):
        return False                          # inserter aborted, still running, or started after snapshot

    # --- rule 2: was the deleting transaction visible? ---
    if t.xmax == 0 or t.xmax_is_lock_only:    # never deleted, or only locked (SELECT FOR UPDATE)
        return True
    if t.xmax == me:
        return t.cmax >= cur_cid              # deleted by a later command of mine: still visible now
    if clog[t.xmax] == "aborted":
        return True                           # the delete was rolled back
    if not in_past(t.xmax, snap):
        return True                           # deleter still running (or later) for this snapshot
    return False                              # deleter committed before the snapshot: row is gone

Walking through cases. Snapshot 1002:1005:1002 (1002 is the only other transaction still running), reader is transaction 1004:

xmin (status)	xmax (status)	Visible?	Reason
990 (committed)	0	Yes	Old committed insert, never deleted
1002 (committed)	0	No	1002 was running at snapshot time, even though it has since committed
990 (committed)	1003 (committed)	No	Deleted by a transaction in the past
990 (committed)	1002 (committed)	Yes	Deleter was running at snapshot time
990 (committed)	1001 (aborted)	Yes	The delete was rolled back
1001 (aborted)	0	No	Inserter rolled back
1004 (me, earlier cmd)	0	Yes	Own insert
990 (committed)	1006 (running)	Yes	Deleter started after the snapshot

Hint bits speed up the lookup. Checking clog[...] for every tuple would be slow, so the first reader to learn a transaction's final status stores it in the tuple's t_infomask (XMIN_COMMITTED, XMIN_INVALID, XMAX_COMMITTED, XMAX_INVALID). Later readers skip the commit log entirely. The flags can be inspected:

SELECT lp, t_xmin, t_xmax,
       (t_infomask & 256)  <> 0 AS xmin_committed,     -- HEAP_XMIN_COMMITTED  0x0100
       (t_infomask & 512)  <> 0 AS xmin_invalid,       -- HEAP_XMIN_INVALID    0x0200
       (t_infomask & 1024) <> 0 AS xmax_committed,     -- HEAP_XMAX_COMMITTED  0x0400
       (t_infomask & 2048) <> 0 AS xmax_invalid,       -- HEAP_XMAX_INVALID    0x0800
       (t_infomask & 128)  <> 0 AS xmax_lock_only      -- HEAP_XMAX_LOCK_ONLY  0x0080
FROM heap_page_items(get_raw_page('accounts', 0));

Locks reuse xmax. SELECT ... FOR UPDATE writes the locker's XID into xmax with the LOCK_ONLY flag. Readers ignore it (rule 2, first line), while writers see that the row is locked and wait. When several transactions share a row lock (FOR SHARE), xmax holds a multixact ID that points to a list of lockers, stored in pg_multixact.

Every visibility check needs the heap. Index entries carry no xmin/xmax. An index scan therefore finds candidate tuples through the index and then visits the heap to test them, unless the visibility map marks the page all-visible (Free Space Map and Visibility Map).

Key takeaways

• A tuple is visible when its inserter is visible to the snapshot and its deleter is not.
• "Visible" means committed and finished before the snapshot: a commit that happens later does not count.
• Own changes are visible from the next command on, tracked with command IDs.
• Hint bits cache commit status in the tuple so later readers avoid the commit log.
• Row locks reuse xmax with a lock-only flag; shared locks use multixact IDs.

🧪 Practice

1. (Beginner) Using the table above, decide visibility for a tuple with xmin = 1003 (committed) and xmax = 0, and for one with xmin = 999 (committed) and xmax = 1004 (me, earlier command).
2. (Intermediate) Run SELECT * FROM accounts WHERE id = 2 FOR UPDATE in an open transaction, then inspect the tuple with heap_page_items from another session. Decode t_infomask and confirm the lock-only bit.
3. (Interview) "After a bulk load of 50 million rows, the first SELECT count(*) takes much longer than the second, and it generates WAL even though it only reads. Explain." Hint: What does the first reader learn about each tuple, where does it record it, and what happens when a page is modified with wal_log_hints or data checksums enabled?

Dead Tuples and Row Versions

Every UPDATE and DELETE leaves an old version behind. As long as some running transaction might still need it, the version must stay. Once no snapshot can see it anymore, it is a dead tuple: wasted space that still slows down scans and indexes until vacuum removes it. Most PostgreSQL bloat problems are not really about vacuum being slow; they are about something preventing old versions from becoming removable.

Live, recently dead, dead

State	Meaning	Can vacuum remove it?
Live	Current version, visible to new snapshots	No
Recently dead	Deleted by a committed transaction, but some snapshot still in use might see it	No
Dead	Deleted, and no current or future snapshot can see it	Yes
Aborted insert	Inserted by a transaction that rolled back	Yes

The xmin horizon. Vacuum computes the oldest XID any active snapshot might still need, the xmin horizon (also called the removable cutoff). Tuples deleted by transactions older than the horizon are dead; newer ones are only recently dead.

 XID time ------------------------------------------------------------------->
          |<-- versions deleted here are DEAD -->|<-- recently dead (kept) -->| now
                                                 ^
                                  oldest snapshot still in use (xmin horizon)
                                  e.g. a REPORT transaction started 3 hours ago

One old snapshot holds back cleanup for every table in the database, and some sources hold it back cluster-wide.

What holds the horizon back

Source	Where to see it	Fix
Long-running transaction or query	pg_stat_activity.backend_xmin, xact_start	Shorten it, statement_timeout, transaction_timeout
Session idle in transaction	pg_stat_activity.state	idle_in_transaction_session_timeout
Replication slot not being consumed	pg_replication_slots.xmin, catalog_xmin	Drop unused slots; max_slot_wal_keep_size
Standby query with hot_standby_feedback = on	pg_stat_replication.backend_xmin	Limit long queries on the standby
Forgotten prepared transaction	pg_prepared_xacts	COMMIT PREPARED / ROLLBACK PREPARED

A single query finds the worst offender in each category:

SELECT 'session' AS source, pid::text AS id, age(backend_xmin) AS xmin_age, state,
       now() - xact_start AS duration
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'replication slot', slot_name, age(xmin), active::text, NULL
FROM pg_replication_slots WHERE xmin IS NOT NULL
UNION ALL
SELECT 'prepared xact', gid, age(transaction), NULL, now() - prepared
FROM pg_prepared_xacts
ORDER BY xmin_age DESC NULLS LAST
LIMIT 5;
--       source      |  id   | xmin_age |        state        |    duration
-- ------------------+-------+----------+---------------------+-----------------
--  session          | 4412  |   812455 | idle in transaction | 03:12:44.10812
--  session          | 5120  |       12 | active              | 00:00:00.0131

Demonstrating it

-- Session A: hold an old snapshot
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1 FROM accounts LIMIT 1;

-- Session B: create dead versions, then try to clean them
UPDATE accounts SET balance = balance;              -- every row gets a new version
VACUUM (VERBOSE) accounts;
-- INFO:  finished vacuuming "bank.public.accounts": index scans: 0
-- tuples: 0 removed, 6 remain, 3 are dead but not yet removable
-- removable cutoff: 10951, which was 4 XIDs old when operation ended

-- Session A
COMMIT;

-- Session B
VACUUM (VERBOSE) accounts;
-- tuples: 3 removed, 3 remain, 0 are dead but not yet removable

Counting dead tuples. The statistics views give estimates without scanning the table:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Pruning between vacuums. Vacuum is not the only cleaner. When a query reads a page that is nearly full and contains dead tuples, PostgreSQL performs page pruning: it removes dead tuples within that one page on the spot. Pruning frees space inside the page for new versions but cannot remove index entries; only vacuum does that.

Key takeaways

• Old versions become removable only when no snapshot can see them: the xmin horizon decides.
• Long transactions, idle-in-transaction sessions, stale replication slots, standby feedback, and prepared transactions hold the horizon back.
• VACUUM VERBOSE reports "dead but not yet removable" tuples and the cutoff it used.
• pg_stat_user_tables.n_dead_tup estimates dead tuples per table.
• Page pruning cleans single pages during normal reads; vacuum handles indexes and whole tables.

🧪 Practice

1. (Beginner) Reproduce the two-session demo and note the "not yet removable" count with and without the open transaction.
2. (Intermediate) On a test server with wal_level = logical, create a logical replication slot with pg_create_logical_replication_slot('test', 'pgoutput'), generate updates, and show that vacuum cannot remove dead catalog tuples. Drop the slot afterward. Which column of pg_replication_slots explains this?
3. (Interview) "A table with a stable 100,000 rows has grown from 50 MB to 20 GB over a month, and autovacuum runs on it every minute. What is the most likely cause and how do you confirm it?" Hint: Autovacuum running often but removing nothing; check the vacuum log for "not yet removable" and look for the oldest backend_xmin, slot, or prepared transaction.

Serializable Snapshot Isolation

Snapshot isolation (PostgreSQL's REPEATABLE READ) prevents most anomalies, but not write skew. Researchers found that every anomaly under snapshot isolation has a recognizable shape in the graph of dependencies between transactions. Serializable Snapshot Isolation (SSI), added in PostgreSQL 9.1, runs transactions under snapshot isolation and watches for that shape, aborting one transaction when it appears. It turns a known weakness into a detectable condition, without making readers block anyone.

Read/write dependencies. The key dependency is the rw-antidependency (rw-conflict): T1 reads a version of some data, and T2, running concurrently, writes a newer version T1 did not see. T1 must come before T2 in any equivalent serial order, because T1 did not see T2's write.

   T1 --rw--> T2       "T1 read something that T2 later overwrote; T1 must precede T2"

The dangerous structure. Theory shows that a non-serializable execution under snapshot isolation always contains a transaction with an incoming and an outgoing rw-conflict, both from concurrent transactions:

            rw              rw
     Tin --------> Tpivot --------> Tout
     (read what     (read what
      pivot wrote)   Tout wrote)

  If Tout commits first, the cycle cannot be serialized -> abort one (usually the pivot)

In the doctors example, each transaction read the row the other wrote: T1 rw-> T2 and T2 rw-> T1. Each is a pivot, which forms a cycle, so one must die.

Tracking reads with SIRead locks. To detect rw-conflicts, PostgreSQL must remember what each serializable transaction read. It records SIRead locks (predicate locks). Despite the name, they never block anything; they are markers checked by writers:

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;

-- Session B
SELECT locktype, relation::regclass, page, tuple, mode
FROM pg_locks WHERE mode = 'SIReadLock';
--  locktype | relation | page | tuple |    mode
-- ----------+----------+------+-------+------------
--  relation | doctors  |      |       | SIReadLock     -- a seq scan read the whole table

With an index on the searched column, the lock covers only the index pages and tuples actually read:

CREATE INDEX ON doctors (on_call);
-- (on a large table, the planner now uses the index)
--  locktype | relation           | page | tuple |    mode
--  page     | doctors_on_call_idx |    1 |       | SIReadLock   -- a range of the index, not the table
--  tuple    | doctors             |    0 |     1 | SIReadLock

Granularity and promotion. SIRead locks exist at tuple, page, and relation level. When a transaction holds too many fine-grained locks, they are combined into coarser ones:

Setting	Default	Meaning
max_pred_locks_per_transaction	64	Average shared-memory slots per transaction (total pool = this × max_connections)
max_pred_locks_per_relation	-2	Promote to a relation lock past this many page/tuple locks (negative: max_pred_locks_per_transaction / 2)
max_pred_locks_per_page	2	Promote tuple locks to a page lock past this many on one page

Coarser locks overlap with more writes, which increases false-positive aborts. Long transactions also keep their SIRead locks until all overlapping transactions finish, which is one more reason to keep them short.

Why it is fast enough. Reads take no blocking locks, so SERIALIZABLE throughput is usually close to REPEATABLE READ, minus the retries. The overhead is memory for SIRead locks and the conflict-graph bookkeeping, plus the work wasted by aborted transactions.

Key takeaways

• SSI runs transactions under snapshot isolation and aborts one when a dangerous pattern of rw-conflicts appears.
• An rw-conflict means one transaction read data that a concurrent transaction overwrote.
• A pivot with both an incoming and an outgoing rw-conflict signals a possible anomaly.
• SIRead locks record reads, appear in pg_locks, and never block.
• Indexes keep SIRead locks narrow; promotion to page or relation locks raises false positives.

🧪 Practice

1. (Beginner) Run a serializable SELECT on a small table and list the resulting SIReadLock entries in pg_locks. Commit, and check whether they disappear immediately.
2. (Intermediate) Load 100,000 rows into a table, run a serializable transaction that reads 500 rows through an index range scan, and inspect the lock granularity. Repeat with SET enable_indexscan = off; SET enable_bitmapscan = off; and compare.
3. (Interview) "Explain why SSI can abort a transaction even when the actual interleaving would have been serializable." Hint: SSI looks for the dangerous structure, not for actual cycles, and lock promotion merges reads of unrelated rows.

MVCC vs. Locking Databases

Not every database solves concurrency the PostgreSQL way. Knowing the alternatives helps when you migrate an application, read advice written for another system, or explain why PostgreSQL needs vacuum when others do not. There are two broad families: databases that serialize access with locks, and databases that use multiple versions, which in turn differ in where old versions are kept.

Two-phase locking (2PL). The classic approach: before reading a row, a transaction takes a shared lock; before writing, an exclusive lock; it releases them only at commit ("strict 2PL"). Serializability follows directly, but readers and writers block each other. To prevent phantoms, the database also locks ranges of index keys. SQL Server's default READ COMMITTED and Db2 work this way.

 2PL:   T1 reads row 1 (S lock) ......................... COMMIT (release)
        T2 wants to update row 1 (X lock) -> waits ------> proceeds
 MVCC:  T1 reads row 1 (no lock, uses snapshot) ......... COMMIT
        T2 updates row 1 immediately, creating a new version

Three MVCC designs

	PostgreSQL	Oracle, MySQL InnoDB	SQL Server (RCSI / snapshot)
Where the newest version lives	Heap (as a new tuple)	In place, in the table/clustered index	In place
Where old versions live	Heap, next to the new one	Undo log / rollback segments	Version store in tempdb
Reader of an old snapshot	Finds the old tuple directly	Applies undo records to reconstruct it	Follows a version chain into tempdb
Cleanup of old versions	VACUUM scans tables	Purge of undo when no snapshot needs it	Background cleanup of the version store
Rollback cost	Constant: mark aborted	Proportional to work: apply undo	Proportional to work
Long-running reader	Table and index bloat	"Snapshot too old" (Oracle ORA-01555) or growing undo (InnoDB history list)	tempdb growth
Index entries per update	New entries unless HOT	Usually unchanged if keys unchanged	Unchanged if keys unchanged

Trade-offs of PostgreSQL's design

Advantages	Disadvantages
Instant rollback and crash recovery without undo	Dead tuples and bloat; vacuum is mandatory
Readers of old snapshots never need reconstruction	Updates write full new tuples and often new index entries (write amplification)
Simple, uniform storage: one place for all versions	32-bit XIDs require freezing to avoid wraparound
No "snapshot too old" errors	Visibility checks require heap access (mitigated by the visibility map)

Default isolation in other systems

Database	Default level	Notes
PostgreSQL	Read Committed (MVCC)	RR = snapshot isolation; Serializable = SSI
Oracle	Read Committed (MVCC)	Its "serializable" is snapshot isolation (allows write skew)
MySQL InnoDB	Repeatable Read (MVCC)	Locking reads and gap locks for writes; SERIALIZABLE turns plain selects into locking reads
SQL Server	Read Committed (locking)	READ_COMMITTED_SNAPSHOT switches to versioning; Azure SQL enables it by default
CockroachDB	Serializable	Distributed MVCC with timestamp ordering; Read Committed optional in newer versions

The lesson for portability: "Repeatable Read" or "Serializable" mean different things in different products. Test the specific anomaly you care about rather than trusting the name.

Key takeaways

• Locking databases (strict 2PL) give serializability by making readers and writers block each other.
• MVCC systems differ in where old versions live: in the heap (PostgreSQL), in undo logs (Oracle, InnoDB), or in a version store (SQL Server).
• PostgreSQL's design gives instant rollback and no "snapshot too old", at the price of bloat and vacuum.
• Isolation level names are not portable: Oracle's "serializable" is snapshot isolation, and InnoDB's Repeatable Read behaves differently from PostgreSQL's.

🧪 Practice

1. (Beginner) Draw a timeline of two transactions, one long read and one update of the same row, under strict 2PL and under PostgreSQL MVCC. Mark where each one waits.
2. (Intermediate) Explain why a long-running analytical query in PostgreSQL causes table bloat while the same query in Oracle risks ORA-01555 snapshot too old. Which resource fills up in each system?
3. (Interview) "You are migrating an application from MySQL InnoDB to PostgreSQL, both at their default isolation levels. What concurrency behavior might change?" Hint: Compare the default levels, how each handles a read-modify-write in two statements, and InnoDB's gap locks versus PostgreSQL's lack of them.

<a id="84-locking"></a>

8.4 Locking

MVCC keeps readers and writers apart, but some operations still have to take turns: two updates of the same row, a schema change on a table in use, two workers claiming the same job. This subchapter covers PostgreSQL's table and row locks, the SQL clauses that take them explicitly, application-defined advisory locks, deadlocks, how to monitor locks, and how to change schemas on busy tables without blocking traffic.

Table-Level Lock Modes

Every statement that touches a table first takes a lock on the table itself, even a plain SELECT. These locks are not about protecting rows; they protect the table's structure and existence. A SELECT must be sure the table is not dropped or rewritten underneath it, and an ALTER TABLE must be sure nobody is reading a column it is about to remove. To allow as much concurrency as possible, PostgreSQL has eight lock modes of increasing strength, and each statement takes the weakest mode that keeps it safe.

Think of the modes as signs on a meeting room door: "people reading inside, others welcome" is compatible with more readers, while "renovation in progress, no entry" conflicts with everyone.

The eight modes and who takes them

Mode	Taken by
ACCESS SHARE	SELECT, COPY TO
ROW SHARE	SELECT ... FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE
ROW EXCLUSIVE	INSERT, UPDATE, DELETE, MERGE, COPY FROM
SHARE UPDATE EXCLUSIVE	VACUUM (not FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, ALTER TABLE ... VALIDATE CONSTRAINT, ALTER TABLE ... SET (storage parameter), DETACH PARTITION CONCURRENTLY (first phase)
SHARE	CREATE INDEX (without CONCURRENTLY)
SHARE ROW EXCLUSIVE	CREATE TRIGGER, ALTER TABLE ... ADD FOREIGN KEY (on both tables)
EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE	DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, REINDEX (without CONCURRENTLY), REFRESH MATERIALIZED VIEW, most ALTER TABLE forms, LOCK TABLE without a mode

Despite their names, ROW SHARE and ROW EXCLUSIVE are table-level locks. They signal "I will lock or modify rows in this table" so that stronger table locks know to wait.

Conflict matrix. An "X" means the two modes cannot be held at the same time by different transactions:

Requested \ Held	AS	RS	RE	SUE	S	SRE	E	AE
ACCESS SHARE (AS)								X
ROW SHARE (RS)							X	X
ROW EXCLUSIVE (RE)					X	X	X	X
SHARE UPDATE EXCLUSIVE (SUE)				X	X	X	X	X
SHARE (S)			X	X		X	X	X
SHARE ROW EXCLUSIVE (SRE)			X	X	X	X	X	X
EXCLUSIVE (E)		X	X	X	X	X	X	X
ACCESS EXCLUSIVE (AE)	X	X	X	X	X	X	X	X

Reading the matrix answers practical questions:

• SELECT (AS) conflicts only with AE. Reads are blocked only by drops, rewrites, and most ALTER TABLE.
• CREATE INDEX (S) conflicts with RE: it blocks all writes for its whole duration, but not reads. CREATE INDEX CONCURRENTLY (SUE) blocks neither (Concurrent Index Creation).
• SUE conflicts with itself: two VACUUMs on one table, or VACUUM and ANALYZE together, cannot run at the same time.
• S does not conflict with itself: several CREATE INDEX on the same table can run in parallel.

Locks last until the end of the transaction. A lock taken by any statement is held until COMMIT or ROLLBACK, not until the statement ends:

BEGIN;
ALTER TABLE accounts ADD COLUMN note text;   -- instant, takes ACCESS EXCLUSIVE
SELECT pg_sleep(30);                         -- every query on accounts now waits 30 s
COMMIT;

The queue effect. Lock requests are granted in order. A waiting AE request blocks every later request on the table, even compatible ones such as SELECT. One slow transaction holding AS plus one waiting ALTER TABLE is enough to stall all traffic. That chain, and how to diagnose it, is shown in Detecting Lock Contention.

Explicit table locks. LOCK TABLE takes a mode manually, inside a transaction:

BEGIN;
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;   -- allow reads, block other writers and itself
-- ... compute something that requires that nobody else writes accounts meanwhile ...
COMMIT;

BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE NOWAIT;
-- ERROR:  could not obtain lock on relation "accounts"     (if anyone else holds any lock on it)
ROLLBACK;

Explicit table locks are rarely needed in application code; row locks and isolation levels are usually more precise.

Key takeaways

• Every statement takes a table-level lock; eight modes range from ACCESS SHARE (SELECT) to ACCESS EXCLUSIVE (most DDL).
• SELECT is blocked only by ACCESS EXCLUSIVE; CREATE INDEX blocks writes, CREATE INDEX CONCURRENTLY blocks neither reads nor writes.
• Locks are held until the transaction ends, not until the statement ends.
• A waiting strong lock request blocks all later requests on the table.
• LOCK TABLE ... IN <mode> MODE [NOWAIT] takes a table lock explicitly.

🧪 Practice

1. (Beginner) Using the matrix, decide whether these pairs can run concurrently on the same table: VACUUM + INSERT; CREATE INDEX + UPDATE; ANALYZE + CREATE INDEX CONCURRENTLY; SELECT + TRUNCATE.
2. (Intermediate) In one session, BEGIN and SELECT from accounts without committing. In a second, run ALTER TABLE accounts ADD COLUMN x int. In a third, run a plain SELECT. Explain why the third session waits, and list the locks with pg_locks.
3. (Interview) "Why does PostgreSQL need a SELECT to take any lock at all, given MVCC?" Hint: What could happen to a running query if another session dropped the table, or rewrote it with VACUUM FULL, halfway through the scan?

Row-Level Locks

Table locks say "someone is changing rows in this table"; they do not say which rows. When two transactions want to modify the same row, one has to wait for the other, otherwise the second could overwrite a version it never saw. PostgreSQL protects individual rows with row-level locks. Unlike most databases, it does not keep these locks in a memory table: it writes them into the row itself, using the xmax field. That way, locking a million rows costs no shared memory.

The four row lock modes

Mode	Taken by	Purpose
FOR KEY SHARE	Foreign key checks (inserting a child row locks the referenced parent row)	"Do not delete this row or change its key"
FOR SHARE	SELECT ... FOR SHARE	"Do not change this row at all"
FOR NO KEY UPDATE	UPDATE that does not modify a key column; SELECT ... FOR NO KEY UPDATE	"I am changing non-key columns"
FOR UPDATE	DELETE; UPDATE that modifies a column in a unique index usable by a foreign key; SELECT ... FOR UPDATE	"I am deleting this row or changing its identity"

Conflict matrix

Requested \ Held	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

The weaker modes exist mainly for foreign keys. Inserting an order for customer 7 takes FOR KEY SHARE on customer 7, which does not conflict with an ordinary UPDATE of the customer's name (FOR NO KEY UPDATE). Before PostgreSQL 9.3, both used stronger modes and blocked each other constantly.

Row locks do not block readers. A plain SELECT takes no row locks and ignores them, thanks to MVCC. Only other lockers and writers wait:

 T1                                       T2
 BEGIN;
 UPDATE accounts SET balance = 0
   WHERE id = 2;      -- FOR NO KEY UPDATE on row 2
                                          SELECT * FROM accounts WHERE id = 2;      -- returns at once (old version)
                                          SELECT * FROM accounts WHERE id = 2 FOR SHARE;  -- waits
                                          UPDATE accounts SET owner = 'B' WHERE id = 2;   -- would also wait
 COMMIT;
                                          -- the FOR SHARE now returns the new version

How waiting works. Since row locks live in tuples, a waiter cannot queue on "row 2". Instead it reads the locker's XID from xmax and waits on that transaction's lock, which is released when the transaction ends. This is why pg_locks shows row conflicts as locktype = transactionid with granted = false, plus a short-lived tuple lock that keeps the waiters in order.

Shared row locks and multixacts. When several transactions hold a shared row lock (FOR SHARE, FOR KEY SHARE) on the same row, one xmax field cannot store all their XIDs. PostgreSQL then stores a multixact ID that points to a member list in pg_multixact. Heavy foreign-key traffic to the same parent row (for example, thousands of order inserts per second for one tenant) can create many multixacts, which must also be frozen by vacuum (Transaction ID Wraparound and Freezing).

Inspecting row locks. The pgrowlocks extension reads a table and reports locked rows:

CREATE EXTENSION IF NOT EXISTS pgrowlocks;

-- while another session holds: BEGIN; SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT locked_row, locker, multi, xids, modes, pids FROM pgrowlocks('accounts');
--  locked_row | locker | multi |  xids   |     modes      |  pids
-- ------------+--------+-------+---------+----------------+--------
--  (0,5)      |  10960 | f     | {10960} | {"For Update"} | {4412}

pgrowlocks scans the whole table, so use it only on small tables or for diagnosis.

Key takeaways

• Row locks are stored in the tuple's xmax, so locking many rows uses no shared memory.
• Four modes: FOR KEY SHARE < FOR SHARE < FOR NO KEY UPDATE < FOR UPDATE.
• UPDATE normally takes FOR NO KEY UPDATE; DELETE and key-changing updates take FOR UPDATE; FK checks take FOR KEY SHARE.
• Plain SELECT never waits for row locks.
• Waiters wait on the holder's transaction ID; shared locks by several transactions use multixacts.

🧪 Practice

1. (Beginner) In session A, update bob's balance without committing. In session B, try a plain SELECT, then SELECT ... FOR KEY SHARE, then SELECT ... FOR SHARE on the same row. Which ones wait?
2. (Intermediate) Create orders(id, account_id REFERENCES accounts). In session A, insert an order for account 1 without committing. In session B, update account 1's balance, then try to delete account 1. Explain each outcome using the row lock matrix.
3. (Interview) "Why does PostgreSQL not run out of memory when one transaction locks 100 million rows with SELECT ... FOR UPDATE, while some databases escalate to a table lock?" Hint: Where is each row lock recorded, and what does writing that record cost instead?

SELECT FOR UPDATE and FOR SHARE

Under READ COMMITTED, reading a row and later updating it leaves a gap in which another transaction can change it (Read Committed). A locking read closes the gap: SELECT ... FOR UPDATE reads the rows and locks them, so no one else can modify or lock them until your transaction ends. It is the database equivalent of picking up an item in a shop and holding it while you decide, rather than leaving it on the shelf where someone else can take it.

Syntax

SELECT ... FROM ...
WHERE ...
FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
    [ OF table_name [, ...] ]           -- which tables in a join to lock (default: all)
    [ NOWAIT | SKIP LOCKED ];           -- behavior when a row is already locked

The pessimistic read-modify-write

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;    -- 100, and row 1 is now locked
-- application checks: 100 >= 30, ok
UPDATE accounts SET balance = 70 WHERE id = 1;           -- no one could have changed it meanwhile
COMMIT;                                                  -- releases the lock

A second session running the same SELECT ... FOR UPDATE waits at the SELECT until the first commits, then reads the new balance (70), because under READ COMMITTED a locking read returns the latest committed version after waiting.

Choosing the strength

Clause	Use when
FOR UPDATE	You will delete the row or change its key, or you want maximal exclusion
FOR NO KEY UPDATE	You will update non-key columns; does not block FK inserts referencing the row (preferred for most updates)
FOR SHARE	You need the row to stay unchanged while you work, but others may also read-lock it
FOR KEY SHARE	You need the row to keep existing with the same key (what FK checks do)

Locking in joins. Without OF, every table in the FROM clause gets its rows locked. Lock only what you will change:

SELECT a.id, a.balance, t.amount
FROM accounts AS a
JOIN transfers AS t ON t.from_id = a.id
WHERE t.id = 7
FOR NO KEY UPDATE OF a;            -- lock the account row, not the transfer row

Locking a consistent set: order matters. When locking several rows, take them in a fixed order to avoid deadlocks with other transactions doing the same (Deadlock Detection and Prevention):

SELECT id, balance FROM accounts
WHERE id IN (2, 1)
ORDER BY id                         -- always lock lower id first
FOR UPDATE;

Restrictions and surprises

• Not allowed with GROUP BY, HAVING, aggregates, DISTINCT, window functions, or set operations: the result rows do not correspond to single table rows.
• With LIMIT, rows are locked as they are returned. Under READ COMMITTED, if a locked row changes while you wait and no longer matches the WHERE clause, it is skipped, so you may get fewer rows than the limit.
• The re-check after waiting applies the WHERE clause to the new row version, but the ORDER BY was computed earlier, so results may come back out of order.
• At REPEATABLE READ or SERIALIZABLE, if the row was modified after your snapshot, FOR UPDATE fails with 40001 instead of returning the new version.

FOR SHARE as a guard. Use a shared lock to prevent a referenced row from changing while you insert something that depends on it, without blocking other readers that do the same:

BEGIN;
SELECT plan FROM users WHERE id = 42 FOR SHARE;    -- plan stays 'pro' until we commit
INSERT INTO pro_features_usage (user_id, feature) VALUES (42, 'export');
COMMIT;

Key takeaways

• SELECT ... FOR UPDATE reads and locks rows until the transaction ends, preventing lost updates under READ COMMITTED.
• Prefer FOR NO KEY UPDATE for updates that do not change keys, to avoid blocking FK inserts.
• Use OF table in joins to lock only the rows you will modify.
• Lock multiple rows in a consistent order to avoid deadlocks.
• Locking reads cannot be combined with aggregates or DISTINCT, and may return fewer rows than LIMIT after waiting.

🧪 Practice

1. (Beginner) Run the pessimistic transfer from two sessions at once and confirm that the second SELECT ... FOR UPDATE waits and then returns the updated balance.
2. (Intermediate) Write a transfer function that locks both accounts with one SELECT ... ORDER BY id FOR NO KEY UPDATE, checks the balance, and performs both updates. Test it with two concurrent transfers in opposite directions.
3. (Interview) "When would you choose SELECT ... FOR UPDATE under Read Committed over running the transaction at Serializable?" Hint: Compare waiting versus aborting, the need for retry logic, lock duration, and whether the rule involves rows you can actually lock.

SKIP LOCKED and NOWAIT

By default, a locking read that hits a locked row waits for as long as it takes. That is right for a bank transfer, but wrong for two other situations: a user interface that should say "someone else is editing this" immediately, and a pool of workers pulling jobs from a queue, where waiting for a job someone else already took is pure waste. NOWAIT and SKIP LOCKED change what happens on a conflict.

Option	On a locked row	Typical use
(none)	Wait until the lock is released	Correctness-critical updates
NOWAIT	Fail immediately with 55P03 lock_not_available	Interactive editing, fail-fast APIs
SKIP LOCKED	Silently skip the row and continue	Job queues, work distribution

NOWAIT: fail fast

-- Session A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Session B
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR:  could not obtain lock on row in relation "accounts"

For waiting a little, but not forever, use lock_timeout instead: SET LOCAL lock_timeout = '500ms'.

SKIP LOCKED: a job queue in plain SQL. Many systems use PostgreSQL as a reliable work queue instead of adding a separate message broker. The core is a table and one query:

CREATE TABLE jobs (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue       text        NOT NULL DEFAULT 'default',
  payload     jsonb       NOT NULL,
  status      text        NOT NULL DEFAULT 'ready',     -- ready | done | failed
  run_at      timestamptz NOT NULL DEFAULT now(),
  attempts    int         NOT NULL DEFAULT 0,
  locked_by   text,                                     -- used by the claim-and-commit variant below
  locked_at   timestamptz
);
CREATE INDEX jobs_ready_idx ON jobs (queue, run_at) WHERE status = 'ready';   -- partial index: only pending jobs

INSERT INTO jobs (payload)
SELECT jsonb_build_object('email_id', g) FROM generate_series(1, 1000) AS g;

Each worker claims one job per transaction:

BEGIN;
SELECT id, payload
FROM jobs
WHERE queue = 'default' AND status = 'ready' AND run_at <= now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED;                  -- rows locked by other workers are invisible to this query
-- -> id = 17; process the job in the application ...
UPDATE jobs SET status = 'done' WHERE id = 17;
COMMIT;                                  -- if the worker crashes before this, the lock is released
                                         -- and job 17 is picked up by another worker

  worker 1:  SELECT ... SKIP LOCKED -> job 1 (locks it)
  worker 2:  SELECT ... SKIP LOCKED -> job 1 locked, skip -> job 2 (locks it)
  worker 3:  SELECT ... SKIP LOCKED -> jobs 1, 2 locked, skip -> job 3
  no worker ever waits; each job is processed by exactly one worker at a time

Claim-and-commit variant. Holding a transaction open while a job runs for minutes holds back vacuum. For long jobs, claim in one short transaction and track ownership in a column:

UPDATE jobs SET status = 'running', attempts = attempts + 1, locked_by = 'worker-3', locked_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'ready' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;
-- a periodic "reaper" resets jobs stuck in 'running' with an old locked_at back to 'ready'

Caveats

• SKIP LOCKED deliberately gives an inconsistent view; never use it where you need to see all rows (reports, balance checks).
• A queue table has constant inserts and updates, so it produces dead tuples fast. Keep it small (delete or archive done jobs), and tune autovacuum for it (Autovacuum Configuration and Tuning).
• Strict ordering is not guaranteed: a skipped older job is processed after newer ones.

Key takeaways

• NOWAIT fails immediately with 55P03 when a row is locked; lock_timeout waits a bounded time.
• SKIP LOCKED ignores locked rows, letting many workers claim different rows without waiting.
• FOR UPDATE SKIP LOCKED LIMIT n plus a partial index is a complete, crash-safe job queue.
• For long jobs, claim with a short transaction and a status column instead of holding the lock.
• Never use SKIP LOCKED for queries that must see every row.

🧪 Practice

1. (Beginner) Lock row 1 of accounts in one session and run SELECT ... FOR UPDATE NOWAIT and ... SKIP LOCKED from another. Compare the results.
2. (Intermediate) Build the jobs table, start four psql sessions that each claim one job in an open transaction, and show that all four get different jobs. Then kill one session and show its job becomes available again.
3. (Interview) "Design a PostgreSQL-backed job queue that supports retries with exponential backoff, priorities, and jobs that run for up to 30 minutes. What are the failure modes?" Hint: Use run_at for backoff, an index matching the claim query, claim-then-commit for long jobs, a reaper for crashed workers, and think about table bloat.

Advisory Locks

Sometimes the thing you need to protect is not a row or a table. Examples: "only one instance of the nightly billing job may run", "only one process may rebuild the cache for tenant 42", "serialize all operations on a customer even though they touch different tables". You could create a dummy row and lock it, but PostgreSQL offers a cleaner tool: advisory locks, locks on arbitrary numbers whose meaning the application decides. The database enforces them, but only applications that ask for them are affected; they are "advisory" in that sense.

The functions

Function	Behavior
pg_advisory_lock(key)	Session-level exclusive lock; waits if taken
pg_try_advisory_lock(key)	Returns true if acquired, false immediately if not
pg_advisory_unlock(key)	Releases a session-level lock; returns false if not held
pg_advisory_unlock_all()	Releases all session-level locks of this session
pg_advisory_xact_lock(key)	Transaction-level lock: released automatically at commit or rollback
pg_try_advisory_xact_lock(key)	Non-blocking transaction-level variant
pg_advisory_lock_shared(key) and ..._shared variants	Shared mode: many shared holders, conflicts with exclusive

The key is one bigint or two int values. To lock on a string, hash it:

SELECT pg_advisory_xact_lock(hashtextextended('billing:tenant:42', 0));   -- string -> bigint

Session-level vs. transaction-level

	Session-level	Transaction-level
Released	Explicitly, or when the connection closes	Automatically at transaction end
Survives ROLLBACK?	Yes	No
Reentrant?	Yes: must unlock as many times as locked	Yes
Safe with transaction-mode connection pooling?	No: the lock stays on a server connection that other clients reuse	Yes

Prefer transaction-level locks unless the lock must span several transactions.

Singleton job example

-- Run by a scheduler on every app server; only one should do the work
SELECT pg_try_advisory_lock(424242) AS got_it;   -- 424242 = "nightly billing" by convention
--  got_it
-- --------
--  t                 -- on the first server; others get f and exit

-- ... run billing in several transactions ...

SELECT pg_advisory_unlock(424242);

If the process crashes, its connection closes and the lock is released, a big advantage over a "lock" row with a flag that stays set forever.

Serializing work per key

CREATE OR REPLACE FUNCTION apply_tenant_change(p_tenant int, p_change jsonb) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_advisory_xact_lock(1, p_tenant);       -- (class 1 = tenant changes, id = tenant)
  -- everything below runs one-at-a-time per tenant, in parallel across tenants
  -- ... read and update several tables for this tenant ...
END $$;

Seeing advisory locks

SELECT pid, locktype, classid, objid, objsubid, mode, granted
FROM pg_locks WHERE locktype = 'advisory';
--  pid  | locktype | classid | objid  | objsubid |     mode      | granted
-- ------+----------+---------+--------+----------+---------------+---------
--  4412 | advisory |       0 | 424242 |        1 | ExclusiveLock | t      -- bigint key: high/low 32 bits in classid/objid
--  5120 | advisory |       1 |     42 |        2 | ExclusiveLock | t      -- two-int key: objsubid = 2

Pitfalls

• Advisory locks use the shared lock table (sized by max_locks_per_transaction). Locking millions of keys in one session can exhaust it.
• Keys are global to the database: agree on a scheme (for example, the first int as a namespace) so different features do not collide.
• pg_advisory_lock in a SELECT over many rows (SELECT pg_advisory_lock(id) FROM t WHERE ...) can lock more rows than you expect if the planner evaluates the function before filtering.

Key takeaways

• Advisory locks lock application-defined numbers; only code that asks for them is affected.
• Transaction-level locks release automatically and are safe with connection poolers; session-level locks must be released or the connection closed.
• pg_try_advisory_lock builds non-blocking singletons; pg_advisory_xact_lock(class, id) serializes work per key.
• Hash strings to keys with hashtextextended, and keep a namespace convention.
• Advisory locks are visible in pg_locks with locktype = 'advisory'.

🧪 Practice

1. (Beginner) Take pg_advisory_lock(1) in one session and pg_try_advisory_lock(1) in another. Then close the first session and try again.
2. (Intermediate) Write a cron-style script that uses pg_try_advisory_lock so that, when started on three machines at the same time, exactly one performs the work. Verify with pg_locks during the run.
3. (Interview) "Your team uses PgBouncer in transaction mode, and a singleton job protected by pg_advisory_lock sometimes runs twice, and sometimes never runs. Explain both symptoms." Hint: Which server connection holds the lock after the transaction ends, who uses that connection next, and which connection does the unlock run on?

Deadlock Detection and Prevention

Two transactions can each hold a lock the other needs: T1 locked account 1 and wants account 2, while T2 locked account 2 and wants account 1. Neither can continue, and no amount of waiting will help. This is a deadlock, like two cars meeting on a one-lane bridge. PostgreSQL detects deadlocks automatically and resolves them by aborting one of the transactions, which then must be retried.

Producing a deadlock

 T1                                             T2
 BEGIN;                                         BEGIN;
 UPDATE accounts SET balance = balance - 10
   WHERE id = 1;            -- locks row 1
                                                UPDATE accounts SET balance = balance - 10
                                                  WHERE id = 2;            -- locks row 2
 UPDATE accounts SET balance = balance + 10
   WHERE id = 2;            -- waits for T2
                                                UPDATE accounts SET balance = balance + 10
                                                  WHERE id = 1;            -- waits for T1: cycle
                                                -- after deadlock_timeout (1 s):
                                                ERROR:  deadlock detected
                                                DETAIL:  Process 5120 waits for ShareLock on transaction 10971; blocked by process 4412.
                                                Process 4412 waits for ShareLock on transaction 10972; blocked by process 5120.
                                                HINT:  See server log for query details.
                                                CONTEXT:  while updating tuple (0,1) in relation "accounts"
 -- T1's UPDATE now proceeds
 COMMIT;                                        ROLLBACK;   -- T2 must retry

How detection works. Checking for cycles on every lock wait would be expensive, and most waits end quickly. So a waiting backend sleeps for deadlock_timeout (default 1 s) first. If it is still waiting, it builds the wait-for graph from the lock table and looks for a cycle that includes itself. If it finds one, it aborts its own transaction with SQLSTATE 40P01.

   wait-for graph
   T1 ----waits for----> T2
    ^                     |
    +-----waits for-------+        cycle -> the backend that runs the check aborts itself

Raising deadlock_timeout makes detection cheaper but deadlocks last longer; lowering it below about 200 ms rarely helps. It also controls when log_lock_waits reports a wait.

Prevention strategies

Strategy	How	Example
Consistent lock order	Every code path locks resources in the same global order	Lock accounts by ascending id
Lock everything up front	Take all needed locks at the start in one statement	SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE
Fewer, shorter transactions	Less time holding locks means fewer overlaps	Move non-database work out
Coarser lock	Serialize on one resource instead of several	pg_advisory_xact_lock(customer_id)
Avoid lock upgrades	Do not take FOR SHARE and later FOR UPDATE on the same row from two transactions	Take FOR UPDATE from the start

Ordering a transfer

CREATE OR REPLACE FUNCTION transfer(p_from int, p_to int, p_amount numeric) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM 1 FROM accounts
  WHERE id IN (p_from, p_to)
  ORDER BY id                              -- global order: lower id first
  FOR NO KEY UPDATE;                       -- both rows locked before any change

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END $$;

Now concurrent transfer(1, 2, ...) and transfer(2, 1, ...) both lock row 1 first; the second simply waits.

Multi-row updates are not ordered. A single UPDATE accounts SET ... WHERE id IN (1, 2) locks rows in whatever order the plan visits them, which may differ between two statements with different plans. For batch updates that may run concurrently, lock first with SELECT ... ORDER BY ... FOR UPDATE, or sort the batch the same way in every client.

Hidden lock sources. Deadlocks also involve locks you did not write: foreign key checks (FOR KEY SHARE on parents), unique index insertions waiting for another transaction's uncommitted duplicate, and triggers that update other tables. The CONTEXT line in the error and the server log show which statement was waiting.

Monitoring

SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();

Any non-zero, growing value deserves investigation: each deadlock costs at least deadlock_timeout of waiting plus a retry. The full details, including both queries, are written to the server log.

Key takeaways

• A deadlock is a cycle of transactions waiting for each other; PostgreSQL detects it after deadlock_timeout and aborts one with 40P01.
• The aborted transaction must be retried in full, like a serialization failure.
• Prevent deadlocks by acquiring locks in a consistent order, ideally all at once at the start.
• Multi-row statements, foreign keys, unique checks, and triggers can take locks in unexpected orders.
• Track pg_stat_database.deadlocks and read the server log for the statements involved.

🧪 Practice

1. (Beginner) Reproduce the two-session deadlock and identify which session was aborted and after how long. Change deadlock_timeout to 5s for your session and repeat.
2. (Intermediate) Produce a deadlock that involves a foreign key: two sessions each insert a child row referencing a parent, then each update the parent the other referenced. Explain the lock modes involved.
3. (Interview) "Deadlocks appear in production only during a nightly batch that updates many rows while the web application runs. How would you find the cause and fix it?" Hint: Read the server log DETAIL and statements, compare the order in which the batch and the app lock rows, and consider sorting the batch or chunking it.

Lock Monitoring and Blocking Queries

A lock wait is invisible from the outside: the client just sees a slow query. When the whole application slows down, you need to answer quickly which sessions are waiting, on what, and which session is at the root of the chain. Detecting Lock Contention introduced pg_blocking_pids and a three-session incident. This topic gives you a toolkit of queries to keep ready and the meaning of each pg_locks column.

pg_locks columns that matter

Column	Meaning
locktype	relation, tuple, transactionid, virtualxid, advisory, object, page, extend, ...
relation, page, tuple	The locked object for relation, page, and tuple locks
transactionid, virtualxid	The transaction being waited on (row conflicts appear here)
mode	Lock mode, for example RowExclusiveLock, AccessExclusiveLock, ShareLock
granted	true if held, false if waiting
fastpath	Taken through the per-backend fast path (weak relation locks), not the shared table
waitstart	When the wait began (PostgreSQL 14+), NULL for granted locks
pid	The process holding or waiting

Who is waiting, for how long, and on whom

SELECT a.pid, a.application_name AS app, a.usename,
       now() - l.waitstart                 AS waiting_for,
       l.locktype, l.mode, l.relation::regclass AS rel,
       pg_blocking_pids(a.pid)             AS blocked_by,
       left(a.query, 60)                   AS query
FROM pg_locks AS l
JOIN pg_stat_activity AS a USING (pid)
WHERE NOT l.granted
ORDER BY l.waitstart;

The blocking tree. A recursive query starting from root blockers (sessions that block others but wait for nobody) prints the whole chain:

WITH RECURSIVE
  waits AS (                                   -- one row per (waiter, blocker) pair
    SELECT pid AS waiter, unnest(pg_blocking_pids(pid)) AS blocker
    FROM pg_stat_activity
    WHERE cardinality(pg_blocking_pids(pid)) > 0
  ),
  tree AS (
    SELECT DISTINCT w.blocker AS pid, 0 AS depth, ARRAY[w.blocker] AS path
    FROM waits AS w
    WHERE w.blocker NOT IN (SELECT waiter FROM waits)       -- roots: block others, wait for nobody
    UNION ALL
    SELECT w.waiter, t.depth + 1, t.path || w.waiter
    FROM tree AS t JOIN waits AS w ON w.blocker = t.pid
    WHERE NOT w.waiter = ANY (t.path)                       -- guard against cycles
  )
SELECT repeat('    ', t.depth) || t.pid AS pid_tree,
       a.state, a.wait_event_type, a.wait_event,
       round(extract(epoch FROM now() - a.xact_start)) AS xact_s,
       left(a.query, 50) AS query
FROM tree AS t JOIN pg_stat_activity AS a USING (pid)
ORDER BY t.path;
--      pid_tree      |        state        | wait_event_type |  wait_event   | xact_s |                 query
-- -------------------+---------------------+-----------------+---------------+--------+---------------------------------------
--  4412              | idle in transaction |                 |               |    845 | UPDATE accounts SET balance = 0 WHERE
--      5120          | active              | Lock            | transactionid |    120 | UPDATE accounts SET owner = 'B' WHERE
--          5133      | active              | Lock            | relation      |     30 | ALTER TABLE accounts ADD COLUMN note t
--              5140  | active              | Lock            | relation      |     12 | SELECT * FROM accounts WHERE id = 3
--      5133          | active              | Lock            | relation      |     30 | ALTER TABLE accounts ADD COLUMN note t
--          5140      | active              | Lock            | relation      |     12 | SELECT * FROM accounts WHERE id = 3

The root here is an idle in transaction session: a classic forgotten transaction. A session blocked by several others appears under each of them: the ALTER TABLE (5133) waits for both 4412 and 5120, because both hold ROW EXCLUSIVE on the table, and the SELECT (5140) queues behind the ALTER.

All locks on one table

SELECT l.pid, a.application_name, l.mode, l.granted, a.state, left(a.query, 50) AS query
FROM pg_locks AS l JOIN pg_stat_activity AS a USING (pid)
WHERE l.relation = 'accounts'::regclass
ORDER BY l.granted DESC, l.pid;

Run this before a migration: any long-running holder will make your ALTER TABLE wait.

Lock waits over time. Snapshots tell you about now; for trends, sample periodically or rely on logs:

Source	What it gives
count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock' sampled every few seconds	Number of sessions waiting on locks over time
log_lock_waits = on	Log entry for each wait longer than deadlock_timeout, with holders and queue
pg_stat_database.deadlocks	Cumulative deadlock count
pg_stat_statements (high total_exec_time but low CPU)	Statements that spend their time waiting

Running out of lock slots. Relation-level locks that cannot use the fast path go into a shared table with room for max_locks_per_transaction × (max_connections + max_prepared_transactions) entries. Queries over heavily partitioned tables lock every partition they touch, and can hit:

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

Raise max_locks_per_transaction (requires a restart), and make sure partition pruning keeps queries from touching all partitions (Partition Pruning).

Acting on a blocker

SELECT pg_cancel_backend(4412);      -- cancel the running statement; an idle-in-transaction session keeps its locks
SELECT pg_terminate_backend(4412);   -- end the session: rolls back and releases all its locks

Key takeaways

• pg_locks lists every held and awaited lock; waitstart shows how long a wait has lasted.
• A recursive query over pg_blocking_pids reveals the full blocking tree and its root.
• Check the locks on a table before running DDL against it.
• Track lock waits over time with sampling, log_lock_waits, and deadlock counters.
• out of shared memory with a hint about max_locks_per_transaction usually comes from touching many partitions.

🧪 Practice

1. (Beginner) Create a two-level blocking chain (a row lock and a waiting update) and display it with the "who is waiting" query.
2. (Intermediate) Build a four-session chain like the example output and run the blocking tree query. Then terminate the root and watch the tree dissolve.
3. (Interview) "You are paged because API latency spiked; the database CPU is low. Walk through the first five minutes of your investigation." Hint: Low CPU plus high latency suggests waiting. Which view do you check first, how do you find the root blocker, and what do you record before killing anything?

Lock-Free Migration Patterns

Schema changes are inevitable, and on a small table they are trivial. On a busy table with millions of rows, the same ALTER TABLE can take an ACCESS EXCLUSIVE lock and either hold it while rewriting the whole table, or wait in the lock queue and stall all traffic. "Lock-free" migrations are not literally lock-free; they replace one long strong lock with brief ones plus work done under weak locks. The goal is to keep every ACCESS EXCLUSIVE lock to milliseconds.

Rule 1: always set a lock timeout and retry. Even an instant DDL statement must first acquire its lock. If it waits behind a long transaction, it blocks everyone else meanwhile:

SET lock_timeout = '2s';             -- fail fast instead of blocking the queue
SET statement_timeout = '0';         -- long-running steps like VALIDATE may legitimately take minutes
ALTER TABLE accounts ADD COLUMN note text;
-- on "canceling statement due to lock timeout": wait a few seconds and retry, up to N times

# Retry wrapper for a migration step
for i in 1 2 3 4 5; do
  psql -v ON_ERROR_STOP=1 -c "SET lock_timeout = '2s'; ALTER TABLE accounts ADD COLUMN note text;" && break
  sleep $((i * 5))                   # back off: 5, 10, 15 ... seconds
done

Rule 2: know which operations rewrite or scan the table

Operation	Naive cost	Safe pattern
ADD COLUMN (nullable, or with a constant default)	Metadata only (PostgreSQL 11+)	Safe as is, with lock_timeout
ADD COLUMN ... DEFAULT gen_random_uuid() (volatile default)	Full rewrite under AE	Add nullable column, backfill in batches, then set default
CREATE INDEX	Blocks writes for the whole build	CREATE INDEX CONCURRENTLY
ADD CONSTRAINT ... UNIQUE / PRIMARY KEY	Index build under AE	CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX
ADD FOREIGN KEY	Validation scan holding SRE on both tables	ADD ... NOT VALID, then VALIDATE CONSTRAINT
ADD CHECK	Validation scan under AE	ADD ... NOT VALID, then VALIDATE CONSTRAINT
SET NOT NULL	Full scan under AE	Valid CHECK (col IS NOT NULL) first; then SET NOT NULL skips the scan (12+)
ALTER COLUMN TYPE (int to bigint)	Full rewrite and index rebuild under AE	New column + trigger + backfill + swap
ALTER COLUMN TYPE (varchar(50) to varchar(100) or text)	Metadata only	Safe as is
DROP COLUMN	Metadata only	Remove all application references first
RENAME COLUMN / RENAME TABLE	Metadata only, but breaks running code	Expand/contract: add new, dual-write, switch reads, drop old

Adding a foreign key on a large table

SET lock_timeout = '2s';
ALTER TABLE transfers
  ADD CONSTRAINT transfers_from_fk_v2 FOREIGN KEY (from_id) REFERENCES accounts (id)
  NOT VALID;                          -- brief lock; enforced for new rows from now on, old rows unchecked

ALTER TABLE transfers VALIDATE CONSTRAINT transfers_from_fk_v2;
-- scans existing rows under SHARE UPDATE EXCLUSIVE: reads and writes continue meanwhile

Making a column NOT NULL

ALTER TABLE accounts ADD CONSTRAINT accounts_owner_nn CHECK (owner IS NOT NULL) NOT VALID;   -- brief
ALTER TABLE accounts VALIDATE CONSTRAINT accounts_owner_nn;     -- long scan, weak lock
ALTER TABLE accounts ALTER COLUMN owner SET NOT NULL;           -- brief: proven by the valid CHECK
ALTER TABLE accounts DROP CONSTRAINT accounts_owner_nn;         -- brief cleanup

PostgreSQL 18 also accepts ALTER TABLE ... ADD CONSTRAINT ... NOT NULL owner NOT VALID directly, followed by VALIDATE CONSTRAINT.

Backfilling in batches. A single UPDATE of 50 million rows holds row locks on all of them for its whole duration, creates 50 million dead tuples at once, and produces a huge WAL burst for replicas. Instead, update in chunks, committing each one. Procedures may COMMIT inside a loop:

ALTER TABLE accounts ADD COLUMN currency char(3);            -- nullable: metadata only

CREATE OR REPLACE PROCEDURE backfill_currency(batch int DEFAULT 5000)
LANGUAGE plpgsql AS $$
DECLARE
  n int;
BEGIN
  LOOP
    UPDATE accounts SET currency = 'EUR'
    WHERE id IN (SELECT id FROM accounts
                 WHERE currency IS NULL
                 LIMIT batch
                 FOR UPDATE SKIP LOCKED);                    -- do not wait on rows the app is updating
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    COMMIT;                                                  -- release locks, let vacuum and replicas catch up
    PERFORM pg_sleep(0.05);                                  -- throttle
  END LOOP;
END $$;

CALL backfill_currency();                                    -- must be called outside an explicit transaction
ALTER TABLE accounts ALTER COLUMN currency SET DEFAULT 'EUR';

The application must already write currency for new rows before the backfill finishes, otherwise new NULLs keep appearing.

Changing a column type without a rewrite lock: expand and contract

 1. EXPAND    ALTER TABLE t ADD COLUMN id_new bigint;                         (instant)
 2. SYNC      trigger: on INSERT/UPDATE set NEW.id_new := NEW.id;             (brief lock to create)
 3. BACKFILL  batched UPDATE t SET id_new = id WHERE id_new IS NULL;          (no long locks)
 4. INDEX     CREATE UNIQUE INDEX CONCURRENTLY ... ON t (id_new);
 5. SWITCH    in one short transaction (with lock_timeout): drop old PK, add PK USING INDEX,
              rename columns, drop trigger, move the sequence/identity
 6. CONTRACT  drop the old column once nothing uses it

The same pattern renames columns and splits tables: every step is either instant or runs under weak locks, and the application keeps working throughout.

Key takeaways

• Every DDL statement needs lock_timeout and a retry loop, because waiting for its lock blocks everyone else.
• Know which operations rewrite or scan the table under ACCESS EXCLUSIVE; replace them with CONCURRENTLY or NOT VALID + VALIDATE variants.
• Make columns NOT NULL via a validated CHECK constraint first.
• Backfill in committed batches with throttling, never in one giant UPDATE.
• Type changes and renames on large tables use expand/contract: add, sync, backfill, switch, drop.

🧪 Practice

1. (Beginner) Create a table with 5 million rows and time ALTER TABLE ... ADD COLUMN c int DEFAULT 0 versus ... DEFAULT (random() * 10)::int. Explain the difference.
2. (Intermediate) On the same table, add a NOT NULL constraint on a new column using the four-step CHECK pattern, while a second session runs a loop of UPDATE statements. Show that the updates are never blocked for more than a moment.
3. (Interview) "Your orders.id is an integer approaching 2.1 billion. Plan the migration to bigint on a table with 2 billion rows and 5,000 writes per second, with no downtime." Hint: Expand/contract with a trigger, batched backfill, concurrent unique index, the switch transaction, foreign keys that reference orders.id, and how you would test it.

<a id="85-vacuum-and-maintenance"></a>

8.5 Vacuum and Maintenance

MVCC leaves old row versions behind and stamps every row with a transaction ID from a finite counter. This subchapter covers the maintenance that keeps that design healthy: what VACUUM does and why it cannot be skipped, the difference between plain and full vacuum, tuning autovacuum, preventing transaction ID wraparound, measuring bloat, getting the most from HOT updates, and rebuilding indexes and tables.

Why VACUUM Is Necessary

In PostgreSQL, DELETE and UPDATE never free space; they only mark old tuples as deleted (MVCC Fundamentals). Without a cleanup process, every table would grow forever, even if its row count stayed constant, and every scan would wade through more and more dead tuples. Something must also deal with the fact that transaction IDs are 32-bit numbers that eventually wrap around. VACUUM is that cleanup process: the garbage collector of MVCC.

What one VACUUM run does

Task	Why it matters
Removes dead tuples from heap pages	Frees space for new rows in the same table
Removes index entries pointing to dead tuples	Keeps indexes from growing and slowing down
Updates the free space map (FSM)	So inserts and updates can find and reuse the freed space
Updates the visibility map (VM)	Enables index-only scans and lets later vacuums skip clean pages
Freezes old tuples	Prevents transaction ID wraparound (Transaction ID Wraparound and Freezing)
Updates relpages, reltuples in pg_class	Keeps planner estimates current
Truncates empty pages at the end of the table	The only case where plain VACUUM returns space to the operating system

VACUUM ANALYZE additionally refreshes column statistics (ANALYZE and Autoanalyze).

Space is reused, not returned. Plain VACUUM makes the space of dead tuples available for future rows of the same table. The file stays the same size, except for empty pages at the very end:

 before VACUUM:  [live dead live dead][dead dead dead dead][live live dead live]      3 pages
 after  VACUUM:  [live free live free][free free free free][live live free live]      still 3 pages
 new inserts fill the free slots first, so the table stops growing

The phases. A vacuum of a table goes through several phases, visible in pg_stat_progress_vacuum while it runs:

 1. scanning heap          read pages (skipping all-visible ones), prune, collect dead tuple IDs
 2. vacuuming indexes      scan EVERY index once, removing entries for the collected IDs
 3. vacuuming heap         mark those line pointers unused
    (2 and 3 repeat if the dead tuple IDs do not fit in maintenance_work_mem)
 4. cleaning up indexes    update index statistics, recycle empty index pages
 5. truncating heap        cut empty pages from the end (needs a brief ACCESS EXCLUSIVE lock)

SELECT p.pid, p.relid::regclass AS table_name, p.phase,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS scanned_pct,
       p.index_vacuum_count,                     -- > 1 means memory was too small for one pass
       p.num_dead_item_ids                       -- PostgreSQL 17+ (older versions: num_dead_tuples)
FROM pg_stat_progress_vacuum AS p;

Every pass over the indexes is a full scan of each index, which is why tables with many indexes are expensive to vacuum. PostgreSQL 17 stores dead tuple IDs much more compactly and removed the old 1 GB memory cap, so multiple index passes are now rare.

Running it manually

VACUUM accounts;                          -- one table
VACUUM (VERBOSE, ANALYZE) accounts;       -- with a report and fresh statistics
VACUUM;                                   -- every table the user may vacuum in the current database

vacuumdb --all --analyze-in-stages --jobs=4   # whole cluster; useful after a major-version upgrade

What happens without vacuum. Tables and indexes bloat, sequential scans read mostly dead data, index-only scans stop working because the visibility map is never set, statistics go stale, and eventually PostgreSQL refuses to assign new transaction IDs to protect the data from wraparound. In practice, nobody runs without vacuum because autovacuum does it automatically; the job of the administrator is to make sure autovacuum keeps up (Autovacuum Configuration and Tuning).

Key takeaways

• VACUUM removes dead tuples and their index entries, updates the FSM and VM, freezes old tuples, and refreshes size statistics.
• Freed space is reused by the same table; plain VACUUM shrinks the file only by truncating empty trailing pages.
• Each vacuum scans every index of the table at least once.
• pg_stat_progress_vacuum shows the phase and progress of running vacuums.
• Autovacuum does this automatically; skipping vacuum leads to bloat, slow scans, and eventually wraparound protection.

🧪 Practice

1. (Beginner) Create a table with 100,000 rows, delete every second row, and compare pg_relation_size before and after VACUUM. Then insert 50,000 rows and check the size again.
2. (Intermediate) Delete the last half of a table's rows (by physical order) and vacuum it. Compare the size change with deleting the first half. Explain the difference.
3. (Interview) "Why does PostgreSQL need vacuum when databases like Oracle or MySQL do not have an equivalent user-visible process?" Hint: Where do those systems keep old row versions, and who cleans them up?

VACUUM vs. VACUUM FULL

When a table is heavily bloated, say 90 % dead space after a mass delete, plain VACUUM makes the space reusable but does not shrink the file. VACUUM FULL does shrink it: it writes a brand-new, compact copy of the table and its indexes and throws the old files away. The price is that the table is completely unavailable while this happens. Choosing between them is a choice between availability and immediate disk space.

Comparison

	VACUUM	VACUUM FULL
How it works	Cleans pages in place	Rewrites the table and rebuilds all indexes into new files
Lock	SHARE UPDATE EXCLUSIVE: reads and writes continue	ACCESS EXCLUSIVE: no reads or writes
Returns space to the OS	Only trailing empty pages	Yes, all free space
Extra disk needed	None	Up to the size of the new table plus indexes
Duration	Proportional to changed pages	Proportional to the whole table
Run by autovacuum	Yes	Never
Typical use	Routine, continuous	Rare, after a one-time mass delete, in a maintenance window

A demonstration

CREATE TABLE bloat_demo AS SELECT g AS id, repeat('x', 100) AS pad FROM generate_series(1, 1000000) AS g;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- 135 MB

DELETE FROM bloat_demo WHERE id % 10 <> 0;               -- delete 90 %
VACUUM bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- 135 MB: space is free inside, file unchanged

VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));   -- 14 MB: rewritten compactly

Useful VACUUM options

Option	Effect
VERBOSE	Print a detailed report
ANALYZE	Also update planner statistics
FREEZE	Freeze all tuples now (aggressive), for example after a bulk load of static data
PARALLEL n	Vacuum indexes with up to n parallel workers (not available for FULL)
INDEX_CLEANUP off	Skip index vacuuming; emergency use to finish faster near wraparound
TRUNCATE off	Skip the final truncation and its brief ACCESS EXCLUSIVE lock
SKIP_LOCKED	Skip tables that cannot be locked immediately
DISABLE_PAGE_SKIPPING	Scan every page even if the VM says it is clean (for suspected VM corruption)
BUFFER_USAGE_LIMIT '256MB'	Size of the ring buffer (PostgreSQL 16+); larger values speed up vacuum at the cost of cache pressure
PROCESS_TOAST off	Skip the table's TOAST table

VACUUM (VERBOSE, PARALLEL 4, BUFFER_USAGE_LIMIT '1GB') events;   -- a faster manual vacuum

Shrinking without a long lock. For tables that cannot be offline, use an extension that rebuilds the table online and takes ACCESS EXCLUSIVE only for a brief swap at the end:

Tool	Approach
pg_repack	Copies the table into a new one, captures concurrent changes with a trigger, applies them, swaps files
pg_squeeze	Similar result, using logical decoding instead of triggers

pg_repack --dbname=bank --table=public.bloat_demo --jobs=2   # needs a primary key or unique NOT NULL index

Both need free disk space for a full copy, and both still need a brief strong lock, so set a lock wait limit (pg_repack --wait-timeout).

Avoid routine VACUUM FULL. A table that is regularly fully vacuumed will just grow back to its working size, since normal update traffic needs free space in pages. Steady-state tables with 10–30 % free space are healthy. Use VACUUM FULL only after one-time events that removed most of a table permanently.

Key takeaways

• Plain VACUUM runs alongside normal traffic and makes space reusable; it does not shrink files.
• VACUUM FULL rewrites the table and indexes compactly but holds ACCESS EXCLUSIVE and needs extra disk space.
• Options such as PARALLEL, FREEZE, INDEX_CLEANUP off, and BUFFER_USAGE_LIMIT tune manual vacuums.
• pg_repack or pg_squeeze shrink tables online with only a brief lock at the end.
• Some free space in a table is normal and useful; reserve VACUUM FULL for one-time mass deletions.

🧪 Practice

1. (Beginner) Run the demonstration and record the size after each step. Add an index on id and repeat, measuring index size as well.
2. (Intermediate) While VACUUM FULL bloat_demo runs on a larger table (10 million rows), try a SELECT count(*) from another session and inspect pg_locks. Then repeat with plain VACUUM.
3. (Interview) "A 500 GB table lost 80 % of its rows after an archival job. Disk space is at 85 %. How do you reclaim the space safely?" Hint: VACUUM FULL and pg_repack both need room for a full copy. Consider the size of the copy, partitioning, moving to another tablespace, and doing the work in stages.

Autovacuum Configuration and Tuning

Running VACUUM by hand on a schedule would either waste effort on idle tables or fall behind on busy ones. Autovacuum is a background system that watches table statistics and vacuums and analyzes each table when enough of it has changed. The defaults are conservative, designed for small databases on modest hardware. On large or write-heavy databases, they often make autovacuum run too rarely on big tables and too slowly once it starts.

Architecture. The autovacuum launcher wakes up every autovacuum_naptime (default 1 min) per database and starts up to autovacuum_max_workers (default 3) worker processes, each vacuuming one table at a time.

When a table is vacuumed. A table qualifies when its dead tuple count exceeds a threshold that grows with its size:

 vacuum threshold  = autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) * reltuples
 insert threshold  = autovacuum_vacuum_insert_threshold (1000) + autovacuum_vacuum_insert_scale_factor (0.2) * reltuples
 analyze threshold = autovacuum_analyze_threshold (50) + autovacuum_analyze_scale_factor (0.1) * reltuples

 Example: a 100-million-row table is vacuumed only after 20,000,050 dead tuples.

The 20 % scale factor is the main problem on large tables: dead tuples pile up for a long time, and the eventual vacuum has a huge amount of work to do. PostgreSQL 18 adds autovacuum_vacuum_max_threshold (default 100,000,000), which caps the threshold for very large tables. The insert threshold (PostgreSQL 13+) makes sure append-only tables are vacuumed too, so their visibility map is set and old tuples get frozen.

How fast it works. To avoid hurting foreground queries, autovacuum throttles itself with cost-based delay: each page hit, miss, and dirtied page costs points, and after autovacuum_vacuum_cost_limit points (default -1, meaning vacuum_cost_limit = 200, shared among all workers) it sleeps for autovacuum_vacuum_cost_delay (default 2 ms). On modern SSDs, these defaults allow only a few tens of MB/s, often too slow for busy databases.

A starting point for a busy server

# postgresql.conf
autovacuum_max_workers           = 6        # more tables in parallel (needs a restart before PostgreSQL 18)
autovacuum_naptime               = 15s      # check databases more often
autovacuum_vacuum_cost_limit     = 2000     # 10x the default work budget, shared among workers
autovacuum_vacuum_cost_delay     = 2ms
autovacuum_vacuum_scale_factor   = 0.05     # vacuum after 5 % change instead of 20 %
autovacuum_analyze_scale_factor  = 0.02
autovacuum_work_mem              = 1GB      # memory for dead tuple IDs per worker (default: maintenance_work_mem)
log_autovacuum_min_duration      = 1s       # log every run longer than 1 s (default 10min)

The cost limit is divided among running workers, so adding workers without raising the limit makes each one slower.

Per-table settings. The most effective tuning is per table, through storage parameters:

-- A 500-million-row table: vacuum after 1 million dead tuples, regardless of size
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000000);

-- A small, extremely hot queue table: vacuum often, without throttling
ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 0);

-- See what is set
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;

Is autovacuum keeping up? This query computes each table's threshold and shows how close it is:

SELECT s.relname,
       s.n_live_tup, s.n_dead_tup,
       round(current_setting('autovacuum_vacuum_threshold')::numeric
             + current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples) AS vacuum_at,
       s.last_autovacuum, s.autovacuum_count
FROM pg_stat_user_tables AS s
JOIN pg_class AS c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 10;
-- (ignores per-table overrides; check reloptions for tables that have them)

Signs of trouble:

Symptom	Likely cause	Remedy
n_dead_tup far above vacuum_at and no running worker on the table	All workers busy elsewhere	More workers; lower naptime
Worker on the same table for hours	Throttled too much, or many indexes	Raise cost limit or set per-table delay to 0; drop unused indexes
Vacuum runs often but removes little	xmin horizon held back (Dead Tuples and Row Versions)	Find the old transaction or slot
last_autovacuum never set on a big append-only table	Before 13: no insert trigger	Upgrade, or schedule manual vacuum
Autovacuum canceled repeatedly ("canceling autovacuum task" in the log)	DDL or LOCK TABLE conflicts	Reschedule DDL; note that wraparound vacuums are not canceled

Reading the log. With log_autovacuum_min_duration set, each run reports what it did:

LOG:  automatic vacuum of table "bank.public.events": index scans: 1
      pages: 0 removed, 128450 remain, 30211 scanned (23.52% of total)
      tuples: 412000 removed, 9988011 remain, 0 are dead but not yet removable
      removable cutoff: 1209945, which was 3 XIDs old when operation ended
      index scan needed: 15010 pages from table (11.69% of total) had 412000 dead item identifiers removed
      avg read rate: 41.207 MB/s, avg write rate: 12.902 MB/s
      buffer usage: 40321 hits, 18843 misses, 5900 dirtied
      system usage: CPU: user: 0.95 s, system: 0.21 s, elapsed: 3.57 s

Key takeaways

• Autovacuum vacuums a table when dead tuples exceed threshold + scale_factor × rows, and also after many inserts (13+).
• The default 20 % scale factor delays vacuum on large tables; use lower or zero scale factors with absolute thresholds per table.
• Cost-based throttling defaults are slow for modern hardware; raise autovacuum_vacuum_cost_limit.
• The cost limit is shared among workers; more workers need more budget.
• Log autovacuum runs and watch n_dead_tup, last_autovacuum, and "not yet removable" counts.

🧪 Practice

1. (Beginner) Compute the autovacuum trigger point for tables of 1,000, 1 million, and 1 billion rows with default settings.
2. (Intermediate) Set log_autovacuum_min_duration = 0, generate 300,000 updates on a 1-million-row table, and read the autovacuum log entry. Change the table's autovacuum_vacuum_cost_delay to 0 and compare elapsed time and read rate.
3. (Interview) "Autovacuum is constantly running on your largest table but never finishes before it starts again, and the table keeps growing. What do you check, and in what order?" Hint: Cost limits and delay, number of indexes and index passes, memory for dead tuples, the xmin horizon, and whether the table should be partitioned.

Transaction ID Wraparound and Freezing

Transaction IDs are 32-bit numbers, about 4.3 billion values. A busy system using 10,000 write transactions per second consumes that in five days. PostgreSQL makes the counter circular: every XID treats the 2 billion before it as the past and the 2 billion after it as the future. That raises a danger: a row inserted by XID 100 would, after 2 billion more transactions, suddenly appear to be from the future and become invisible. Data would not be lost on disk, but it would vanish from queries. Freezing prevents this, and PostgreSQL will stop accepting writes rather than let it happen.

The circle

                      XID 0 / 4.29 billion
                             |
           "past"            |            "future"
      (visible if committed) |   (invisible: started after us)
                     <-------+------->
                             |
   current XID ------------> * <------ 2^31 XIDs ahead are "future",
                                       2^31 XIDs behind are "past"

   A tuple with xmin = 100 is "past" today. After 2^31 more XIDs, 100 would flip to "future".

Freezing. Vacuum marks tuples that are old enough as frozen: a flag in t_infomask that means "committed in the distant past, visible to everyone, ignore xmin". A frozen tuple never flips. Once every tuple on a page is frozen, the page's all-frozen bit in the visibility map is set, and later vacuums skip it entirely.

Tracking progress. Each table records the oldest unfrozen XID it might contain in pg_class.relfrozenxid; each database records the minimum over its tables in pg_database.datfrozenxid. Their age is the number of transactions since:

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_shutdown
FROM pg_database ORDER BY xid_age DESC;
--  datname  |  xid_age  | pct_to_shutdown
-- ----------+-----------+-----------------
--  bank     | 187220114 |             9.4
--  postgres | 187219950 |             9.4

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class AS c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 5;

The settings, from routine to emergency

Setting	Default	Effect when a table's age passes it
vacuum_freeze_min_age	50 million	Tuples older than this are frozen when vacuum touches their page
vacuum_freeze_table_age	150 million	The next vacuum is aggressive: scans all pages not marked all-frozen
autovacuum_freeze_max_age	200 million	Autovacuum forces an anti-wraparound vacuum, even if autovacuum is disabled; it will not cancel itself for conflicting locks
vacuum_failsafe_age	1.6 billion	Failsafe (14+): vacuum drops throttling and skips index cleanup to finish fast
(fixed)	~2.06 billion (2^31 minus 3 million)	Server refuses to assign new XIDs

Multixact IDs (Row-Level Locks) wrap around the same way and have parallel settings: vacuum_multixact_freeze_min_age, vacuum_multixact_freeze_table_age, autovacuum_multixact_freeze_max_age (400 million), and vacuum_multixact_failsafe_age. Check them with mxid_age(datminmxid).

PostgreSQL 18 also lets normal vacuums eagerly freeze some all-visible pages ahead of time (vacuum_max_eager_freeze_failure_rate), spreading out the work that an aggressive vacuum would otherwise do all at once.

What the end looks like. About 40 million XIDs before the limit, every new transaction triggers warnings; at 3 million, writes stop:

WARNING:  database "bank" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "bank"
HINT:  Execute a database-wide VACUUM in that database.

Reads still work. Recovery: find and remove whatever blocks freezing (old transactions, stale replication slots, prepared transactions), then run VACUUM on the oldest tables, as a superuser in normal multi-user mode. VACUUM (FREEZE, INDEX_CLEANUP off, VERBOSE) on the oldest table first is the fastest way to buy back headroom. On very large tables this can take hours, which is why monitoring matters.

Prevention

• Alert when age(datfrozenxid) exceeds 500 million–1 billion; it should normally oscillate between 50 and 200 million.
• Keep the xmin horizon moving: anti-wraparound vacuum cannot freeze tuples newer than the oldest snapshot.
• Give autovacuum enough speed so aggressive vacuums of large tables finish in time.
• For very high-throughput systems, consider raising autovacuum_freeze_max_age (for example to 500 million–1 billion) to make aggressive vacuums less frequent, but only with good monitoring.
• VACUUM (FREEZE) static tables right after a bulk load, so the work does not all arrive at once later.

Key takeaways

• 32-bit XIDs are compared circularly; unfrozen tuples older than 2 billion XIDs would become invisible.
• Vacuum freezes old tuples so they are visible forever; relfrozenxid and datfrozenxid track progress.
• Past autovacuum_freeze_max_age, autovacuum forces non-cancellable anti-wraparound vacuums.
• Near the limit, PostgreSQL warns and then stops assigning XIDs; reads keep working.
• Monitor age(datfrozenxid) and mxid_age(datminmxid), and fix whatever holds the xmin horizon back.

🧪 Practice

1. (Beginner) List the five tables with the oldest relfrozenxid in your database and the age of each. Run VACUUM (FREEZE) on one and check its age again.
2. (Intermediate) Insert 1 million rows into a new table, check how many pages are marked all-frozen with pg_visibility_map_summary('t') (from the pg_visibility extension), run VACUUM (FREEZE), and check again.
3. (Interview) "Your monitoring shows age(datfrozenxid) at 1.4 billion and rising. Autovacuum is running. What do you do in the next hour?" Hint: Why is the age not falling? Check for the oldest snapshot, slots, and prepared transactions; identify the oldest tables; consider a manual vacuum with failsafe-like options.

Table and Index Bloat Measurement

Bloat is space in tables and indexes that holds no live data: dead tuples not yet vacuumed, and free space left behind after vacuum. Some free space is normal and useful, but a table that is three times larger than its data costs three times the I/O for sequential scans, more cache memory, larger backups, and slower replicas. Before fixing bloat, you need to measure it, and the measurement tools range from fast estimates to exact but expensive scans.

Measurement methods

Method	Cost	Accuracy
pg_stat_user_tables.n_dead_tup	Free	Estimate of dead tuples only, not free space
Statistics-based estimate (query below)	Free	Rough; misleading for tables with wide, variable-length, or TOASTed columns
pgstattuple_approx(table)	Reads only pages not marked all-visible	Good for tables
pgstattuple(table)	Reads the entire table	Exact
pgstatindex(index)	Reads the entire index	Exact for B-trees

Exact numbers with pgstattuple

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(table_len) AS size, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('bloat_demo');
--   size   | tuple_percent | dead_tuple_percent | free_percent
-- ---------+---------------+--------------------+--------------
--  135 MB  |          9.31 |               0.00 |        88.92     -- after DELETE + VACUUM: 89 % free

SELECT pg_size_pretty(table_len) AS size, scanned_percent, approx_tuple_percent, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('events');               -- much faster on large, mostly clean tables

SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('events_pkey');                 -- healthy B-tree: density near 90 (its fillfactor)

A quick statistics-based estimate. Compares each table's actual pages with the minimum space its rows need, using average widths from pg_stats:

SELECT c.relname,
       pg_size_pretty(c.relpages::bigint * 8192) AS actual_size,
       pg_size_pretty((c.reltuples * (w.data_width + 32))::bigint) AS min_size,  -- 32 = header + line pointer + padding
       round((100 * (1 - c.reltuples * (w.data_width + 32)
                         / nullif(c.relpages::bigint * 8192, 0)))::numeric) AS est_bloat_pct
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT sum(s.avg_width) AS data_width
  FROM pg_stats AS s
  WHERE s.schemaname = n.nspname AND s.tablename = c.relname
) AS w
WHERE c.relkind = 'r' AND n.nspname = 'public' AND c.reltuples > 0
ORDER BY c.relpages DESC
LIMIT 10;

Use it only to find candidates, and confirm with pgstattuple before acting. Community queries (for example from the pgx_scripts or pgexperts collections) refine this estimate with alignment and null bitmaps.

What counts as bloated?

Free + dead space	Interpretation
Below 20–30 %	Normal working space for updates
30–50 %	Watch: check whether it keeps growing
Above 50 % and growing	Investigate the cause before rebuilding

Find the cause before the cure. Rebuilding a bloated table without fixing its cause just restarts the cycle:

Cause	Evidence	Fix
Autovacuum too slow or too rare	High n_dead_tup, old last_autovacuum	Tune per table (Autovacuum Configuration and Tuning)
Old snapshot holds the horizon	"Dead but not yet removable" in vacuum logs	Find the long transaction, slot, or prepared transaction
One-time mass delete or update	History of a batch job	One-time pg_repack or VACUUM FULL
Non-HOT updates on indexed columns	Low HOT ratio	Fewer indexes on updated columns, lower fillfactor (HOT Updates and Fillfactor)
Index bloat from random deletes	Low avg_leaf_density	REINDEX CONCURRENTLY (REINDEX and CLUSTER)

Track it over time. A single measurement says little. Record sizes and dead tuple counts daily, as described in Capacity and Growth Monitoring, and investigate tables whose size grows while their row count does not.

Key takeaways

• Bloat is dead tuples plus unused free space; some free space is healthy.
• n_dead_tup and statistics-based estimates are cheap but rough; pgstattuple and pgstatindex are exact but read everything.
• pgstattuple_approx gives good table numbers quickly by skipping all-visible pages.
• Always identify the cause (slow autovacuum, held-back horizon, mass deletes, non-HOT updates) before rebuilding.
• Track sizes over time; growth without a matching row-count increase is the signal.

🧪 Practice

1. (Beginner) Run pgstattuple on bloat_demo after each step of the VACUUM vs. VACUUM FULL demonstration and explain each change in dead_tuple_percent and free_percent.
2. (Intermediate) Compare the statistics-based estimate with pgstattuple for three tables in your database. For which kind of table is the estimate furthest off, and why?
3. (Interview) "A colleague runs VACUUM FULL on the biggest table every Sunday because 'it gets bloated'. Is this a good practice? What would you do instead?" Hint: What happens to the table during the week after the rebuild, what does the rebuild cost, and how would you find out why it bloats?

HOT Updates and Fillfactor

Every UPDATE writes a new tuple, and normally every index on the table also gets a new entry pointing to it, even indexes on columns that did not change. On a table with eight indexes, updating one unindexed status column costs nine writes plus WAL for all of them. Heap-only tuple (HOT) updates avoid this in the common case, and fillfactor is the main knob for making them more frequent. Heap File Organization introduced the idea; this topic covers how HOT chains work in detail and how to measure and tune them.

When an update is HOT. Both conditions must hold:

1. No column referenced by any index changes (including INCLUDE columns, expression and partial index columns). Since PostgreSQL 16, columns used only in BRIN indexes do not count.
2. The new tuple fits on the same page as the old one.

The HOT chain. Indexes keep pointing at the original line pointer. The new tuple is reached by following the ctid chain inside the page:

  index entry (key 42) ---> lp 1
                             |
  page 7:  lp 1 [v1  xmax=900, HOT_UPDATED, ctid->(7,4)]
           lp 4 [v2  xmin=900, HEAP_ONLY,  xmax=950, HOT_UPDATED, ctid->(7,6)]
           lp 6 [v3  xmin=950, HEAP_ONLY]                  <- current version

  after pruning (v1, v2 dead and invisible to everyone):
           lp 1 [REDIRECT -> lp 6]                         <- index entry still valid, no index work needed
           lp 4 [unused]
           lp 6 [v3]

Pruning of HOT chains happens during normal page access (Dead Tuples and Row Versions), without waiting for vacuum, so HOT-updated tables also stay smaller.

Seeing it with pageinspect

CREATE TABLE hot_demo (id int PRIMARY KEY, status text, hits int) WITH (fillfactor = 100);
INSERT INTO hot_demo VALUES (1, 'new', 0);
UPDATE hot_demo SET hits = hits + 1 WHERE id = 1;        -- no indexed column changes -> HOT
UPDATE hot_demo SET hits = hits + 1 WHERE id = 1;

SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid,
       (t_infomask2 & 16384) <> 0 AS hot_updated,       -- HEAP_HOT_UPDATED 0x4000
       (t_infomask2 & 32768) <> 0 AS heap_only          -- HEAP_ONLY_TUPLE  0x8000
FROM heap_page_items(get_raw_page('hot_demo', 0));
--  lp | lp_flags | t_xmin | t_xmax | t_ctid | hot_updated | heap_only
-- ----+----------+--------+--------+--------+-------------+-----------
--   1 |        1 |  11001 |  11002 | (0,2)  | t           | f            -- lp_flags 1 = normal
--   2 |        1 |  11002 |  11003 | (0,3)  | t           | t
--   3 |        1 |  11003 |      0 | (0,3)  | f           | t

SELECT itemoffset, ctid FROM bt_page_items('hot_demo_pkey', 1);
--  itemoffset | ctid
--           1 | (0,1)          -- still only one index entry, pointing at the chain head

Measuring the HOT ratio

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,
       n_tup_newpage_upd                              -- 16+: non-HOT updates that moved to another page
FROM pg_stat_user_tables
WHERE n_tup_upd > 10000
ORDER BY n_tup_upd DESC;
--  relname  | n_tup_upd | n_tup_hot_upd | hot_pct | n_tup_newpage_upd
--  accounts |   8812004 |       8590110 |    97.5 |            221894    -- good
--  orders   |   5130422 |        410221 |     8.0 |           1203112    -- poor: investigate

A low ratio has two possible reasons, and n_tup_newpage_upd helps tell them apart:

Pattern	Cause	Fix
Low HOT, high n_tup_newpage_upd	Pages are full, new versions go elsewhere	Lower fillfactor
Low HOT, low n_tup_newpage_upd	Updates change indexed columns	Drop or redesign indexes on frequently updated columns

Setting fillfactor. fillfactor is the percentage to which inserts fill a heap page; the rest stays free for updated versions of rows on that page:

ALTER TABLE orders SET (fillfactor = 85);   -- affects pages written from now on
VACUUM FULL orders;                         -- or pg_repack: rewrite existing pages with the new setting

Table workload	Suggested fillfactor
Insert-only, append logs	100 (default)
Occasional updates	90–100
Frequent updates of non-indexed columns (status, counters, timestamps)	70–90

Lower fillfactor makes the table larger for the same data, so sequential scans read more pages. Measure the HOT ratio before and after changing it.

Design for HOT. Avoid indexing columns that change often unless queries truly need the index, such as updated_at or last_seen. One rarely used index on a hot column can turn every update into a non-HOT update. A partial index on status still blocks HOT when status changes.

Key takeaways

• A HOT update writes no index entries; it requires no change to indexed columns and room on the same page.
• HOT chains are followed within the page; pruning replaces dead chain members with a redirect without vacuum.
• Measure n_tup_hot_upd / n_tup_upd; use n_tup_newpage_upd (16+) to tell full pages from indexed-column changes.
• Lower fillfactor on update-heavy tables leaves room for HOT updates; it applies to newly written pages.
• Avoid indexes on frequently updated columns: each one disables HOT for those updates.

🧪 Practice

1. (Beginner) Reproduce the hot_demo example, then add an index on hits and run two more updates. Inspect the page and the index to see what changed.
2. (Intermediate) Create two copies of a 1-million-row table with fillfactor 100 and 80, run 2 million random updates of a non-indexed column on each, and compare hot_pct, n_tup_newpage_upd, table size, and index size.
3. (Interview) "An orders table has 12 indexes, and the status column is updated five times during each order's life. Writes are slow and WAL volume is high. What would you look at?" Hint: Which indexes include status or other changing columns, the HOT ratio, fillfactor, and whether some indexes are unused.

REINDEX and CLUSTER

Vacuum keeps tables and indexes usable, but it does not compact them or reorder data. Two commands rebuild structures from scratch. REINDEX builds an index anew from the table data, which removes index bloat and repairs corruption. CLUSTER rewrites a table in the physical order of an index, which makes range scans on that index read far fewer pages. Both are powerful, and both take strong locks unless used carefully.

When to REINDEX

Situation	Notes
Index bloat	Low avg_leaf_density after random deletes or many non-HOT updates (Table and Index Bloat Measurement)
Index corruption	Detected by amcheck, or errors such as "index contains unexpected zero page"
Collation library change	An OS upgrade that changes glibc or ICU collation rules can silently corrupt text indexes; PostgreSQL warns about a collation version mismatch
Leftover invalid index	A failed CREATE INDEX CONCURRENTLY leaves an INVALID index that costs writes but is never used
Changing index storage parameters	For example a new fillfactor or deduplicate_items

REINDEX forms and locks

REINDEX INDEX accounts_owner_idx;                 -- blocks writes on the table, and reads that would use this index
REINDEX INDEX CONCURRENTLY accounts_owner_idx;    -- 12+: builds a new copy, swaps it in; reads and writes continue
REINDEX TABLE CONCURRENTLY accounts;              -- all indexes of a table (including TOAST indexes)
REINDEX SCHEMA CONCURRENTLY public;
REINDEX DATABASE CONCURRENTLY bank;               -- user indexes in the current database

reindexdb --concurrently --jobs=4 --dbname=bank   # command-line wrapper, parallel across tables

REINDEX CONCURRENTLY takes longer, needs space for both copies, and cannot run inside a transaction block. If it fails, it leaves an invalid index with the suffix _ccnew (or _ccold), which you must drop:

SELECT c.relname AS index_name, i.indrelid::regclass AS table_name
FROM pg_index AS i JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
--      index_name           | table_name
-- --------------------------+------------
--  accounts_owner_idx_ccnew | accounts

DROP INDEX CONCURRENTLY accounts_owner_idx_ccnew;

Checking for corruption with amcheck

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('accounts_pkey', heapallindexed => true);   -- light lock; errors if inconsistent

CLUSTER: physical ordering. Rows are stored in the order they were inserted and updated, not in key order. A query such as "all transfers of account 7 in March" may find 500 matching index entries scattered across 500 different pages. CLUSTER rewrites the table sorted by an index, so those rows sit together on a few pages:

 before:  page 1 [acct 7, acct 3, acct 9]  page 2 [acct 1, acct 7, acct 4]  page 3 [acct 7, ...]
 after CLUSTER ... USING (from_id):
          page 1 [acct 1, acct 1, acct 3]  page 2 [acct 4, acct 7, acct 7]  page 3 [acct 7, acct 9]

CREATE INDEX transfers_from_idx ON transfers (from_id, created_at);
CLUSTER transfers USING transfers_from_idx;        -- ACCESS EXCLUSIVE for the whole rewrite
ANALYZE transfers;                                 -- refresh correlation statistics

SELECT attname, correlation FROM pg_stats
WHERE tablename = 'transfers' AND attname = 'from_id';   -- near 1.0 right after CLUSTER

CLUSTER is a one-time sort. New and updated rows are not placed in order, so the correlation decays over time. PostgreSQL remembers the index used (CLUSTER transfers; reuses it), but you must rerun the command periodically. Like VACUUM FULL, it locks out all access, needs space for a full copy, and also rebuilds every index.

Alternatives that avoid the long lock

Goal	Online alternative
Rebuild an index	REINDEX CONCURRENTLY
Cluster a table	pg_repack --order-by=from_id,created_at
Keep data naturally ordered	Partition by time, or insert in order (append-only tables are naturally ordered by insertion time)
Fast access to ranges without reordering	A covering index enabling index-only scans (Covering Indexes with INCLUDE)

Watching progress

SELECT pid, relid::regclass, index_relid::regclass, command, phase,
       blocks_done, blocks_total
FROM pg_stat_progress_create_index;               -- CREATE INDEX and REINDEX

SELECT pid, relid::regclass, command, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_cluster;                    -- CLUSTER and VACUUM FULL

Raising maintenance_work_mem for the session (SET maintenance_work_mem = '2GB') speeds up both commands by allowing larger in-memory sorts.

Key takeaways

• REINDEX rebuilds indexes to remove bloat, fix corruption, or recover from collation changes; use CONCURRENTLY (12+) in production.
• A failed concurrent rebuild leaves an invalid _ccnew index; find it with pg_index.indisvalid and drop it.
• amcheck verifies B-tree integrity without a strong lock.
• CLUSTER physically sorts a table by an index under ACCESS EXCLUSIVE; the order decays as data changes.
• pg_repack reorders tables online; progress views report on rebuilds and clustering.

🧪 Practice

1. (Beginner) Bloat an index by inserting 1 million rows and deleting 90 % at random, then compare pgstatindex output and index size before and after REINDEX INDEX CONCURRENTLY.
2. (Intermediate) Load 2 million transfers in random from_id order. Measure EXPLAIN (ANALYZE, BUFFERS) for a query fetching all transfers of one account, then CLUSTER the table and measure again. Check correlation in pg_stats before and after.
3. (Interview) "After upgrading the operating system on a database server, some queries on a text column return wrong results, and unique constraints appear to be violated. What happened and how do you recover?" Hint: Collation rules come from the OS library; think about what an index sorted under old rules looks like to new comparison functions, how amcheck helps, and what to reindex.

<a id="9-server-side-programming"></a>