PostgreSQL Performance Tuning

I once handled a ticket where the order list took eight seconds to open. The product team's first reaction was, "Do we need a larger database instance?" I glanced at the RDS metrics: CPU was at 30%, half the memory was still free, and IOPS was nowhere near saturated. Scale up? This 8-core, 32G database was not even fully utilized. The real cause was a query performing a Seq Scan on a 20-million-row orders table, plus a badly implemented N+1 at the application layer. Of those eight seconds, 7.5 were spent spinning inside the database.

After years of tuning PG, one impression stands out: 80% of performance problems originate in queries, indexes, or configuration rather than hardware. Adding CPU and memory or switching to SSD is the last step, not the first. This article presents a systematic "locate first, then optimize" method, covering everything from slow-query logs and EXPLAIN to shared_buffers and VACUUM.

I. Do Not Rush to Add Hardware: Identify the Real Culprit First

When a database is slow, the most common instinct is to add hardware. That instinct is often wrong for three reasons:

1. Hardware masks problems instead of solving them. An unindexed query that takes eight seconds on a 4-core machine might take six seconds on a 16-core machine. That is an improvement, but a Seq Scan remains a Seq Scan, and the problem returns when the data volume doubles. The money has been spent, but the problem was merely postponed.
2. Scaling cloud databases is especially expensive. Upgrading RDS or Aurora from 4 cores to 8 can double the monthly bill. A single index might have solved the same problem.
3. The real bottleneck is often not the hardware. The cases I have handled break down roughly as follows: missing or unused indexes (40%), poorly written queries (25%), untuned configuration (10%), exhausted connections (10%), stale statistics or inadequate VACUUM (5%), and genuinely insufficient hardware (10%). Insufficient hardware is the final conclusion only after every preceding cause has been ruled out.

The first question should therefore always be: Where is it slow? Spending money on a larger machine without answering that question is like prescribing medicine without making a diagnosis.

II. Diagnostic Toolkit: pg_stat_statements, EXPLAIN ANALYZE, and Lock Waits

1. pg_stat_statements: Find the Slowest Queries

This is the first stop in PG performance tuning. It is an extension and is disabled by default, so first add the following to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

After restarting, create the extension and run this query:

-- The 10 queries with the highest average execution time
SELECT query,
       calls,
       mean_exec_time AS mean_ms,
       total_exec_time AS total_ms,
       rows,
       shared_blks_hit * 100.0
         / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_ratio
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

This immediately tells you which queries hurt the most, how many times they were called, and their cache hit rates. A hit_ratio below 90% generally indicates cache misses, either because the dataset is too large or because shared_buffers is too small.

Do not focus only on mean_exec_time. Queries with high total_exec_time and high calls are the real drivers of total latency. A query called 100,000 times at 5ms each is more worthy of optimization than one called once and taking five seconds.

2. EXPLAIN ANALYZE: See What a Query Is Actually Doing

Once you have a slow query, the next step is EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC;

When reading an execution plan, I focus on several things:

• Seq Scan on orders: A full table scan. It is harmless on a small table, but on a large table it usually means the index is missing, incorrectly designed, or not being used.
• Index Scan / Index Only Scan: An index is being used. Index Only Scan is the ideal case because the index covers the query and PG does not need to fetch rows from the table.
• Sort: A sort operation. If it includes Sort Method: external merge Disk, work_mem is insufficient and the sort has spilled to disk, which is extremely slow.
• Buffers: shared hit=12 read=4: A high read count means the pages were not cached and had to be read from storage.
• Estimated rows vs. actual rows: A large difference usually indicates stale statistics; see Section VI.

Here is a typical bad example:

Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212) (actual time=1.2..6234.8 rows=2)
  Filter: (status = 'paid' AND created_at > '2025-01-01')
  Rows Removed by Filter: 19999998
  Buffers: shared hit=1245 read=482311

It scanned 20 million rows, discarded 19,999,998 of them, and returned only two. This is conclusive evidence of a seriously missing index.

3. Lock Waits and Connection Backlogs

Sometimes "slow" does not mean slow execution. It means waiting for a lock or waiting for a connection:

-- Queries that are currently waiting
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS wait_time, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Blocking chain: who is blocking whom
SELECT blocked.pid     AS blocked_pid,
       blocked.query   AS blocked_query,
       blocking.pid    AS blocking_pid,
       blocking.query  AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

If wait_event_type = 'Lock' appears everywhere, row-lock or table-lock conflicts are likely, usually because a long transaction is blocking subsequent writes. Exhausting the connection limit, with the connection count approaching max_connections, is a separate issue covered in Section V.

III. Configuration Tuning: Four Parameters Determine 80% of Memory Behavior

PG's default configuration uses conservative values designed to run on any machine. Running a 32G server with the default shared_buffers=128MB is a tremendous waste. Tune these parameters:

# Example for a server with 32G of RAM
shared_buffers = 8GB              # 25%
effective_cache_size = 24GB       # 75%
work_mem = 16MB                   # 16MB per operation per connection; watch the total
maintenance_work_mem = 1GB
max_connections = 100             # Use with PgBouncer; do not raise this further

Several practical points matter here:

