A transaction is the unit in which a database promises to be correct. PostgreSQL implements that promise with MVCC — multi-version concurrency control — where writers never block readers and readers never block writers, because every row can exist in several versions at once. That design explains almost everything else in this post: why an UPDATE writes a new row instead of editing one, why VACUUM exists at all, why an idle transaction can quietly break your cluster, and why SERIALIZABLE hands you an error instead of a lock. This post covers the mechanics, the four isolation levels as PostgreSQL actually implements them, explicit locking, deadlocks, and the vacuum consequences.

Series map: 00 Foundations & Setup → 01 SQL Fundamentals & Data Types → 02 Joins, Aggregation & Window Functions → 03 Indexes & the Query Planner → 04 Query Optimization & Statistics → 05 Transactions, MVCC, Isolation & Locking (this post) → 06 Schema Design, Normalization & JSONB → 07 Operations, Backup, Replication & Security.

Learning Outcome

State each ACID property precisely and name the PostgreSQL mechanism that provides it.
Explain MVCC through xmin, xmax, tuple versions and snapshots, and say why an UPDATE creates a new tuple.
Choose an isolation level knowing exactly which anomalies it prevents in PostgreSQL.
Write a serialization-failure retry loop, because at SERIALIZABLE the application is required to have one.
Use FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, SKIP LOCKED and NOWAIT deliberately.
Reproduce a deadlock, read the server's deadlock report, and fix it with consistent lock ordering.
Explain how long transactions block vacuum, what transaction ID wraparound is, and why idle in transaction is dangerous.
Distinguish VACUUM, autovacuum, VACUUM FULL and freezing, and measure bloat.

Chapter 1 — ACID, stated precisely

Terminology

Atomicity — a transaction's effects are all applied or none are. PostgreSQL gets this from the commit record in the WAL: until the commit record is durable, the transaction's tuples are simply invisible.
Consistency — the database moves from one state satisfying all declared constraints to another. This is the application's and the schema's property; the database enforces it through constraints, not by magic.
Isolation — concurrent transactions produce a result consistent with some acceptable ordering. How strong that guarantee is depends on the isolation level (Chapter 3).
Durability — once COMMIT returns, the effects survive a crash. Provided by write-ahead logging plus fsync; weakened deliberately by synchronous_commit = off.
Anomaly — a concurrency outcome that a given isolation level permits: dirty read, non-repeatable read, phantom read, lost update, write skew.

1
2
3
4
5 BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                 -- or ROLLBACK; nothing in between is visible to others

Every statement in PostgreSQL runs in a transaction. Outside an explicit BEGIN, each statement is its own transaction that commits on success — "autocommit". There is no such thing as an unwrapped statement.

Savepoints give partial rollback inside a transaction:

1
2
3
4
5
6
7
8
9 BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (7, 250);
  SAVEPOINT before_lines;
  INSERT INTO order_lines (order_id, sku, qty) VALUES (currval('orders_id_seq'), 'X', 0);
  -- constraint violation
  ROLLBACK TO SAVEPOINT before_lines;   -- order survives, bad line does not
  INSERT INTO order_lines (order_id, sku, qty) VALUES (currval('orders_id_seq'), 'X', 1);
COMMIT;

Watch out: after any error inside a transaction block, PostgreSQL puts the session into the aborted state — every subsequent statement fails with "current transaction is aborted" until you ROLLBACK or ROLLBACK TO SAVEPOINT. Drivers that wrap each statement in an implicit savepoint hide this and pay for it in overhead.

Chapter 2 — MVCC mechanics

Terminology

Tuple — one physical row version on a heap page. A logical row is a chain of tuples.
xmin — the transaction ID that inserted this tuple version.
xmax — the transaction ID that deleted or superseded it; 0 while the version is live.
ctid — the physical location (page, offset) of a tuple. Changes on every update.
Snapshot — the set of transactions considered committed at a point in time. Determines which tuple versions a statement can see.
Dead tuple — a version no snapshot in the system can still see. Reclaimable by vacuum.

Every table has these hidden system columns. Select them directly:

1
2
3
4
5
6
7
8
9
10
11 CREATE TABLE t (id int PRIMARY KEY, v text);
INSERT INTO t VALUES (1, 'a');
 
SELECT ctid, xmin, xmax, id, v FROM t;
--  (0,1) | 812 | 0 | 1 | a
 
UPDATE t SET v = 'b' WHERE id = 1;
 
