PostgreSQL Performance Tuning: From Slow to Sub-Second

Orientation

This page is a practical map for turning a PostgreSQL instance that feels slow into one that consistently returns queries in under a second. It matters when response times are edging past interactive thresholds, when a production workload is consistently missing its latency SLO, or when you’ve hit a point where adding hardware no longer reduces wait time. The focus is on levers you can pull today with real commands and configuration, not on architectural overhauls or vendor‑specific tooling.

Find the slow spot

Run a quick diagnostic

The fastest way to see where time is being spent is to look at active sessions and their current statements. The built‑in view pg_stat_activity provides a snapshot without requiring extra extensions.

psql -U postgres -c "
SELECT pid,
       state,
       query,
       wait_event_type,
       wait_event
FROM   pg_stat_activity
WHERE  state = 'active'
ORDER  BY query_start DESC
LIMIT  10;"

Typical output patterns: a session stuck in Lock, one waiting on Client, another in Extension (e.g., pg_stat_statements), or a query that has been running for minutes without a visible wait event—often a sign of a long‑running DML operation or a missing index.

If you need a historical view, pg_stat_activity retains data only for the lifetime of the backend, but pg_log or third‑party log parsers can fill the gap. For a one‑off deep dive, EXPLAIN ANALYZE on a representative query will surface the actual planner decisions and execution times.

Pinpoint expensive queries

psql -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';"

Study the Plan node types: Seq Scan on a large table, Hash Join spilling to disk, or Sort exceeding work_mem. The Execution Time line at the bottom is the wall‑clock time the query actually took. If Plan Rows is far fewer than Actual Rows, the planner misestimated cardinality—often fixable with statistics or a better index.

Reduce query latency

Add covering indexes

A common pattern in PostgreSQL is to create an index that satisfies both the WHERE clause and the projected columns, avoiding a table fetch.

CREATE INDEX idx_orders_created_covering
    ON orders (created_at)
    INCLUDE (id, total, status);

The INCLUDE clause adds non‑key columns to the index leaf pages. The query planner can then satisfy the query entirely from the index without touching the heap. This works only when every column the query asks for is in the index — the key columns plus whatever INCLUDE carries. So it pays off for narrow, known projections and specifically NOT for SELECT *, which requests every column and therefore forces a heap fetch however much the index covers (see below). The case that wins is filtering on a high‑cardinality column while returning only the included columns.

If the query pattern involves range predicates and sorting, a composite index with the sort column last can eliminate an explicit Sort node:

CREATE INDEX idx_orders_created_status
    ON orders (created_at DESC, status);

Avoid SELECT * in hot paths

Even with a covering index, SELECT * forces the planner to fetch every column from the heap (or the index leaf, if fully covering). Rewrite queries to list only the columns needed by the application layer. This reduces I/O, network payload, and the chance of hitting wide‑row bloat.

-- Before
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- After
SELECT id, total, status FROM orders WHERE created_at > '2024-01-01';

Tune planner statistics for skewed data

When a column has a highly skewed distribution (e.g., status = 'pending' is 95% of rows), the planner may choose a sequential scan even with an index. Update statistics on that column:

psql -c "ANALYZE orders (status);"

Or, for a whole table:

psql -c "ANALYZE orders;"

After ANALYZE, re‑run EXPLAIN ANALYZE to verify the planner’s choice shifted.

Tune server memory

work_mem – per‑operation, not per‑query

The configuration key work_mem controls the amount of memory available for sort, hash, and aggregate operations *per* operation. If a query performs multiple sorts or builds multiple hash tables, each consumes work_mem independently.

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

Raising work_mem reduces the frequency of disk‑based spills, but setting it too high across many concurrent queries can cause the server to exceed its total RAM and trigger the OOM killer. A pragmatic approach is to raise it for critical, single‑query workloads and keep the default (-1, which defaults to 1/4 of maintenance_work_mem on some builds) for ad‑hoc sessions.

shared_buffers – the buffer pool

