PostgreSQL performance tuning

PostgreSQL performance tuning finds the bottleneck, tests the change, and proves the result

In short: PostgreSQL performance tuning finds which queries and system constraints actually consume database time, changes them under control, and proves the result. Work starts with a representative baseline—latency percentiles, throughput, and error rate for the operations that matter—then ranks statements in pg_stat_statements by total execution time, reads representative plans with EXPLAIN (ANALYZE, BUFFERS), and separates query cost from index design, planner statistics, lock contention, connection pressure, autovacuum and bloat, WAL and checkpoint behavior, and storage limits. The fix is the smallest change the evidence supports: a rewritten query, an added or dropped index, refreshed statistics, a corrected work_mem or autovacuum threshold, or a pooling change in PgBouncer or Pgpool-II. Every change carries an expected effect, a test, and a rollback condition, and is validated by repeating the original measurements at p95 and p99 under comparable load—not by reporting a better average.

JusDB produces a reproducible diagnosis and change plan. Improvement targets and validation criteria are agreed after the workload, environment, and observation window are understood.

Looking for an informational walkthrough instead of an engagement? Read the PostgreSQL performance tuning playbook.

Request a performance assessment Discuss the workload

Slow query diagnosis

How do you find slow PostgreSQL queries?

Four sources of evidence, used in this order: pg_stat_statements to rank the whole workload by total execution time, log_min_duration_statement to catch individual slow executions with their real parameters, auto_explain to record the plan that was actually slow, and EXPLAIN (ANALYZE, BUFFERS) to confirm what a candidate fix really does. A slow query is often slow for a different reason than the first look suggests, so nothing is changed until one of these shows why.

-- Rank statements by the total time they consume, not by how slow one run feels.
-- Needs shared_preload_libraries = 'pg_stat_statements' and CREATE EXTENSION pg_stat_statements;
SELECT
  calls,                                          -- executions since the last reset
  round(total_exec_time::numeric, 1) AS total_ms, -- calls x mean: the real workload cost
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  shared_blks_read,                               -- blocks read outside shared_buffers
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- PostgreSQL 12 and older expose total_time / mean_time instead of the _exec_ columns.
-- Call pg_stat_statements_reset() first so the measurement window is explicit.

The ranking matters more than any single row. A statement that takes 8 ms and runs two million times a day consumes far more database time than a four-second report that runs twice, and the cheapest fix is rarely on the query that feels slowest to the person reporting it.

pg_stat_statements

Normalized, cumulative statistics for every statement the server has executed. Ordering by total_exec_time answers the question that has to come first: which statements consume the most database time in aggregate, regardless of how fast any single execution looks.

CaveatIt is a running total, so it only describes a window if a pg_stat_statements_reset() time is recorded next to it. Statements evicted once pg_stat_statements.max is reached disappear without notice, and it reports timing rather than the waits behind that timing.

log_min_duration_statement

PostgreSQL has no separate slow query log file the way MySQL does. Setting log_min_duration_statement sends the text and duration of any statement that exceeds a threshold to the regular server log, together with the actual parameter values. This is how the outliers that an aggregate view averages away become visible, including the plan-flip that only happens for one customer or one date range.

CaveatA threshold set too low turns logging into its own I/O problem. Set it per role or per database rather than cluster-wide, start well above the current p99, and use log_min_duration_sample with log_statement_sample_rate on high-volume workloads.

auto_explain

Records the execution plan of statements that cross a duration threshold, so the plan that was actually slow in production is captured instead of a plan reproduced later under different statistics, parameters, or cache state.

Caveatauto_explain.log_analyze adds per-node instrumentation to every statement it measures, so it is enabled deliberately for a bounded window, usually with auto_explain.sample_rate below 1 and log_timing considered separately.

EXPLAIN (ANALYZE, BUFFERS)

Executes the statement and reports estimated versus actual rows, loop counts, per-node timing, and shared, local, and temp buffer hits, reads, and writes. BUFFERS is what separates a query that is slow because of the plan from one that is slow because it is reading from storage.