SELECT ctid, xmin, xmax, id, v FROM t;
--  (0,2) | 813 | 0 | 1 | b       <- a NEW tuple at a new ctid

The old tuple at (0,1) is still on the page with xmax = 813. It stays there, invisible, until vacuum reclaims it.

MVCC: an UPDATE by transaction 813 leaves the old tuple (0,1) with xmax 813 and writes a new tuple (0,2) with xmin 813; a snapshot taken before 813 commits sees 'a' and one taken after sees 'b'; Exercise 2 shows READ COMMITTED returning 1000 then 900 while REPEATABLE READ stays at 900.

Why UPDATE writes a new version

Overwriting in place would destroy the version that concurrent readers are still entitled to see. Instead:

The new version is written (usually on the same page if there is room).
The old version's xmax is set to the updating transaction's ID.
Readers with older snapshots continue to see the old version; readers with newer snapshots see the new one.
Vacuum removes the old version once no snapshot needs it.

Consequences you have to live with:

An UPDATE costs roughly as much as a DELETE plus an INSERT.
Every index entry pointing at the row must be updated too — unless the update is a HOT (Heap-Only Tuple) update, which requires that no indexed column changed and that the new version fits on the same page. HOT updates are why fillfactor below 100 can help write-heavy tables.
Tables grow under update load. Reclaiming the space is vacuum's job (Chapter 8).

Snapshots and visibility

A snapshot records: the next unassigned transaction ID, and the list of transactions in flight. A tuple is visible if its xmin committed before the snapshot and its xmax either is zero or belongs to a transaction that had not committed at snapshot time.

READ COMMITTED takes a new snapshot at the start of each statement. Two identical SELECTs in one transaction can return different data.
REPEATABLE READ and SERIALIZABLE take one snapshot at the first statement and keep it for the whole transaction.

1
2
3
4 SELECT txid_current();               -- current XID (assigns one if none yet)
SELECT pg_current_xact_id();         -- PostgreSQL 13+ name, 64-bit "full" XID
SELECT * FROM pg_current_snapshot(); -- xmin:xmax:in-progress list

Chapter 3 — Isolation levels, as PostgreSQL implements them

The SQL standard defines four levels by which anomalies they may permit. PostgreSQL's implementations are strictly stronger than the minimum in two important places.

Level	Dirty read	Non-repeatable read	Phantom read	Serialization anomaly (write skew)
READ UNCOMMITTED	not possible	possible	possible	possible
READ COMMITTED (default)	not possible	possible	possible	possible
REPEATABLE READ	not possible	not possible	not possible	possible
SERIALIZABLE	not possible	not possible	not possible	not possible

Two facts that trip people coming from other databases:

READ UNCOMMITTED behaves exactly like READ COMMITTED. It is accepted for standards compliance but there is no dirty-read mode in PostgreSQL. MVCC never exposes uncommitted tuple versions, so dirty reads are structurally impossible.
REPEATABLE READ also prevents phantom reads, which the standard permits at that level. Because the whole transaction runs off one snapshot, a range query re-executed later returns exactly the same rows. PostgreSQL's REPEATABLE READ is snapshot isolation.

What REPEATABLE READ still allows is write skew: two transactions each read a set of rows, each decides its write is safe based on what it read, and the combined result violates an invariant that neither transaction alone broke.

1
2
3
4
5
6
7
8
9
10
11
12 -- Invariant: at least one doctor must remain on call.
-- Session A                          -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call
 WHERE on_call = true;   -- 2         SELECT count(*) FROM on_call
                                       WHERE on_call = true;   -- 2
UPDATE on_call SET on_call = false
 WHERE doctor = 'alice';              UPDATE on_call SET on_call = false
                                       WHERE doctor = 'bob';
COMMIT;                               COMMIT;
-- Both succeed. Zero doctors on call. Neither transaction saw the other.

At REPEATABLE READ, a write-write conflict on the same row raises could not serialize access due to concurrent update (SQLSTATE 40001). Different rows, as above, produce no error at all.

SERIALIZABLE and SSI

PostgreSQL 9.1 and later implement SERIALIZABLE with Serializable Snapshot Isolation (SSI). It runs on top of snapshot isolation and tracks read/write dependencies between concurrent transactions with predicate locks (SIREAD locks). When it detects a dependency cycle that could not occur in any serial ordering, it aborts one of the transactions with SQLSTATE 40001.

Properties worth internalising:

