PostgreSQL Performance Fundamentals in 2026

PostgreSQL performance optimization is one of the highest-leverage skills in backend development. A single poorly optimized query can make a fast application slow; a single well-placed index can make a slow query 100x faster. Understanding the fundamentals — how indexes work, how the query planner makes decisions, and how to interpret EXPLAIN ANALYZE output — enables developers to diagnose and fix performance issues systematically rather than through trial and error.

PostgreSQL's query planner is sophisticated and usually makes good choices when given enough information. The most common performance problems come from missing indexes, misunderstood index usage, N+1 query patterns in application code, and connection pool exhaustion rather than fundamental PostgreSQL configuration issues. Fix application-level problems before tuning PostgreSQL configuration.

Index Strategy: When and What to Index

Index every foreign key column — PostgreSQL does not automatically index foreign keys, unlike some other databases. Index every column used in WHERE clauses with high cardinality (many distinct values). Index columns used in ORDER BY when queries include LIMIT (without an index, PostgreSQL must sort the entire result set before applying LIMIT). Index columns used in JOIN conditions if they're not primary keys.

Partial indexes are underused and highly effective: CREATE INDEX ON posts (created_at) WHERE published = true creates an index covering only published posts, dramatically smaller than a full index and faster for the most common query pattern. Expression indexes enable indexing the result of a function: CREATE INDEX ON users (lower(email)) supports case-insensitive email lookups. Composite indexes are ordered — the leftmost columns must be used for the index to be used.

EXPLAIN ANALYZE: Reading Query Plans

EXPLAIN ANALYZE executes the query and shows the actual execution plan with real timing data. Key metrics to understand: the estimated rows vs actual rows (large discrepancies indicate stale statistics — run ANALYZE to refresh), the sequential scan vs index scan distinction (sequential scans on large tables are the most common performance problem), the cost estimate (arbitrary units — relative cost between nodes matters, absolute values don't), and the actual time in milliseconds per node. Nodes with high actual time relative to estimated time are targets for optimization.

Connection Pooling with PgBouncer

PostgreSQL creates a new OS process for each connection. At high connection counts, the overhead of process management and memory allocation becomes significant. PgBouncer sits between the application and PostgreSQL, maintaining a small pool of real PostgreSQL connections and multiplexing application connections through them. Transaction-mode pooling — where a PostgreSQL connection is allocated for the duration of a transaction and returned to the pool immediately after — is the most efficient mode and supports the highest connection multiplexing ratios. Configure max_client_conn in PgBouncer to match your application's concurrent request count, and pool_size to the maximum connections PostgreSQL can efficiently handle (typically 10-20 per CPU core).

Query Optimization Patterns

The N+1 query problem — where fetching N records results in N+1 database queries — is the most common performance killer in Django and Rails applications. Use select_related for foreign key traversal and prefetch_related for many-to-many and reverse foreign key traversal in Django. Use EXPLAIN ANALYZE to verify that queries are using indexes as expected. For complex reporting queries, CTEs (WITH clauses) improve readability without hurting performance in PostgreSQL 12+ (the optimization fence behavior was removed). Window functions eliminate the need for correlated subqueries in ranking and cumulative sum calculations. Download our Django + PostgreSQL performance-optimized template at proofmatcher.com.

Find Slow Queries with pg_stat_statements

Before optimising anything, find out which queries actually cost the most time. The pg_stat_statements extension records every query shape along with how often it runs and how long it takes in total. Enable it in shared_preload_libraries, create the extension, and sort by total_exec_time. The top of that list is where your effort pays off: a query that takes 5 milliseconds but runs a million times a day often matters more than a 2-second report that runs once. Many managed PostgreSQL providers enable this extension by default.

Composite and Covering Indexes

When queries filter on more than one column, a single composite index usually beats several separate indexes. Column order matters: put columns used with equality conditions first and the column used for ranges or sorting last. An index on (user_id, created_at) serves WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20 perfectly, while an index on (created_at, user_id) does not.

A covering index goes one step further. With the INCLUDE clause you can add extra columns to the index so PostgreSQL can answer a query from the index alone, known as an index-only scan, without visiting the table. Use this for hot queries that read only a few columns.

Build indexes on busy production tables with CREATE INDEX CONCURRENTLY. It takes longer, but it does not block writes while the index is being built.

Keyset Pagination Instead of OFFSET

LIMIT 20 OFFSET 100000 looks innocent, but PostgreSQL still has to read and discard the first hundred thousand rows, so deep pages get slower and slower. Keyset pagination remembers the last row the client saw and asks for rows after it, for example WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching index, page 5,000 is as fast as page one. It is ideal for infinite scroll and APIs; keep OFFSET only for small admin tables where users jump to specific page numbers.

Vacuum, Bloat, and Statistics

PostgreSQL never overwrites rows in place. Updates and deletes leave dead row versions behind, which VACUUM cleans up so the space can be reused. The autovacuum daemon handles this automatically, and you should almost never turn it off. On tables with heavy update traffic, the default settings can fall behind, leading to table and index bloat and slower queries. Monitor dead tuples in pg_stat_user_tables, and lower the autovacuum scale factor for large, busy tables so cleanup runs more often.

The planner also relies on statistics about your data, collected by ANALYZE, which autovacuum runs as well. After large data loads or bulk updates, run ANALYZE manually so the planner does not choose a poor plan based on outdated information.

Memory Settings Worth Knowing

Configuration tuning comes after query and index work, but a few settings matter. shared_buffers is PostgreSQL's own cache, commonly set to around a quarter of the server's memory on a dedicated database server. work_mem is the memory available to each sort or hash operation; too low and large sorts spill to disk, too high and many concurrent queries can exhaust memory, because every operation in every connection can use it. effective_cache_size tells the planner how much memory the operating system is likely using to cache data, which influences whether it prefers index scans. Change one setting at a time and measure the effect.

Application-Level Habits

Many database problems start in application code. Select only the columns you need instead of SELECT *, especially when tables contain large text or JSON fields. Replace N+1 query loops with a join or a single query using WHERE id = ANY($1). Wrap related writes in one transaction, keep transactions short, and never hold one open while waiting on an external API. Set statement timeouts so a runaway query cannot take down the whole application, and cache results that are expensive to compute but rarely change. Combined with the indexing and pooling techniques above, these habits keep PostgreSQL fast as your data grows.

Measure Before and After Every Change

Treat each optimisation as an experiment. Record the query's execution time and plan with EXPLAIN (ANALYZE, BUFFERS) before the change, apply one change, and measure again with realistic data volumes. An index that helps on a table of a thousand rows may be ignored by the planner on a table of ten million, and the reverse is also true.