PostgreSQL Performance Tuning for Small Teams: The 80/20 Guide

The Uncomfortable Truth About Postgres Tuning

The majority of production Postgres installations run on default settings that were written to be safe on machines with 256 MB of RAM. Those defaults have not meaningfully changed in decades. Ship a modern application on a $40-a-month cloud instance and you are leaving 70–80% of the database’s potential performance on the table from day one, before writing a single query.

The flip side is equally important: most applications never need advanced tuning. If your dataset fits in memory, your queries use indexes, and you are not hammering the server with thousands of concurrent connections, Postgres will handle more traffic than you expect. The goal of this guide is not to turn every engineer into a DBA. The goal is to make sure you are not leaving easy wins behind.

The Three Memory Settings That Actually Matter

Postgres exposes hundreds of configuration knobs. Three of them produce the vast majority of the gains available through configuration alone. Get these right first; revisit everything else if you still have a problem.

shared_buffers

shared_buffers is the amount of memory Postgres uses for its own shared cache of data pages. The default is 128MB, which is absurdly low for any modern server. A widely accepted starting point is 25% of total system RAM. On a server with 16 GB of RAM, set this to 4GB.

Postgres is not the only consumer of memory on the host. The operating system’s page cache also holds database file pages and is, in many workloads, more efficient at eviction than Postgres’s own buffer pool. Setting shared_buffers too high steals RAM from the OS page cache and can make performance worse. Stay at or below 40% of system RAM.

work_mem

work_mem controls how much memory each sort or hash operation can use before spilling to disk. The default is 4MB. When a sort spills to disk, query execution time can jump by an order of magnitude. You will see this in EXPLAIN ANALYZE output as “Batches: N” on a Hash node or “Sort Method: external merge Disk” on a Sort node — both are signs that work_mem is too low.

The catch: this value is per sort operation, and a single complex query can run many sort operations in parallel. A session can also open multiple parallel workers. The effective memory ceiling is roughly work_mem × max_connections × average_operations_per_query. On a shared server, setting work_mem to 64MB with 200 connections could consume 12 GB of RAM at peak. Start at 16MB to 32MB and raise it only for roles or sessions that need it, using SET work_mem = '256MB' at the session level for known heavy analytics queries.

effective_cache_size

effective_cache_size does not allocate memory. It is a hint to the query planner about how much memory the OS page cache plus shared_buffers can reasonably provide. A higher value tells the planner that index scans are cheaper because the required pages are likely already in cache. Set this to roughly 75% of total system RAM. On a 16 GB server that means 12GB.

Under-setting this value causes the planner to prefer sequential scans over index scans in situations where the index would actually be faster, because it assumes pages will be fetched from disk.

Reading EXPLAIN ANALYZE Without a Degree in Query Planning

EXPLAIN ANALYZE is the most powerful diagnostic tool available for individual queries. The output looks intimidating, but there are four things to look for that cover the majority of problems.

Actual vs. Estimated Rows

Every node in the plan shows “rows=N” (the planner’s estimate) and “actual rows=N” (what happened at runtime). When the estimate is off by a factor of ten or more, the planner likely made a poor plan choice based on stale statistics. Run ANALYZE tablename to refresh statistics. If the mis-estimation persists, consider increasing default_statistics_target for the specific column causing the problem using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500.

Seq Scan vs. Index Scan

A sequential scan on a large table inside a loop is almost always a problem. Look for “Seq Scan” nodes with high loop counts or high actual row counts. An index scan on the same table with similar selectivity will typically reduce execution time by one to three orders of magnitude.

Sort Method: External Merge Disk

As mentioned above, this phrase means the sort spilled to disk. Raise work_mem for the session or query, or add an index that provides pre-sorted output and eliminates the sort step entirely.

Nested Loop on Large Row Counts