CaveatANALYZE runs the statement, so data-modifying statements are wrapped in BEGIN and rolled back. A large gap between estimated and actual rows points at stale statistics, correlated predicates that need extended statistics, or a distribution the planner cannot currently see.

For a query that is slow right now rather than slow on average, pg_stat_activity shows in-flight statements, their state, and the wait event they are stuck on; joined against pg_locks it also shows which session is blocking which. That distinction decides the fix: a plan problem is solved with SQL, indexes, or statistics, while a wait problem is usually solved in transaction boundaries, pooling, or storage.

What does PostgreSQL performance tuning investigate?

The work follows the evidence across the database, operating environment, and application boundary instead of prescribing one configuration recipe to every system. Each area below lists the evidence it is judged on and the guardrail that stops a local win from becoming a global regression.

Queries, plans, and planner statistics

Rank statements by total database time, then read representative plans for scan choice, join order and method, row-estimate error, sort and hash spills, and parallel worker behavior before anything is rewritten.

Evidence

pg_stat_statements ordered by total_exec_time
EXPLAIN (ANALYZE, BUFFERS) with realistic parameters
Estimate-versus-actual rows, pg_stats, and extended statistics

Guardrail

PostgreSQL has no query hints, so a plan changes only through statistics, cost settings, indexes, or the SQL itself. A plan that is faster for one parameter set can be slower for another, so candidate rewrites are tested across the distributions the application actually sends.

Indexes and access paths

Assess index coverage, redundancy, selectivity, partial and expression candidates, partition pruning, and whether an existing index can already serve the workload before adding another one.

Evidence

idx_scan and seq_scan from pg_stat_user_indexes and pg_stat_user_tables
Index size, bloat, and write amplification per table
Plan node choice: sequential scan, index scan, or bitmap heap scan

Guardrail

Every index adds insert, update, WAL, and vacuum cost. Builds use CREATE INDEX CONCURRENTLY with a check that no INVALID index was left behind, and drops get an observation window because a rarely scanned index may serve a month-end job or back a constraint.

Locks, long transactions, and concurrency

Trace blocking chains, transaction duration, hot rows, deadlocks, lock escalation from DDL, and the application retry behavior that turns one slow statement into a queue.

Evidence

pg_locks joined to pg_stat_activity for blocking chains
Sessions in idle in transaction and their age
Deadlock, lock-wait, and timeout evidence in the server log

Guardrail

Sessions left idle in transaction hold locks and stop vacuum from removing dead rows, so the fix usually belongs in application transaction boundaries rather than a server setting. log_lock_waits, statement_timeout, and idle_in_transaction_session_timeout are used before any limit is raised.

Connections and pooling

Measure active versus idle backends, pool queueing, transaction duration, session state requirements, and connection storms before changing pool size, pool mode, or max_connections.

Evidence

Backend state distribution in pg_stat_activity
Pooler-side queueing from PgBouncer SHOW POOLS and SHOW STATS
Concurrency and transaction duration per application endpoint

Guardrail

PostgreSQL runs one backend process per connection, so raising max_connections trades memory and shared-structure contention for queueing. A pooler bounds the queue and makes it visible; it does not add CPU, storage, or lock capacity behind it.

Autovacuum, bloat, and transaction-ID age

Review dead-tuple accumulation, autovacuum frequency and duration, per-table thresholds and cost limits, index bloat, and how close the oldest relations are to anti-wraparound work.

Evidence

n_dead_tup, last_autovacuum, and autovacuum_count in pg_stat_user_tables
pg_stat_progress_vacuum for in-flight vacuum work
Table and index bloat estimates plus age(datfrozenxid)

Guardrail

Throttling or disabling autovacuum to reduce load increases bloat and brings anti-wraparound vacuum forward under worse conditions. Per-table scale factors, thresholds, cost limits, and worker counts are tuned instead, and long transactions are treated as a vacuum problem as much as a lock problem.