It does not block. There is no extra waiting; the cost is aborts and the memory used for predicate locks.
The guarantee is real: if every transaction runs at SERIALIZABLE and every serialization failure is retried, the outcome is equivalent to some serial execution. No hand-written locking required.
It is all-or-nothing per workload. A transaction at READ COMMITTED running alongside serializable ones is not protected and can break their guarantee.
Read-only transactions can be marked to reduce false positives: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE; waits until a safe snapshot exists, then never aborts.

Watch out: the same rewritten SSI example above, run at SERIALIZABLE, aborts one session with could not serialize access due to read/write dependencies among transactions. That abort is the feature.

The retry loop is mandatory

Any code path that uses REPEATABLE READ or SERIALIZABLE must be prepared to run the whole transaction again. This is not optional error handling; it is part of the contract.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 import time, random
import psycopg
from psycopg import errors
 
SERIALIZATION_FAILURE = "40001"
DEADLOCK_DETECTED     = "40P01"
 
def run_serializable(conn_factory, work, max_attempts=5):
    """Run `work(cur)` at SERIALIZABLE, retrying on 40001/40P01."""
    for attempt in range(1, max_attempts + 1):
        try:
            with conn_factory() as conn:
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                with conn.cursor() as cur:
                    result = work(cur)
                # exiting the `with conn` block commits
                return result
        except psycopg.errors.SerializationFailure as e:
            if attempt == max_attempts:
                raise
        except psycopg.errors.DeadlockDetected:
            if attempt == max_attempts:
                raise
        # full jitter backoff: never retry in lockstep
        time.sleep(random.uniform(0, 0.05 * (2 ** attempt)))
    raise RuntimeError("unreachable")
 
 
def transfer(cur):
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    cur.execute("INSERT INTO transfers (src, dst, amount) VALUES (%s, %s, %s)", (1, 2, 100))

Rules for the retry body:

Retry the whole transaction, not the failing statement. The snapshot is gone; re-reading is the point.
Never leave side effects outside the transaction inside the retried block — no emails, no queue publishes, no HTTP calls. Collect them and fire after commit.
Cap the attempts and back off with jitter, or a hot row turns into a retry storm.
Instrument it. A rising 40001 rate is a real signal about contention.

MongoDB's driver-level withTransaction helper does the equivalent retry automatically for TransientTransactionError — see the MongoDB course post mongo_09_transactions for the contrast. PostgreSQL's drivers deliberately do not retry for you, because only the application knows whether the transaction body is safe to re-execute.

Chapter 4 — Explicit row locking

MVCC removes the need for read locks, but some patterns genuinely need to reserve a row.

1
2
3
4
5 BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- blocks other writers
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Clause	Blocks	Use for
`FOR UPDATE`	other `FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, `FOR KEY SHARE`, and updates/deletes	Read-modify-write on a row you are about to change
`FOR NO KEY UPDATE`	same, except it does not block `FOR KEY SHARE`	Updates that do not touch the primary/unique key — the lock `UPDATE` takes internally
`FOR SHARE`	writers, not other `FOR SHARE` readers	"This row must not change until I commit"
`FOR KEY SHARE`	only `FOR UPDATE`	The lock a foreign key check takes on the parent row

The weaker modes exist so that inserting a child row (which must verify the parent exists) does not block an unrelated update of that parent's non-key columns.

SKIP LOCKED — the queue pattern

SKIP LOCKED (PostgreSQL 9.5+) tells the executor to silently ignore rows another transaction has locked. That turns a table into a safe multi-consumer work queue with no external broker.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 CREATE TABLE jobs (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state       text NOT NULL DEFAULT 'pending',
    run_after   timestamptz NOT NULL DEFAULT now(),
    payload     jsonb NOT NULL,
    attempts    int NOT NULL DEFAULT 0
);
CREATE INDEX jobs_ready ON jobs (run_after) WHERE state = 'pending';
 
-- Each worker claims a disjoint batch, no coordination required:
WITH claimed AS (
    SELECT id
    FROM jobs
    WHERE state = 'pending' AND run_after <= now()
    ORDER BY run_after
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs j
SET state = 'running', attempts = j.attempts + 1
FROM claimed c
WHERE j.id = c.id
RETURNING j.id, j.payload;

Without SKIP LOCKED every worker queues behind the first one on the same row and throughput collapses to one worker.

NOWAIT

FOR UPDATE NOWAIT raises lock_not_available (SQLSTATE 55P03) immediately instead of waiting. Use it when a fast failure is better than an unbounded wait — an interactive "edit this record" screen, for example.

1
2
3 SELECT * FROM documents WHERE id = 9 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "documents"

Between them: NOWAIT = "fail if busy", SKIP LOCKED = "give me whatever is free", default = "wait your turn".

Table-level locks

Every statement takes a table lock, usually implicitly. The modes, weakest to strongest:

Mode	Taken by	Conflicts with
`ACCESS SHARE`	`SELECT`	`ACCESS EXCLUSIVE` only
`ROW SHARE`	`SELECT ... FOR UPDATE/SHARE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`, `MERGE`	`SHARE` and above
`SHARE UPDATE EXCLUSIVE`	`VACUUM` (non-full), `ANALYZE`, `CREATE INDEX CONCURRENTLY`, many `ALTER TABLE` variants	itself and everything above
`SHARE`	`CREATE INDEX` (non-concurrent)	`ROW EXCLUSIVE` and above, except `SHARE` itself
`SHARE ROW EXCLUSIVE`	`CREATE TRIGGER`, some `ALTER TABLE`	`ROW EXCLUSIVE` and above
`EXCLUSIVE`	`REFRESH MATERIALIZED VIEW CONCURRENTLY`	everything except `ACCESS SHARE`
`ACCESS EXCLUSIVE`	`DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, `REINDEX`, most `ALTER TABLE`, `REFRESH MATERIALIZED VIEW`	everything, including plain `SELECT`

