P-8 — Performance Tuning for Application Engineers
There is a version of performance tuning that belongs to DBAs: they adjust shared_buffers, tune checkpoint_completion_target, read kernel parameters, and operate at the infrastructure level. That work matters at scale.
There is another version that belongs to application engineers — and it produces far larger wins in far less time. This module covers the second kind: reading EXPLAIN ANALYZE output confidently, eliminating N+1 queries, understanding the configuration knobs that actually matter for applications, and using PgBouncer correctly. These are the skills that turn a slow application into a fast one without needing infrastructure access.
Reading EXPLAIN ANALYZE
EXPLAIN shows the query plan PostgreSQL chose. EXPLAIN ANALYZE executes the query and shows both the plan and the actual runtime measurements. Always use ANALYZE when debugging real performance — the plan alone is often misleading.
sql
Anatomy of an EXPLAIN Output
text
Key things to read:
cost=X..Y — estimated cost units. The first number is startup cost (cost before first row). The second is total cost. These are planner estimates, not milliseconds.
actual time=X..Y — real milliseconds. First number: time to first row. Second: total time. This is what you actually care about.
rows=N (estimated) vs rows=N (actual) — if these diverge significantly (10x or more), the planner has bad statistics. Run ANALYZE table_name to refresh them.
loops=N — how many times this node executed. If a nested loop runs 10,000 times, multiply the actual time by loops to get true total time.
Rows Removed by Filter — rows that were scanned and discarded. High numbers here mean a sequential scan is doing unnecessary work that an index could avoid.
The Most Important Nodes to Recognise
Seq Scan — full table scan. Not always bad (small tables, high selectivity filters), but suspicious on large tables with a filter.
Index Scan — uses a B-tree index to find rows. Good.
Index Only Scan — all needed data is in the index itself, no heap fetch. Best case.
Bitmap Index Scan + Bitmap Heap Scan — fetches a set of matching row locations via index, then reads those pages. Good for queries returning many rows from an index.
Nested Loop — for each row in the outer relation, look up matching rows in the inner relation. Fine when the outer set is small. Catastrophic when the outer set is large (N+1 pattern).
Hash Join — build a hash table from the smaller relation, probe it with the larger. Good for large joins on equality conditions.
Merge Join — sort both sides and merge. Good when both inputs are already sorted.
The BUFFERS Option
sql
Adding BUFFERS shows cache hits and misses:
Buffers: shared hit=12 read=3
hit — pages served from PostgreSQL's shared buffer cache (fast, no disk I/O)
read — pages read from disk (slow, or from OS page cache)
A query with many read pages on a hot table suggests shared_buffers is too small or the working set doesn't fit in memory.
Identifying Missing Indexes
The most common fix for slow queries is a missing index. Signs to look for in EXPLAIN ANALYZE:
Seq Scan on a large table with Rows Removed by Filter >> actual rows returned
actual time on a Seq Scan node is large
The filter column isn't indexed
sql
CREATE INDEX CONCURRENTLY builds the index without locking the table — always use it in production.
The N+1 Query Problem
The N+1 problem is the most common performance killer in production applications. It happens when you execute one query to fetch N parent records, then execute one query per record to fetch related data — producing N+1 total queries.
javascript
At 100 orders this is noticeable. At 1000 orders this is a page timeout.
Fix 1: JOIN in SQL
sql
One query. The database does the join efficiently using indexes.
Fix 2: IN clause batch fetch
javascript
Fix 3: ORM eager loading
In ORMs, N+1 manifests through lazy loading. Most ORMs have an "eager load" or "include" option:
javascript
How to Detect N+1 in Production
Enable log_min_duration_statement to catch slow queries. But for N+1, individual queries may each be fast — the problem is volume. Use a query counter middleware or APM tool (Datadog, New Relic) that shows query count per request. Any request making 50+ identical queries is almost certainly N+1.
sql
Key Configuration Parameters for Application Engineers
Most PostgreSQL config lives in postgresql.conf. These are the parameters that meaningfully affect application performance and are safe to tune without DBA-level expertise.
work_mem
Memory per sort/hash operation per query. Default is 4MB — often too low.
sql
If you see external merge Disk in sort nodes, work_mem is too low for that query. Increase it globally carefully — work_mem is allocated per sort per connection, so 64MB * 200 connections * 3 sorts = 38GB. Set it conservatively globally, and use SET LOCAL work_mem for specific heavy queries.
shared_buffers
The size of PostgreSQL's buffer cache — how much data it holds in memory. Default is 128MB, absurdly low for any production server. Rule of thumb: 25% of total RAM.
sql
effective_cache_size
Not actual memory allocation — this tells the planner how much memory is available for caching (PostgreSQL buffers + OS page cache combined). It affects index vs. sequential scan decisions. Set it to 50-75% of total RAM.
sql
random_page_cost
The planner's estimate of the cost of a random disk read, relative to sequential reads. Default is 4.0 (spinning disk). On SSDs or cloud storage, set it to 1.1-1.5 — this makes the planner more willing to use index scans.
sql
This single change often fixes "planner prefers Seq Scan over Index Scan" complaints on cloud databases.
max_connections
The maximum number of concurrent client connections. Default is 100. Each connection consumes memory (~5-10MB for the backend process). The answer to "we need more connections" is almost never to increase max_connections — it's to add a connection pooler.
Connection Pooling with PgBouncer
Each PostgreSQL connection is a dedicated backend process. At 500 concurrent connections, PostgreSQL is spending significant memory and CPU just on connection overhead. Most connections spend most of their time idle, waiting for application code to execute.
PgBouncer is a lightweight connection pool that sits between your application and PostgreSQL. Applications connect to PgBouncer; PgBouncer maintains a smaller pool of actual PostgreSQL connections.
PgBouncer Pool Modes
Transaction pooling (most common for web apps): A PostgreSQL connection is assigned to a client only for the duration of a transaction. After COMMIT or ROLLBACK, the connection returns to the pool. 1000 application connections can share 20 PostgreSQL connections if transactions are short.
Session pooling: A PostgreSQL connection is assigned to a client for the entire session duration. Better compatibility (session-level state is preserved) but lower multiplexing.
Statement pooling: Connection released after each statement. Highest multiplexing, but incompatible with multi-statement transactions.
ini
Transaction Pooling Incompatibilities
Transaction pooling is the right default for web applications, but a few PostgreSQL features don't work with it:
SET session variables — use SET LOCAL inside a transaction, or set_config(..., true) for transaction-local config
Advisory locks (session-scoped) — use transaction-scoped advisory locks instead
Prepared statements — disable server-side prepared statements in your driver, or use PgBouncer's server_reset_query option
LISTEN/NOTIFY — requires a dedicated non-pooled connection
For most CRUD web applications, none of these are issues. If you use RLS with session variables (as in P-6), use set_config('app.tenant_id', $1, true) — the true flag makes it transaction-local, which is safe with transaction pooling.
Monitoring PgBouncer
sql
cl_waiting > 0 means clients are queued waiting for a pool connection — your default_pool_size may be too small or queries are taking too long.
Slow Query Logging
The simplest performance monitoring tool: log queries that take longer than a threshold.
sql
Logs appear in PostgreSQL's log file (location shown by SHOW log_directory). In production, ship these to a log aggregator and set up alerts on slow query frequency.
pg_stat_statements
For aggregate slow query analysis, pg_stat_statements is the essential extension. It tracks cumulative statistics for every distinct query shape.
sql
This is your primary tool for finding what to optimize in production — it shows real cumulative cost, not just the worst individual queries.
Common Performance Patterns
Avoid SELECT *
sql
SELECT * prevents index-only scans and increases network transfer between PostgreSQL and your application.
Use LIMIT with ORDER BY
Without ORDER BY, LIMIT returns arbitrary rows — and PostgreSQL may still scan the whole table. With ORDER BY on an indexed column, PostgreSQL can use the index to fetch exactly the rows needed.
sql
Pagination: Keyset vs. OFFSET
OFFSET pagination gets slower with each page:
sql
Keyset (cursor) pagination stays fast regardless of depth:
sql
Keyset pagination requires a composite index on (created_at DESC, id DESC) and works best for "load more" / infinite scroll UIs. For page-number navigation, OFFSET is sometimes necessary — just be aware of the performance cliff at high page numbers.
Partial Indexes for Hot Subsets
If your application frequently queries a small subset of a table (pending orders, unread notifications, active users), a partial index on that subset is much smaller and faster than a full index:
sql
The partial index is tiny compared to a full index on status, fits in memory more easily, and updates only when status = 'pending' rows change.
Practical Checklist
When a query is slow, work through this list:
Run EXPLAIN (ANALYZE, BUFFERS) — understand the actual plan, not the estimated one
Look for Seq Scans on large tables with high Rows Removed by Filter — candidate for an index
Check estimated vs. actual row counts — large divergence means stale statistics; run ANALYZE
Check for loops=N on Nested Loop nodes — if N is large and the inner query touches a big table, you may have an N+1 or a missing index on the join column
Check Sort Method: external merge — increase work_mem for this query
Check Buffers: read=N on hot queries — large read counts suggest the working set isn't in shared_buffers
Check pg_stat_statements for cumulative slow queries — fix highest total_exec_time first
Count queries per request in your application — N+1 shows up as many fast identical queries, not one slow one
Summary
Application-level performance tuning follows a predictable pattern: measure with EXPLAIN ANALYZE and pg_stat_statements, fix the biggest issues first (missing indexes, N+1 queries), then tune configuration to match your hardware.
The parameters that matter most for application engineers are work_mem (sorts), random_page_cost (SSD vs. spinning disk planning), and log_min_duration_statement (finding slow queries). PgBouncer in transaction mode is the right connection pooling setup for most web applications.
Next up: P-9 — External Services, Caching Layers, and Deployment — ORMs vs. raw SQL, read replicas, materialised views, managed databases, and a production-ready deployment checklist.
PgBouncer in Depth — The Bugs That Only Appear in Production
The mention of PgBouncer earlier in this module covers the surface: install it, point your app at it, fewer connections. What it doesn't cover: the silent bugs that appear when you use PgBouncer in transaction mode with an ORM that uses prepared statements — the "prepared statement does not exist" error that only fires in production at 2am, intermittently, once real concurrency starts reassigning connections between transactions.
That failure mode — why it happens, the fix for Prisma/node-postgres/Drizzle/SQLAlchemy, why SET (but not SET LOCAL) breaks under transaction pooling, session- vs. transaction-scoped advisory locks, and how to read SHOW POOLS/SHOW CLIENTS and size pool_size correctly — is covered in full in P-11, Connection Pooling Failure Modes, which exists specifically to go deep on this. The short version for this module: pick transaction mode for most web applications (session mode gives you zero pooling benefit for short-lived connections; statement mode breaks multi-statement transactions), and expect prepared-statement and session-state bugs the moment you introduce it — see P-11 before you hit them in production.
Knowledge Check
A backend application suddenly experiences severe latency spikes during peak hours. You inspect EXPLAIN ANALYZE for the most frequent query and notice the Sort node reports Sort Method: external merge Disk: 24576kB. What is the immediate architectural implication and recommended fix?
You are configuring PgBouncer in transaction mode for a Node.js API using Prisma ORM. Upon deployment, you start seeing intermittent errors stating prepared statement "s1" does not exist. What causes this, and how do you resolve it?
An application developer implements a "Load More" button for an activity feed. They write the query as: SELECT * FROM events ORDER BY created_at DESC LIMIT 50 OFFSET 10000;. The query is slow, taking over 800ms. They add an index: CREATE INDEX idx_events_created ON events(created_at DESC);. The query runtime drops slightly to 600ms, but remains unacceptably slow. Why didn't the index solve the problem, and what is the correct pattern?
Test your knowledge with more question sets
Sign in to access a wider variety of questions and get notified when new practice sets are added to this module.
EXPLAIN(ANALYZE, BUFFERS, FORMAT TEXT)SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status='pending'ORDERBY o.created_at DESCLIMIT20;
EXPLAIN(ANALYZE, BUFFERS)SELECT*FROM orders WHERE user_id =42;
-- This query is slow: status is unindexed, table has 500k rowsEXPLAINANALYZESELECT*FROM orders WHEREstatus='pending';-- Seq Scan on orders (cost=0.00..12500.00 rows=1000 width=128)-- (actual time=0.012..234.567 rows=987 loops=1)-- Filter: (status = 'pending')-- Rows Removed by Filter: 499013-- Fix: add an indexCREATEINDEX CONCURRENTLY idx_orders_status ON orders (status);-- Now: Index Scan using idx_orders_status on orders-- (actual time=0.034..1.234 rows=987 loops=1)
// Bad: N+1 in application codeconst orders =await db.query('SELECT id, user_id FROM orders LIMIT 100');// 100 queries follow:for(const order of orders){const user =await db.query('SELECT email FROM users WHERE id = $1',[order.user_id]); order.user= user;}
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT100;
// Better: two queries totalconst orders =await db.query('SELECT id, user_id FROM orders LIMIT 100');const userIds = orders.map(o=> o.user_id);const users =await db.query('SELECT id, email FROM users WHERE id = ANY($1)',[userIds]);// Join in application memory
// Prismaconst orders =await prisma.order.findMany({take:100,include:{user:true}// single JOIN query, not N queries});
-- In development: log ALL queries to spot N+1ALTER SYSTEM SET log_min_duration_statement =0;SELECT pg_reload_conf();-- In production: log queries slower than 100msALTER SYSTEM SET log_min_duration_statement =100;SELECT pg_reload_conf();
-- Current valueSHOW work_mem;-- Set for a specific session (tuning without global impact)SET work_mem ='64MB';-- Check if a sort spilled to diskEXPLAIN(ANALYZE, BUFFERS)SELECT...ORDERBY...;-- Look for: Sort Method: external merge Disk: 4096kB-- vs: Sort Method: quicksort Memory: 128kB
SHOW shared_buffers;-- Requires restart to change:-- shared_buffers = '4GB' in postgresql.conf
-- This is advisory only, doesn't allocate memorySET effective_cache_size ='12GB';
-- For SSD-backed storageALTER SYSTEM SET random_page_cost =1.1;SELECT pg_reload_conf();
-- Connect to PgBouncer's admin databasepsql -p 6432 pgbouncer
-- Pool statusSHOW POOLS;-- Shows: database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used-- Client listSHOW CLIENTS;-- Server connection listSHOW SERVERS;
-- Log queries slower than 500msALTER SYSTEM SET log_min_duration_statement =500;SELECT pg_reload_conf();-- Also useful: log lock waitsALTER SYSTEM SET log_lock_waits =on;ALTER SYSTEM SET deadlock_timeout ='1s';SELECT pg_reload_conf();
-- Enable (requires restart)-- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements'-- After restart:CREATE EXTENSION pg_stat_statements;-- Top 10 queries by total timeSELECTround(total_exec_time::numeric,2)AS total_ms, calls,round(mean_exec_time::numeric,2)AS mean_ms,round(stddev_exec_time::numeric,2)AS stddev_ms,left(query,80)AS query
FROM pg_stat_statements
ORDERBY total_exec_time DESCLIMIT10;-- Top 10 by average time (worst per-call performance)SELECTround(mean_exec_time::numeric,2)AS mean_ms, calls,left(query,80)AS query
FROM pg_stat_statements
WHERE calls >100-- ignore rarely-called queriesORDERBY mean_exec_time DESCLIMIT10;
-- Bad: fetches all columns, including large TEXT/JSONB columnsSELECT*FROM articles WHERE id =42;-- Good: fetch only what you needSELECT id, title, created_at FROM articles WHERE id =42;
-- Bad: full scan, then limitSELECT*FROM events LIMIT20;-- Good: index scan on created_at, stops after 20 rowsSELECT*FROM events ORDERBY created_at DESCLIMIT20;
-- Page 1000 at 20 items/page: scans and discards 19,980 rowsSELECT*FROM posts ORDERBY created_at DESCLIMIT20OFFSET19980;
-- First pageSELECT id, title, created_at FROM posts
ORDERBY created_at DESC, id DESCLIMIT20;-- Next page: pass the last row's values as the cursorSELECT id, title, created_at FROM posts
WHERE(created_at, id)<('2024-01-15 10:30:00',12345)ORDERBY created_at DESC, id DESCLIMIT20;
-- Instead of indexing all orders by status:CREATEINDEX idx_orders_status ON orders (status);-- Index only the hot subset (pending orders are << 1% of all orders):CREATEINDEX idx_orders_pending ON orders (created_at)WHEREstatus='pending';-- This query uses the partial index:SELECT*FROM orders WHEREstatus='pending'ORDERBY created_at;