PostgreSQL performance tuning with practical examples: practical implementation guide

Intro

PostgreSQL can deliver excellent performance when the workload, schema, queries, and settings fit together. This guide shows how to find bottlenecks, size resources, reduce latency, raise throughput, and roll out changes safely. You will get concrete commands, SQL, and patterns you can apply immediately.

Workflow Overview

Use a repeatable process to avoid guesswork:

1. Baseline

• Capture workload facts: connections, QPS, p95 latency, CPU, memory, I/O, and largest tables.
• Record current settings and hardware.

1. Triage bottlenecks

• Identify if you are bound by CPU, memory, I/O, locks, or query design.

1. Form a hypothesis

• Pick one root cause to address (example: missing index, hot table bloat, or mis-sized work_mem).

1. Change minimally

• Prefer session-level tests first. For DDL, use CONCURRENTLY where possible.

1. Verify with measurement

• Use EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements deltas, and system metrics.

1. Roll out and watch

• Apply in a maintenance window if risk is high. Monitor leading and lagging indicators.

1. Iterate

• Move to the next bottleneck only after verifying gains.

Quick Health Check

Run these checks to understand the current state.

Connections and load:

SELECT numbackends, xact_commit, xact_rollback, blks_read, blks_hit
FROM pg_stat_database WHERE datname = current_database();

Top queries by mean time and total time (requires pg_stat_statements):

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Cache effectiveness (higher hit rate is better):

SELECT sum(blks_hit) / NULLIF(sum(blks_hit + blks_read),0)::numeric AS cache_hit_ratio
FROM pg_stat_database;

Autovacuum pressure and dead tuples:

SELECT relname, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Lock waits (active blockers first):

SELECT a.pid, a.usename, a.state, l.locktype, l.mode,
       l.relation::regclass AS relation, now() - a.query_start AS age,
       a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
ORDER BY age DESC
LIMIT 20;

Resource Sizing and Config

Right-size settings to your hardware and workload. The values below are starting points, not absolutes.

Core principles

• Keep fsync=on for durability; never disable for production.
• Use a connection pooler so max_connections stays modest (for example 100-300). Fewer backends reduce context switching and memory pressure.
• Tune memory with concurrency in mind. work_mem is per sort/hash operation, not per session.

Memory

Example: 64 GB RAM, shared_buffers 16 GB, headroom 8 GB -> 40 GB left. With ~200 concurrent queries and 2 sorts each -> 40 GB / 400 = 100 MB. Set work_mem ~64-96 MB globally, and increase per session for heavy jobs:

• shared_buffers: 15-25% of RAM on a dedicated host. Too high can fight the OS cache.
• effective_cache_size: 50-75% of RAM to reflect OS cache; it guides the planner.
• work_mem: budget = (RAM - shared_buffers - OS headroom) / (concurrent_sorts).

SET work_mem = '512MB';  -- for one session only

• maintenance_work_mem: use higher values for VACUUM, CREATE INDEX, and REINDEX.

I/O and WAL

• max_wal_size: increase to reduce checkpoint frequency on write-heavy systems (for example 8-32 GB+ depending on volume).
• checkpoint_timeout: 10-15 min is common; keep checkpoints predictable.
• checkpoint_completion_target: 0.8-0.95 to spread checkpoint I/O.
• wal_compression = on can reduce write I/O for update-heavy workloads.
• synchronous_commit: on for safety; consider off for noncritical, high-throughput inserts after testing.
• effective_io_concurrency: higher (for example 100-300) helps on fast storage with good parallelism.

Planner

• random_page_cost: for SSDs set near 1.1-1.5 (default is higher), seq_page_cost ~1.0.
• default_statistics_target: raise (for example 200-500) on columns with skew to improve estimates.

Parallelism

• max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather: enable 1-4 workers per gather for analytic queries after verifying gains.

Latency Checks

Find slow paths, explain them, and confirm the fix.

Step 1: Identify outliers

SELECT queryid, query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 2: Explain one slow query

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.customer_id, sum(o.total_cents) AS revenue
FROM orders o
WHERE o.status = 'PAID'
  AND o.created_at >= now() - interval '30 days'
GROUP BY o.customer_id
ORDER BY revenue DESC
LIMIT 20;

If you see Seq Scan on orders with many rows filtered, add a targeted index.

Fix: partial + covering index for recent paid orders

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
  ON orders (created_at DESC)
  WHERE status = 'PAID';

Re-check the plan; it should switch to Index Only Scan if the table is well vacuumed:

• Index Only Scan using idx_orders_paid_recent
• Buffers: fewer reads, lower shared/local hits
• Execution time drops; confirm rows are correct

Step 3: Validate improvements