Nested Loop joins are efficient when the inner side is small. When both sides are large, a Hash Join or Merge Join is faster. The planner usually makes the right call, but stale statistics can cause it to underestimate row counts and choose a Nested Loop incorrectly. The fix is fresh statistics and, if needed, explicit join hints via the enable_nestloop = off session parameter while you investigate.

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) rather than plain EXPLAIN ANALYZE. The BUFFERS option shows how many shared blocks were hit from cache versus fetched from disk, which immediately tells you whether the query is I/O bound.

Index Strategy: Choosing the Right Tool

Postgres supports multiple index types. Using the wrong type is not just a missed optimization — in some cases it actively hurts performance by increasing write overhead without helping reads.

B-tree: The Default That Covers Most Cases

B-tree indexes support equality, range, and sort operations on scalar values. If you are not sure what index type to use, start with a B-tree. They work on text, numbers, timestamps, UUIDs, and most other common column types. They support =, <, >, BETWEEN, LIKE 'prefix%', and ORDER BY.

GIN: Full-Text Search and Arrays

GIN (Generalized Inverted Index) indexes the individual elements of a composite value — array elements, JSONB keys, or lexemes in a tsvector. Use GIN when querying with @>, &&, or @@ operators. GIN indexes are larger and slower to build than B-tree, but dramatically faster for containment queries on array or JSONB columns.

GiST: Geometric and Range Types

GiST supports geometric types (point, polygon, circle), range types (daterange, tsrange), and is the backing index for PostGIS. Use GiST when the data type does not map to a simple scalar comparison. For geographic proximity queries, GiST with PostGIS is the standard approach.

Partial Indexes: Index Only What You Query

A partial index includes only rows that satisfy a WHERE clause. If 95% of your queries filter on status = 'active' and only 3% of rows are active, a partial index eliminates 97% of the index size:

CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active';

Partial indexes are often overlooked by teams that reach for covering indexes first. They should be evaluated whenever a column has highly skewed value distribution.

Covering Indexes: Eliminate the Heap Fetch

When Postgres uses an index to find matching rows and then fetches additional columns from the table heap, that second read is called a heap fetch. If the query is executed frequently and the additional columns can be included in the index using INCLUDE, the heap fetch is eliminated entirely:

CREATE INDEX idx_users_email ON users (email) INCLUDE (id, name);

Covering indexes are most valuable on frequently executed, high-volume queries where the index selectivity is high and the INCLUDE columns are narrow.

The N+1 Query Problem: Silent Killer of API Performance

The N+1 problem occurs when code fetches a list of N records and then issues one additional query per record to fetch related data. Fetching 100 orders and then querying for the customer details of each order produces 101 queries instead of 2. Each query individually takes 1–5 ms and looks harmless in isolation. Together they consume 100–500 ms on every API request.

How to Detect It

The most reliable detection method is pg_stat_statements. Enable it by adding pg_stat_statements to shared_preload_libraries and running CREATE EXTENSION pg_stat_statements. Then query:

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

A query with tens of thousands of calls and a low per-call time but high total time is a classic N+1 signature. The fix is almost always a JOIN or a batched IN (...) query combined with in-memory association of the results.

Application-level logging is the other detection vector. Frameworks like Rails, Django, and SQLAlchemy can be configured to log every SQL statement. Sort by frequency. Any parameterized query that runs dozens of times per request is a candidate for batching.

Connection Pooling with PgBouncer

Each Postgres connection consumes approximately 5–10 MB of server memory and requires a backend process. An application that opens 200 persistent connections has already consumed 1–2 GB of RAM before executing a single query. When the application scales horizontally, connection count scales with it, and Postgres eventually spends more time managing connections than serving queries.

PgBouncer sits between the application and Postgres and multiplexes many application connections onto a smaller pool of actual Postgres connections. In transaction pooling mode — the most aggressive setting — a Postgres connection is only held for the duration of a single transaction, then returned to the pool. A PgBouncer pool of 50 connections can comfortably serve thousands of application connections.

PgBouncer Configuration That Works

