The PostgreSQL Cookbook
Full-incident runbooks for the failures single error pages can't cover — replication lag, autovacuum bloat, lock storms, failovers. Each one a complete diagnose → fix → verify playbook.
★ Recently added
PostgreSQL Crash Due to OOM Kill
⚠️ Symptoms All active connections drop simultaneously with no individual query error — clients see disconnect errors (connection reset, FATAL:…
Advisory Lock Leakage — Dangling Locks
⚠️ Symptoms One or more application threads hang at the point where they call pg_advisory_lock — not on a database…
Unused Indexes Consuming Space
Disk usage on a table is far larger than the data warrants; write throughput is lower than expected on a…
PostgreSQL Crash Due to OOM Kill
⚠️ Symptoms All active connections drop simultaneously with no individual query error — clients see disconnect errors (connection reset, FATAL: terminating connection…
Locking & ConcurrencyAdvisory Lock Leakage — Dangling Locks
⚠️ Symptoms One or more application threads hang at the point where they call pg_advisory_lock — not on a database query, but…
Performance & TuningUnused Indexes Consuming Space
Disk usage on a table is far larger than the data warrants; write throughput is lower than expected on a write-heavy table;…
Schema & ConstraintsForeign Key Violation Adding Constraint With Orphans
A schema migration or ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statement fails with SQLSTATE 23503 and the message "violates foreign…
Connections & PoolingPgbouncer Pool Exhaustion
Clients see connection acquisition or query latency spikes while the database CPU and lock graphs stay calm. The number of waiting application…
Schema & ConstraintsRelation Does Not Exist Search Path
An unqualified statement such as SELECT ... FROM orders raises 42P01 relation "orders" does not exist, while the exact same statement works…
Data Types & CoercionDate Time Field Value Out Of Range On Load
An INSERT ... SELECT that casts a text column to date stops with 'ERROR: date/time field value out of range: "25/01/2025"' and…
Query Planning & StatisticsStale Statistics Bad Query Plan
One specific query -- frequently a reporting or join query that touches a recently loaded table -- becomes dramatically slower with no…
Performance & TuningIndex Not Being Used
A query you expected to be fast is slow; EXPLAIN shows Seq Scan (often Parallel Seq Scan) with a Filter line instead…
Storage & Data IntegrityCollation Version Mismatch Indexes Silently
Connections and queries that touch a text collation start logging a collation version mismatch WARNING -- often right after an apt/yum upgrade,…
Replication & HAReplication Lag Growing
Read queries routed to the standby return old data / miss recently-committed rows; pg_stat_replication.replay_lag on the primary keeps climbing (seconds,…
Durability & RecoveryUnlogged Table Data Loss After Crash
After an unclean restart -- an OOM kill, a power loss, a kubelet SIGKILL, a `docker kill`, a crashed node -- one…
Configuration & TuningCheckpoints Occurring Too Frequently
The log line 'checkpoints are occurring too frequently (N seconds apart)' with the HINT 'Consider increasing the configuration parameter "max_wal_size"',…
Replication & HAReplication Slot Wal Bloat
pg_wal grows without bound and disk usage climbs steadily; checkpoints complete but reclaim nothing. pg_replication_slots shows a slot with active = false…
Query PerformanceJsonb Query Slow Without Gin Index
A jsonb filter is functionally correct but reads far more of the table than the result size justifies, and its latency scales…
Schema & IndexingUnindexed Foreign Key Slow Delete
A DELETE on a small parent table, or an UPDATE that changes its primary key, is unexpectedly slow and contended even though…
Connections & Resource LimitsConnection Exhaustion
The application suddenly cannot get new connections; healthy queries on existing connections still work, but anything that opens a fresh connection fails…
Maintenance & VacuumTable Bloat Dead Tuples
pg_relation_size / \dt+ shows a table far larger than the live data justifies (e.g. 40 MB for 200,000 small rows). Sequential scans…
Concurrency & LockingDeadlock Detected
An application transaction fails intermittently and abruptly with 'ERROR: deadlock detected' (SQLSTATE 40P01), usually under concurrency and usually on…
Queries & ComputationDivision By Zero In Rate Calculation
A previously reliable rate/ratio query or UPDATE suddenly fails with 22012 division_by_zero. The failure is all-or-nothing: because one statement computes…
Maintenance & AutovacuumTransaction Id Wraparound Risk
Writes still work and the server is quiet -- there is no 'must be vacuumed within N transactions' warning yet, which is…
Schema & Data TypesInteger Primary Key Sequence Exhaustion
Writes to one table -- always the inserts, never the reads -- begin failing all at once with no deploy and no…
Schema & ConstraintsNot Null Constraint Add Column Contains Null Values
ALTER TABLE ... ALTER COLUMN ... SET NOT NULL fails with SQLSTATE 23502 and the message "column ... of relation ... contains…
Maintenance & VacuumTables Not Being Autovacuumed
Growing table/index bloat; queries slowing as dead tuples accumulate; pg_stat_user_tables shows a high n_dead_tup with last_autovacuum NULL (or very old)…
Query PerformanceGeneric Plan Nested Loop Explosion
A parameterized query has wildly bimodal latency: fast (sub-millisecond to low-millisecond) for almost every bind value, then suddenly catastrophic --…
Data IntegrityDuplicate Key From Out Of Sync Sequence
Inserts into a recently restored or bulk-loaded table fail with 'ERROR: duplicate key value violates unique constraint "_pkey"' and a DETAIL line…
Schema & ConstraintsCheck Constraint Add Violated By Some Row
A CREATE/ALTER migration or a hand-run ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) aborts with SQLSTATE 23514 and the message 'check…
Data Types & CoercionFunction Round Double Precision Does Not Exist
Calling round (or another function) with two arguments on a floating-point value raises 'function round(double precision, integer) does not exist', with…
Performance & TuningFind Your Slowest Queries With Pg Stat Statements
Elevated CPU or I/O with no single obvious culprit; p95 latency creeping up; a dashboard showing the database busy but no long-running…
Concurrency & LockingTable Level Lock Blocking Ddl Alter Table
A schema migration -- often a trivial-looking ALTER TABLE ... ADD COLUMN -- 'hangs', and within seconds the application in front of…
Locking & ConcurrencySerialization Failure Error Could Not Serialize
Transactions under SERIALIZABLE isolation intermittently fail with 'ERROR: could not serialize access ...'; the failure appears only under concurrency and…
Data Types & CoercionInteger Out Of Range Arithmetic Overflow
An UPDATE, INSERT, SELECT, or aggregate that multiplies (or adds) integer values fails with SQLSTATE 22003 and 'integer out of range'. The…
Maintenance & AutovacuumTransaction Id Wraparound Approaching
Writes still work but every commit and the server log carry a rising-volume WARNING: 'database "postgres" must be vacuumed within 27484380 transactions',…
Schema & IndexingAdd Index Concurrently Recover From Failed Cic
A CREATE INDEX CONCURRENTLY or CREATE UNIQUE INDEX CONCURRENTLY command ends with an ERROR rather than CREATE INDEX -- for a unique…
Schema & ConstraintsColumn Does Not Exist Quoted Identifier Case
A query that looks obviously correct returns 'column "" does not exist', where the name in the message is the lowercased form…
Queries & ComputationSubquery Returned More Than One Row
A SELECT, UPDATE, or INSERT that embeds a scalar subquery -- (SELECT ...) used where one value is expected, such as a…
Query PerformanceWork Mem External Sort Spill
A query that sorts, DISTINCTs, or hash-aggregates a large set is far slower than the row count suggests and generates heavy temporary-file…
Schema & ConstraintsExclusion Constraint Conflicts With Existing Rows
Two related failures, both SQLSTATE 23P01. At schema-change time, ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE ... fails with 'could not create…
PerformanceDisk Based Sort Spill
A specific query is much slower than its row count suggests, and slows further under concurrency. The data volume's free space drops…
MaintenanceLong Transaction Blocking Vacuum
n_dead_tup for one or more tables stays high and climbs even though last_vacuum / last_autovacuum are recent. VACUUM completes 'successfully' but reclaims…
Schema & Data TypesValue Too Long Varchar Truncation
An INSERT or UPDATE that used to succeed now fails with 'ERROR: value too long for type character varying(N)' and SQLSTATE 22001,…
Configuration & TuningStatement Timeout Cancels Long Query
A statement that ran fine before now fails with SQLSTATE 57014 and the message 'canceling statement due to statement timeout'. It typically…
Storage & Data IntegrityPg Control File Corrupt
The whole instance is down and will not start. The postmaster log shows a single line at the top: 'FATAL: incorrect checksum…
Data Types & CoercionValue Too Long For Character Varying On Narrowing
A column narrowing (ALTER ... TYPE varchar(n) or char(n)), an INSERT/UPDATE, or a load into a width-limited column fails with SQLSTATE 22001…
Schema & ConstraintsDuplicate Key Creating Unique Index With Duplicate Rows
CREATE UNIQUE INDEX, ALTER TABLE ... ADD CONSTRAINT ... UNIQUE, or ALTER TABLE ... ADD PRIMARY KEY fails with SQLSTATE 23505 and…
Transactions & ConcurrencyCurrent Transaction Is Aborted Cascade
After one statement fails inside a transaction, every following statement returns 'current transaction is aborted, commands ignored until end of…
Performance & TuningIndex Bloat Identifying Candidates For Reindex
An index is much larger on disk than the table's live row count justifies; index-only and range scans are slower than they…
Locking & ConcurrencyDiagnosing A Blocked Query Queue
A group of identical or related queries all hang at once; pg_stat_activity shows several backends with wait_event_type = 'Lock' and state =…
Storage & Data IntegrityData File Corruption
A specific query errors out instantly with 'invalid page in block N of relation base//' and keeps erroring on every retry; the…
Connections & Resource LimitsIdle In Transaction Sessions Accumulating
Connection count climbs and never falls back; new connections eventually fail with 'too many clients already'. pg_stat_activity shows many backends stuck…
Data Types & CoercionInvalid Input Syntax For Type Integer On Load
An ETL/import step, a column type change (ALTER ... TYPE integer USING ...), or an ad-hoc INSERT ... SELECT that casts text…
Maintenance & AutovacuumMultixact Wraparound Approaching
Writes still work but the server log and new-multixact creation carry a WARNING: 'database "postgres" must be vacuumed before 7483648 more MultiXactIds…
Read any recipe free. Unlock the executed proof with Pro.
Every recipe gives you the diagnose-fix-verify arc for free. Pro adds the executed verification, catalog audits, and the cloud-provider notes for RDS, Azure & Cloud SQL.
- ✓ The full verification step for every recipe — executed, not described.
- ✓ Catalog & log queries that surface every at-risk object up front.
- ✓ Cross-version gotchas across PostgreSQL 14–18.
- ✓ Exactly where each symptom surfaces on managed Postgres.