Postgresql Performance

PostgreSQL Performance

Purpose

Trace PostgreSQL symptoms to tuple visibility/maintenance, plan work, concurrency, memory, WAL/I/O, connections, or driver/pooler behavior. VACUUM, indexes, cost constants, and memory settings must be chosen from table- and workload-level evidence, not universal ratios.

Investigation contract

exact PostgreSQL, pgjdbc, and PgBouncer versions/configuration; topology and failover role:
query id/text, application parameters, prepared/generic plan state, and plan with actuals/buffers:
table/index size, churn, dead tuples, HOT ratio, vacuum/analyze history, reloptions, XID age:
oldest transaction/xmin, replication slots, prepared transactions, standby feedback:
locks/waits, isolation, serialization/deadlock errors, transaction and statement age:
connections/process memory, work_mem nodes/spills, CPU, I/O, WAL/checkpoint and replica lag:
pool mode and session features; pgjdbc prepare, batch, fetch, timeout and autocommit behavior:

Workflow

Start with available evidence; unavailable counters/plans remain explicit gaps, not a reason to guess or require every contract field. EXPLAIN ANALYZE executes the statement, including writes and invoked functions. Inspect side effects and use an isolated representative copy or an explicitly authorized bounded production measurement; transaction rollback does not undo sequence advancement or all external effects. Plain EXPLAIN is the first alternative when execution is unsuitable. Diagnosis alone does not authorize session termination, slot removal or blocking rewrites.

1. Bound the symptom to statement, relation, database, instance, or replica and align the workload, plan, VACUUM/checkpoint, transaction, and deployment intervals.
2. Classify the dominant mechanism:
   • maintenance/visibility: dead tuples, blocked xmin, vacuum/analyze/freeze, visibility map, HOT;
   • plan: first estimate divergence, loops, buffers, rows filtered, heap fetches, spill, JIT;
   • concurrency: row/table/advisory locks, DDL, deadlock, snapshot age, SSI abort;
   • memory/I/O: per-node memory multiplication, temp I/O, cache misses, async I/O, checkpoint/WAL;
   • connection/session: backend count, pool mode, session state, named prepared plans;
   • client: pgjdbc generic/custom transition, rewrite batching, cursor prerequisites, timeouts.
3. Capture evidence at relation/query/session granularity before changing globals. A cluster average can hide one table whose scale-factor threshold or one transaction whose xmin controls the outcome.
4. Predict the counter that should move, apply the narrowest reversible change, and validate with the same plan/workload plus bloat/WAL/memory/lag guardrails.

Rules

• PostgreSQL updates create a new heap tuple and leave the old version for VACUUM. Long-lived xmin holders can prevent removal even when VACUUM reports success.
• Autovacuum is correctness-critical because of transaction-ID wraparound. Never disable it as a tuning fix; tune relation thresholds/cost/capacity from churn, table size, and completion evidence.
• HOT avoids new ordinary index entries when updated columns are not referenced by non-summarizing indexes and the new version fits on the same page. Summarizing indexes such as BRIN are an exception and may still need summary maintenance. Choose fillfactor from row size/update cadence and validate the HOT ratio delta.
• Index-only scan is a runtime condition, not only an index definition. High Heap Fetches points to visibility-map/maintenance state.
• work_mem is per sort/hash operation, per worker/session, and hash can use a multiplier. Count plan nodes and concurrency before raising it globally.
• Read EXPLAIN (ANALYZE, BUFFERS) from the deepest estimate divergence and include loops, reads, batches, disk sort, heap fetches, and rows removed. A sequential scan alone is not a defect.
• Prepared planning has two layers: pgjdbc's named-statement threshold and PostgreSQL's custom-versus- generic decision. Warm-up on the same connection can change the plan without a deploy.
• PgBouncer transaction pooling does not preserve arbitrary session state. Named-protocol prepared statement support does not make SET, LISTEN, temp tables, session advisory locks, or every SQL PREPARE use safe.
• READ COMMITTED, REPEATABLE READ, and SERIALIZABLE are MVCC modes with different snapshot/conflict behavior. Serialization failure 40001 requires retrying the whole transaction, including decisions that produced its SQL, under a safe bounded policy; external effects need separate protection.
• A bigger max_connections, work_mem, WAL size, or cost-constant change is not a diagnosis. State the measured bottleneck, multiplication, expected effect, and failure guardrail.

Output

Report versions and evidence window, observations, mechanism and alternatives, confidence reason, relation/query/session scope, intervention and predicted signal, validation, guardrails, and rollback.

References

• MVCC, VACUUM, and indexes — read for bloat, blocked cleanup, freeze/wraparound, HOT/fillfactor, visibility map, BRIN, or partial-index behavior.
• Plans, memory, WAL, and concurrency — read for plan evidence, work memory/spills, JIT, checkpoints/WAL, locks, isolation, or instance configuration.
• pgjdbc and PgBouncer — read when plans change after warm-up, fetch/batch does not behave as expected, or pool mode conflicts with session state.