• pool_mode = transaction — Best efficiency. Incompatible with session-level features like advisory locks and prepared statements in some configurations.
• max_client_conn — Set to expected peak application connections plus headroom. Start at 1000.
• default_pool_size — Actual Postgres connections per database/user pair. A common formula is (2 × CPU cores) + effective_spindle_count. On a 4-core server with SSD storage, start at 10–20.
• server_idle_timeout — How long an idle server connection lives before being closed. 600 seconds is a reasonable default.

Teams running on managed databases like RDS or Cloud SQL that do not allow PgBouncer at the infrastructure level should use the managed connection poolers those platforms provide (RDS Proxy, Cloud SQL Auth Proxy) rather than skipping pooling altogether.

VACUUM and Autovacuum: The Maintenance Work You Cannot Ignore

Postgres uses MVCC (Multi-Version Concurrency Control) to handle concurrent transactions. Every UPDATE and DELETE creates a new row version rather than modifying in place. Dead row versions accumulate over time and must be cleaned up by VACUUM. If dead tuples are not reclaimed, table bloat increases, queries slow down as they scan more dead rows, and eventually the transaction ID counter wraps around in a catastrophic event called transaction ID wraparound.

Autovacuum is enabled by default and handles this automatically — but its default configuration is tuned for light workloads. High-write tables require more aggressive settings.

Key Autovacuum Settings

• autovacuum_vacuum_scale_factor — Fraction of table size that must be dead before vacuum triggers. Default is 0.2 (20%). On large tables, 20% of dead tuples can be millions of rows. Drop this to 0.01 or 0.02 for high-write tables using per-table storage parameters.
• autovacuum_vacuum_threshold — Minimum dead rows before vacuum triggers, regardless of scale factor. Default is 50. Raise to 1000 on large, stable tables.
• autovacuum_max_workers — Number of concurrent autovacuum workers. Default is 3. Raise to 5–6 on servers with many tables or high write throughput.
• autovacuum_vacuum_cost_delay — Throttle applied to autovacuum to reduce I/O impact. Default is 2ms. On SSD storage, set to 0 or 1ms to allow autovacuum to run at full speed.

Per-table overrides are cleaner than changing global settings when only a few tables are problematic:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);

Monitoring Bloat

Query pg_stat_user_tables to see n_dead_tup per table. A persistent count of millions of dead tuples on an active table means autovacuum is not keeping up. Check pg_stat_activity for long-running transactions — a transaction open for hours blocks vacuum from reclaiming any rows modified after it started.

Finding Slow Queries with pg_stat_statements

pg_stat_statements is the first extension to enable on any production Postgres instance. It tracks cumulative execution statistics for every distinct query shape, normalized by parameter substitution. The most useful query for a quick audit:

SELECT query, calls, total_exec_time, mean_exec_time, stddev_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Sort by total_exec_time descending to find queries that consume the most aggregate database time — not just the slowest individual queries. A query that takes 50 ms but runs 100,000 times per hour costs more than a query that takes 5 seconds but runs once a day.

High stddev_exec_time relative to mean_exec_time signals inconsistency — the query is sometimes fast and sometimes slow, often caused by cache misses, lock waits, or checkpoint interference.

Reset statistics before a load test with SELECT pg_stat_statements_reset() to get a clean baseline.

Before/After: Real Numbers from Configuration Changes

The following examples are representative of gains seen when applying the settings in this guide to previously untuned instances.

When to Add Read Replicas

Read replicas are frequently proposed as a scaling solution before the primary instance has been properly tuned. Adding a replica while leaving shared_buffers at the default and tolerating N+1 queries is an expensive way to scale bad practices horizontally.

Consider read replicas when:

• The primary is CPU-bound from query execution, not from a small number of poorly written queries that could be optimized.
• Reporting and analytics queries are causing lock contention or checkpoint pressure on the primary during business hours.
• The read/write ratio is genuinely skewed — 90%+ reads — and the primary has already been tuned.
• High availability requirements mandate a standby regardless of load.

Teams running on RDS or Cloud SQL can enable a read replica in minutes. The temptation to do so before exhausting single-instance tuning is understandable, but it adds operational complexity — connection routing, replication lag awareness, and potential consistency issues — that most small teams do not need.