The rule that matters operationally: ACCESS EXCLUSIVE blocks readers, and a queued ACCESS EXCLUSIVE request blocks every later request behind it. A migration waiting behind one long SELECT will freeze the whole table for everyone who arrives afterwards. Always set a lock_timeout before DDL:

1
2
3 SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN promo_code text;   -- fails fast instead of queueing

Inspect current locks with:

1
2
3
4
5
6 SELECT l.pid, l.mode, l.granted, c.relname, a.state, a.query
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted OR c.relname = 'orders';

Chapter 5 — Deadlocks

A deadlock is a cycle in the wait-for graph: A holds a lock B wants and B holds a lock A wants. PostgreSQL detects it after deadlock_timeout (default 1s) and aborts one transaction with SQLSTATE 40P01.

Reproducing one

Two psql sessions:

1
2
3
4
5
6
7
8
9
10
11
12 -- Session A                         -- Session B
BEGIN;                               BEGIN;
UPDATE accounts SET balance =
  balance - 10 WHERE id = 1;         UPDATE accounts SET balance =
                                       balance - 10 WHERE id = 2;
-- (A now holds row 1)               -- (B now holds row 2)
UPDATE accounts SET balance =
  balance + 10 WHERE id = 2;         -- A waits for B
                                     UPDATE accounts SET balance =
                                       balance + 10 WHERE id = 1;  -- B waits for A
-- after ~1s one of them is aborted

Reading the log entry

1
2
3
4
5
6
7
8 ERROR:  deadlock detected
DETAIL:  Process 24118 waits for ShareLock on transaction 5512; blocked by process 24101.
         Process 24101 waits for ShareLock on transaction 5513; blocked by process 24118.
         Process 24118: UPDATE accounts SET balance = balance + 10 WHERE id = 1;
         Process 24101: UPDATE accounts SET balance = balance + 10 WHERE id = 2;
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,3) in relation "accounts"

Everything you need is there: two PIDs, the statement each was executing, and the tuple involved. Set log_lock_waits = on to also log waits that exceed deadlock_timeout without becoming deadlocks — those are the near-misses that precede real ones.

The fix: consistent lock ordering

Deadlocks come from two transactions acquiring the same set of locks in different orders. Impose a global order — sorting by primary key is the standard choice — and the cycle becomes impossible.

1
2
3
4
5
6
7
8
9
10
11 -- Always touch the lower id first, whatever the business direction of the transfer.
BEGIN;
SELECT id FROM accounts
WHERE id IN (:from_id, :to_id)
ORDER BY id
FOR UPDATE;                     -- locks acquired in id order, deterministically
 
UPDATE accounts SET balance = balance - :amt WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amt WHERE id = :to_id;
COMMIT;

Other reliable mitigations: keep transactions short, do not interleave user think-time inside a transaction, and touch tables in the same order everywhere in the codebase. And keep the retry loop from Chapter 3 — a deadlock abort is retryable by definition.