• Larger shared_buffers is not always better. Benefits decline sharply beyond 40% of memory because the OS page cache does the same kind of work. A value of 25% is the practical sweet spot.
• Calculate total work_mem as maximum concurrency × operations per query. With 100 connections at 16MB each, the worst case is 1.6GB, which is manageable. At 64MB, it becomes 6.4GB and can exhaust memory. Start small, then increase it only if EXPLAIN shows sorts spilling to disk.
• effective_cache_size affects only optimizer decisions; it does not allocate memory. If it is set too low, the optimizer may assume little cache is available and choose a Seq Scan instead of an Index Scan. Setting this value too conservatively is a common mistake.
• Do not blindly increase max_connections. PG uses a process model, and with 300+ connections, context switching alone can consume the CPU. The conclusion is simple: keep direct production PG connections between 100 and 200; use PgBouncer beyond that. See Section V.

IV. Query Optimization: Indexes, SELECT *, N+1, and Subqueries

Once you locate a slow query, apply optimizations in order of ROI:

1. Add an Index, but Add the Right One

The most common cause of a slow query is a missing index. Before adding one, confirm that the query is really not using an index by checking that EXPLAIN shows a Seq Scan. After adding it, run EXPLAIN again to verify that it is used:

-- Single-column index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index: order matters; equality conditions and high selectivity come first
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Partial index: index only hot data for a smaller size and higher hit rate
CREATE INDEX idx_orders_unpaid ON orders(created_at)
  WHERE status = 'pending';

Remember three pitfalls:

• Column order in a composite index. (user_id, created_at) can serve WHERE user_id=? and WHERE user_id=? ORDER BY created_at, but it cannot serve WHERE created_at=?. Indexes use leftmost-prefix matching, so column order is decisive.
• More indexes are not always better. Every index adds write overhead because INSERT/UPDATE/DELETE must update it. A table with 10 indexes can write several times more slowly than one with two. Add only indexes that queries actually use, and regularly use pg_stat_user_indexes to remove indexes that have never been read.
• LIKE '%xxx' cannot use a regular index. Prefix matching with LIKE 'xxx%' can, but substring matching requires the pg_trgm extension or full-text search.

2. Do Not Use SELECT *

-- Bad: fetch 200 columns into the application and waste network and memory
SELECT * FROM users JOIN profiles ON ... WHERE ...;

-- Good: fetch only the 3 required columns
SELECT u.id, u.email, p.avatar FROM users u JOIN profiles p ON ... WHERE ...;

On wide tables with dozens or hundreds of columns, the network transfer and deserialization cost of SELECT * may exceed the query's execution cost. Explicitly listing columns can also enable an Index Only Scan; when an index covers the query, PG does not need to fetch rows from the table.

3. Eliminate N+1 Queries

ORMs are breeding grounds for N+1 queries:

// Bad: 1 query for users + N queries for orders
const users = await db.user.findMany();
for (const u of users) {
  u.orders = await db.order.findMany({ where: { userId: u.id } });
}

// Good: let the ORM handle it with one JOIN or one IN query
const users = await db.user.findMany({ include: { orders: true } });

The simplest way to identify N+1 queries is to enable the ORM's query log and check whether the same kind of query is repeated dozens of times during one request.

4. CTE and Subquery Pitfalls

Before PG 12, a CTE (WITH) was an optimization fence: the query was materialized, preventing outer filters from being pushed into it. Version 12+ uses NOT MATERIALIZED by default, so this is no longer a problem in most cases. However, if a complex CTE is scanned repeatedly, it may still need to be explicitly marked MATERIALIZED. In addition, IN (SELECT ...) can degrade into a Seq Scan on some versions; rewriting it as EXISTS or a JOIN often produces an unexpected speedup.

V. Connection Pools: Do Not Make PG Handle Too Many Direct Connections

PostgreSQL Connection Pooling and PgBouncer provides the full analysis. Here are the core conclusions:

• PG uses a process model, so connections are expensive. Each connection has its own backend process, and even an idle connection consumes 5–10MB.
• Use the ORM's built-in Pool on the application side, whether Prisma, Drizzle, or pg. A pool size of about 10 is enough; do not make it unnecessarily large.
• Place PgBouncer in front of the database and use transaction pooling mode to multiplex hundreds of application connections onto dozens of PG connections.

Once PG performance tuning reaches a certain point, connection management is the inevitable next step. I have seen too many incidents where direct connections exhausted the database, all because no connection pool was used.

VI. VACUUM/ANALYZE: You Have to Pay the Cost of MVCC

PG uses MVCC for concurrency. UPDATE/DELETE does not remove the old row; it marks it as a "dead tuple" and retains it for concurrent transactions to read. These dead tuples occupy storage and slow down scans, so VACUUM must reclaim them.

-- Check the dead-tuple ratio for each table
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_ratio DESC;

When dead_ratio exceeds 10%~20%, the table needs VACUUM.

In addition to VACUUM, there is ANALYZE. It does not clean up data; it only updates statistics. The optimizer uses statistics to estimate how many rows a condition will match. If that estimate is wrong, the execution plan goes off course. A typical symptom is choosing a Seq Scan despite the presence of an index. You must run ANALYZE after a large batch of UPDATE or DELETE operations, or subsequent query plans will rely on stale statistics.

