PostgreSQL Performance Tuning: Server Configuration, Query Optimization, and Connection Pooling

Published: April 27, 2026 · Author: Marcin Szewczyk-Wilgan

This article is a comprehensive guide to PostgreSQL performance tuning – covering memory and buffer configuration, WAL and checkpoint optimization, autovacuum tuning, indexing strategies, query plan analysis, and connection pooling with PgBouncer. It also covers the performance improvements introduced in PostgreSQL 17.

Introduction

PostgreSQL's default configuration is deliberately conservative – designed to start correctly on any hardware, from a Raspberry Pi to a server with 512 GB of RAM. In a production environment this means the server uses only a fraction of its available resources. The shared_buffers parameter set to 128 MB by default on a machine with 32 GB of RAM is like a sports car engine running at idle.

Tuning PostgreSQL is a systematic process: start with memory allocation, move to write configuration (WAL), ensure effective dead tuple cleanup (autovacuum), build appropriate indexes, identify slow queries, and finally deploy connection pooling. Skipping any of these stages creates a bottleneck that negates the gains from all the others.

Memory and buffers

shared_buffers and effective_cache_size

When PostgreSQL reads a row from a table, it does not go directly to disk – it first checks whether the needed data block (an 8 KB page) is already in memory. The shared_buffers parameter defines the size of this shared memory pool where PostgreSQL caches the most frequently accessed blocks. The more data that fits in memory, the less the database reads from disk – and every RAM read is thousands of times faster than an SSD read.

The standard recommendation is 25% of available RAM. On a 16 GB server, set shared_buffers = 4GB. Why not more? PostgreSQL is not the only memory consumer on the server. Linux maintains its own caching layer – the page cache – which automatically holds recently accessed disk data in free RAM. PostgreSQL uses both layers: it checks shared_buffers first, and if the data is not there, the OS may have it in the page cache. Raising shared_buffers above 40% of RAM takes memory away from the page cache and delivers diminishing returns.

The effective_cache_size parameter does not allocate any memory – it is a hint for the query planner. The planner is the PostgreSQL component that, before executing every SQL query, evaluates available indexes and table statistics to choose the fastest execution strategy. effective_cache_size tells the planner how much memory is collectively available for caching (shared_buffers plus the OS page cache). On a dedicated database server, set it to 75% of RAM.

This value drives key planner decisions. A high effective_cache_size causes the planner to prefer Index Scan (reading via an index – fast, but requires the index to fit in memory) over Sequential Scan (reading the full table row by row – slower, but requires no memory for the index). A value set too low makes the planner pessimistically assume that data will not fit in cache, leading to worse execution plans.

work_mem and maintenance_work_mem

The work_mem parameter controls how much memory is allocated for each sort, hash, or join operation within a single query. To understand why this matters, consider a query with an ORDER BY clause on a table with a million rows. PostgreSQL must sort the results – if they fit within the memory allocated by work_mem, the sort happens instantaneously in RAM. If they do not, data must be written to disk temporarily and sorted in chunks (a spill-to-disk operation), which is many times slower.

The default 4 MB forces disk spills for any moderately large sort. However, setting it too high is risky – a single complex query can consume work_mem multiple times (once per Sort or Hash node in the execution plan). A query joining three tables with two sorts will use work_mem five times. With hundreds of concurrent queries it is easy to exhaust all available RAM.

The safe strategy is to set work_mem at a moderate global level (for example 16–64 MB in postgresql.conf) and raise it per-session for specific analytical queries. Before running a heavy report, an administrator can execute SET work_mem = '256MB' – the setting applies only to that session and does not affect other connections.

The maintenance_work_mem parameter controls memory for administrative operations: VACUUM (dead tuple cleanup), CREATE INDEX (index building), and ALTER TABLE ADD FOREIGN KEY. These operations run infrequently but need far more memory than regular queries on large tables. The default 64 MB slows index building on tables with millions of rows. The recommended value is 512 MB–2 GB – since these operations rarely run simultaneously in large numbers, a higher limit is safe.