Chapter 6 — Advisory locks

Advisory locks are application-defined mutexes stored in PostgreSQL's lock manager. The database attaches no meaning to them; you choose the key.

1
2
3
4
5
6
7
8
9
10
11
12 -- Session-scoped: held until unlocked or the session ends
SELECT pg_advisory_lock(42);
SELECT pg_advisory_unlock(42);
 
-- Non-blocking variant: returns true/false immediately
SELECT pg_try_advisory_lock(hashtext('nightly-report'));
 
-- Transaction-scoped: released automatically at COMMIT or ROLLBACK - prefer these
SELECT pg_advisory_xact_lock(42);
 
SELECT * FROM pg_locks WHERE locktype = 'advisory';

Typical uses: making a cron job single-instance across many app servers, serialising a migration, or guarding a per-tenant critical section keyed by tenant ID.

Trick: prefer pg_advisory_xact_lock over pg_advisory_lock. Transaction-scoped locks cannot be leaked by a crashed code path — they disappear at the end of the transaction.

Common pitfall: advisory locks are per session, and PgBouncer in transaction pooling mode hands your session to someone else between statements. Session-scoped advisory locks are unusable there; transaction-scoped ones are fine. Post 07 covers this.

Chapter 7 — Long transactions, wraparound, and "idle in transaction"

Why long transactions are expensive

Vacuum can only remove a dead tuple if no snapshot in the entire cluster could still see it. One transaction that started an hour ago and is still open pins the global horizon at its own snapshot. For that hour, no dead tuple newer than it can be removed anywhere in the database — even in tables that transaction has never touched.

The result is bloat everywhere, slowly, invisibly, until someone notices the disk.

1
2
3
4
5
6
7 -- The offenders, worst first
SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS in_state, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' OR state = 'idle in transaction'
ORDER BY xact_start NULLS LAST;

idle in transaction is the worst state on that list: the client issued BEGIN, did some work, and then went off to call an external API or wait on a user. It holds its snapshot, holds any locks it took, and consumes a connection slot, while doing nothing.

Defences, all of which should be set in production:

1
2
3
4
5 -- postgresql.conf or ALTER ROLE / ALTER DATABASE
idle_in_transaction_session_timeout = '60s'   -- kill idle transactions
statement_timeout = '30s'                     -- kill runaway statements
lock_timeout = '5s'                           -- do not queue forever on a lock

Set them per role rather than globally when a reporting user legitimately needs longer.

Transaction ID wraparound

Transaction IDs are 32-bit. PostgreSQL treats them as a circle: from any XID, roughly 2 billion are "in the past" and 2 billion are "in the future". A tuple whose xmin fell more than 2 billion transactions behind would suddenly appear to be from the future — invisible. To prevent that, old tuples are frozen: marked as unconditionally visible, so their xmin no longer matters.

Freezing is vacuum's other job.

Parameter	Default	Meaning
`vacuum_freeze_min_age`	50 million	Age at which a tuple becomes eligible for freezing during a normal vacuum
`autovacuum_freeze_max_age`	200 million	Age at which an anti-wraparound autovacuum is forced, even on a table nothing writes to
`vacuum_freeze_table_age`	150 million	Age at which vacuum scans the whole table rather than only unfrozen pages

If freezing never catches up, PostgreSQL first warns loudly in the log, then refuses new write transactions to protect the data:

1
2
3 WARNING: database "shop" must be vacuumed within 10985430 transactions
ERROR:   database is not accepting commands to avoid wraparound data loss in database "shop"

Monitor the margin continuously:

1
2
3
4
5
6 SELECT datname,
       age(datfrozenxid) AS xid_age,
       2100000000 - age(datfrozenxid) AS txns_remaining
FROM pg_database
ORDER BY xid_age DESC;

The usual causes of a wraparound scare are a long-lived open transaction, an abandoned replication slot, an orphaned prepared transaction (pg_prepared_xacts), or autovacuum being throttled too aggressively. Check all four.

Chapter 8 — VACUUM, autovacuum, and bloat

Terminology

Bloat — space occupied by dead tuples and free space that has not been returned to the OS.
VACUUM — reclaims dead tuples for reuse within the table, updates the visibility map, freezes old tuples. Takes only SHARE UPDATE EXCLUSIVE; runs concurrently with reads and writes.
VACUUM FULL — rewrites the table into a new file, returning space to the OS. Takes ACCESS EXCLUSIVE for the whole rewrite and needs disk for a second copy.
Autovacuum — the background daemon that runs VACUUM and ANALYZE on a threshold.
Visibility map — per-page bits recording "all tuples on this page are visible to everyone", which is what makes index-only scans possible.