Memory, WAL, checkpoints, and storage

Correlate the working set with shared_buffers behavior, temp-file volume, checkpoint spread, WAL generation, background writer activity, and the latency and throughput ceiling of the underlying storage.

Evidence

Checkpoint counts and timing from pg_stat_bgwriter, or pg_stat_checkpointer on PostgreSQL 17
temp_bytes in pg_stat_database and log_temp_files output
Storage latency, IOPS ceiling, and burst or credit behavior

Guardrail

work_mem applies per sort or hash node, not per query, so a global increase multiplies with concurrency and parallel workers. Checkpoint spreading, WAL volume, and storage headroom are changed together, because forcing one down commonly moves the cost into another.

Connection pooling

How do PgBouncer and Pgpool-II fix PostgreSQL connection bottlenecks?

PostgreSQL runs one operating-system process per connection. Each backend costs memory and adds contention on shared structures before it executes a single statement, and an application pool full of idle connections still holds those backends open. Pooling converts a large, bursty client population into a bounded set of server backends—which is a scheduling change, not extra capacity.

Pool mode	What it does	What it constrains
session	A server backend is assigned for the life of the client connection and returned only at disconnect.	Fully transparent, so nothing in the application has to change, but reuse is limited: an idle client still holds a backend, which is why session mode does little for connection storms.
transaction	The backend returns to the pool at COMMIT or ROLLBACK, so a small pool can serve far more clients than it has connections.	Session-scoped state does not survive between transactions. SET, session-level advisory locks, WITH HOLD cursors, LISTEN and NOTIFY, and temporary tables break unless the application stops relying on them. PgBouncer 1.21 and later can track protocol-level prepared statements with max_prepared_statements; older builds cannot.
statement	The backend returns after every statement completes, and multi-statement transactions are rejected outright.	Reserved for autocommit-only traffic such as sharded or analytical access paths. Most application frameworks assume transactions and will fail immediately here.

PgBouncer: bounding the backend count

PgBouncer is a single-purpose pooler with very low per-connection overhead. It addresses the failure mode where connection growth—new services, autoscaling workers, retry storms—exhausts memory or degrades every session at once. Behind PgBouncer that growth becomes a measurable queue instead of a memory event, and the queue is visible in SHOW POOLS as waiting clients rather than inferred from a latency graph.

Levers: pool_mode, default_pool_size, reserve_pool_size, max_client_conn, query_wait_timeout, and server_idle_timeout, read against SHOW POOLS and SHOW STATS. PgBouncer does not parse or route queries, so it will not split reads across replicas and it is not a failover mechanism by itself.

PgBouncer services

Pgpool-II: routing reads and following the topology

Pgpool-II pools connections as well, but it also health-checks backends, load-balances eligible SELECT traffic across a primary and its streaming replicas, and can coordinate failover and online recovery. It matters when the primary is spending capacity on reads that could safely be served by a replica, or when clients cannot follow an endpoint change on their own.

Levers: load_balance_mode, backend weights, delay_threshold, disable_load_balance_on_write, health-check timings, and num_init_children in pgpool.conf, checked against actual routing and measured replication lag. Pgpool-II performance tuning is a routing-correctness exercise before it is a throughput exercise.

Pgpool-II services

Two constraints apply to both. First, load balancing is only correct for statements that are safe to route: transaction state, functions, temporary tables, locking, and replication delay all remove queries from the eligible set, and Pgpool-II itself becomes a component that needs its own availability design rather than a new single point of failure. Second, pooling reorganizes demand without creating capacity— if the primary is already saturated on CPU, storage, or locks, a larger pool simply moves the queue closer to the database. Pool size is derived from measured concurrency and transaction duration, leaves headroom for superuser_reserved_connections and maintenance work, and is validated with the same p95 and p99 comparison as any other change. Moving an application to transaction-mode pooling is a correctness change first, so it is tested against session state rather than assumed to be transparent.

Method

How is PostgreSQL performance tuned safely?