Interaction with ZFS ARC

On servers with ZFS, an additional caching layer is present – the ARC (Adaptive Replacement Cache). Both PostgreSQL's shared_buffers and ZFS ARC compete for the same physical RAM. The combined total of shared_buffers + zfs_arc_max should not exceed 75% of RAM, in order to leave headroom for work_mem, system processes, and the kernel. A detailed discussion of this interaction can be found in our article Advanced ZFS Optimization for PostgreSQL and MySQL.

WAL and checkpoint tuning

Consider this scenario: an application saves an order to the database. PostgreSQL confirms the write, but a second later the server loses power. Does the order survive? Yes – thanks to the WAL (Write-Ahead Log) mechanism.

The principle is straightforward: before PostgreSQL modifies the main table files on disk, it writes a description of the planned change to a sequential WAL journal. Writing to the WAL is very fast (sequential disk write), and once complete, PostgreSQL confirms the transaction to the client. The actual modification of table files happens later, in the background. If the server crashes, PostgreSQL reads the WAL journal on restart and replays all confirmed but not yet flushed changes – this process is called crash recovery.

A checkpoint is the operation in which the engine transfers all buffered changes (dirty pages – pages modified in memory but not yet written to disk) from memory to the main data files. After a checkpoint completes, PostgreSQL can delete old WAL files because the changes they recorded are already safely on disk.

Too-frequent checkpoints (the default max_wal_size = 1GB) generate intense I/O operations – the server must flush all modified pages to disk at once, causing latency spikes that are visible to users. Too-infrequent checkpoints mean PostgreSQL must replay more changes from WAL after a crash, extending recovery time. For production servers, increase max_wal_size to 4–16 GB – checkpoints will be less frequent and I/O load will be distributed more evenly.

The checkpoint_completion_target parameter (default 0.9) controls how evenly write activity is spread between checkpoints. A value of 0.9 means PostgreSQL aims to spread flushing across 90% of the interval between checkpoints rather than doing it all at the end. This prevents sudden disk load spikes and should remain at its default value.

The WAL buffer (wal_buffers) is the memory where PostgreSQL temporarily accumulates journal entries before writing them to disk. It is auto-set to 1/32 of shared_buffers by default (max 64 MB). For servers with heavy transactional traffic, explicitly set wal_buffers = 64MB to avoid a situation where many concurrent transactions compete over a buffer that is too small.

Autovacuum, MVCC, and the bloat problem

The MVCC mechanism and dead tuples

PostgreSQL implements concurrency control using MVCC (Multi-Version Concurrency Control). To understand why bloat is a problem, you need to know what happens at the disk level during ordinary operations.

When you run UPDATE users SET email = 'new@example.com' WHERE id = 1, PostgreSQL does not overwrite the existing row. Instead it creates a new copy of the row with the updated email address and marks the old version as dead (a dead tuple). The old row physically remains on disk – it is not removed, because other transactions that started before this UPDATE may still need to see the previous version of the data (this is precisely what MVCC transaction isolation guarantees).

The same applies to DELETE – the row is merely marked as dead but physically occupies space in both the table and all associated indexes.

The accumulation of dead tuples is called bloat. The scale of the problem grows with the intensity of updates: a table with a million rows where every row is updated once a day will, after a month without cleanup, occupy space for 31 million rows – 30 million of which are dead copies. Indexes swell proportionally, because every dead row version still has its entries in each index. Queries slow down because PostgreSQL must skip dead tuples during table scans.

Tuning autovacuum

Autovacuum is a background process that automatically cleans dead tuples and updates planner statistics (ANALYZE – it collects data distribution information about tables so the planner can make informed decisions about query execution strategies). Without current statistics, the planner might choose a Sequential Scan on a million-row table despite a perfect index existing – simply because it does not know how many rows a query will return.

