PostgreSQL Performance Engineering: Deep Tuning Under Real Load

01 · BUSINESS PROBLEM

Latency spikes, dashboards timing out, and an on-call team running on vibes

The platform's PostgreSQL estate had become the single point where every incident converged. Peak-hour latency spiked unpredictably; customer-facing dashboards timed out; the support queue filled with "the app is slow" tickets that no one could attribute to a specific cause. The engineering team's tuning practice was reactive: every complaint produced a new index, the slow log was effectively unexamined, and nobody could say with evidence which queries actually consumed the database's time. The estate wasn't mis-sized — it was mis-understood. The work needed wasn't a bigger instance; it was a method.

02 · INDUSTRY

Anonymized

Industry

A B2B SaaS platform serving business customers, running a multi-tenant PostgreSQL estate as its system of record.

Anonymization

Client name and identifying details withheld. Presented as experience informing AnovaCloud's methodology, not as an AnovaCloud delivery claim.

03 · SCALE

Orders of magnitude that shaped the design

Estate

A primary with hot-standby read replicas, carrying a mixed workload: multi-tenant OLTP at peak business hours plus internal analytics and export jobs running against the same cluster.

Growth

Steady tenant growth meant table sizes and connection counts had been climbing for years while the tuning settings dated from the estate's much smaller beginnings.

Workload shape

A long tail of query patterns — the application's ORM plus a handful of hand-written reports — with a few dominant query families hiding inside thousands of distinct query texts.

04 · CONSTRAINTS

The non-negotiables

• Production stays up: tuning had to happen against a live, loaded database during business hours — no maintenance windows long enough for rebuild-everything approaches.
• Every change reversible: each remediation shipped one at a time with a rollback story, and was re-measured before the next one went in.
• Tune the database before rewriting the app: the organization agreed not to start with application rewrites — the estate had headroom that measurement could unlock first.
• Analytics stays on Postgres: the reporting jobs couldn't be moved off the cluster immediately, so the primary had to get healthier while still carrying them.
• No hero settings: global "performance" config pasted from blog posts was explicitly banned — every setting had to be justified against this workload's evidence.

05 · BASELINE

What the estate actually looked like

The first pass was an audit, not a fix. The findings were ordinary and damning: pg_stat_statements wasn't installed, so nobody had a ranked list of what the database spent its time on. Autovacuum ran at factory defaults — settings chosen when the tables were a fraction of their size. The schema carried indexes nobody owned, some never scanned since creation, each one taxing every write and blocking HOT updates. Connection count had grown to whatever the app servers happened to open, with thousands of mostly-idle backends. And one table had dead tuples accumulating for months because a reporting job held a transaction open long enough to pin the xmin horizon. None of this was exotic. That's the point: the baseline of a "slow Postgres" is almost always a stack of ordinary neglect, and the fix is almost always methodical.

06 · THE TUNING LOOP

Triage, diagnose, remediate — then measure again

The engagement ran as a loop, not a project plan with an end date. Triage produced a ranked list of where database time actually went. Diagnose explained why, with execution plans and storage forensics. Remediate shipped one change at a time, each re-measured against the same ranked list. Nothing graduated from "looks faster in dev" to "helps in production" without surviving the loop — and the loop kept running after the engagement ended, which is what keeps the estate healthy rather than merely tuned once.

07 · SLOW-QUERY TRIAGE

Ranking offenders with pg_stat_statements discipline, not vibes

The discipline mattered more than any single query found. Before triage, tuning arguments were settled by seniority and anecdote. After triage, they were settled by the ranked list — which also meant arguments ended faster.

08 · VACUUM & BLOAT

Vacuum warfare: the dead tuples were coming from inside the transaction

Bloat forensics came first: per-table dead-tuple counts from pg_stat_user_tables, confirmed with sampling on the suspects. Two sources emerged. The first was the long-running reporting job, which held its transaction open for hours and pinned the xmin horizon — vacuum could see the dead tuples but couldn't remove them. The second was autovacuum itself: factory defaults meant a table had to accumulate twenty percent of its rows as dead tuples before vacuum even started, by which time the largest tables were drowning. The remediation was layered:

• Per-table autovacuum settings on the hot tables — lower scale factors and explicit thresholds via ALTER TABLE ... SET, so vacuum fired early and often instead of late and desperately. Settings were tuned to the table's churn pattern, not copied from a guide.
• Cost throttling rebalanced: autovacuum's cost delay and limit were adjusted so workers actually finished their passes without being throttled into irrelevance — aggressive enough to keep up, gentle enough not to fight the workload for I/O.
• The long transaction was fixed at the source: the export was moved to a replica, chunked into bounded transactions, and given a statement_timeout so it could never again hold the horizon hostage.
• Freeze discipline: wraparound monitoring went into the alerting tier (see Monitoring) so anti-wraparound vacuums stopped being surprises.

09 · INDEX STRATEGY

Index surgery: what was added, what was dropped, and the one that made things worse

Indexes were treated as a portfolio to manage, not a list to grow. Added: covering indexes for the two dominant query families from the triage list — key columns plus the selected payload columns, turning them into index-only scans; and a partial index on the "active tenant" predicate the application filtered on in nearly every query, far smaller than a full-table equivalent. Dropped: every index with zero scans in pg_stat_user_indexes across a full cycle, plus redundant indexes sharing leading columns with stronger siblings. Each dead index had been taxing every write and, worse, blocking HOT updates on the tables it indexed. The index that made things worse deserves its own paragraph: an index on a timestamp column, added to speed up "recent activity" dashboards. It worked for those dashboards — and then the planner started choosing it for wide-range exports that were faster as parallel sequential scans. Reads got slower, writes paid the index tax, and HOT updates died on the hottest table in the estate. It was measured, confirmed as a net negative, and dropped. The lesson the team kept: an index is a hypothesis until production data says otherwise.

