PostgreSQL Performance Tuning Guide
On this page
- "The database is slow." Where do you start?
- Step 1: Find the costliest queries with pg_stat_statements
- Step 2: Size shared_buffers, work_mem and effective_cache_size
- Step 3: Connection limits and pooling
- Step 4: Tune checkpoints and max_wal_size
- Step 5: random_page_cost on SSDs
- A measured tuning loop
- Check yourself
"The database is slow." Where do you start?
PostgreSQL's defaults are deliberately small so it can start on a laptop or a tiny container: 128MB of shared_buffers, 4MB of work_mem. On a dedicated 32GB server they leave a lot unused, so changing settings is part of the job.
But a server that's "slow" is usually slow because of a few specific queries, not because of a setting. So the order matters: find what's slow first, then tune. Otherwise you'll spend a day adjusting memory for a problem a single index would have fixed.
Step 1: Find the costliest queries with pg_stat_statements
The pg_stat_statements extension records every query shape the server runs: how often, how long in total, and how much it read. It needs to be loaded at startup, so add it to postgresql.conf and restart once:
shared_preload_libraries = 'pg_stat_statements'Then enable it in your database and ask for the top offenders:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT left(query, 60) AS query,
calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Sort by total time, not mean time. A query that takes 5 ms but runs 2 million times a day costs far more than a 3-second report that runs twice. The top three or four rows of that list are usually most of your server's work.
For each one, run EXPLAIN (ANALYZE, BUFFERS) and fix the plan as on Day 22: add an index, refresh statistics, rewrite the query. That often solves the "slow database" outright.
Two settings complete the picture. log_min_duration_statement = 250 logs any statement slower than 250 ms, which catches rare outliers that pg_stat_statements averages away. log_checkpoints (on by default since PostgreSQL 15) shows how often checkpoints happen. More on that below.
Step 2: Size shared_buffers, work_mem and effective_cache_size
Once the worst queries are fixed, check the memory settings. Here's a reasonable starting point for a dedicated server with 32GB of RAM:
| Setting | Starting point | Why |
|---|---|---|
shared_buffers | 8GB (about 25% of RAM) | PostgreSQL's own page cache. Needs a restart. Much more than 40% tends to compete with the OS cache. |
effective_cache_size | 24GB (about 75% of RAM) | Allocates nothing. Tells the planner how much data is likely cached, which makes index scans look cheaper. |
work_mem | 16MB | Per sort or hash, per query, per connection. Keep it modest globally. |
maintenance_work_mem | 1GB | Used by VACUUM and CREATE INDEX. Few run at once, so it can be large. |
The work_mem row is where people get hurt. Set it to 256MB globally, and 200 connections each running a query with three sorts can ask for 150GB. The server runs out of memory and the operating system kills PostgreSQL. Keep the global value moderate and raise it only where it's needed:
ALTER ROLE reporting SET work_mem = '256MB'; -- every session of this role
SET work_mem = '256MB'; -- or just this sessionHow do you know work_mem is too low? Look for Sort Method: external merge Disk: ... in EXPLAIN ANALYZE output, or set log_temp_files = 0 to log every query that spills to disk.
Step 3: Connection limits and pooling
Each connection is a separate process with its own memory (Day 2). A thousand mostly idle connections still cost RAM and scheduling time. If the application wants more connections than the server handles comfortably, put a connection pooler such as PgBouncer in front, rather than raising max_connections into the thousands. Set max_connections to cover the pool plus some room for administrators.
Also set idle_in_transaction_session_timeout, for example to 5 minutes. A session stuck idle inside a transaction holds locks and blocks vacuum cleanup (Day 24).
Step 4: Tune checkpoints and max_wal_size
A checkpoint writes every changed page from memory to disk (Day 2). Frequent checkpoints mean constant write I/O. Each checkpoint also causes a burst of extra WAL, because the first change to each page afterwards logs the whole page. Checkpoints are triggered by time (checkpoint_timeout, default 5 minutes) or by WAL volume (max_wal_size, default 1GB).
On a busy server, 1GB of WAL can fill in a minute, so checkpoints happen far more often than intended. The server log says so with "checkpoints are occurring too frequently". A common adjustment:
checkpoint_timeout = 15min
max_wal_size = 8GB
checkpoint_completion_target = 0.9 # the default: spread writes over 90% of the intervalThe cost is crash recovery time, because more WAL must be replayed after a crash. For most systems, a few extra minutes of recovery is a fair trade for steady I/O.
Step 5: random_page_cost on SSDs
Two settings matter on modern storage:
random_page_costdefaults to 4, which assumes a spinning disk where random reads are slow. On SSDs and NVMe, 1.1 to 1.5 is more realistic, and the planner will choose index scans more often.effective_io_concurrencycontrols how many reads PostgreSQL issues ahead of time during bitmap scans. PostgreSQL 18 raised the default from 1 to 16 and added asynchronous I/O (io_method), so on 18 you may not need to touch it.
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();A measured tuning loop
Tuning by copying a blog post's settings means you never know what helped. Use a loop instead:
- Record a baseline: the top queries in pg_stat_statements, checkpoint frequency, CPU, I/O and memory.
- Pick the single most likely bottleneck.
- Change one thing: one setting, one index or one query.
- Measure the same workload again and compare with the baseline.
- Keep the change if it helped, revert it if not, and write down what you did either way.
SELECT pg_stat_statements_reset(); clears the counters, so each measurement starts clean.
Check yourself
Raise shared_buffers to about 16GB, which needs a restart. Keep work_mem modest globally, because it's per operation per connection and could exhaust memory. Raise it per role or session for known heavy reports.
Yes. It runs so often that it dominates total load. Shaving 2 ms off it, for example with a covering index, can save more server time than fixing any slow report.
Raise max_wal_size so checkpoints are triggered by checkpoint_timeout rather than WAL volume, and keep checkpoint_completion_target near 0.9 so writes are spread out.
It doesn't allocate anything. It tells the planner roughly how much memory PostgreSQL and the operating system can use for caching data, so it can judge how likely index pages are to be in memory. Setting it to about half to three quarters of RAM is a common starting point.
The default of 4 assumes random reads are much slower than sequential ones, which was true for spinning disks. On SSDs the gap is small, so values around 1.1 to 1.5 make the planner more willing to use index scans when they really are faster.