PostgreSQL Tuning: a production performance guide for real workloads

PostgreSQL tuning is not copying a parameter list into postgresql.conf. It is an engineering loop: measure the workload, locate the limiting resource, form a hypothesis, change the minimum necessary, and prove the result. A configuration that is excellent for a transactional ERP can cripple an analytical workload; an index that accelerates reads can increase WAL, storage, and write cost.

What a complete PostgreSQL tuning review covers

Performance emerges from the application, data model, planner, configuration, operating system, and storage. A useful review separates five layers: workload and concurrency; queries and plans; schema, indexes, and statistics; memory, WAL, checkpoints, and autovacuum; and the CPU, storage, network, or container limits underneath them.

Changing only one layer often moves the bottleneck. Raising work_mem may eliminate one disk sort while causing an out-of-memory event when many concurrent operations consume the new limit.

1. Establish a baseline before changing parameters

Record a normal window and a problematic window. Compare latency percentiles, transactions per second, CPU, physical reads, temporary files, checkpoints, locks, and active sessions. Use deltas over time instead of a single snapshot.

SELECT now(), datname, xact_commit, xact_rollback,
       blks_read, blks_hit, temp_files, temp_bytes, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

SELECT wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2 ORDER BY sessions DESC;

2. Find the queries that consume the system

The slowest query is not necessarily the most expensive. Rank pg_stat_statements by total execution time, calls, mean time, block reads, and temporary writes. A five-second report run twice a day may cost less than an 80 ms query called thousands of times per minute.

SELECT queryid, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_read, temp_blks_written,
       left(query, 180) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

Capture EXPLAIN (ANALYZE, BUFFERS, WAL) safely and compare estimated rows, actual rows, loops, buffers, disk sorts, and WAL. Remember that ANALYZE executes the statement, including writes. Continue with our query and EXPLAIN guide.

3. Budget memory from real concurrency

shared_buffers reserves shared memory, effective_cache_size is a planner estimate, and work_mem is a limit per sort or hash operation. One query can use several operations and parallel workers, so dividing RAM by max_connections is not a safe formula.

potential use = work_mem × operations per query × concurrent queries × workers

Prefer role-, database-, or session-level increases for targeted workloads before raising the global value. Use the PostgreSQL Configuration Planner to model memory, concurrency, WAL, and I/O.

4. Control WAL and checkpoints

Frequent requested checkpoints concentrate writes. Very large checkpoint intervals require more recovery capacity and disk headroom. Evaluate max_wal_size, checkpoint_timeout, and checkpoint_completion_target together with WAL generation, recovery objectives, slots, and available storage. PostgreSQL 17 moved checkpoint metrics into pg_stat_checkpointer, so always use the views for your server version.

5. Match planner costs to storage

random_page_cost and effective_io_concurrency should represent the real storage. Values copied from local NVMe do not describe network storage or a cloud volume capped by IOPS. Fix stale or insufficient statistics before changing costs; bad cardinality estimates are often the real cause of a poor plan.

6. Treat indexes as investments with write cost

Every candidate needs a query and plan that justify it, a selectivity assessment, an expected size, a write-cost review, an overlap check, and a production creation and rollback plan. CREATE INDEX CONCURRENTLY avoids blocking writes but runs longer, consumes resources, and can leave an invalid index after failure. Review our guide to B-tree, GIN, and BRIN indexes.

7. Tune autovacuum per table

Tables of different sizes and write rates rarely fit one global threshold. Track dead tuples, dead ratio, last autovacuum, vacuum duration, and wraparound risk. Before making vacuum more aggressive, find long transactions, idle-in-transaction sessions, and replication slots that hold back xmin. Read the full autovacuum tuning guide.

8. Control connections and contention

max_connections is a safety ceiling, not a performance target. Measure active connections, pool wait time, and real concurrency. PgBouncer transaction pooling can help applications with many short connections, but validate session features, prepared statements, temporary tables, advisory locks, and application behavior first.

9. Tune PostgreSQL on RDS and Aurora with database context

On Amazon RDS and Aurora PostgreSQL, parameter groups, storage limits, CloudWatch, and Performance Insights change the control surface, not the method. Correlate provider metrics with pg_stat_statements, waits, plans, autovacuum, and WAL. Do not resize from CPU alone before identifying the query, plan, or batch job behind the load.

10. Use hypotheses, validation, and rollback

1. Define the symptom, window, impact, and frequency.
2. Record latency, throughput, resources, plans, and current configuration.
3. State why the proposed change should affect that symptom.
4. Test against representative load or use a controlled rollout.
5. Change one variable at a time and compare the same measurement window.
6. Roll back when the success criterion is not met or side effects appear.

Production PostgreSQL tuning checklist

• Configure pg_stat_statements with appropriate tracking capacity.
• Capture baselines during normal load and the incident window.
• Prioritize queries by total impact, not maximum duration alone.
• Compare estimated and actual rows with EXPLAIN.
• Budget work_mem from operations and concurrency.
• Correlate checkpoints, WAL, slots, and disk capacity.
• Review missing, redundant, and write-expensive indexes.
• Tune autovacuum per table and remove xmin blockers.
• Document the hypothesis, success metric, rollout, and rollback.

PostgreSQL tuning consulting

PG Monitoring reviews queries, plans, indexes, configuration, autovacuum, WAL, RDS, Aurora, and capacity with controlled collection and evidence-based recommendations. This guide was prepared by João Victor Oliveira, a Senior Database Administrator with 13+ years of experience across mission-critical banking, public-sector, cloud, and enterprise environments. Discuss a real environment through our PostgreSQL tuning consulting service or request a PostgreSQL assessment.