Indexes and MVCC

A database query must answer two different questions: where might the matching rows be, and which versions of those rows may this transaction see? An index supplies an access path. Multiversion concurrency control (MVCC) lets concurrent transactions read appropriate row versions while other transactions change the data. Finding a row quickly does not make an unsafe update correct, and a consistent snapshot does not make a full-table scan cheap.

These examples use PostgreSQL 18.6. Other databases can use different index structures and isolation rules. The complete runnable lab requires Python 3.11+, Docker, and the postgres:18.6 image. It creates an isolated database, coordinates separate connections, asserts the observations, and removes its container and data afterward. It publishes no host ports and installs no application dependencies.

docker pull postgres:18.6
# Unzip the download, then run from its directory:
python3 lab.py
# PASS all assertions

Narrow the search before fetching rows

A B-tree keeps keys in sorted order, allowing a query to navigate to a useful interval rather than inspect every row. PostgreSQL stores this index separately from the table heap. An ordinary index scan follows matching entries to heap rows; this additional access becomes expensive when the query needs much of the table.

The lab inserts 100,000 orders across 100 tenants, with 20% marked open. Each tenant has 200 open orders. Its primary key already indexes id, which does little for a tenant's recent-order query. Run the same statement before and after adding an index designed for that request:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX orders_tenant_status_time
  ON orders (tenant_id, status, created_at DESC);

-- Repeat EXPLAIN for the identical SELECT.
-- Before: sequential scan, then sort, then limit.
-- After: ordered index scan, then limit.

Read a plan from its inputs upward. Initially, the scan visits the table and filters candidates; the sort orders the survivors. With the composite index, the two equalities identify a contiguous key interval, already ordered by descending time. The scan can stop after producing 20 rows. The fixture checks that both plans return the same IDs and that a nonexistent tenant returns no rows.

Selectivity describes how much data matches a predicate. Selecting 200 of 100,000 rows favors a narrow access path; fetching every order generally favors a sequential scan, which the lab checks separately. An index existing does not oblige the planner to use it. Small tables, stale statistics, heap access costs, and different distributions can change the decision. ANALYZE supplies estimates about the data; compare estimated rows with actual rows when diagnosing a plan.

Column order follows the queries you need to serve. In a composite B-tree, leading equalities followed by a range constraint give a useful scan boundary. (tenant_id, status, created_at) suits this query; it does not provide the same ordering for every query on created_at. PostgreSQL 18 can sometimes use skip scans for predicates missing a leading key when that key has few distinct values. Treat “leftmost columns matter” as a design principle, not a claim that other predicates can never use the index.

EXPLAIN ANALYZE executes the statement. BUFFERS exposes database block activity, and planner costs are estimates in cost units, not milliseconds. The lab records block counts without asserting a universal latency improvement. Warm caches and storage change elapsed time.

An index also consumes space and write work. Inserts maintain index entries; updates can create new row versions and index entries. PostgreSQL's heap-only tuple optimization can avoid new index entries when indexed columns remain unchanged and the page has enough space. Indexing every column makes that opportunity harder to preserve. A covering index can hold additional requested values, but an index-only scan must still establish visibility, sometimes by visiting the heap. Measure the read benefit against storage, write amplification, and maintenance.

A snapshot chooses visible versions

An update does not immediately erase the previous row version. PostgreSQL uses transaction metadata and snapshot rules to decide which versions a reader can see. A snapshot is a visibility boundary over shared data; beginning a transaction does not copy the database.

For a counter initially containing 10, alternate commands between two connections. In the lab, B's update happens after A has read:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT value FROM counter WHERE id = 1;  -- 10

-- Session B
BEGIN;
UPDATE counter SET value = 11 WHERE id = 1;
-- Before B commits, A still reads 10.
COMMIT;

-- Session A
SELECT value FROM counter WHERE id = 1;  -- still 10
COMMIT;
SELECT value FROM counter WHERE id = 1;  -- 11