1
Record a representative baseline
Capture workload volume, latency distributions, database time, query statistics, waits, locks, I/O, WAL, vacuum activity, and host or managed-service metrics across a representative observation window.
2
Identify and rank bottlenecks
Use pg_stat_statements, execution plans, PostgreSQL cumulative statistics, logs, and infrastructure telemetry to rank query, index, contention, maintenance, connection, and resource constraints by user impact.
3
Design controlled changes
Create workload-specific query, index, configuration, vacuum, pooling, or capacity changes with expected effects, dependencies, risk, test criteria, and a rollback path.
4
Test and implement
Test changes with representative data and concurrency where possible, then apply approved production changes through the agreed change window and observe for regressions.
5
Validate against the baseline
Repeat the original measurements, compare latency and resource behavior under equivalent load, document tradeoffs, and retain only changes that meet the agreed acceptance criteria.

Configuration guardrails

Why PostgreSQL memory settings must be workload-specific

Configuration values interact with concurrency, the operating system, query plans, managed-service limits, and one another. Each change needs a reason, a test, and a rollback condition.

Setting	How it is evaluated
shared_buffers	Treat common percentages as test hypotheses, not universal targets. Hosting model, operating-system cache, workload, memory pressure, huge pages, and provider limits all affect the safe value.
effective_cache_size	This is a planner estimate of cache likely to be available; it does not reserve memory. Set it from observed system and database behavior, then inspect plan changes.
work_mem	Budget for concurrent operations and parallel workers because the value can be consumed more than once per query. Test spills and peak memory together.
random_page_cost	Calibrate against measured storage and cache behavior. Lowering it simply to force index scans can exchange one bad plan for another.

Version and platform awareness

PostgreSQL tuning levers change by release and hosting platform

Advice written for PostgreSQL 14 on a dedicated server can be unavailable, unsafe, or simply irrelevant on a managed service. The available statistics views, the parameters that can be edited, and the amount of operating-system access all differ, so every recommendation is checked against the release and platform in scope.

EnvironmentReview focusCaution

PostgreSQL 14 and 15

Confirm the exact minor release first. PostgreSQL 14 added compute_query_id, which exposes the same query identifier in pg_stat_activity, EXPLAIN VERBOSE, and pg_stat_statements, and makes correlation across those sources reliable.

Minor releases carry planner, vacuum, and replication fixes. Tuning around behavior that a minor upgrade already corrects spends change budget for nothing.

PostgreSQL 16 and 17

PostgreSQL 16 added pg_stat_io, which attributes reads, writes, and extends to their backend type and context. PostgreSQL 17 moved checkpointer counters into pg_stat_checkpointer and changed vacuum memory management.

Monitoring queries and dashboards written against older catalog views can silently return nothing after an upgrade, so observability is re-validated as part of the version change, not after it.

Amazon RDS and Aurora PostgreSQL

Parameters change through parameter groups, some require a reboot, and there is no superuser: work happens through the rds_superuser role, with Performance Insights and Enhanced Monitoring supplying the host-level evidence.

Aurora PostgreSQL does not use community streaming replication or the same storage and checkpoint path, so shared_buffers, WAL, and checkpoint guidance written for self-managed PostgreSQL does not transfer unchanged.

Cloud SQL and Azure Database for PostgreSQL

Establish which flags the provider supports, which need a restart, which extensions are allowed, and what the maintenance window permits before a change plan is written.

The supported-flag list, not the PostgreSQL manual, defines what can actually be changed. When a lever is unavailable, the fix has to move into the query, the schema, the pooling layer, or the instance tier.

Self-managed on VM, bare metal, or Kubernetes

Every parameter is available, plus the operating-system levers that managed platforms hide: huge pages, transparent huge pages, filesystem and mount options, I/O scheduler, cgroup limits, and container memory ceilings.

Full access also means full responsibility for memory that is over-committed across work_mem, autovacuum workers, and parallel workers. That over-commitment stays invisible until the kernel terminates a backend and the cluster restarts.

