Performance Tuning

Query optimization fixes individual slow statements. Performance tuning is the broader, server-level counterpart: configuring PostgreSQL itself, managing connections, keeping tables healthy, and monitoring so problems surface before they become outages.

Key configuration settings

A handful of settings in `postgresql.conf` have an outsized effect on performance. See the Configuration page for the full picture — the three below are the ones most worth getting right early.

Setting	What it controls
`shared_buffers`	The amount of memory PostgreSQL dedicates to its own shared cache of data pages. Commonly set to around 25% of system RAM as a starting point.
`work_mem`	Memory available to a single sort or hash operation before it spills to disk. Too low and complex queries spill temp files to disk; too high and many concurrent connections running such operations can exhaust system memory.
`effective_cache_size`	An estimate (not an allocation) of how much memory is available for disk caching overall, including the OS cache. It only influences planner cost estimates — set too low, the planner may avoid indexes it should use.

Inspect current settings

SQL

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

-- Or query them directly:
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

Connection pooling

PostgreSQL is process-based: every client connection gets its own backend process on the server. That model is simple and robust, but it means connections are not free.

Idle connections have a real cost

Because each connection is a full OS process with its own memory overhead, a large number of idle or short-lived connections — the norm for many web applications — can waste significant server resources and, at high counts, degrade performance for everyone. This makes an external connection pooler such as PgBouncer or PgPool a near-essential piece of infrastructure for most production deployments, sitting between the application and PostgreSQL to multiplex many client connections onto a smaller pool of real database connections.

Routine maintenance

PostgreSQL's MVCC storage model leaves behind dead row versions as data is updated and deleted, and query planning quality depends on up-to-date table statistics. Regular VACUUM and ANALYZE maintenance (mostly handled automatically by autovacuum, but worth monitoring) keeps tables compact and the planner well informed. Neglecting it is one of the most common causes of a database that "used to be fast."

Monitoring slow queries

You cannot tune what you cannot see. The `pg_stat_statements` extension — covered on the Extensions page — tracks execution statistics for every distinct query PostgreSQL runs, making it straightforward to find the queries consuming the most total time or run the most often.

Top 5 queries by total execution time

SQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Right-size shared_buffers, work_mem, and effective_cache_size for your hardware and workload, then re-measure.
Put a connection pooler in front of PostgreSQL once your application opens more than a small, fixed number of connections.
Let autovacuum run, and watch for tables it is falling behind on rather than disabling it.
Keep pg_stat_statements enabled in every environment where performance matters — it costs little and answers "what is actually slow" directly.

Measure, don't guess

Every tuning change on this page should be validated with evidence — EXPLAIN ANALYZE for individual queries, `pg_stat_statements` for workload-wide trends, and OS-level monitoring for memory and disk pressure. Changing a setting because "it's supposed to help" without measuring before and after is how production databases end up with configurations nobody can explain.