1
2
3
4 VACUUM (VERBOSE, ANALYZE) orders;
VACUUM (FREEZE) orders;
VACUUM FULL orders;          -- only in a maintenance window; consider pg_repack instead

Autovacuum thresholds

A table is vacuumed when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples — by default 50 rows plus 20 % of the table. On a 100-million-row table that is 20 million dead tuples before anything happens, which is far too lenient. Tune the big tables individually:

1
2
3
4
5
6
7 ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor  = 0.01,   -- 1 % instead of 20 %
    autovacuum_vacuum_threshold     = 1000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_limit    = 2000    -- let it work faster on this table
);

PostgreSQL 13 added autovacuum_vacuum_insert_threshold (default 1000), which triggers vacuum on insert-only tables so their visibility map and freeze state keep up — a real fix for append-only time-series tables that previously never got vacuumed at all.

Measuring dead tuples and bloat

1
2
3
4
5
6
7
8
9
10
11 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_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
 
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total,
       pg_size_pretty(pg_relation_size('orders'))       AS heap;

For a precise bloat figure install the pgstattuple extension: SELECT * FROM pgstattuple('orders'); gives exact dead-tuple percentage at the cost of a full scan.

Steady state is a dead-tuple percentage that oscillates and comes back down. A percentage that only climbs means autovacuum is not keeping up: either it is being blocked by long transactions, or its cost limits are throttling it, or the table's thresholds are too lenient.

Watch out: VACUUM FULL is not a routine maintenance command. It locks the table completely, and if the table is bloated because of a long-transaction problem, the bloat comes straight back. Fix the cause; use pg_repack when you need an online rewrite.

Chapter 9 — Contrast with MongoDB

Worth holding both models in mind, especially if you work across the two.

Aspect	PostgreSQL	MongoDB
Default unit of atomicity	The statement (autocommit), extendable with `BEGIN`	The single document
Multi-record transactions	Native since forever; the normal way to work	Available since 4.0 / 4.2, deliberately used sparingly
Isolation inside a transaction	Choice of four levels, `READ COMMITTED` default	Snapshot isolation
Concurrency control	MVCC with row versions on the heap	MVCC in WiredTiger with document-level locking
Retry on conflict	Application must retry `40001` / `40P01`	Driver `withTransaction` retries transient errors automatically
Time limit	None by default; bounded with `statement_timeout`	60 s default `transactionLifetimeLimitSeconds`
Reclaiming dead versions	`VACUUM` / autovacuum, visible in operations	Handled inside the storage engine

The design advice rhymes: in MongoDB you ask "can single-document atomicity do this?" before reaching for a transaction; in PostgreSQL you ask "can one statement do this?" before reaching for SELECT ... FOR UPDATE and a read-modify-write round trip. UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100 needs no explicit lock and no retry at READ COMMITTED; check the row count to detect the insufficient-funds case. See mongo_09_transactions in the MongoDB course for that side in full.

Practice Exercises

Exercise 1 — Watch a row version move

Create a small table, insert a row, and select ctid, xmin, xmax. Update the row twice and observe the ctid change each time. Run VACUUM and check whether the space is reused.

Exercise 2 — Non-repeatable read, then not

In two sessions, demonstrate that a SELECT repeated inside a READ COMMITTED transaction can change when the other session commits — then show the same sequence at REPEATABLE READ returning stable results.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14 -- Session A
BEGIN;                              -- default READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;   -- 1000
-- Session B: UPDATE accounts SET balance = 900 WHERE id = 1;  COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- 900  <- non-repeatable read
COMMIT;
 
-- Session A, second run
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 900
-- Session B: UPDATE accounts SET balance = 800 WHERE id = 1;  COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- still 900  <- stable snapshot
COMMIT;

Exercise 3 — Produce a serialization failure

