Indexes, MVCC and Performance: Unlearning the Clustered Index

Previously, in Part 5, we rebuilt your stored procedures in PL/pgSQL. That was translation work. This part is different: it's the one where a mental model you've carried for years — the clustered index — gets gently taken out of your hands.

There is no clustered index

In PostgreSQL, every table is a heap. Full stop. There is no clustered index, no physically-maintained key order, and therefore no distinction between clustered and nonclustered — every index is what you'd call nonclustered: a separate structure whose entries point at row locations in the heap. Yes, Postgres has a CLUSTER command, and it's a trap for the SQL Server brain: it physically sorts the table by an index once, as a one-time operation, and the order decays from the very next insert. Nothing maintains it.

What does this do to primary key design? Mostly, it relaxes it. All those instincts about choosing a narrow, ever-increasing clustering key, and the low-grade anxiety about GUID primary keys fragmenting the table — that whole worry ships back home. A uuid primary key doesn't scatter your table, because the table has no order to scatter. One instinct does survive, though: random keys still make for cold, cache-unfriendly indexes. Time-ordered UUIDs keep recent keys clustered in the hot part of the B-tree, and PostgreSQL 18 ships uuidv7() built in — the best of both worlds for distributed-friendly keys.

MVCC: readers never block writers

The second unlearning is bigger. SQL Server's default locking taught you that readers and writers fight, and it taught you the coping mechanism: WITH (NOLOCK) sprinkled over reporting queries, dirty reads accepted with a sigh. PostgreSQL runs on MVCC — multi-version concurrency control — and the fight simply doesn't happen.

When a transaction updates a row, PostgreSQL doesn't overwrite it — it writes a new version of the row alongside the old one. Every query runs against a consistent snapshot: readers see the versions that were committed when their query (or transaction) began, writers create new versions, and neither waits for the other. You get clean, committed data at READ COMMITTED — no dirty reads, no shared-lock queues behind a long update, no blocking chain from a slow report. Retire NOLOCK, and retire the guilt that came with it.

The bill for MVCC: VACUUM

Old row versions don't vanish on their own. Once no running transaction can see them, they're dead weight — and VACUUM is the process that reclaims that space so it can be reused. In practice you almost never run it by hand: autovacuum watches table activity and cleans up in the background, and on most databases it just works. Map it to your index-maintenance-plan instincts: the same "a healthy database needs housekeeping" reflex, already scheduled for you. Your job shrinks to monitoring — keep an eye on bloat for hot, high-churn tables (a quick look at n_dead_tup in pg_stat_user_tables tells you a lot) and tune autovacuum to run more aggressively on those specific tables if it falls behind.

The index toolbox

B-tree is the default in both engines, and your B-tree knowledge transfers wholesale — if you want the refresher, our indexes explainer covers the fundamentals. What PostgreSQL adds is a wider shelf:

And your covering-index play survives intact — INCLUDE exists in PostgreSQL with the same meaning:

-- Covering index: same instinct, same keyword
CREATE INDEX ix_orders_customer ON orders (customer_id)
    INCLUDE (placed_at, total);

-- Partial index: only active users
CREATE INDEX ix_users_active_email ON users (email)
    WHERE is_active;

-- Expression index: solves Part 4's case-sensitivity gotcha
CREATE UNIQUE INDEX ix_users_email_ci ON users (lower(email));

Two PostgreSQL 18 notes worth knowing before you size hardware or design indexes: skip scans let a multicolumn B-tree index serve queries that don't filter on its leading column, so fewer near-duplicate indexes; and asynchronous I/O delivers up to 3× faster sequential reads, which changes the math on how scary a big sequential scan really is.

Reading a plan without the pretty pictures

There's no SSMS-style graphical plan; there's EXPLAIN ANALYZE, which actually runs the query and reports text. Where you read graphical plans right-to-left, you read these innermost-out: the most-indented node runs first, and results flow up to its parent.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM appointments
WHERE patient_id = 42
ORDER BY starts_at DESC
LIMIT 10;

 Limit (actual time=0.041..0.049 rows=10 loops=1)
   Buffers: shared hit=5
   ->  Index Scan Backward using ix_appt_patient_starts on appointments
         (actual time=0.039..0.045 rows=10 loops=1)
         Index Cond: (patient_id = 42)

Reading it: the inner node ran an index scan backward (satisfying the descending sort for free), found 10 rows in a fraction of a millisecond, and the Limit node above it stopped there. BUFFERS is the option you'll learn to love — shared hit=5 means five pages, all from cache. The habits transfer directly: compare actual rows against estimates to spot stale statistics, and watch for sequential scans where you expected an index — same instincts, new typeface.

Faster reads, no blocking, and one less thing to defragment — a fair trade for learning to love a background vacuum. Next we cover the topic every DBA checks before agreeing to migrate anything: Part 7: backup and restore for SQL Server DBAs.