PostgreSQL's default Read Committed isolation obtains a fresh snapshot for each statement. Repeat this sequence with BEGIN ISOLATION LEVEL READ COMMITTED: A's second statement sees 11 after B commits, even within A's existing transaction. Neither isolation level exposes B's uncommitted value. A transaction can also see its own earlier writes.

At Repeatable Read, the snapshot is established by the first non-transaction-control statement and retained for the transaction. This is useful when several reads need the same view. It also means the application may knowingly work with an older view. Repeatable Read does not guarantee that a multi-row business invariant survives every concurrent execution; Serializable provides a stronger guarantee and can require transaction retries.

If a Repeatable Read transaction tries to update a row changed since its snapshot, PostgreSQL can reject the write with SQLSTATE 40001. The lab exercises this failure. Recovery retries the whole transaction so its decisions are recomputed from a new snapshot.

Put the condition inside the write

MVCC allows ordinary reads and writes to overlap, but competing writers still coordinate with locks. A prior SELECT quantity followed by an unconditional decrement leaves a race between checking availability and consuming it.

For stock initially equal to one, use a guarded update with a database constraint as a second boundary:

CREATE TABLE stock (
  id integer PRIMARY KEY,
  quantity integer NOT NULL CHECK (quantity >= 0)
);
INSERT INTO stock VALUES (7, 1);

-- Session A, Read Committed
BEGIN;
UPDATE stock SET quantity = quantity - 1
WHERE id = 7 AND quantity >= 1
RETURNING quantity;                    -- one row: 0
-- Leave A uncommitted while B runs the same UPDATE.

-- Session B waits for A. After A commits:
-- B returns zero rows; quantity remains 0.

The fixture dispatches B on another connection and observes pg_blocking_pids before committing A. This proves an actual conflict instead of hoping two fast operations overlap. At Read Committed, once A commits, B reevaluates its search condition against the updated row. Zero no longer satisfies quantity >= 1, so B does not decrement it.

Treat the returned row or affected-row count as the outcome. Zero rows means this operation did not reserve stock; it could mean insufficient stock or a missing product. The example checks both, plus a deliberately unguarded negative update that the CHECK constraint rejects. A returned row inside an explicit transaction is still provisional until commit succeeds.

This statement protects a one-row condition. A transfer, booking across several resources, or invariant over many rows needs a transaction design that covers all participating data. Locks, constraints, ordering, isolation, and retry behavior must match that invariant. A fast index helps locate rows; it does not supply those guarantees.

Old readers keep old versions alive

Version retention is the cost of letting readers keep their view. PostgreSQL's VACUUM makes obsolete versions reusable when they are no longer potentially visible. An old snapshot can hold back that work while updates and deletes continue.

The lab creates 5,000 rows in a disposable churn table. It disables autovacuum on that table solely to make the demonstration controlled. Keep one reader's snapshot open across another connection's committed deletion:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM churn;             -- 5000; establishes snapshot

-- Session B, autocommit
DELETE FROM churn;
SELECT count(*) FROM churn;             -- 0
VACUUM (VERBOSE, TRUNCATE false) churn;
-- Reports 5000 dead tuples not yet removable.

-- Session A
SELECT count(*) FROM churn;             -- still 5000
COMMIT;

-- Session B
VACUUM (VERBOSE, TRUNCATE false) churn;
-- Now removes the 5000 obsolete versions.

Both readers were correct for their snapshots. The retained versions were necessary until A finished. A long transaction does not necessarily block every cleanup operation, but it can prevent removal of versions newer than its visibility horizon. Repeated churn can therefore increase space use and maintenance work.

The fixture checks the verbose vacuum report and dead-row statistics before and after releasing the snapshot. It also checks that heap file size stays unchanged with truncation disabled: reusable database space and space returned to the operating system are different outcomes. Routine vacuuming supports reuse; rewriting a table to shrink it has different locking and space costs.

Keep transactions bounded, avoid waiting for user input inside them, and investigate old snapshots alongside dead-row growth. Leave autovacuum enabled in normal operation. Index choice, visibility rules, and cleanup are connected: each optimization trades work now for work later, and the useful design is the one that keeps both reads and writes sustainable.