Build the on-call write-skew scenario. Run it at REPEATABLE READ and confirm the invariant breaks. Run it at SERIALIZABLE and confirm one session receives SQLSTATE 40001.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13 CREATE TABLE on_call (doctor text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO on_call VALUES ('alice', true), ('bob', true);
 
-- Both sessions:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE on_call;          -- 2 in both
-- A:
UPDATE on_call SET on_call = false WHERE doctor = 'alice';
-- B:
UPDATE on_call SET on_call = false WHERE doctor = 'bob';
COMMIT;   -- first one wins
COMMIT;   -- ERROR: could not serialize access due to read/write dependencies

Exercise 4 — Deadlock and fix

Reproduce the two-session deadlock from Chapter 5. Copy the deadlock report out of the server log and identify both statements. Then rewrite both transactions to lock in primary-key order and confirm the deadlock no longer occurs.

Exercise 5 — Job queue with SKIP LOCKED

Build the jobs table. Start three concurrent workers claiming batches of 10. Verify no job is processed twice and that throughput scales with worker count. Then remove SKIP LOCKED and observe the collapse.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14 WITH claimed AS (
    SELECT id FROM jobs
    WHERE state = 'pending' AND run_after <= now()
    ORDER BY run_after
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs j SET state = 'running'
FROM claimed c WHERE j.id = c.id
RETURNING j.id, j.payload;
 
-- verification: every id appears exactly once across all workers
SELECT id, count(*) FROM processed_log GROUP BY id HAVING count(*) > 1;

Exercise 6 — Long transaction blocks vacuum

Open a transaction in session A and leave it idle after one SELECT. In session B, generate heavy update churn on an unrelated table and run VACUUM (VERBOSE). Read the "dead row versions cannot be removed yet" line. Commit A and vacuum again.

Exercise 7 — Autovacuum tuning

Take a table with high update churn and record its n_dead_tup curve over an hour. Lower autovacuum_vacuum_scale_factor for that table only, and show the curve flattening.

Cheat-sheet

What	How
Start a transaction	`BEGIN;` … `COMMIT;` / `ROLLBACK;`
Partial rollback	`SAVEPOINT s;` … `ROLLBACK TO SAVEPOINT s;`
Set isolation for one transaction	`BEGIN ISOLATION LEVEL REPEATABLE READ;`
Set the session default	`SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ...`
See row versions	`SELECT ctid, xmin, xmax, * FROM t;`
Current transaction ID	`SELECT pg_current_xact_id();` (13+), `txid_current()` earlier
Serialization failure	SQLSTATE `40001` — retry the whole transaction
Deadlock	SQLSTATE `40P01` — retry, then fix lock ordering
Lock a row for update	`SELECT ... FOR UPDATE`
Weaker row lock	`FOR NO KEY UPDATE`, `FOR SHARE`, `FOR KEY SHARE`
Queue consumer	`FOR UPDATE SKIP LOCKED LIMIT n`
Fail instead of waiting	`FOR UPDATE NOWAIT` (SQLSTATE `55P03`)
Blocking DDL lock	`ACCESS EXCLUSIVE` — always precede DDL with `SET lock_timeout`
Inspect locks	`pg_locks` joined to `pg_stat_activity`
Log lock waits	`log_lock_waits = on`, `deadlock_timeout = '1s'`
Application mutex	`pg_advisory_xact_lock(key)`
Find long transactions	`pg_stat_activity` ordered by `xact_start`
Kill idle transactions	`idle_in_transaction_session_timeout = '60s'`
Dead tuples per table	`pg_stat_user_tables.n_dead_tup`
Wraparound margin	`SELECT datname, age(datfrozenxid) FROM pg_database;`
Aggressive autovacuum for one table	`ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01);`
Online table rewrite	`pg_repack`, not `VACUUM FULL`

What's next

Post 06 covers schema design, normalization and JSONB — normal forms worked through one progressively decomposed example, natural versus surrogate keys and why UUIDv4 hurts index locality, the standard modelling patterns for hierarchies and many-to-many relationships, range types with exclusion constraints for temporal data, when a column should be JSONB and when it absolutely should not, and how to run migrations that do not take an ACCESS EXCLUSIVE lock at the worst possible moment.

References

Concurrency Control
Transaction Isolation
Explicit Locking
SELECT ... FOR UPDATE / locking clauses
Routine Vacuuming, including wraparound
Advisory lock functions
pg_locks
Ports & Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012.
MongoDB course, post 09 (mongo_09_transactions) — the counterpart treatment of multi-document transactions.

Note: this post is in progress. Planned additions: a SSI predicate-lock memory-exhaustion walkthrough, two-phase commit (PREPARE TRANSACTION) and when it is justified, and a measured comparison of HOT-update rates at different fillfactor settings.