shared_buffers is the amount of memory PostgreSQL uses for its shared buffer cache. Increasing it reduces the number of reads that hit the OS page cache or disk.

ALTER SYSTEM SET shared_buffers = '256MB';
SELECT pg_reload_conf();

A full restart is required for the change to take effect; pg_reload_conf() only reloads parameters that support it. The PostgreSQL documentation recommends setting shared_buffers to about 25% of available system RAM on dedicated database hosts. On shared hosts, leave headroom for the OS and other processes.

maintenance_work_mem – VACUUM and index builds

maintenance_work_mem governs the memory used by VACUUM, CREATE INDEX, ALTER TABLE … ADD CONSTRAINT, and other maintenance operations. It is separate from work_mem and does not count toward per‑query memory limits.

ALTER SYSTEM SET maintenance_work_mem = '512MB';
SELECT pg_reload_conf();

Increasing this value speeds up schema changes and vacuum cycles, which in turn keeps bloat in check and prevents the silent degradation that comes from an under‑vacuumed table.

effective_cache_size – planner hint, not a hard limit

effective_cache_size tells the planner how much memory the OS and PostgreSQL buffer cache are likely to hold. It does not reserve that memory; it is purely a hint for cost estimation.

ALTER SYSTEM SET effective_cache_size = '1GB';
SELECT pg_reload_conf();

If this value is set far above reality, the planner may choose plans that assume too much caching and fail when the cache is cold. If set too low, it may avoid plans that would actually perform well. Treat it as an approximate signal, not a constraint.

Sharp edge: work_mem interaction with parallel queries

When a query runs with parallelism (parallel_query), each worker process receives its own work_mem allocation. A query with parallel_workers = 4 and work_mem = '64MB' will attempt roughly 4 × 64MB = 256MB of memory for the duration of the query. If max_parallel_workers_per_gather is high and many such queries run concurrently, memory pressure can build quickly. Monitor with:

psql -c "SELECT * FROM pg_stat_activity WHERE query LIKE '%Parallel%';"

Manage connections

max_connections – the hard ceiling

The configuration key max_connections caps the number of simultaneous client connections. The default is typically 100, but the actual limit is also constrained by the OS file‑descriptor limit (ulimit -n) and the kernel’s somaxconn.

ALTER SYSTEM SET max_connections = 200;
SELECT pg_reload_conf();

After increasing max_connections, adjust the OS limit:

ulimit -n 2048

* soft nofile 2048
* hard nofile 2048

If max_connections is set too high for the available OS resources, PostgreSQL may fail to start or accept connections, returning:

FATAL: too many connections

or, at the OS level, Connection refused / Too many open files.

Connection pooling – avoid bypassing the pool

In production, direct connections beyond the pool size increase context‑switch overhead and can exhaust max_connections. Use a middleware pooler such as pgbouncer. A typical deployment:

# Install via package manager (real command)
apt-get install -y pgbouncer

# Minimal config example
cat > /etc/pgbouncer/pgbouncer.ini <<'EOF'
[database]
pool_mode = transaction
default_pool_size = 50
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

[pgbouncer]
listen_addr = *
listen_port = 6543

Point application DSNs to localhost:6543 instead of the PostgreSQL port. pgbouncer multiplexes connections at the transaction level, keeping the number of actual PostgreSQL backends well below max_connections. If pool_mode = session, each client holds a backend for the entire session—use transaction mode for most web workloads.

Sharp edge: max_connections vs. prepared statements

Each prepared statement consumes a small amount of memory in the backend’s pg_prepared_statements cache. With many connections and many prepared statements, the cumulative memory can become non‑trivial. If you observe FATAL: too many connections despite low active query count, check:

psql -c "SHOW prepared_transactions;"
psql -c "SELECT count(*) FROM pg_prepared_xacts;"

A spike in prepared transactions (e.g., from an ORM that leaves transactions open) can effectively reduce the usable max_connections.

Schedule maintenance without the silent failures

VACUUM and ANALYZE