Production systems rely on autovacuum, but its defaults are conservative to limit interference with workloads and often cannot keep up with high write rates. Tune several key values:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1    # Trigger when dead tuples reach 10% (default: 20%)
autovacuum_analyze_scale_factor = 0.05  # Recompute statistics after 5% of rows change
autovacuum_naptime = 30s                # Check interval

For a particularly write-heavy large table, you can lower the trigger threshold specifically for that table:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  toast.autovacuum_vacuum_scale_factor = 0.02
);

One real counterexample involved a 100-million-row log table where autovacuum could not keep up. Dead tuples accumulated to 60%, doubling the cost of sequential scans and polluting the cache. A manual VACUUM FULL, which locks the table, cleaned it up and reduced query time from 12 seconds to 800ms. Do not wait for this to happen in production. Monitor pg_stat_user_tables.n_dead_tup and alert before it becomes a problem.

VII. Hardware: SSD Is the Baseline, More Memory Helps, and CPU Is Not Magic

If the previous six layers are fully optimized and the database is still slow, then consider hardware:

• Storage: SSD is the baseline. Running PG on a mechanical hard drive in 2025 is inexcusable. More specifically, random-read IOPS determines latency for small queries, while sequential read and write throughput determines the performance of large scans and WAL. NVMe SSD is currently the best option. In the cloud, size Provisioned IOPS or general-purpose SSD capacity for peak load.
• Memory: more is better, with diminishing returns after shared_buffers reaches 25% of RAM. Leave the remaining memory to the OS page cache, which PG relies on heavily. The direct benefit of more memory is a higher cache hit rate. When all hot data is in memory, latency can fall from tens of milliseconds to under 1ms.
• CPU: it helps with sorting, aggregation, and hashing, but a single PG query struggles to utilize many cores fully because parallel querying is disabled by default and has limited value in many scenarios. CPU's main value is supporting more concurrent connections, not making one query faster. Instead of focusing only on clock speed, pay attention to connection count and the queries themselves.

In short: hardware is the foundation of PG performance, but even the best foundation cannot rescue a poorly built system. Tune the first six layers before considering this one.

VIII. A Systematic Method: Locate, Optimize, Then Measure

Putting everything together produces a reusable troubleshooting workflow:

1. Use pg_stat_statements to find the most time-consuming queries. Examine mean_exec_time and total_exec_time × calls to identify the true heavy hitters.
2. Use EXPLAIN (ANALYZE, BUFFERS) to analyze execution plans. Look for Seq Scan, sorts spilling to disk, cache hit rates, and discrepancies in estimated row counts.
3. Apply the appropriate fix: missing index → add one and verify that it is used; poor query → rewrite it by selecting explicit columns and eliminating N+1; conservative configuration → tune shared_buffers and work_mem; stale statistics → run ANALYZE; accumulated dead tuples → run VACUUM; exhausted connections → deploy PgBouncer.
4. Measure again. Rerun EXPLAIN ANALYZE and load tests after making changes to confirm that performance actually improved. Do not deploy an unmeasured change.
5. Establish long-term monitoring. Keep pg_stat_statements recording continuously, use the slow-query log (log_min_duration_statement = 200) to catch new slow queries, and alert on the dead-tuple ratio.

The core principle of this workflow is: every optimization must have a measurement, a hypothesis, and a verification step. Deploying changes based on intuition alone makes it impossible to know whether they helped or to conduct a useful postmortem when the next problem occurs.

Summary

• 80% of PG performance problems are not caused by hardware but by queries, indexes, and configuration. Scaling up is the last step, not the first. Answer "Where is it slow?" before deciding what to do.
• The three-part diagnostic toolkit is pg_stat_statements for finding the slowest queries, EXPLAIN (ANALYZE, BUFFERS) for inspecting execution plans, and pg_stat_activity plus lock-wait analysis for finding blocking. These three techniques can locate 90% of problems.
• Configuration tuning centers on four parameters: shared_buffers at 25%, effective_cache_size at 75%, work_mem with careful attention to total usage, and max_connections kept under control. Defaults are designed merely to run; production values must be tuned.
• Optimize queries in order of ROI: add the right index, paying attention to composite-index column order and using partial indexes to save space; avoid SELECT *; eliminate N+1 queries; then rewrite poor subqueries. Verify every change with EXPLAIN.
• Manage the cost of MVCC proactively: reclaim dead tuples with VACUUM, update statistics with ANALYZE, and tune autovacuum separately for large tables. Monitor n_dead_tup instead of waiting for it to overwhelm production.
• Hardware: SSD is the baseline, more memory improves the cache hit rate, and CPU primarily supports concurrency rather than accelerating a single query. Expanding the foundation does not help if the system built on top of it is poorly designed.

In one sentence: the core of PostgreSQL performance tuning is not a magical parameter but the engineering discipline of locating the problem, optimizing it, and then measuring the result. Most slowness can be resolved with one index, one EXPLAIN, or one VACUUM, without spending money on a larger machine.