How are tuning changes validated?

Before-and-after comparisons use the same metric definitions and a comparable workload. A lower average latency alone is not enough if tail latency, errors, resource cost, or operational risk gets worse.

Area	Baseline	Validation
User-facing latency	p50, p95 and p99 by critical operation	Equivalent workload, same definitions and comparison window
Database work	Calls, total time, mean time, rows, reads and writes	Normalized statement comparison with reset times recorded
Resource behavior	CPU, storage latency, IOPS, memory, WAL and checkpoints	Confirm the change did not move the bottleneck elsewhere
Operational safety	Locks, replication lag, vacuum age and error rate	Acceptance thresholds and rollback triggers remain healthy

PostgreSQL performance tuning questions

How do you find slow PostgreSQL queries?

Four sources of evidence, in order. pg_stat_statements ranks the workload by total_exec_time, which surfaces the statements that consume the most database time in aggregate rather than the ones that feel slowest. log_min_duration_statement logs individual executions that exceed a threshold together with their real parameter values. auto_explain captures the execution plan of those slow executions in production, so the plan that was actually slow is recorded. EXPLAIN (ANALYZE, BUFFERS) then confirms a candidate fix by reporting estimated versus actual rows, per-node timing, and buffer reads. For statements that are slow right now, pg_stat_activity and pg_locks show the in-flight session, its wait event, and any blocking chain.

What causes PostgreSQL performance issues?

Common causes include inefficient queries, missing or redundant indexes, inaccurate planner statistics, lock contention, table or index bloat, connection pressure, storage latency, and configuration that does not match the workload. Diagnosis starts with a representative baseline rather than assuming one setting is responsible.

How does a PostgreSQL performance tuning engagement work?

We record workload and system baselines, rank bottlenecks by user impact, inspect execution plans and database statistics, propose controlled changes, and test those changes in a safe environment where possible. Production changes use an agreed change and rollback plan, followed by measurement against the original baseline.

How much faster will PostgreSQL be after tuning?

There is no credible universal percentage. The result depends on the baseline, query shape, data distribution, concurrency, hardware or instance class, PostgreSQL version, platform limits, and how much safe change is actually available. We agree targets first, such as p95 or p99 latency for a named operation at a defined throughput and error rate, then report the measured before-and-after comparison including any change that was tested and rejected.

Which PostgreSQL versions can you assess?

We tune currently supported PostgreSQL releases, typically 14 through 17, and can assess older installations as part of an upgrade or risk-reduction plan. Available metrics, planner behavior, extensions, and safe configuration options vary by PostgreSQL version and hosting platform, so recommendations are version-specific. PostgreSQL 14 added compute_query_id for cross-view correlation, 16 added pg_stat_io, and 17 moved checkpointer counters into pg_stat_checkpointer, so the same diagnostic query does not work everywhere.

Which tools and metrics are used for PostgreSQL tuning?

Depending on access and workload, analysis can use pg_stat_statements, EXPLAIN with appropriate options, auto_explain, PostgreSQL cumulative statistics, logs, pgBadger, operating-system metrics, and cloud-provider telemetry. Measurements commonly include latency distributions, call volume, database time, buffer and I/O activity, locks, WAL, checkpoints, vacuum progress, and connection demand.

How long does a PostgreSQL performance assessment take?

The schedule depends on workload variability, access, data sensitivity, test-environment availability, and the number of queries or systems in scope. After discovery, we define the observation window, deliverables, change gates, and validation period instead of promising a fixed duration before seeing the workload.

Can you tune PostgreSQL on AWS, Azure, or Google Cloud?

Yes. The same measurement-led method applies to managed and self-managed PostgreSQL, but available parameters, extensions, telemetry, restart behavior, storage choices, and connection options differ by service. On RDS and Aurora, changes go through parameter groups without superuser access; Cloud SQL and Azure Database for PostgreSQL expose their own supported-flag lists. Recommendations stay within the documented capabilities and change controls of the selected platform.

