←Back to Guides
๐Ÿ—„๏ธDatabase Internalsโฑ๏ธ 15 min read•Updated November 2026•โœ๏ธBy Chris

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.

#PostgreSQL#SQLite#EXPLAIN ANALYZE#MVCC#VACUUM#WAL#Indexing

1. Reading PostgreSQL EXPLAIN (ANALYZE, BUFFERS) Without Guessing

Never tune SQL blindly based on wall-clock duration aloneโ€”cached queries hide massive I/O waste. Running 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.
Audit Query Plans Safely Inside a Rollback Transaction
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 / MetricHealthy SignalRed Flag Requiring ActionCopy
Seq Scan on large tableReads <= 10-15% rows -> use Index ScanFull table scan returning < 1% of rows
Rows Estimate vs ActualWithin 2x factor100x mismatch -> run ANALYZE table
Buffers: shared readLow read blocks, high hit %Gigabytes spilled from disk / Temp sort
Sort Method: external mergeSort Method: quicksort MemoryWork_mem too small for ORDER BY / Hash

2. PostgreSQL MVCC, Dead Tuples & Zero-Downtime Indexing

PostgreSQL uses Multi-Version Concurrency Control (MVCC): an 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.
Zero-Downtime Partial Index & Bloat Inspection
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

Default SQLite rollback journals block readers during every write, leading to immediate 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.
Recommended Production SQLite Connection PRAGMAs
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -64000; -- 64 MiB page cache

Applies safe crash recovery, concurrent reads during writes, and foreign-key integrity.

4. Connection Pooling & Prepared Statement Discipline

Every idle PostgreSQL backend forks an OS process (~5-10 MB RAM) and adds lock contention past a few hundred active slots. Place PgBouncer (or built-in poolers) in transaction pooling mode in front of serverless functions and keep application pool sizes bounded roughly near (CPU cores * 2) + SSD spindles.
๐Ÿ‘จโ€๐Ÿ’ป
Authored & Reviewed by ChrisMayPixels • Netherlands

Independent software developer focused on client-side architectures, local-first computing, WebAssembly, and privacy-first web systems.

About MayOnlineTools→

๐ŸŽ“Explore More Guides

โœจ