Checklists

A checklist is this book with the arguments taken out: one line per habit, each linked to the section that shows why. The sections follow the playbook's order, cheapest lever first (The order is the method). Find the expensive work. Fix plans, indexes, and schema. Keep upkeep, connections, and configuration healthy. Only then ask for a bigger box.

Work top to bottom. Skip an item only when your measurements say it's fine, never because a later one looks easier. The last section is for the day it's already slow.

Find the expensive work before you fix anything

The slow query you're thinking of is rarely the one costing the most. Measure first, and keep measuring after every change.

• Install pg_stat_statements with track_io_timing = on (setting it up), and diff two snapshots over a busy window (snapshot and diff).
• Rank statements by total time and by shared blocks per call (four rankings).
• Sample wait events under the load that hurts, and name the resource (sample waits).
• Write one sentence before you change anything: bound by X, because Y shows Z (find the expensive work).
• Change one thing at a time, and measure the same workload on production-shaped data before and after (measure it end to end).
• Record pages per call before and after every fix; 35,249 to 91 is what a good line looks like (the first index).

Review the plan, not the code

An ORM call that looks harmless can read the whole table. Every query that runs per request gets one explain (analyze, buffers) before it ships.

• Run the plan on production-shaped data; wrap writes in begin and rollback (EXPLAIN guesses; EXPLAIN ANALYZE runs it).
• Read the Buffers line first, and compare pages touched with rows returned (buffers are the evidence).
• Multiply a node's actual time and rows by its loops before you judge it: 70 ms at loops=20 is 1.4 seconds (Multiply by loops; buffers are already totals).
• Compare estimated and actual rows bottom up; at the lowest node off by 10x or more, fix that estimate before anything above it, with extended statistics for correlated columns (extended statistics).
• Keep indexed columns bare: no function or cast on the column, parameters in its type (functions on columns, implicit casts).
• Write time ranges half-open (date ranges).
• Paginate with keyset over a matching index, never deep OFFSET (OFFSET reads every row it skips).
• Replace N+1 loops with a join, one = any($1) array, or lateral (N+1 is a loop, not a slow query).
• Grep the code for not in (select; rewrite each as not exists, which is also correct when the subquery returns a NULL (NOT IN is a trap; use NOT EXISTS).
• Don't count(*) a big table per request: estimate, cap, or read a rollup (exact counts).
• When a plan with an or across two joined tables shows a sequential scan on either side, rewrite it as a union of two indexed queries (OR across a join defeats both indexes).
• Write CTEs for readability, and add materialized only on purpose (A CTE used once is inlined, not fenced).
• Use on conflict for application upserts, merge for batch jobs, and an idempotency key for retried writes (idempotency keys).
• Do read-modify-write arithmetic in SQL, not in the app (read committed).
• Commit per batch, every few thousand to few hundred thousand rows on a live table, never per row, and load bulk data with COPY (commit once per batch).
• Select only the columns you render; wide values live in TOAST (Large values are compressed, then moved out of the row).
• Keep logic on the primary only if it shrinks data, keeps a rule atomic, or saves a round trip; format in the app (our position).
• Suspect the generic plan when a prepared query slows after five calls (prepared statements).
• Distrust a Limit above an index scan that throws away most of what it reads; index the filter (Limit bets on finding rows early).
• Never hold a transaction open across a network call or a user's think time (long transactions).

Edges that bite once

Each of these runs, returns something plausible or fails politely, and surprises most teams once. Catch them in review, so the surprise happens in a test.

SQL that runs and is wrong

• Use clock_timestamp() when you mean the wall clock; now() is the transaction's start (timestamps).
• Cast one side to numeric before dividing integers; 7/2 is 3 (division, ||, and NULL ordering).
• Join strings that may hold a NULL with concat_ws() or coalesce(), not bare || (same section).
• Add nulls last to newest-first sorts on nullable columns, and to the index that serves them (same section).
• Group by every selected column or the primary key, and say any_value() when any value will do (smaller things).
• Update in batches chosen by key; there is no UPDATE ... LIMIT (backfills).
• Skip updates that change nothing; a no-op still writes a row version and its WAL (where the WAL comes from).

Transactions

• Expect one error to abort the whole transaction; prevent it with on conflict, or contain it with a savepoint (one error aborts).
• Keep savepoints, exception blocks, and ORM nested transactions out of loops; past 64 in one transaction, every session pays (subtransactions).
• Retry the whole transaction on 40001 and 40P01, with capped, jittered backoff, and on nothing else (retry).
• Lock a parent row for no key update unless you'll delete it or change its key; for update blocks inserts of its children (row locks).
• Keep truncate for staging tables and tests; an older snapshot sees the table empty (a delete only stamps the row).

An index earns its write tax or it goes

Every index is a bet: faster reads for some queries, a tax on every write for as long as it exists. Make each one name the query it serves.

• Design from the query, equality columns first, then one range or sort column, and name the query it serves (step by step).
• Don't index a column with a handful of values on its own (Why 2% of the rows can cost 68% of the table).
• Use a partial index for the hot subset, with its predicate written as literals (partial indexes).
• Use an expression index when the query must use the expression, like lower(email) (expression indexes).
• Pin the time zone in date expressions, ((created_at at time zone 'UTC')::date), and never label a function immutable to pass the check (why created_at::date can't be indexed).
• Under a linguistic collation, serve like 'abc%' with a text_pattern_ops index, and '%abc%' with trigrams (prefix search).
• Cover the hot query with include, not every query (covering indexes).
• On every Index Only Scan, compare Heap Fetches with the rows returned; close to the row count means vacuum isn't keeping the visibility map current (index-only scan).
• Pick the index type for the operator: GIN, trigram, GiST, BRIN, or hash (choosing an index type).
• Use jsonb_path_ops for containment; no GIN serves payload->>'key' = 'x' (jsonb containment).
• Find and index unindexed foreign keys (the query).
• Count the write tax before you add one: every index is touched by every insert and can turn updates cold (a tax on writes).
• Drop indexes with idx_scan = 0 over a long window, checked on every replica, unless they back a constraint, and drop duplicates (unused and duplicate).
• Ask HypoPG before building on a big table (hypothetical questions).
• Rebuild a bloated index with reindex concurrently (measuring bloat).
• Use plain create index only on new, empty, or write-free tables; everything that takes writes gets concurrently (the decision rule).
• After a concurrent build, check for an INVALID leftover and drop it before retrying (when a build fails).
• Reindex text indexes after any OS, glibc, or ICU upgrade, and only then refresh the recorded collation version (collations, detect it).

Search inside Postgres before you add an engine

Full-text and vector search are indexes with their own costs. Measure recall and pages the way you'd measure any other query.

• Pass the text search configuration, such as 'english', in every call; an index built for one doesn't serve another (the configuration).
• Store the tsvector in a generated column with a GIN index; ranking through an expression index took 170 ms against 4.6 (store the tsvector).
• Parse anything a person types with websearch_to_tsquery (user input).
• Narrow by tenant or date before ranking, and run ts_headline only on the rows you return (snippets).
• Before trusting an approximate vector index, measure recall@10 against an exact search on your own queries (exact search).
• On HNSW, keep hnsw.ef_search at 40 or above (recall 0.97 in our test) and raise it per query where misses are expensive (ef_search trades latency for recall).
• On IVFFlat, never leave ivfflat.probes at 1: recall@10 was 0.636 at one probe and 1.000 at five (IVFFlat's default of one probe is a trap).
• With a filter on a vector search, turn on hnsw.iterative_scan, or let a B-tree make a selective filter exact (filters).
• Merge keyword and vector results with reciprocal rank fusion, not by adding their scores (hybrid search).

Review the schema before the first insert

The schema decides what every row costs for as long as the table lives, and changing it later is a migration, not a deploy.

Types

• Use bigint for every id and every column that references one (ids are bigint).
• Use timestamptz for every moment, with sessions in UTC (always timestamptz).
• Use text with a check constraint, not varchar(n), and never char(n) (just use text).
• Store money as numeric or bigint cents beside a currency column, never as a float (money).
• Prefer archived_at to is_archived, and text plus a check to an enum you'll want to change (small sets).
• Choose one case-insensitive strategy per column and enforce it in the schema (pick one).
• Default new databases to the builtin C.UTF-8 collation, and ask for a linguistic one only where a person reads the order (three providers).
• Never quote identifiers: unquoted snake_case, with the ORM's naming strategy set before the first migration (smaller things).
• Use identity columns, not serial; skip money, char(n), and timetz (types to avoid).

Keys and public ids

• Give every table a surrogate bigint generated always as identity key; make natural keys unique (natural keys change).
• Never build invoice numbers or change feeds on a sequence: it has gaps and isn't ordered by commit (gaps).
• Use uuidv7() stored as uuid if you need UUIDs; random v4 keys cost 3.8 times the WAL (random keys).
• If clients mint ids, keep a server created_at, validate each id, and answer a conflict with do nothing plus a lookup scoped to the caller's account, never do update (A client's UUIDv7 is input, not a clock).
• Never hand out a counter: publish a separate public id, stored as uuid, printed with a prefix (store the uuid, print the prefix).
• Put account_id on every tenant table and lead keys with it (carry the tenant).
• Use composite foreign keys so references can't cross tenants (make it impossible).

Constraints, chosen on purpose

• Store each fact once, and give every deliberate copy exactly one writer (one writer).
• Keep foreign keys on accounts, users, projects, memberships, and money (where we keep them).
• Drop them, or declare them not enforced, on events, logs, metrics, audit trails, cross-boundary references, and imports (same section).
• Schedule an orphan check for every reference you stopped enforcing (check what you no longer enforce).
• Index every enforced foreign key on the referencing side (the delete that scans).
• Cascade deletes only to small dependent rows (use cascade deliberately).
• Make columns not null unless unknown is a real state, and say the rest with check (check constraints).
• Don't trust unique on nullable columns, since it admits any number of NULLs: make the columns not null, or write unique nulls not distinct (nulls are distinct).
• Use an exclusion constraint for "no overlaps", not application code (exclusion constraints).
• Give soft-deleted tables a partial unique index (soft deletes).
• Replace polymorphic type/id pairs with real foreign key columns (polymorphic associations).
• Enforce rules across rows with a constraint, serializable, or a parent-row lock, never a check that reads other rows (invisible to constraints).

Layout and HOT

• Order columns from 8-byte fixed-width down to 1-byte, variable-length last (column order is free space).
• Give update-heavy tables fillfactor = 90; leave append-only tables at 100 (leave room).
• Don't index updated_at or anything most updates change (don't index updated_at).
• Move constantly changing counters to a narrow table (hot counters).
• Watch the HOT share of update-heavy tables, and find out why when it falls (measuring HOT).

jsonb or columns

• Use jsonb, never json, for documents (jsonb).
• Promote any field you filter, join, sort, or constrain on to a typed column; the planner can't see inside jsonb (when to promote).
• Never build entity-attribute-value tables (a trap).

Functions and triggers

• Write a function that is one query in SQL, labeled stable or immutable, so the planner can inline it (inlining).
• Label volatility as tightly as the truth allows, never tighter (volatility).
• Budget a row trigger at about a microsecond a row, and an audit trigger at about two, before you put one on a table that takes bulk jobs (trigger cost).
• Never maintain a parent's counter from a child's trigger; it serializes every writer on that parent row (counter triggers).
• Add a trigger only when the work must be atomic with the write and happen for every writer (earn its place).
• Turn on track_functions so functions show up in pg_stat_user_functions, and pg_stat_statements.track = all so statements inside them get their own entries (tracking).

Check every migration for the lock it waits on

A migration is safe when it can't queue the site behind it. The ALTER is rarely slow; the wait for its lock is the outage.

• Set lock_timeout to 1 to 5 seconds on every migration session, retry, and add a generous statement_timeout (rule one).
• Never let DDL wait in the lock queue: one waiting ALTER blocks every later query on its table, reads included (the lock queue).
• Check for long and idle-in-transaction sessions before you start (long transactions).
• Label each step catalog only, scan, or rewrite before you run it (metadata, scan, or rewrite).
• Add columns with constant defaults; never a volatile default on a big table in one step (defaults).
• Add check constraints not valid, then validate in a separate transaction (check constraints).
• Add foreign keys not valid, then validate; both lock the child and the parent (foreign keys).
• Reach not null through a validated check, or not null ... not valid on PostgreSQL 18 (NOT NULL goes through a CHECK first).
• Choose the index build by the table's traffic (pick the build).
• Run concurrent builds outside a transaction, with lock_timeout off, then check indisvalid (four rules).
• Build unique constraints and primary keys from a concurrent unique index (unique constraints).
• Never run plain reindex on a live table (Rebuild with REINDEX CONCURRENTLY; plain REINDEX blocks reads too).
• Treat type changes as rewrites unless they widen, and plan int to bigint as an online swap (the int to bigint problem).
• Ship renames as expand and contract, across deploys (expand and contract).
• Backfill in batches by primary-key range, outside the migration, each batch its own transaction (backfills).
• Drop the code first, then the column (drop code first).
• Keep each migration transaction short; DDL holds every lock until commit (transactional DDL).
• Attach partitions with a validated check, and detach them concurrently (convert a live table).
• Rehearse on a production-sized clone (copy production).

Bulk loads and partitions

• Load with COPY, or unnest arrays if you must insert; never one autocommit insert per row (COPY is the bulk path).
• Load into a bare table, then build indexes and add foreign keys not valid plus validate: six times faster and half the WAL in our load (after the load).
• Use copy ... freeze into a table created or truncated in the same transaction, so the first read doesn't write (COPY FREEZE writes the rows finished).
• Raise max_wal_size before a big load, and finish with vacuum (freeze, analyze), or at least analyze, before traffic reads the table (before you open the doors).
• Upsert in bulk from a deduplicated staging table in one statement (bulk upserts).
• Partition by the key you delete by, usually time, and keep the count in the tens to low hundreds (choose the key).
• Run explain on every hot query against a partitioned table and confirm pruning: only the matching partitions appear, or Subplans Removed is above zero (pruning).
• Create future partitions ahead of time, and alert when they run short (create ahead).
• Analyze a partitioned parent yourself after loads and retention; autovacuum never does (smaller traps).

Production readiness

Most outages in this book grow quietly for months, then fail all at once. Check these before launch, and again every time the data grows tenfold.

Backups and recovery

• Write down RPO and RTO first; a replica is not a backup (Write down RPO and RTO first).
• Take physical base backups plus continuous WAL archiving, to storage the database host can't delete (use a backup tool).
• Practice point-in-time recovery to a separate server with recovery_target_action = 'pause' (recovering to a point in time).
• Automate restore tests and record the real RTO (test your restores).
• On a managed service, time a real restore; it builds a new server (restore time).

Monitoring and alerts

• Alert on the short list: unreachable, transaction ID age, long transactions, replication, disk, archiving (what to alert on).
• Turn on slow-query, lock-wait, temp-file, and autovacuum logging, plus auto_explain (logging).

Connections and timeouts

• Use an app pool everywhere, and an external pooler for many processes or serverless (pool in both places).
• Size the pool from cores and disks, not users (size the pool).
• Keep max_connections in the low hundreds, with slots reserved for admin (max_connections).
• Set timeouts on the app role, starting from statement_timeout = '5s', lock_timeout = '3s', idle_in_transaction_session_timeout = '30s', and transaction_timeout = '5min' on PostgreSQL 17+; run migrations and batch jobs as a separate role with their own limits (timeouts).
• Make the app reconnect and retry through a failover (failover is DNS).
• Before moving a pooler to transaction mode, replace session set, session advisory locks, listen, SQL prepare, and temp tables (transaction pooling).
• Run PgBouncer 1.21 or later for prepared statements in transaction mode; 1.24 turns it on by default (newer PgBouncer).
• Build queues on a table claimed with for update skip locked, committed with the business write, and keep listen/notify modest (the queue).

Configuration and the box

• Budget memory before tuning: shared_buffers, then work_mem times nodes times connections (memory).
• Raise work_mem per role for reports, never globally (work_mem).
• Set random_page_cost = 1.1 on SSDs (cost model).
• Raise max_wal_size for timed checkpoints, and turn on wal_compression (Space out checkpoints and size max_wal_size to your WAL rate).
• Keep the OOM killer off the postmaster (the OOM killer).
• Never run with fsync = off or full_page_writes = off (durability).

Vacuum and wraparound

• Set per-table scale factors of 0.01 to 0.05 on big tables, and raise the cost limit well above 200 (the defaults are wrong).
• Find whatever holds back the cleanup cutoff: sessions, slots, feedback, prepared transactions (what blocks vacuum).
• Alert on age(datfrozenxid) at 500 million to 1 billion, and never kill an anti-wraparound vacuum (wraparound).
• Graph sequences against their type's maximum (the int to bigint problem).

Replication and failover

• Cap every slot with max_slot_wal_keep_size, and alert on inactive ones (slots).
• Decide how many acknowledged commits a failover may lose (asynchronous is the default).
• Use a failover manager, never a script, and practice on a schedule (don't hand-roll failover).
• Keep failover candidates apart from reporting replicas (replica queries fight replay).
• Route read-your-writes to the primary, or wait for the write's LSN (read-your-writes).

Security and tenancy

• Connect the app as a role that isn't a superuser, has no BYPASSRLS, and doesn't own the tables (the app never owns its tables).
• Set default privileges so new tables get the right grants (default privileges).
• Enable and force row-level security on every tenant table (RLS skips the table owner unless you force it).
• Set the tenant per transaction through one helper (pooled connections leak tenants).
• Lead indexes with the tenant column; policies are predicates (RLS performance: policies are predicates).
• Pin search_path on every security definer function (SECURITY DEFINER functions need a pinned search_path).
• Require hostssl, scram-sha-256, and sslmode=verify-full (Use scram-sha-256 and verify-full, never trust).

Managed or self-managed

• Buy as much management as you can afford; self-manage only for a named reason, with the people to own it (what we'd do).
• Write down the lower of your volume's and instance's I/O limits (two ceilings).
• Check the extension allowlist before designing around one (allowlist).
• Rehearse major upgrades on a clone (upgrades).
• Plan the exit before you need it (plan the exit).

Before you ask for a bigger database

Our answer to an upsize request is no until the sections above are done and the data shows it (Earn the bigger box). On one app, the playbook took pages per transaction from 91,448 to 38 on the same laptop, with no new hardware at all (the proof).

• Write next to each of the top five statements: fixed, why it can't be, or why it's already cheap (what an upsize request brings).
• Stop if the profile says Lock, LWLock, or Client: a bigger box doesn't shorten a queue (measure before you buy).
• Look for one hot row that every transaction updates before buying cores (don't count with a hot row).
• Measure the working set against RAM; if it fits already, more RAM buys nothing (size RAM to the working set).
• Prove reads dominate before paying for faster storage (is storage your bottleneck?).
• When the bound is CPU, move per-row formatting and side effects off the primary first (the primary is your most expensive CPU).
• Rule out indexes, rewrites, round trips, pooling, configuration, vacuum, and rollups, one sentence each (precompute what you show).
• State the headroom: peak use today, what the new size gives, and how many months it lasts at your growth (ask one question).
• File it on the template (the request, as a ticket).
• Don't reach for replicas, partitions, or shards to fix a slow query; each answers its own signal (the ceiling).

If the evidence says the box is out of a resource the work needs, buy it without argument, and keep the evidence as the baseline for next time.

Before you scale out or add a second system

Each step past one primary answers one signal and multiplies operations. Name the signal and the number before you take it.

• Add a read replica for read load or failover, never for a slow query, and route reads that must see their own writes to the primary (read-your-writes).
• Partition for retention and maintenance, not lookup speed; queries that don't filter on the key search every partition (not faster lookups).
• Shard only on a real signal: writes saturating the biggest primary after tuning and batching, data too big to restore within your RTO, or a residency rule (when to shard).
• Until then, keep the door open: account_id on every tenant table, in every key, and in every query (keep the door open).
• Check the hottest queries' plans for fan-out before you pick a sharding approach (cross-shard queries).
• For analytics, try a rollup, a reporting replica, or pg_duckdb before a column store; a rollup read 12 pages where the table read 35,173 (the middle ground).
• Add a second system only for a property you can name and a number you measured, and keep Postgres the source of truth (a measured reason).

Incident triage: the database is slow right now

Look before you touch. A restart clears the evidence along with the cache, and the cause comes back with the traffic (warm the cache after a restart). Run these in order; each answer tells you which step matters.

1. Count sessions by state and wait. The first query on any slow server:

select state, wait_event_type, wait_event, count(*)
  from pg_stat_activity
 where backend_type = 'client backend'
 group by 1, 2, 3
 order by 4 desc;

• Read the result: active with no wait event is CPU, compared with your core count; Lock means go to step 2; IO DataFileRead is page reads; LWLock is a crowded internal structure; many idle in transaction is the app (wait events).
• Compare the total with max_connections: select count(*), current_setting('max_connections') from pg_stat_activity; (count connections).

2. Find the root blocker. Every session waiting on a lock, and every session blocking one:

select pid, pg_blocking_pids(pid) as blocked_by, state, wait_event,
       now() - xact_start as xact_age, left(query, 50) as query
  from pg_stat_activity
 where cardinality(pg_blocking_pids(pid)) > 0
    or pid in (select unnest(pg_blocking_pids(pid)) from pg_stat_activity)
 order by xact_start;

• Find the root: the row with an empty blocked_by, often idle in transaction (find the root blocker).

3. List the oldest transactions. They hold locks and hold back vacuum:

select pid, state, now() - xact_start as xact_age,
       now() - state_change as in_state, left(query, 50) as query
  from pg_stat_activity
 where xact_start is not null and pid <> pg_backend_pid()
 order by xact_start
 limit 5;

• Treat idle in transaction for minutes as a bug; query is its last statement, not its current one (finding long transactions).

4. End the right session. select pg_cancel_backend(pid) stops a running statement; an idle-in-transaction session has none, so use select pg_terminate_backend(pid), which rolls its transaction back.

• Note what you ended and why, then fix whatever left it open (same section).

5. Sample waits for a minute. One look is a photo; ten a second for a minute is a profile.

• Run the sampler and read it as average active sessions per wait event (take the samples, read the profile).

6. Rank what ran since the trouble started. Snapshot pg_stat_statements, wait a minute, diff:

create temp table ss0 as
select userid, dbid, toplevel, queryid, calls, total_exec_time
  from pg_stat_statements;
 
-- a minute later
select s.calls - coalesce(o.calls, 0) as calls,
       round((s.total_exec_time - coalesce(o.total_exec_time, 0))::numeric) as ms,
       left(s.query, 50) as query
  from pg_stat_statements s
  left join ss0 o using (userid, dbid, toplevel, queryid)
 order by ms desc
 limit 10;

• Look for a trivial statement with a big mean: it's waiting, not working (measure before you buy).
• Run explain (analyze, buffers) on the top statement with real parameters (a method).
• Suspect a plan that flipped: the generic plan, or stale statistics after a load (when the plan is wrong).

7. Check what runs under the queries.

• Check vacuum progress: select pid, relid::regclass, phase from pg_stat_progress_vacuum; (monitoring vacuum).
• Check replica lag: select application_name, state, replay_lag from pg_stat_replication; (watching replication).
• Check archiving: select failed_count, last_failed_time from pg_stat_archiver; (archiving).
• Check wraparound: select datname, age(datfrozenxid) from pg_database order by 2 desc; (wraparound).
• Check the host: CPU against cores, disk latency, and IOPS flat at the volume's ceiling (Throttled I/O shows as flat IOPS and rising latency).

8. Close the loop.

• Write one sentence: bound by X, because Y shows Z (find the expensive work).
• Add the alert or graph that would have shown it an hour earlier (what to alert on).