PostgreSQL does not reclaim disk space automatically; VACUUM marks dead tuples for reuse and updates statistics. VACUUM ANALYZE does both in one pass.

psql -c "VACUUM (ANALYZE, verbose) orders;"

The verbose flag outputs per‑table counts of dead tuples and pages reclaimed. Run this on a staging copy first to observe the output format.

Autovacuum defaults

PostgreSQL ships with autovacuum enabled, but default thresholds (threshold and scale) may be mismatched to your data growth pattern. For a small table that sees frequent updates, the default “one dead tuple per 50 tuples” may trigger vacuum too often. For a large table with rare updates, the same threshold may let bloat accumulate for weeks.

Check current settings:

psql -c "SELECT * FROM pg_autovacuum_settings WHERE relname = 'orders';"

Typical adjustments:

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';
ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.02';
SELECT pg_reload_conf();

Sharp edge: autovacuum and notify/listen

If your application uses NOTIFY/LISTEN heavily, autovacuum can be delayed because PostgreSQL treats NOTIFY as a potential source of transaction ID wraparound risk. In rare cases, autovacuum may skip a table that has seen many NOTIFY events but few actual row changes. Monitor pg_stat_user_tables for n_dead_tup and n_live_tup:

psql -c "SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

Sharp edge: VACUUM skipping tables with row_security

On tables with row‑level security (RLS) enabled, VACUUM may report “skipping table” if the current user lacks permission to see all rows. Ensure the vacuuming role has SELECT on the table, or use a superuser role for maintenance jobs.

Sharp edges you only learn after it breaks in production

work_mem vs. maintenance_work_mem

A common confusion is treating work_mem as a global memory cap. It is per‑operation. A single complex query with multiple sorts and hashes can consume work_mem × (number of operations). If you set work_mem = '1GB' and run ten concurrent such queries, you may instantly exceed server RAM. Always pair work_mem increases with monitoring of ps -eo pid,pmem,comm | grep postgres.

ALTER SYSTEM vs. editing postgresql.conf directly

ALTER SYSTEM SET writes the value to the pg_settings catalog and flags it as a system‑level override. The value persists across restarts. Editing postgresql.conf directly and restarting achieves the same result, but ALTER SYSTEM is safer because it does not require a text editor on the config file and respects the order of precedence (system overrides user‑level settings). After ALTER SYSTEM, use SELECT pg_reload_conf() to apply the change without a restart where supported.

effective_io_concurrency

This knob tells the planner how many concurrent IO operations the subsystem can handle. The default is 0, which means the planner assumes a single‑disk, seek‑dominated layout. On modern SSDs or RAID arrays, raising it can enable more aggressive parallelism choices:

ALTER SYSTEM SET effective_io_concurrency = 200;
SELECT pg_reload_conf();

Setting it too high can cause the planner to underestimate seek costs on slower storage. On pure‑SSD workloads, values between 100 and 200 are common; on spinning disks, keep it near 0 or 1.

max_parallel_workers_per_gather

Increasing parallelism is not free. Each parallel worker consumes work_mem and adds CPU overhead. A query that runs fast serially may actually slow down with parallel_workers = 4 if the overhead of coordinating workers exceeds the parallelism benefit. Use EXPLAIN ANALYZE with Parallel Aware to see the actual worker count and execution time:

psql -c "EXPLAIN (costs off) SELECT * FROM large_join WHERE id = ANY(ARRAY[1,2,3,4,5]);"

If the plan shows Parallel Seq Scan but the Execution Time is higher than the serial equivalent, reduce max_parallel_workers_per_gather:

ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
SELECT pg_reload_conf();

Next steps

Pick one bottleneck from the diagnostics section and apply the corresponding lever: add a covering index, adjust work_mem for a recurring heavy query, or raise max_connections alongside the OS file‑descriptor limit. Re‑run EXPLAIN ANALYZE after each change to verify the plan shifted as expected. Iterate until query latency consistently falls below your SLO threshold, then move to the next area.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.