Databases
Production failure modes and plan stability
Crux Eight real failure modes — stale stats, generic-plan trap, correlated columns, SSD mis-tuning, JIT overhead, work_mem spill, random_page_cost drift, and plan flips — plus the operational strategy to detect and prevent plan regressions across deploys.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitA B2B SaaS product’s orders dashboard degrades every Monday morning. By Tuesday it is fine again. The pattern: a batch import runs Sunday night, shifting data distributions; autovacuum has not triggered ANALYZE yet by Monday morning; the planner picks a Nested Loop on a 1000× underestimated join. ANALYZE in the post-batch hook eliminates the incident forever.
Eight real production failure modes
| Failure mode | Symptom | Fix |
|---|---|---|
| Stale statistics cascade | Sudden slowdown after bulk insert; rows-estimated wildly off actual | ANALYZE in post-batch hook; lower autovacuum_analyze_scale_factor |
| Generic-plan trap | High stddev_exec_time; bimodal latency on prepared statement | plan_cache_mode = force_custom_plan for the role |
| Correlated-column blowup | Nested Loop running 1M iterations; rows-estimated ~1000× low | CREATE STATISTICS (dependencies, mcv); ANALYZE |
| random_page_cost mis-tuned (SSD) | Planner picks Seq Scan where Index Scan should win; queries read whole tables | SET random_page_cost = 1.1 in postgresql.conf |
| JIT overhead on OLTP | Short queries slower than expected; JIT compilation time exceeds execution savings | Raise jit_above_cost to 500000 or SET jit = off for OLTP role |
| work_mem spill | Sort Method: external merge; Hash Batches > 1; queries slow on joins/sorts | SET work_mem = ‘32MB’ per session; not globally without memory budget check |
| effective_cache_size too low | Planner prefers Seq Scan even on SSD with large RAM because it assumes cold data | SET effective_cache_size = ‘24GB’ (~75% of total RAM) |
| Plan flips with workload phase | Same query gets different plans at different times; autovacuum eventually rebalances | pg_hint_plan for critical paths; otherwise accept variance and alert on it |
Plan stability across deploys
The most common operational pain: a deploy with new tables, new data, or different volumes causes plan regressions on unchanged queries.
The five-layer strategy:
Layer 1 — cost-constant tuning (one-time): Set random_page_cost = 1.1, effective_cache_size to 75% of RAM, work_mem = 16-32MB. Verify with EXPLAIN on the top-20 queries. This ensures the planner has the right mental model of your hardware.
Layer 2 — statistics freshness: Lower autovacuum_analyze_scale_factor = 0.02 on hot tables. Add ANALYZE critical_tables to every deploy hook. Create CREATE STATISTICS objects for correlated column groups identified via EXPLAIN.
Layer 3 — plan cache for skewed workloads: Set plan_cache_mode = 'force_custom_plan' on roles that run parameterised queries with skewed parameter distributions (identified via high stddev_exec_time).
Layer 4 — observability: Configure auto_explain (log_min_duration = 500ms, log_analyze = true, log_buffers = true, log_format = json, sample_rate = 0.05). Archive pg_stat_statements weekly (reset + save to file/S3).
Layer 5 — deploy procedure: Before every deploy, snapshot pg_stat_statements + EXPLAIN of top-20 queries to JSON. After deploy: ANALYZE critical tables, wait 5 minutes, re-snapshot. Diff key metrics. Flag any query whose plan structure changed or whose mean_exec_time grew by more than 50%.
Trace it
1/5 A senior engineer tunes a Postgres 16 OLTP database on NVMe SSD (32GB RAM, 500GB DB, ~5k QPS) that was deployed with default planner settings.
1
Step 1 of 5
Step 1: baseline.
Snapshot pg_stat_statements: top queries by total_exec_time, mean_exec_time, stddev. Capture EXPLAIN (ANALYZE, BUFFERS) for the top-20. Note: which use Seq Scan on large tables? Which spill (Hash Batches > 1, Sort Method: external merge)? Which have large rows-estimated vs actual gaps? Which have high stddev? Save plans as JSON for later comparison.
2
Locked
Step 2: SSD cost tuning.
ALTER SYSTEM SET random_page_cost = 1.1; ALTER SYSTEM SET effective_cache_size = '24GB'; SELECT pg_reload_conf(); Verify: SHOW random_page_cost. Re-run EXPLAIN on the top-20. Expect some plans to shift from Seq Scan to Index Scan — that is correct on SSD. For any shift that looks like a regression, inspect per-query before continuing.
3
Locked
Step 3: statistics refresh and extension.
ANALYZE; -- entire database. For high-skew columns: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000. For correlated column groups: CREATE STATISTICS (dependencies, mcv) ON col1, col2 FROM tbl. Re-ANALYZE. Re-EXPLAIN top queries; rows-estimated should track rows-actual within 2x on most nodes.
4
Locked
Step 4: memory and JIT.
SET work_mem = '16MB' in postgresql.conf (with max_connections budget: 200 × 16MB = 3.2GB worst case). Lower autovacuum_analyze_scale_factor on hot tables. For OLTP-only workloads: ALTER SYSTEM SET jit_above_cost = 500000 or SET jit = off for the application role.
5
Locked
Step 5: observability.
Configure auto_explain (log_min_duration = '500ms', log_analyze = true, log_buffers = true, sample_rate = 0.05). Set up weekly pg_stat_statements_reset() with prior-week snapshot archival. Set up alerts: any top-20 query mean_exec_time > 50ms, or stddev_exec_time > 10x mean. Document all settings with justifications — every non-default value needs a comment.
✓ Traced.
A team needs to stabilise execution plans across deploys for the top-20 OLTP queries. Pick the strategy.
Right answer for ~90% of cases. Fresh, accurate statistics let the planner make the right choice even as data grows. CREATE STATISTICS for correlated columns eliminates the largest class of row-estimate failures. auto_explain catches regressions automatically. Cost: a few ANALYZE commands in post-deploy hook + auto_explain config + weekly review of pg_stat_statements deltas. Preserves the planner's ability to adapt as data shapes shift.
Right for stability-critical queries where regression cost > adaptation benefit (financial reporting, fixed-SLA endpoints). Embed hints in SQL: /*+ IndexScan(orders idx_x) HashJoin(orders customers) */. Trades planner adaptability for predictability — tomorrow's data shape may make today's hint wrong. Reserve for the 1-3 queries that absolutely cannot regress; use statistics-first for the rest.
Forces Postgres to respect the explicit JOIN order in SQL — useful when developers hand-tune queries and want them to stay tuned. Global flag is a sledgehammer; sets it for every query. Better as per-session or per-role for analytic workloads. Side effect: large OLTP queries may miss optimal join order. Use selectively, not globally.
Sometimes seen in extreme scale-out setups. Wrong default. Reinventing planner caching outside Postgres adds operational complexity, breaks tooling, and ages poorly. Use only when no Postgres-native mechanism (hints, statistics, plan_cache_mode) suffices — which is rare.
Quiz
Completed
A batch job inserts 20M rows into the orders table every Sunday night. Every Monday morning, queries that were fast on Friday are slow. By Tuesday they are fast again. What is the root cause and the fix?
Heads-up Index usefulness depends on selectivity, not just table size. The issue is stale statistics, not index bloat.
Heads-up The pattern (slow Monday, fine by Tuesday) is consistent with autovacuum catching up, not traffic differences.
Heads-up Lock contention manifests as waiting queries, not slow queries. And it resolves when the transaction ends, not by Tuesday.
Quiz
Completed
A query shows `Sort Method: external merge Disk: 450MB` in EXPLAIN ANALYZE. What is the interpretation and fix?
Heads-up External merge means the data did not fit in work_mem and Postgres used disk — this is a performance problem, not normal.
Heads-up An index can sometimes eliminate a sort, but the external merge symptom is about work_mem, not index presence.
Heads-up External merge is a planned disk-based sort, not a corruption symptom.
- 01Explain why the gap between rows-estimated and rows-actual at every plan node is the single most important diagnostic in Postgres.
- 02Design the plan-stability strategy for a B2B SaaS Postgres 16 database on NVMe SSD (32GB RAM, 500GB DB, 5k OLTP QPS). Top-20 queries identified via pg_stat_statements. 3 of 20 are heavy aggregations.
Recap
Eight production failure modes cover most Postgres plan regressions: stale statistics after bulk operations (ANALYZE in post-batch hook), generic-plan trap on prepared statements with skewed parameters (plan_cache_mode = force_custom_plan), correlated-column row-estimate failures (CREATE STATISTICS dependencies + mcv), SSD mis-tuning (random_page_cost = 1.1), JIT overhead on OLTP (raise jit_above_cost), work_mem spill (sort / hash spill to disk — fix per-session), effective_cache_size mismatch, and plan flips with data phase changes. The operational strategy has five layers: SSD-tuned cost constants, aggressive statistics maintenance, selective plan_cache_mode overrides, auto_explain + pg_stat_statements observability, and a deploy procedure that diffs plans and metrics before and after every deploy. Use pg_hint_plan only for the 1-3 queries where the plan must be pinned and the correct plan shape is permanently known; let the planner adapt to everything else.
Practice
Do these to turn recognition into skill.
Connected lessons
appears again in258
- Why GraphQL gets N+1junior
- DataLoader mechanics: tick-boundary batchingmiddle
- Batch function contracts: ordering, shapes, errorsmiddle
- Federation and lookahead: batching beyond DataLoadermiddle
- Query complexity defences: depth, cost, persisted queriesmiddle
- Senior GraphQL API: scheduling contract, tenant isolation, observabilitysenior
- The journey of a request: seven stops from socket to responsejunior
- Accept and parse: from kernel queue to a typed requestmiddle
- Routing and middleware: choosing what runs, and in what ordermiddle
- Handler and response: from business logic to bytes on the wiremiddle
- Streaming and backpressure: when the client reads slower than you writesenior
- Timeouts and tail latency: budgets, deadlines, and the fan-out trapsenior
- Middleware and DI: the two patterns that shape every backendjunior
- Writing middleware: signatures, next(), and the three framework modelsmiddle
- Inversion of control: how dependencies reach a classmiddle
- DI scopes and lifecycles: singleton, request, transientmiddle
- DI as a testing seam: fakes, mocks, and the boundary that matterssenior
- DI containers in production: resolution graphs, circular deps, and when not tosenior
- Blocking vs non-blocking I/O: two ways to waitjunior
- The event loop: one thread, ordered phasesmiddle
- What blocks the loop: CPU work and sync callsmiddle
- Offloading CPU work: worker threads and the libuv poolmiddle
- Backpressure and bounded concurrencysenior
- Throughput under load: tail latency and saturationsenior
- Why pool: the cost of creating a connectionjunior
- Pool sizing: why bigger is not fastermiddle
- Acquisition and timeouts: the wait queue is the real latency dialmiddle
- Why idempotency: making retries safejunior
- Server-side state machine: four states of an idempotency keymiddle
- Retry strategies: backoff, jitter, and thundering herdmiddle
- Outbox and inbox: effectively-once across the dual-write boundarymiddle
- Concurrency and cache architecture for idempotency at scalesenior
- Observability, production failures, and global-scale designsenior
- The event loop: one thread, three queuesjunior
- Tasks, microtasks, and scheduler.yield()middle
- Timer accuracy, throttling, and idle workmiddle
- Microtask starvation, Long Tasks, and LoAFsenior
- Node.js event loop: phases, nextTick, and loop lagsenior
- React, Vue, and INP observability in productionsenior
- The render pipeline: six stages from bytes to pixelsjunior
- Stage costs and the renderer process modelmiddle
- Invalidation, dirty bits, and containmiddle
- Compositor layers: promotion, overlap, and GPU memorymiddle
- DevTools flame strip and the frame lifecyclemiddle
- Layout thrash: forced synchronous layoutsenior
- BeginMainFrame, compositor-driven animations, and GPU memorysenior
- Production observability: LoAF, INP, and the full attack surfacesenior
- What V8 is and why performance varies 100×junior
- V8''''s four-tier JIT pipeline and profile-guided tieringmiddle
- Hidden classes, transition trees, and memory layoutmiddle
- Inline caches, IC states, and deoptimizationmiddle
- Orinoco GC: parallel scavenger, concurrent marking, and write barriersmiddle
- TurboFan''''s speculative engine and the deopt-loop trapsenior
- V8 in production: isolates, pointer compression, and real failuressenior
- Service worker lifecycle and cache strategiesmiddle
- Service worker edge cases: version skew, durability, and navigation trapssenior
- What the reconciler does: render vs commitjunior
- The fiber object and the double-buffer treemiddle
- Render phase purity and commit phase sub-stepsmiddle
- Reconciliation: diffing heuristics and the key trapmiddle
- Priority lanes, time-slicing, and useTransitionmiddle
- Bailout, memoisation, and tearingsenior
- React Profiler, the Compiler, and production observabilitysenior
- Rendering strategies: SSG, SSR, ISR, streaming, and hydrationjunior
- SSG, SSR, ISR, streaming, and RSC — how each worksmiddle
- Hydration cost: selective, progressive, islands, resumabilitymiddle
- Hydration mismatch: causes, detection, and the determinism rulesenior
- RSC, per-route strategy, and production observabilitysenior
- Core Web Vitals: what LCP, INP, and CLS measurejunior
- LCP: four phases, one dominant costmiddle
- INP: input delay, processing, presentationmiddle
- CLS: why layout shifts happen and how to stop themmiddle
- Lab vs field: why the two disagree and how to use eachmiddle
- Metric tradeoffs, RUM attribution, and the CI+field loopsenior
- The full picture: URL to LCP to INP as a relay racejunior
- Eight layers traced: from the service worker to the second navigationmiddle
- Five canonical breaks: where production reliably diessenior
- The three-track method: reading traces and building a monitored systemsenior
- What is a cache stampede and why it makes things worsejunior
- Lock and single-flight: bounding concurrent rebuildsmiddle
- XFetch: coordination-free probabilistic early expirationmiddle
- Stale-while-revalidate and CDN request coalescingmiddle
- Detecting stampedes and designing TTL for productionmiddle
- Metastable failure, fencing tokens, and production postmortemssenior
- Raft roles, terms, and why majority quorums prevent split brainjunior
- How Raft replicates a log entry and decides it is safe to commitmiddle
- Raft leader election: timeouts, voting rules, and the four safety propertiesmiddle
- Raft in the real world: partitions, slow disks, and client routingmiddle
- Raft extensions: pre-vote, learners, snapshots, and linearizable readssenior
- Raft in production: membership changes, Multi-Raft, and observabilitysenior
- Where data fetching happens — and why it decides LCPjunior
- Fetch waterfalls — diagnosis and the Promise.all curemiddle
- React Server Components and Suspense streamingmiddle
- Client-side cache: TanStack Query, SWR, and stale-while-revalidatemiddle
- LCP, prefetch, and race conditions in interactive fetchingmiddle
- Senior internals: RSC payload, caching layers, and production failure modessenior
- Bits on the wirejunior
- Latency mathmiddle
- Bufferbloat and congestionsenior
- The physical frontiersenior
- The three-way handshakejunior
- Sequence numbers and connection statemiddle
- Flow control and congestion controlmiddle
- BBR, production observability, and beyond TCPsenior
- DNS: what it does and why it existsjunior
- The resolver walk: referrals, record types, and gluemiddle
- TTL, caching, and DNS propagationmiddle
- The 1-RTT handshake: key shares and ECDHEmiddle
- Session resumption and 0-RTTmiddle
- CDN: putting content next doorjunior
- Anycast and GeoDNS: routing to the nearest edgemiddle
- Tiered cache and Cache-Controlmiddle
- Vary header and cache keysmiddle
- Stale-while-revalidate and cache stampedesenior
- Edge workers and edge-side compositionsenior
- CDN operations and observabilitysenior
- WebSocket: the HTTP upgrade handshakejunior
- WebSocket frame format: opcodes, masking, fragmentationmiddle
- WebSocket vs SSE vs long-polling: choosing the right transportmiddle
- WebSocket backpressure: when clients can''''t keep upmiddle
- Reconnection: jittered backoff, thundering herd, message resumptionsenior
- WebSocket at scale: HTTP/2 multiplexing, permessage-deflate, C10Msenior
- WebSocket in production: proxies, security, and distributed architecturesenior
- What reverse proxies dojunior
- Balancing algorithms: round-robin to power-of-two-choicesmiddle
- L4 vs L7 load balancing and client-IP preservationmiddle
- Health checks, connection draining, and slow startmiddle
- Session affinity, consistent hashing, and the right fixmiddle
- Retry storms, circuit breakers, and load sheddingsenior
- Resilient LB architecture: anycast, zone-aware routing, and observabilitysenior
- Why QUIC and not TCP+TLSjunior
- QUIC streams and head-of-line blockingjunior
- Integrated handshake and 1-RTTmiddle
- Connection IDs and network migrationmiddle
- Loss detection and congestion controlmiddle
- 0-RTT resumption and packet encryptionsenior
- Deployment tradeoffs and CPU costsenior
- DDoS: what it is and why it worksjunior
- Amplification attacks and state exhaustionmiddle
- Rate limiting: algorithms and architecturemiddle
- WAFs, firewalls, mTLS, and HSTSmiddle
- DNS cache poisoning and BGP hijackingsenior
- Defense-in-depth architecture and attack economicssenior
- The twelve layers: one URL, seven actorsjunior
- DNS, TCP, TLS in sequence: where the milliseconds gomiddle
- Critical render path and Core Web Vitalsmiddle
- Proxy intercepts and security gates: rate limiters, WAF, mTLSmiddle
- Alternate paths: QUIC 0-RTT, WebSocket upgrade, connection migrationmiddle
- Observability: distributed traces, USE/RED, and samplingsenior
- Resilience: cascading retries, circuit breakers, and error budgetssenior
- What the three signals are: logs, metrics, and tracesjunior
- Metrics and cardinality: the cost model of a time-series databasemiddle
- Logs and volume: the cost model of structured loggingmiddle
- Traces and sampling: the cost model of distributed tracingmiddle
- Join keys and exemplars: making the three signals composemiddle
- Observability 2.0: wide events and the cost shiftsenior
- Failure modes and engineering practice: cardinality budgets, PII, and samplingsenior
- Why structured logs exist: the diary vs the spreadsheetjunior
- The production log schema: fields every line must carrymiddle
- Log levels and alert routingmiddle
- Sampling strategies and log costmiddle
- PII redaction and log injectionsenior
- Trace context propagation in logssenior
- OTel Logs Data Model and audit logs as a subsystemsenior
- OTel signals, Semantic Conventions, and the OTLP wire formatmiddle
- Auto-instrumentation and manual spans: the 80/20 of OTelmiddle
- The OTel Collector: receivers, processors, exporters, and deployment patternsmiddle
- Sampling strategies: head, tail, and parent-basedmiddle
- Vendor neutrality, eBPF instrumentation, the Operator, and browser/serverless OTelsenior
- Operating the OTel Collector: reliability, version skew, failure modes, and governancesenior
- RED and USE: two checklists, one triage disciplinejunior
- Instrumenting RED in Prometheus: counters, histograms, and cardinality disciplinemiddle
- USE on Linux: CPU, memory, disk, network, and PSImiddle
- Golden signals, dashboard layout, and service mesh auto-REDmiddle
- Cardinality as a cost driver: labels, PII, exemplars, and samplingmiddle
- Native histograms, SLO tie-in, and production failure patternsmiddle
- SLI, SLO, and the error budget: reliability by the numbersjunior
- Choosing SLIs and SLO targets: ratios, not feelingsmiddle
- Multi-window multi-burn-rate alerting: why AND beats ORmiddle
- Error budget policy, latency SLOs, and composite journeysmiddle
- Iceberg SLIs, composite SLO math, and SLA vs SLOsenior
- Production SLO failures, self-observability, security, and the big picturesenior
- Flame graphs: reading the picture that shows where time goesjunior
- Sampling vs instrumentation profiling: why 99 Hz wins in productionmiddle
- Profile types: CPU, memory, off-CPU, mutex — which one to reach formiddle
- Continuous profiling: always-on flame graphs with eBPF and trace-id correlationmiddle
- How flame graphs are built from samples, and the production workflows that use themmiddle
- Linux perf, eBPF internals, PGO, and the limits of samplingsenior
- Profiling in production: security, war stories, OTel profiles, and the infrastructure designsenior
- The debugging funnel: SLO → RED → trace → profilejunior
- OTel architecture: one SDK, four signals, one wire formatmiddle
- Cost discipline: keeping observability under 5% of infra spendmiddle
- The incident loop: from pager to postmortem to preventionmiddle
- Scale, security, and the ROI of observable systemssenior
- Why profile first: measure where time actually goesjunior
- Amdahl''''s law and self-time: the ceiling on every speedup you can shipmiddle
- The measurement loop: microbench, macrobench, prod profile, observer effectmiddle
- Reading flame graphs: shapes, per-language profilers, and the 60-second scanmiddle
- Statistical baselines: why one run is not a measurementmiddle
- Profiler history and microbenchmark pitfalls: Knuth to GWPsenior
- Hardware counters, cold-start profiles, and profile securitysenior
- Continuous profiling at scale: costs, CI gates, trace correlation, and anti-patternssenior
- What makes a hot path: symptom vs causejunior
- Five shapes of hotspot: CPU, alloc, cache, lock, syscallmiddle
- Reading parent and child chains: where to apply the fixmiddle
- JIT deopt, the fix-and-verify loop, and PR-time profilingmiddle
- Hardware counters and Intel TMA: sub-category diagnosissenior
- False sharing and native-bridge hot pathssenior
- Hot paths in production: security, tail latency, and tooling lineagesenior
- Memory hierarchy: why the same O(N) loop can be 17x slowerjunior
- Row-major vs column-major: access order and the 9x gapjunior
- Cache lines, struct layout, and false sharingmiddle
- Branch prediction and branchless codemiddle
- SIMD, SoA vs AoS, and memory bandwidthmiddle
- Hardware prefetcher, TLB, and memory-level parallelismsenior
- Cache-oblivious algorithms, PGO, and production failuressenior
- GC basics: what the runtime taxes you forjunior
- GC algorithms: generational, concurrent, and per-runtimemiddle
- GC tradeoffs: pause, throughput, heap — and object poolingmiddle
- GC tuning: pacing, heap shape, and allocation observabilitymiddle
- GC internals: tri-color invariant, write barriers, and per-runtime deep-divessenior
- GC in production: observability, security, edge cases, and fleet governancesenior
- N+1: one logical operation, many round-tripsjunior
- Fix families: JOIN, IN, preload, and DataLoadermiddle
- Detecting N+1: query logs, APM traces, and CI gatesmiddle
- DataLoader: batching across resolver treesmiddle
- Cross-protocol N+1: HTTP fan-out and Redis MGETmiddle
- N+1 at scale: pool exhaustion, plan changes, and denormalisationsenior
- Batching: amortize fixed cost per operationjunior
- The batching window: size and wait timemiddle
- Batching in Kafka and Postgresmiddle
- io_uring and observability of batchingmiddle
- From Nagle to io_uring: evolution of batchingmiddle
- Backpressure, failure isolation, and batch security in productionsenior
- What a bundle actually costs: download, parse, compile, executejunior
- Core Web Vitals: LCP, INP, and CLSmiddle
- Code splitting: route-level, component-level, vendor splittingmiddle
- Tree shaking and compression: removing what you don''''t usemiddle
- Third-party scripts: the silent budget killermiddle
- CI enforcement and RUM: making budgets stickmiddle
- V8 JIT pipeline, HTTP priorities, and bundle securitysenior
- The performance loop: discipline, not a projectjunior
- Classify and fix: matching bottleneck families to remediesmiddle
- Observability stack and CI gates: catching regressions before they shipmiddle
- Incident to enforcement: SLO burn to verified fix in 35 minutesmiddle
- Culture, economics, and org-scale performancesenior
- At-most-once, at-least-once, exactly-once: the three delivery contractsjunior
- The three failure legs — where duplicates and losses actually happenmiddle
- Consumer-side dedup: the cheapest path to exactly-once processingmiddle
- Kafka exactly-once semantics: idempotent producer and transactionsmiddle
- SQS visibility timeout, DLQ, and the outbox patternmiddle
- Exactly-once in production: impossibility proof, hybrid patterns, and real incidentssenior
- What OAuth is and why passwords are not the answerjunior
- Authorization code flow with PKCEmiddle
- ID token validation and JWKS cache managementmiddle
- Refresh token rotation and scope-based least privilegemiddle
- Sender-constrained tokens: DPoP and mTLSsenior
- OAuth in production: audience attacks, observability, and real failuressenior