The Good-Enough Postgres: When to Stop Tuning

A Postgres instance with properly configured memory settings, current table statistics, well-chosen indexes, PgBouncer for connection management, and healthy autovacuum will handle the load of most production web applications without any further intervention. The sweet spot for small teams is investing one focused day in the changes above and then stepping back.

Signs that you have genuinely exhausted single-instance optimization and need to go further:

• CPU utilization is consistently above 70% and the top queries in pg_stat_statements are already optimized.
• Working set exceeds available memory on the largest feasible instance size.
• Write throughput exceeds what a single primary can commit — typically hundreds of thousands of writes per second on hardware, not a concern for most web applications.
• Table sizes are in the hundreds of billions of rows and partition pruning would meaningfully reduce query scope.

If none of those conditions apply, the next performance gain is more likely to come from application-layer caching (Redis, Memcached) or a CDN than from deeper database tuning. Knowing when the database is not the bottleneck is as valuable as knowing how to tune it.

A Practical Starting Checklist

• Set shared_buffers to 25% of RAM, effective_cache_size to 75% of RAM, and work_mem to a conservative 16–32 MB. Restart Postgres.
• Enable pg_stat_statements and run it for 24–48 hours under production traffic before drawing conclusions.
• Query pg_stat_statements sorted by total_exec_time. Investigate the top 10 entries.
• Run EXPLAIN (ANALYZE, BUFFERS) on each of those queries. Look for sequential scans on large tables, disk-spilling sorts, and row count mis-estimations.
• Check pg_stat_user_tables for n_dead_tup. Tables with persistent high dead tuple counts need autovacuum tuning or manual VACUUM.
• Count open connections with SELECT count(*) FROM pg_stat_activity. If it regularly exceeds 100, evaluate PgBouncer.
• Add missing indexes. Prefer partial and covering indexes where query patterns are well understood.
• Only after all of the above: consider whether a read replica or vertical scaling is warranted.

Key Takeaways

• Three configuration changes — shared_buffers, work_mem, and effective_cache_size — produce more gain than almost everything else combined on an out-of-the-box installation.
• pg_stat_statements is the correct starting point for query investigation. Never optimize by intuition alone.
• Index type matters: B-tree for scalars, GIN for arrays and JSONB, GiST for geometric and range types. Partial and covering indexes are underused and highly effective.
• The N+1 query problem is detectable by looking at query call counts in pg_stat_statements, not just per-query duration.
• PgBouncer in transaction pooling mode resolves connection exhaustion problems that no amount of per-query tuning can fix.
• Autovacuum’s default configuration is conservative. High-write tables need per-table overrides.
• Read replicas and partitioning solve real problems — but those problems are not what is slowing down most small-team applications.

Frequently Asked Questions

How do I know if my Postgres instance is properly tuned?

A tuned instance has correct memory settings, no long-running autovacuum backlogs, connection counts well within server limits (or managed by a pooler), and no queries in pg_stat_statements with obvious optimization opportunities — meaning no sequential scans on large tables that should have indexes, and no disk-spilling sorts on frequently executed queries.

Is PgBouncer necessary if I am on a managed database like RDS?

RDS supports RDS Proxy as a managed equivalent. For most small teams on RDS, RDS Proxy is the simpler option because it handles failover awareness automatically. The tradeoff is cost and latency overhead. PgBouncer run on the application server is effectively free and adds sub-millisecond latency.

How often should I run ANALYZE?

Autovacuum runs ANALYZE automatically based on the same cost thresholds as VACUUM. Manual ANALYZE is useful after bulk data loads (imports, large deletes, mass updates) where the automatic trigger may not have fired yet. Run it explicitly after any operation that changes more than 10% of a table’s rows.

When should a small team hire a DBA?

When the performance problems consistently require deeper investigation than the techniques in this guide can address, when data integrity requirements necessitate replication and failover architectures, or when database infrastructure has become a material risk to the business. For most teams below 50 engineers, a PostgreSQL performance tuning small teams guide like this one — applied consistently — defers that need considerably.