Skip to content

Phase 09: Real-World Choke Points & Administration

Chapter 35: Monitoring, Observability & Wait Events

35.1 pg_stat_activity (The Real-Time Incident Dashboard)

During an ongoing outage, this is the first query a senior DBA executes: 

SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    usename,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

35.2 Understanding Wait Events (Finding the Real Bottleneck)

When queries queue up, the CPU might be low, but the database is unresponsive. The wait_event_type reveals what backend processes are waiting for:

  1. Lock: Waiting on heavyweight table/row lock (e.g., waiting for an ALTER TABLE or uncommitted row lock).
  2. LWLock (Lightweight Lock): Waiting on shared memory data structures.
  3. ProcArrayLock: Too many active concurrent queries competing to check visibility.
  4. WALBufMappingLock or WALWriteLock: WAL buffers full; storage cannot write WAL fast enough.
  5. IO: Waiting for disk blocks to be read or written.
  6. DataFileRead: The requested pages are not in shared_buffers or OS Page Cache; waiting for physical disk read.
  7. WALSync: Waiting for disk controller to flush WAL to physical media (fsync).
  8. Client:
  9. ClientRead: Postgres is waiting for the client application to send a query. If state is idle in transaction, the application opened a transaction and went to sleep!

35.3 Defensive Timeouts (Preventing Runaway Outages)

Never run PostgreSQL in production without global safety rails: * statement_timeout = '30s': Automatically cancels any query running longer than 30 seconds. * idle_in_transaction_session_timeout = '10s': Kills application sessions that leave a transaction open and idle for \(> 10s\). * lock_timeout = '3s': Aborts any DDL or lock acquisition if it cannot get the lock in 3 seconds, preventing lock stampedes.

Chapter 36: Post-Mortems: The 4 Common Production Outages

36.1 Outage 1: The Linux OOM Killer Strike

  • Symptom: Postgres suddenly restarts unexpectedly. System log (dmesg) displays: Out of Memory: Kill process 18452 (postgres) score 852 or sacrifice child.
  • Root Cause: work_mem was set too high (e.g., 64MB). A sudden burst of 50 complex analytic queries each ran 4 concurrent hash joins/sorts, attempting to allocate \(50 \times 4 \times 64\text{MB} = 12.8\text{GB}\) of private memory on a 16GB server.
  • Immediate Fix: Set vm.overcommit_memory = 2 and vm.overcommit_ratio = 80 in Linux sysctl to forbid the OS from overpromising RAM. Lower global work_mem to 4MB–8MB.

36.2 Outage 2: The CPU Cliff from Bad Query Plans

  • Symptom: CPU utilization jumps to 100%. All application queries experience massive latency spikes.
  • Root Cause: A table crossed a threshold where the planner flipped from an Index Scan to a full Sequential Scan due to stale statistics (pg_statistic), or an application developer deployed an unindexed join.
  • Immediate Mitigation:
  • Find the offending PID in pg_stat_activity.
  • Kill the runaway query: SELECT pg_cancel_backend(pid); (graceful cancel) or SELECT pg_terminate_backend(pid); (force kill).
  • Run ANALYZE tablename; to refresh planner statistics.

36.3 Outage 3: Connection Pool Starvation

  • Symptom: Application logs scream: FATAL: remaining connection slots are reserved for non-replicated superuser connections.
  • Root Cause: An external API call inside an application transaction became slow (e.g., waiting 5s for Stripe). The application threads held open DB connections while waiting for HTTP responses, consuming all max_connections.
  • Rule: Never make external HTTP/network calls inside a database transaction.

Chapter 37: Upgrading PostgreSQL (Minor vs Major)

37.1 Minor Upgrades (e.g., 16.1 -> 16.2)

  • Binary compatible.
  • Replace the PostgreSQL executables and restart the cluster. Downtime = 2 to 5 seconds.

37.2 Major Upgrades (e.g., 14 -> 16)

Physical storage formats change across major versions. You cannot just point Postgres 16 at Postgres 14 data files.

  • Uses hard links (ln) on the filesystem instead of copying gigabytes of data files.
  • Upgrades a 5TB database in under 2 minutes!
  • Risk: Modifies the original data files in place. If the upgrade fails, you must restore from backup.

Strategy B: Zero-Downtime Logical Replication Upgrade

  1. Spin up a brand-new Postgres 16 cluster alongside the existing Postgres 14 cluster.
  2. Export schema: pg_dump --schema-only.
  3. Set up native Logical Replication: PG 14 publishes to PG 16 subscriber.
  4. Wait for replication lag to reach 0 bytes.
  5. In a 1-second maintenance window: Switch application connection strings to the new PG 16 cluster. Downtime = Near Zero.