10 · PLANNER SURPRISES

The misestimate that mattered, and the statistics that fixed it

The engagement's most instructive moment was a single query joining an events table to tenants with a correlated predicate. EXPLAIN (ANALYZE, BUFFERS) showed the planner estimating a few hundred rows and choosing a nested loop; reality was millions of rows, and the loop ran until the query gave up on any reasonable latency. The planner wasn't malfunctioning — it was working with bad information: default statistics targets on skewed columns, and no knowledge that the two predicate columns were correlated. The fix was statistics, not hints: raised SET STATISTICS targets on the skewed columns and CREATE STATISTICS extended statistics capturing the dependency and ndistinct of the correlated pair. After a fresh ANALYZE, the estimate landed within an order of magnitude and the planner chose the hash join on its own. Two rules came out of it, both now permanent: never tune from EXPLAIN without ANALYZE — estimated plans are the planner's imagination, not evidence — and treat a row misestimate of more than an order of magnitude as a statistics problem first, a plan-shape problem second.

11 · CONNECTION POOLING

PgBouncer in transaction mode — and the transaction-pooling gotcha

The connection picture was familiar: thousands of mostly-idle backends, each holding memory and forcing the OS scheduler to context-switch through a crowd doing nothing. PostgreSQL doesn't scale by connection count; it scales by active connection count. The decision was PgBouncer in transaction pooling mode between the app fleet and the primary, so a backend was held only for the life of a transaction. That choice had a price, and the price was the gotcha: transaction pooling discards session state between transactions, which means prepared statements, advisory locks, LISTEN/NOTIFY, SET parameters, and temp tables all break or leak in subtle ways. The application was using prepared statements through its driver — so the rollout included disabling server-side prepared statements in the driver configuration, auditing the codebase for session-state assumptions, and load-testing the pooler under peak-shaped traffic before cutover. The pool-wait metric became the estate's newest and most honest signal: queueing at the pooler is the first sign the database is saturated, and it now had a number.

12 · PARTITIONING

What was partitioned — and what was deliberately left alone

Partitioning was applied where it paid and refused where it didn't. Partitioned: the events table, range-partitioned by month. Time-bounded queries got partition pruning, and old partitions could be detached — not dropped, since retention policy required keeping them — and moved to cheaper storage. The operational shape of the table changed from "one giant thing we fear" to "twelve small things we understand." Deliberately left alone: the tenants and users reference tables. They were small, hot, and joined constantly; partitioning them would have added planning overhead and join complexity for pruning benefits that didn't exist, since queries never filtered them by a partition key. The sessions table was also left alone — its churn pattern would have turned partition management into constant operational noise. Partitioning is a maintenance strategy with a query-optimization side effect, not a performance strategy; applied to the wrong table it's just distributed regret.

13 · MONITORING NOW

What the estate looks like when it's watched properly

The engagement's last deliverable was a monitoring posture, because every tuned database drifts back toward its baseline without one. The estate now watches leading indicators, not just latency:

• p99 latency by query family — the SLO signal, tracked against the triage families so regressions land on a named suspect.
• The statement registry: the pg_stat_statements top-N is snapshotted weekly; a new entrant in the top ten triggers a review, not a fire drill.
• Vacuum and bloat health: dead-tuple ratios, autovacuum lag per table, and distance-to-wraparound — the alerts that fire before latency moves.
• Pooler pressure: PgBouncer wait times and queue depth — the earliest honest sign of saturation.
• Replica lag and long transactions: the two failure modes that caused the original bloat, now with thresholds and pages.
• Checkpoint frequency: a quiet indicator of write-pattern changes, watched for drift.

Weekly triage review became a standing habit for the platform team: fifteen minutes, the ranked list, any new entrants explained or scheduled. The on-call rotation stopped being a war.

14 · RESULTS

Outcomes, described without invented metrics

• p99 latency for the dominant query families moved back inside the SLO band and stayed there through peak cycles — measured against the triage list, not asserted.
• The reactive tuning culture ended: index additions now require a triage-list entry and a measurement plan, and the "just add an index" era is over.
• Autovacuum stopped being a source of incidents — the per-table settings and the wraparound alerts turned it from a surprise into a background process.
• Connection pressure collapsed under transaction pooling, and pool-wait time became the estate's early-warning signal instead of a mystery.
• The reporting workload moved off the primary's critical path, so analytics and exports no longer compete with customer traffic for the same backends.

15 · HOW ANOVACLOUD APPROACHES THIS

The same method, productized as the PostgreSQL Health Check

This tuning war is the experience behind AnovaCloud's PostgreSQL Health Check: the same evidence-first loop — instrument, rank by total time, diagnose with plans and storage forensics, remediate one change at a time with re-measurement — run as a fixed-scope engagement against your estate. The output is a ranked findings report with remediation priorities and the monitoring posture that keeps the estate from drifting back, not a pile of config diffs without context. The methodology above is what we practice; the Health Check is how you get it applied to your database. See the PostgreSQL Health Check →

16 · RELATED

Pattern, toolkit, service