PostgreSQL & SQLite Production Runbook: EXPLAIN ANALYZE, MVCC, VACUUM & WAL Mode
Operate relational databases in production with confidence: read EXPLAIN (ANALYZE, BUFFERS) plans, eliminate Seq Scan bottlenecks with partial/GIN indexes, understand PostgreSQL MVCC dead tuples and autovacuum, and enable SQLite WAL concurrency.
1. Reading PostgreSQL EXPLAIN (ANALYZE, BUFFERS) Without Guessing
EXPLAIN (ANALYZE, BUFFERS) executes the query inside your transaction (wrap writes in BEGIN; ... ROLLBACK;) and exposes actual row counts vs. planner estimates alongside shared buffer hits and disk reads.BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_cents
FROM orders o
WHERE o.customer_id = 42 AND o.status = 'paid'
ORDER BY o.created_at DESC LIMIT 20;
ROLLBACK;Compare "Estimated rows" vs "Actual rows": stale ANALYZE stats cause catastrophic Nested Loop vs Hash Join choices.
| Plan Node / Metric | Healthy Signal | Red Flag Requiring Action | Copy |
|---|---|---|---|
| Seq Scan on large table | Reads <= 10-15% rows -> use Index Scan | Full table scan returning < 1% of rows | |
| Rows Estimate vs Actual | Within 2x factor | 100x mismatch -> run ANALYZE table | |
| Buffers: shared read | Low read blocks, high hit % | Gigabytes spilled from disk / Temp sort | |
| Sort Method: external merge | Sort Method: quicksort Memory | Work_mem too small for ORDER BY / Hash |
2. PostgreSQL MVCC, Dead Tuples & Zero-Downtime Indexing
UPDATE or DELETE never overwrites a row in placeโit leaves an old dead tuple version visible to active snapshots until autovacuum reclaims the space. Long-running idle-in-transaction connections block vacuuming and balloon table bloat. In production, always create indexes with CREATE INDEX CONCURRENTLY so writes never lock.CREATE INDEX CONCURRENTLY idx_jobs_pending ON jobs (scheduled_at) WHERE status = 'pending';
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;Partial indexes keep hot queue lookups tiny in RAM and avoid blocking live writes.
3. SQLite in Production: WAL Mode, synchronous=NORMAL & Busy Timeout
SQLITE_BUSY errors under web traffic. Enabling Write-Ahead Logging (PRAGMA journal_mode = WAL;) allows unlimited concurrent readers alongside a single serialized writer, slashing single-node latency for embedded and edge workloads.PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -64000; -- 64 MiB page cacheApplies safe crash recovery, concurrent reads during writes, and foreign-key integrity.
4. Connection Pooling & Prepared Statement Discipline
transaction pooling mode in front of serverless functions and keep application pool sizes bounded roughly near (CPU cores * 2) + SSD spindles.Related Free Online Tools
Put these techniques into practice immediately with our privacy-first browser utilities:
Format complex SQL joins, CTEs, and migrations into readable standardized uppercase clauses.
Convert structured JSON arrays into parameterized multi-row SQL INSERT batches offline.
Parse SQL INSERT data dumps cleanly back into typed JSON arrays locally.
Independent software developer focused on client-side architectures, local-first computing, WebAssembly, and privacy-first web systems.