• Compare mean_exec_time and calls in pg_stat_statements before vs after.
• Check buffers in EXPLAIN to confirm fewer reads.
• Monitor p95/p99 latency under the same or higher load.

Throughput Tuning

Raise QPS by removing hot spots and avoiding wasted work.

Indexes

• Composite index order matters: put the most selective or join/filter columns first.
• INCLUDE columns to create covering indexes without affecting ordering.
• Use partial indexes for common predicates (status='PAID', tenant_id=..., soft-deleted=false) to keep indexes small.

Example: speeding up lookups and sorting

-- Before: slow due to filter + sort on large table
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50;

-- After: composite index supports filter + order
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

Query shape

• Select only needed columns; avoid SELECT * in hot paths.
• Use EXISTS instead of IN for semi-joins when appropriate.
• Avoid functions on indexed columns in WHERE (wrap the constant instead).
• Batch writes to reduce commit overhead; prefer COPY for bulk loads.

Connection management

• Keep max_connections modest and use a pooler. Over-provisioned connections kill throughput via context switching and memory fragmentation.

Checkpoints and WAL

• If latency spikes appear every few minutes, raise max_wal_size and checkpoint_completion_target to smooth I/O.

Parallel query (analytics)

• Allow 1-4 parallel workers per gather for large scans and aggregations; verify CPU and I/O can sustain it.

Vacuum and Bloat Control

MVCC creates dead tuples on updates and deletes. Left unchecked, dead tuples cause table/index bloat, poor cache usage, and slow queries.

Autovacuum knobs

• autovacuum_vacuum_scale_factor: reduce from defaults on hot tables (for example 0.05 or lower) so vacuum runs sooner.
• autovacuum_analyze_scale_factor: lower so stats stay fresh on fast-changing tables.
• autovacuum_vacuum_threshold and analyze_threshold: set an absolute floor for small tables.
• autovacuum_naptime: shorten for rapid-update workloads.
• autovacuum_work_mem: raise to speed processing if memory allows.

What to watch

SELECT relname, n_dead_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Reindexing

• If index bloat is high (size much larger than expected), use REINDEX CONCURRENTLY on busy systems.
• Avoid VACUUM FULL during peak hours; it is blocking and rewrites the table.

HOT updates

• Keep frequently updated columns out of hot indexes if possible so HOT updates can happen more often.

Monitoring and Alerts

Build lightweight, actionable visibility.

Core SQL probes

• Top queries:

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

• Cache ratio:

SELECT sum(blks_hit) / NULLIF(sum(blks_hit + blks_read),0)::numeric AS cache_hit_ratio
FROM pg_stat_database;

• Checkpoints and background writer:

SELECT * FROM pg_stat_bgwriter;

• Lock waits and blockers: use pg_locks join pg_stat_activity as shown earlier.

Operational alerts (example thresholds to tune for your system)

• p95 latency above SLO for N minutes.
• Replication lag above failover threshold.
• Cache hit ratio drops unexpectedly.
• Checkpoint distance near max_wal_size and spikes in timed checkpoints.
• Autovacuum lag with many dead tuples on hot tables.
• Disk usage growth rate above budget (watch largest relations).

Local Pilot Plan

Run a small, measurable pilot before wider rollout.

Goal

• Reduce p95 latency of one known slow query by 30%+ without regressions.

Scope

• One table (orders) and one endpoint that runs a target query.

Steps (1-2 days)

1. Baseline

• Enable pg_stat_statements if not enabled.
• Record: calls, mean_exec_time, rows, and p95 for 1-4 hours under normal load.

1. Hypothesis

• Example: add partial covering index for status='PAID' and created_at desc.

1. Safety

• Use CREATE INDEX CONCURRENTLY to avoid long locks.
• Test with EXPLAIN (ANALYZE, BUFFERS) on a staging copy or during off-peak.
• Keep a rollback: DROP INDEX CONCURRENTLY idx_orders_paid_recent if needed.

1. Implement

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at DESC)
WHERE status='PAID';

1. Verify

• Re-run EXPLAIN (ANALYZE, BUFFERS) and compare.
• Compare pg_stat_statements deltas for mean_exec_time and total time.
• Watch CPU, I/O, and lock waits for 1-2 hours.

1. Decide

• If gains are consistent and no regressions, keep the change and document it.
• If not, revert and try the next hypothesis (for example work_mem for this query via SET local).

Conclusion

Effective PostgreSQL tuning is iterative: measure, hypothesize, change, and verify. Start with a clean workflow, right-size memory and WAL settings, target the highest-impact queries with precise indexes, and keep vacuum healthy to avoid bloat. Run a narrow pilot first, confirm real gains with EXPLAIN and pg_stat_statements, then scale out the approach. Revisit settings and indexes as data and traffic grow.