How do you decide whether an index should be added or removed?

Index decisions consider execution plans, query frequency, selectivity, write amplification, storage, maintenance cost, and whether an existing index can serve the workload. Candidate changes are tested with representative queries, built with CREATE INDEX CONCURRENTLY where the platform allows it, and removals require an observation window and rollback plan because low-usage indexes may support infrequent critical operations or back a constraint.

What is the difference between PgBouncer and Pgpool-II?

PgBouncer is a lightweight connection pooler. It bounds how many server backends exist behind a large client population and makes the resulting wait visible as a queue, but it does not parse or route queries, so it will not distribute reads across replicas. Pgpool-II also pools connections and additionally health-checks backends, load-balances eligible SELECT traffic across a primary and its streaming replicas, and can coordinate failover and online recovery. That extra capability adds routing-correctness questions and makes Pgpool-II a component that needs its own availability design. Many clusters run PgBouncer for connection control and handle read routing in the application instead.

How is connection pooling tuned?

Pool sizing is based on application concurrency, transaction duration, database capacity, reserved administrative connections, and failure behavior. Pool mode is chosen against application requirements: session mode is transparent but reuses little, transaction mode gives the highest reuse but breaks session-scoped state such as SET, temporary tables, session advisory locks, and LISTEN or NOTIFY, and statement mode only suits autocommit-only traffic. For Pgpool-II, load-balancing settings are tuned alongside replication delay thresholds and health checks. The goal is stable throughput and bounded queueing, not the largest possible connection count.

Start with a measurable PostgreSQL baseline

Share the workload symptoms, hosting model, PostgreSQL version, change constraints, and available telemetry. We will define an assessment scope and the evidence needed to validate any recommendation.

Scope the assessment

Evidence and review method

Technical review and primary sources

The diagnostic workflow on this page is reviewed against PostgreSQL documentation for cumulative statistics, execution plans, slow-statement logging, auto_explain, server configuration, vacuuming, and the pg_stat_statements extension, plus the PgBouncer and Pgpool-II project documentation for pooling behavior. These sources support the method, not a guaranteed performance result.

Editorial owner: JusDB Database Reliability Engineering team. Last reviewed 12 August 2026. See the team and roles.

Service scope, timelines, availability targets, and outcomes depend on the workload, PostgreSQL version, topology, infrastructure, change controls, and validation method agreed for the engagement.

PostgreSQL performance tuning finds the bottleneck, tests the change, and proves the result

How do you find slow PostgreSQL queries?

pg_stat_statements

log_min_duration_statement

auto_explain

EXPLAIN (ANALYZE, BUFFERS)

What does PostgreSQL performance tuning investigate?

Queries, plans, and planner statistics

Indexes and access paths

Locks, long transactions, and concurrency

Connections and pooling

Autovacuum, bloat, and transaction-ID age

Memory, WAL, checkpoints, and storage

How do PgBouncer and Pgpool-II fix PostgreSQL connection bottlenecks?

PgBouncer: bounding the backend count

Pgpool-II: routing reads and following the topology

How is PostgreSQL performance tuned safely?

Record a representative baseline

Identify and rank bottlenecks

Design controlled changes

Test and implement

Validate against the baseline

Why PostgreSQL memory settings must be workload-specific

PostgreSQL tuning levers change by release and hosting platform

PostgreSQL 14 and 15

PostgreSQL 16 and 17

Amazon RDS and Aurora PostgreSQL

Cloud SQL and Azure Database for PostgreSQL

Self-managed on VM, bare metal, or Kubernetes

How are tuning changes validated?

PostgreSQL performance tuning questions

Start with a measurable PostgreSQL baseline

Technical review and primary sources

Related PostgreSQL Services

PostgreSQL Consulting

PostgreSQL Support

PostgreSQL High Availability

AWS RDS PostgreSQL Consulting

Aurora PostgreSQL Consulting

PostgreSQL Cloud Migration