The default autovacuum parameters are conservative. Cleanup triggers when the number of dead tuples exceeds a threshold calculated as: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × number of rows in the table. With default values (threshold = 50, scale_factor = 0.2), for a table with 10 million rows this means waiting until 2,000,050 dead tuples accumulate – over two million.

For high-traffic tables (such as user sessions, WooCommerce shopping carts, or activity logs), lowering the threshold is essential. Parameters can be set globally in postgresql.conf or – the better practice – individually per table using ALTER TABLE ... SET:

• autovacuum_vacuum_scale_factor = 0.05 – triggers cleanup at 5% dead tuples instead of 20%. For a 10-million-row table the threshold drops to 500,000.
• autovacuum_vacuum_cost_limit = 1000 (default 200) – allows autovacuum to work faster, consuming more I/O. Autovacuum deliberately throttles itself to avoid slowing user queries – this parameter releases that brake.
• autovacuum_vacuum_cost_delay = 1ms (default 2 ms) – shortens pauses between work batches. After completing a given amount of work (measured by I/O cost), autovacuum pauses for this many milliseconds – a smaller value means faster cleanup.

Bloat diagnostics rely on the pg_stat_user_tables system view. The most important columns are: n_dead_tup (dead tuple count), n_live_tup (live tuple count – the ratio of these two shows bloat severity), last_autovacuum (when cleanup last ran), and last_autoanalyze (when statistics were last updated).

A high n_dead_tup alongside a recent last_autovacuum means autovacuum is running but cannot keep up – more aggressive tuning of the parameters above is needed. A NULL last_autovacuum alongside a large n_dead_tup may indicate that the trigger threshold is too high or that autovacuum has been disabled for that table.

Indexing strategies

B-tree, GIN, GiST, and BRIN

An index in a database serves the same role as a table of contents in a book – instead of reading the whole book (scanning the whole table), you check the contents and jump directly to the right page. PostgreSQL offers several index types, each optimized for a different class of queries.

B-tree – the default index type, created automatically when no other type is specified. Optimal for comparison operations: =, <, >, BETWEEN, ORDER BY. If you are looking up a user by email address (WHERE email = 'jan@example.com') or products in a price range (WHERE price BETWEEN 100 AND 500), B-tree is the right choice. It works well in the vast majority of OLTP scenarios.

GIN (Generalized Inverted Index) – designed for querying structures that contain multiple values in a single field. Its three main use cases are: full-text search (tsvector columns – for example finding articles containing both "PostgreSQL" and "performance"), JSONB column queries with the @> (contains) and ? (key exists) operators, and array searches (ARRAY). GIN is slower to build than B-tree (it must analyze and index multiple values per row) but much faster for searching complex structures.

GiST (Generalized Search Tree) – used for geometric and spatial data (for example with the PostGIS extension – "find all restaurants within 5 km"), time and numeric ranges (tsrange, int4range – "find all reservations overlapping March 15"), and as a full-text search alternative to GIN (faster to build, slower to query).

BRIN (Block Range Index) – an extremely compact index that stores only value range summaries for physical disk blocks. Ideal for tables where data is physically sorted in insertion order – typically logs, metrics, events, or order history sorted chronologically. A BRIN on a billion-row table can occupy just a few megabytes, whereas a B-tree would take gigabytes. The requirement is that data must have a natural physical order (new rows appended at the end). If the table is heavily updated and the physical row order does not match column values, BRIN will be inefficient.

Partial and covering indexes

Partial indexes contain only rows satisfying a specified WHERE condition. Consider an orders table with 10 million rows where 95% of orders have status "completed" and only 5% have status "pending." The application most often searches for pending orders. A full index on the status column would index all 10 million rows, but a partial index CREATE INDEX ON orders (status) WHERE status = 'pending' contains only 500,000 rows – it is 20 times smaller, faster to search, and faster to maintain.

