Appendix B: The Checklist

The Diagnostic Queries

Run these before changing anything.

Find your most expensive queries (Chapter 18)

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 1) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total_exec_time, not mean_exec_time. A query called 10,000 times at 50ms each is a bigger problem than a query called once at 5 seconds.

Check your connections (Chapter 15)

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

Mostly idle? Pooling problem. Mostly active? Slow query problem. Idle in transaction? Application bug.

Check table health (Chapter 16)

SELECT relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Dead tuple percentage above 10%? Autovacuum needs tuning.

Diagnose any individual slow query (Chapter 18)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Read from bottom to top. Sequential scan on a large table — missing index. Nested loops with high row counts — unindexed join column. Sort using external merge — increase work_mem or add a pre-sorted index.

The Optimization Order

Cheapest and most effective first. Do not skip steps.

1. Connection pooling — Minutes to implement. PgBouncer or PgCat in transaction mode. Set ORM pool size to 1 in serverless functions. This alone prevents the most common production failure: connection exhaustion. (Chapters 15, 17)
2. Vertical scaling — Minutes, zero code changes. A larger instance buys immediate headroom while you implement the structural optimizations that follow. Unfashionable but effective. (Chapter 16)
3. Fix N+1 queries — Hours. Enable ORM query logging. If a page generates 20+ queries, use eager loading. Django: select_related + prefetch_related. Rails: includes. Hibernate: @EntityGraph. Laravel: with(). Prisma: include. GORM: Preload. (Chapter 3)
4. Add missing indexes — Minutes per index. Check foreign key columns first — PostgreSQL does not auto-index the referencing side. Add B-tree indexes for WHERE and JOIN columns. Add partial indexes when queries consistently filter to a subset. Add covering indexes with INCLUDE for index-only scans. (Chapters 1, 16, 18)
5. Create materialized views — Hours. For any query that runs repeatedly, takes more than 50ms, and powers a dashboard, report, or aggregated API response. Create the view. Add a UNIQUE index. Schedule refresh with pg_cron. The dashboard that took 7 seconds now takes 7 milliseconds. (Chapters 4, 5, 6)
6. Add read replicas — Hours to provision. Route reads to replicas, writes to the primary. Start with one replica (doubles as failover target). Most web applications are 80–95% reads. (Chapter 16)
7. Partition large tables — Hours to days. Range partition by date for time-series data. Partition pruning is automatic. Not sharding — all data stays on one server. (Chapter 16)
8. Add a caching layer — Hours. Redis or Memcached in front of materialized views for the hottest data. Only after steps 1–7 are exhausted. (Chapter 18)
9. Shard — Weeks to months. Permanent complexity. Only when write volume exceeds a single well-optimized server after all of the above. Citus or application-level routing by tenant_id. (Chapter 16)

Materialized View Checklist

Create

CREATE MATERIALIZED VIEW mv_name AS
    SELECT ... FROM ... GROUP BY ...
WITH DATA;

Index immediately (required for CONCURRENTLY)

CREATE UNIQUE INDEX idx_mv_name_pk ON mv_name (key_columns);

Schedule refresh

CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('refresh-mv-name', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name');

Prevent concurrent refresh storms

CREATE OR REPLACE FUNCTION safe_refresh(p_view TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql AS $$
DECLARE lock_id BIGINT;
BEGIN
    SELECT oid INTO lock_id FROM pg_class
    WHERE relname = p_view AND relkind = 'm';
    IF NOT pg_try_advisory_lock(lock_id) THEN RETURN FALSE; END IF;
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', p_view);
    PERFORM pg_advisory_unlock(lock_id);
    RETURN TRUE;
END $$;

Tune autovacuum for CONCURRENTLY-refreshed views

ALTER MATERIALIZED VIEW mv_name SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 50
);

Monitor freshness

SELECT view_name, last_refreshed, NOW() - last_refreshed AS staleness
FROM mv_refresh_log
ORDER BY staleness DESC;

The ORM Integration Pattern

Every framework, same pattern:

1. Create the materialized view in a migration (raw SQL).
2. Map a read-only model to it.
3. Query with the ORM's normal methods — it's a table.
4. Schedule refresh with your framework's task scheduler or pg_cron.

Connection Pooler Selection

All poolers: use transaction mode for serverless. Set ORM pool size to 1 per function.

Refresh Strategy Selection

When in doubt: pg_cron at 15 minutes. Measure. Adjust.

What PostgreSQL Replaces

Target Latencies

The Five-Minute Wins

Things you can do right now, before lunch:

1. Enable pg_stat_statements. One line of configuration. Restart. You now know which queries cost the most.
2. Check foreign key indexes. PostgreSQL does not auto-create them. The most common fixable performance problem across millions of deployments.
3. Set connection_limit=1 in serverless functions. Prevents the ORM default (3–5 connections per function) from multiplying into connection exhaustion.
4. Run EXPLAIN ANALYZE on your slowest endpoint's query. The output tells you exactly what to fix.
5. Create one materialized view for your most expensive dashboard query. Index it. Schedule a 15-minute refresh. Measure the difference.