Covering indexes (the INCLUDE clause, available since PostgreSQL 11) enable what is called an index-only scan. Normally, when PostgreSQL finds a row in an index, it must still visit the table (heap) to retrieve the remaining columns needed by the query – an additional random I/O read. A covering index includes extra columns "on the side," allowing the query to retrieve all needed data directly from the index without touching the table. For example: CREATE INDEX ON orders (user_id) INCLUDE (total, created_at) allows the query SELECT total, created_at FROM orders WHERE user_id = 42 to run as an index-only scan. This eliminates random I/O and can speed up queries severalfold.

Diagnosing unused indexes

Every index slows down write operations (INSERT/UPDATE/DELETE) and occupies disk space. The pg_stat_user_indexes view with its idx_scan column lets you identify indexes that are never or rarely used. Indexes with idx_scan = 0 over an extended period should be dropped – this reduces write overhead and speeds up autovacuum.

Query analysis

EXPLAIN ANALYZE and reading execution plans

The EXPLAIN command shows the plan the planner chose for a query – without actually running it. EXPLAIN (ANALYZE, BUFFERS) goes further: it actually executes the query and returns the real execution plan with measured timings and block read counts. This is the primary diagnostic tool – it answers the question "why is this query slow?"

The execution plan is a tree of operations. PostgreSQL reads it bottom-up – the deepest nodes execute first. Key patterns to recognize:

• Seq Scan (Sequential Scan) on a large table – PostgreSQL is reading the entire table row by row instead of using an index. This typically means a missing index. Exception: the planner may deliberately choose a Seq Scan if the query returns more than roughly 10% of table rows – in that case a sequential read is faster than jumping through an index.
• Nested Loop with a large row count – for every row from one table, PostgreSQL performs a lookup in the other. Without an index on the join column, every iteration is a full scan of the inner table. 1,000 rows × 100,000 rows = 100 million comparisons.
• Sort Method: external merge – data did not fit in work_mem and was spilled to disk. Fix: increase work_mem for this session, or add an index that eliminates the sort.
• High Buffers: read with low Buffers: hit – data is being read from disk rather than from memory. This may indicate shared_buffers is too small or the working set is larger than available cache.

A common mistake that prevents index usage is applying a function to an indexed column. The query WHERE EXTRACT(YEAR FROM created_at) = 2026 forces a sequential scan because PostgreSQL cannot match the function's output against a B-tree index. Rewriting it as WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' allows the index to be used – the query now compares column values directly, without any function transformation.

pg_stat_statements – the top-N slowest queries

The pg_stat_statements extension aggregates execution statistics for all SQL queries. Sorting by total_exec_time identifies queries consuming the most cumulative CPU time. Sorting by mean_exec_time reveals the slowest individual queries. The calls column distinguishes a query that is slow-but-rare from one that is fast-but-runs-a-million-times.

The extension must be enabled in postgresql.conf (shared_preload_libraries = 'pg_stat_statements') and activated (CREATE EXTENSION pg_stat_statements). Statistics are reset periodically with pg_stat_statements_reset() to keep the data representative of current load patterns.

Connection pooling: PgBouncer

The connection scalability problem

PostgreSQL uses a process-per-connection model – every application connection creates a separate OS process (called a backend). This is a fundamental difference from, for example, MySQL, which uses threads. A process is heavier than a thread: a single PostgreSQL backend consumes 5–10 MB of memory for its stack, buffers, and metadata.

In practice, a web application with 1,000 concurrent users each holding an open database connection generates 1,000 PostgreSQL processes occupying 5–10 GB of RAM – before the database executes a single query. On top of that, the OS must perform context switching between hundreds of processes, which by itself degrades CPU performance.

Benchmarks show that PostgreSQL with 200 direct connections and a simple 10 ms query achieves approximately 8,000 req/s. The same server with PgBouncer in transaction pooling mode and 20 real database connections handles approximately 50,000 req/s – a more than sixfold increase. The difference comes from PgBouncer maintaining only a small pool of "real" database connections while serving thousands of application connections as lightweight, near-zero-cost network sockets.

PgBouncer modes: transaction vs session

Transaction pooling (recommended for most web applications) – a database connection is assigned to a client only for the duration of a transaction. When the client sends BEGIN, PgBouncer takes a free connection from the pool and assigns it. After COMMIT or ROLLBACK, the connection returns to the pool and is immediately available for another client. This provides the greatest efficiency – thousands of clients can share a few dozen database connections because at any given moment most of them are not in an active transaction (they are waiting for user input, processing application logic, and so on).

Transaction mode limitation: it does not support PostgreSQL features that require state to persist between transactions. This includes prepared statements created via the binary protocol (not via SQL PREPARE), SET commands (for example SET timezone – the setting lasts only until the end of the current transaction), and advisory locks. If your application uses these features, direct database connections are needed alongside PgBouncer.

Session pooling – a connection is assigned to a client for the entire session (until disconnect). Full compatibility with all PostgreSQL features, but minimal sharing benefit – useful primarily for managing reconnects and protecting the database from sudden spikes in new connections.

Production configuration

Key parameters in pgbouncer.ini:

• pool_mode = transaction – pooling mode.
• max_client_conn = 2000 – maximum number of client connections to PgBouncer (can be in the thousands, since PgBouncer connections are lightweight).
• default_pool_size = 50 – number of real connections to PostgreSQL per (user, database) pair. Must be less than max_connections in PostgreSQL.
• reserve_pool_size = 10 – emergency pool for sudden spikes.
• server_lifetime = 3600 – backend connection lifetime in seconds (recycling every hour prevents memory leaks on PostgreSQL backends).

The rule: max_connections in PostgreSQL should be slightly higher (10–20%) than the sum of default_pool_size × number of pools, leaving headroom for direct administrative connections. This relationship aligns with the connection cascade principles described in our article LEMP Stack Architecture.

Performance improvements in PostgreSQL 17

PostgreSQL 17 introduced a series of performance improvements with direct relevance to production tuning:

• Redesigned VACUUM memory management – a new TidStore mechanism replaced the fixed dead-tuple array, reducing memory consumption during cleanup and accelerating the operation on large tables.
• Read Stream API – a new streaming read interface that accelerates sequential scans, particularly on large tables. In benchmarks, read throughput improved by 30–50% compared to PostgreSQL 16.
• Extended parallelism – queries with FULL OUTER JOIN and aggregates can now use parallel execution, which was previously restricted to simpler plans.
• Improved incremental sorting – the planner better exploits partial ordering of data, reducing the number of sort operations.
• Logical replication slot synchronization – logical slots are now automatically synchronized to physical standbys, ensuring data pipeline continuity after failover. The full details of this mechanism are covered in the article PostgreSQL Replication and High Availability.

A migration note: PostgreSQL 17's query planner is more aggressive in applying parallelism. Some queries that previously used Index Scan may switch to Parallel Sequential Scan. After migration, test the plans of critical queries with EXPLAIN ANALYZE and, if needed, adjust parallel_tuple_cost and max_parallel_workers_per_gather.

Conclusion

PostgreSQL performance tuning is a multi-layered process. It begins with correct memory allocation (shared_buffers, work_mem), progresses through write path optimization (WAL, checkpoints), ensures effective dead tuple cleanup (autovacuum), and builds appropriate indexes (B-tree, GIN, BRIN, partial, covering). Query analysis with EXPLAIN ANALYZE and pg_stat_statements identifies real bottlenecks, while PgBouncer in transaction pooling mode solves the connection scalability problem.

Each of these elements affects the others – aggressive autovacuum needs adequate maintenance_work_mem, connection pooling changes load patterns, and a migration to PostgreSQL 17 requires re-validating query plans. A systematic, measurement-driven approach always produces better results than blindly copying parameters from the internet.

FAQ – PostgreSQL tuning