Table of contents · 10 sections

PostgreSQL Performance Tuning: Indexes, EXPLAIN, and PgBouncer

Your orders page was fine with 10,000 rows and now it isn't, or the app has started throwing "too many clients" errors. This post walks through one 5-million-row orders table in Docker: read the plan, add the right composite and partial indexes, fix deep pagination, then break the connection limit and see what PgBouncer does about it. Every number comes from my machine, and I show the statement or output next to it.

The setup (so you can reproduce it)

Docker Desktop on Windows, 8 cores. The data fits in memory, so every plan shows shared hit rather than disk reads. Absolute timings on your hardware will differ; compare buffer counts and ratios. Unrelated containers were also running on the machine, and as you'll see in the benchmark section, the timings were noisy.

docker network create pgnet
docker run -d --name pgtune --network pgnet -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=shop \
  -p 5432:5432 postgres:18 -c shared_buffers=512MB -c max_connections=100

That is PostgreSQL 18.6 with everything else at defaults. Load the data through docker exec -i pgtune psql -U postgres -d shop < setup.sql:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL,
  status text NOT NULL,
  total_cents int NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO orders (customer_id, status, total_cents, created_at)
SELECT 1+(random()*199999)::int,
  CASE WHEN r<0.90 THEN 'delivered' WHEN r<0.97 THEN 'shipped'
       WHEN r<0.995 THEN 'cancelled' ELSE 'pending' END,
  (random()*50000)::int,
  now() - (random()*730 || ' days')::interval
FROM (SELECT random() r FROM generate_series(1,5000000)) s;
VACUUM ANALYZE orders;

That's 5,000,000 rows and 431 MB for the table plus primary key (pg_total_relation_size), generated in 55 seconds. In my run 25,188 orders were pending (about 0.5%) and about 90% were delivered. That skew matters later.

Read the plan before you touch an index

The first query is the classic "show this customer's latest 20 orders":

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents, created_at
FROM orders WHERE customer_id = 4242
ORDER BY created_at DESC LIMIT 20;

 Limit  (cost=68462.03..68464.36 rows=20 width=29) (actual time=95.002..103.991 rows=20.00 loops=1)
   Buffers: shared hit=41496
   ->  Gather Merge  (cost=68462.03..68465.06 rows=26 width=29) (actual time=95.001..103.986 rows=20.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=41496
         ->  Sort  (cost=67462.00..67462.03 rows=11 width=29) (actual time=92.146..92.148 rows=8.00 loops=3)
               Sort Key: created_at DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on orders  (cost=0.00..67461.81 rows=11 width=29) (actual time=10.155..92.071 rows=9.00 loops=3)
                     Filter: (customer_id = 4242)
                     Rows Removed by Filter: 1666658
                     Buffers: shared hit=41420
 Execution Time: 104.068 ms

(I left out the per-worker sort lines and the planning section.) Three things to read, in this order:

1. The scan type. Parallel Seq Scan on a 5M-row table to find 27 rows is the problem.
2. Rows Removed by Filter: 1666658 per process (the leader plus two workers). The database read and threw away almost everything.
3. Buffers: shared hit=41496. That's 41,496 8 kB pages, about 324 MB touched to return 20 rows. Time varies with cache and load (104 ms and 112 ms on two runs of this query). Buffer counts barely moved, which makes them the better thing to compare between runs.

One gotcha from the PostgreSQL EXPLAIN docs: ANALYZE really executes the statement. For UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK; or you'll change your data.

Composite index: when it matters and when it doesn't

The obvious fix is an index on customer_id:

CREATE INDEX orders_customer_idx ON orders (customer_id);

Result: 0.42 ms and 0.39 ms on two runs, 30 to 33 buffers, down from 104 ms and 41,496 buffers. The plan is a bitmap index scan, then a small sort of the 27 matching rows.

Now the part most tutorials skip. I then added the composite index that matches both the filter and the sort:

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

 Limit  (cost=0.43..83.86 rows=20 width=29) (actual time=0.033..0.220 rows=20.00 loops=1)
   Buffers: shared hit=23
   ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..108.89 rows=26 width=29) (actual time=0.031..0.217 rows=20.00 loops=1)
         Index Cond: (customer_id = 4242)
         Index Searches: 1
         Buffers: shared hit=23
 Execution Time: 0.251 ms

The shape changed: no Sort node and no bitmap scan, just an index scan that is already in order. But for customer 4242 the time went from 0.39 ms to 0.25 ms. That's not worth an extra 150 MB, because 27 rows sort instantly. The composite index pays off when one key has many rows. So I inserted 300,000 extra orders for customer_id = 1 (think one large B2B account):

INSERT INTO orders (customer_id, status, total_cents, created_at)
SELECT 1, 'delivered', (random()*50000)::int, now() - (random()*730 || ' days')::interval
FROM generate_series(1,300000);
ANALYZE orders;

Then I ran the same LIMIT 20 query for customer_id = 1, with and without the composite index (DROP INDEX orders_customer_created_idx for the first row, then re-create it):

Index available	Plan	Buffers	Time (local)
(customer_id) only	Parallel bitmap scan, top-N sort over 300k rows	2,806	250 ms
(customer_id, created_at DESC)	Index scan, stops after 20 rows, no sort	23	0.29 ms

The composite index returns rows already in order, so LIMIT 20 stops after 20 index entries. The single-column index has to fetch all 300,000 rows for that customer and sort them.

Decision rule: if your query is WHERE a = ? ORDER BY b LIMIT n and the number of rows per a can get large for some values, index (a, b) in the sort direction you query. If every a has a few dozen rows, the single-column index is enough and the composite just costs space. Before the 300k insert, (customer_id) was 37 MB and (customer_id, created_at DESC) was 150 MB, from:

SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'orders'::regclass;

A btree on (a, b) can serve lookups on a alone, so once you have the wider index you can usually drop the narrower one. I didn't test the effect of dropping it.

Partial indexes for the small slice you actually query

An ops dashboard that lists the oldest pending orders:

SELECT id, customer_id, created_at FROM orders
WHERE status = 'pending' ORDER BY created_at LIMIT 100;

With no index: 238 ms (parallel seq scan, 43,996 buffers). A full index fixes it:

CREATE INDEX orders_status_created_full ON orders (status, created_at);

That brought the query to 0.95 ms and 1.7 ms (104 buffers) on two runs, but the index is 202 MB, almost all of it delivered rows nobody queries by status. The partial index covers only the rows you care about:

CREATE INDEX orders_pending_created ON orders (created_at) WHERE status = 'pending';

 Limit  ...
   Buffers: shared hit=102
   ->  Index Scan using orders_pending_created on orders  (cost=0.29..69793.28 rows=21553 width=20) (actual time=0.088..1.501 rows=100.00 loops=1)
 Execution Time: 1.600 ms

Same query: 1.6 ms (102 buffers), and the index is 568 kB (\di+), versus 202 MB for the full one, about 360 times smaller. Query time is the same within noise; the saving is space, and fewer pages for vacuum and the buffer cache to carry. See the insert measurement below for what indexes cost on writes.

The catch, from the docs: the planner must be able to prove your WHERE implies the index predicate. status = 'pending' written as a literal matches. The docs say parameterized queries are a problem, so I tested it with a prepared statement:

PREPARE pend(text) AS
  SELECT id, customer_id, created_at FROM orders
  WHERE status = $1 ORDER BY created_at LIMIT 100;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) EXECUTE pend('pending');
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) EXECUTE pend('pending');

With the default plan_cache_mode, the first execution got a custom plan that sees the value and used the partial index (Index Scan using orders_pending_created, 102 buffers, 2.7 ms). With force_generic_plan it fell back to Parallel Seq Scan (Filter: (status = $1), 43,996 buffers, 1,064 ms). So a bind parameter works only while Postgres is planning with the actual value. I only looked at the first execution; I did not test what happens once a prepared statement has run more times, and I did not test a specific ORM or driver. Check your real query with EXPLAIN, or use a literal in that one query.

Fix deep pagination with keyset queries

LIMIT 20 OFFSET n looks harmless. I added an index so the sort is free, then measured how long the 20 rows take at different depths:

CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_cents, created_at FROM orders
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000;   -- vary the OFFSET

Offset	Time (local, two runs; 2,000,000 is three)	Buffers
0	0.43 / 0.53 ms	23
100,000	249 / 331 ms	100,406
500,000	1,266 / 1,535 ms	501,932
1,000,000	2,031 / 2,470 ms	1,003,835
2,000,000	3,820 / 4,369 / 5,459 ms	2,007,643

Even with a perfect index, Postgres walks past every skipped row (rows=2000020 in the index scan node for the last line). Buffers grow in step with the offset, about one per skipped row. Times are noisy (the 2,000,000 row spans 3.8 to 5.5 seconds) but climb with the offset, so the page that a crawler or an "export" script hits is the one that hurts.

The fix is keyset (cursor) pagination: remember the last row you returned and ask for what comes after it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_cents, created_at FROM orders
WHERE (created_at, id) < ('2026-01-09 05:12:41.807929+00', 763038)
ORDER BY created_at DESC, id DESC LIMIT 20;

 Limit  (cost=0.43..2.15 rows=20 width=24) (actual time=0.073..0.441 rows=20.00 loops=1)
   Buffers: shared hit=23
   ->  Index Scan using orders_created_id_idx on orders  (...)
         Index Cond: (ROW(created_at, id) < ROW('2026-01-09 05:12:41.807929+00'::timestamp with time zone, 763038))
 Execution Time: 0.588 ms

That cursor is the row at offset 2,000,000 (SELECT created_at, id ... OFFSET 1999999 LIMIT 1). The same page now takes 0.51 and 0.59 ms and 23 buffers, versus seconds for the OFFSET version. Two rules make it correct: the ORDER BY must include a unique tie-breaker (id), and the row comparison must use the same columns in the same order as the index. The cost is that you can't jump to "page 4,812", only next/previous. For feeds, admin tables and APIs that's fine. For a UI that really needs page numbers, cap the depth.

What the extra indexes cost on writes

This post recommends several indexes, so I timed an insert. I made two copies of orders: one with only the primary key, one with the primary key plus the four indexes from this post (customer_id; customer_id, created_at DESC; the partial index; created_at DESC, id DESC). Each held 1,000,000 rows. Then I inserted 100,000 rows (copied from orders, so created_at is random) inside BEGIN; ... ROLLBACK;, three times each:

Table	100,000-row insert, three runs
Primary key only	804 / 779 / 734 ms
Primary key + 4 indexes	5,416 / 8,406 / 7,893 ms

So for this bulk insert the four extra indexes made it about 7 to 11 times slower. That is one machine, one data shape and a single batch insert, not a measure of single-row web inserts, but it settles the question of whether the indexes are free. They aren't. Add the ones your queries need and check pg_stat_user_indexes later for ones nobody scans.

Connection exhaustion: what breaks first

PostgreSQL's max_connections defaults to typically 100, and changing it needs a restart. superuser_reserved_connections defaults to 3 (SHOW superuser_reserved_connections returned 3 here). Each connection is a separate backend process, which is why the answer isn't simply "set it to 1,000".

I created a normal role (CREATE ROLE app LOGIN PASSWORD 'app') and opened 100 psql sessions at once, each running select pg_sleep(20), from a second container on the same network:

sessions that failed: 24
     21 psql: error: ... FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
      1 psql: error: ... FATAL:  sorry, too many clients already
      ... (two more lines, same messages, interleaved by the parallel output)

I got 24 failures because other connections were already open on the server when I started (I did not clean them up first). The mechanism is what matters: new connections fail outright, not slowly. Then pgbench with 150 clients straight at Postgres (as the superuser, so the message differs):

pgbench: error: connection to server at "pgtune" (192.168.80.2), port 5432 failed: FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 25

That's what a deploy spike or a runaway worker pool looks like. I can't reproduce a real traffic pattern on one machine.

PgBouncer: what it fixes and what it costs

PgBouncer sits between app and database and multiplexes many client connections onto few server connections. It has three modes:

• Session (the default): a server connection is assigned for as long as the client stays connected. Supports every Postgres feature, but doesn't reduce connections for apps that hold them open.
• Transaction: a server connection is assigned only during a transaction. This is the mode that lets 400 clients share 20 backends.
• Statement: transaction pooling where multi-statement transactions are disallowed. Rarely what a web app wants.

I ran PgBouncer 1.26.0 from the edoburu/pgbouncer image, which builds its config from environment variables:

docker run -d --name pgb --network pgnet \
  -e DB_HOST=pgtune -e DB_USER=postgres -e DB_PASSWORD=pw -e DB_NAME=shop \
  -e AUTH_TYPE=scram-sha-256 -e POOL_MODE=transaction \
  -e DEFAULT_POOL_SIZE=20 -e MAX_CLIENT_CONN=500 edoburu/pgbouncer:latest

The generated pgbouncer.ini contained pool_mode = transaction, max_client_conn = 500 and default_pool_size = 20 (20 is also the documented default).

The benchmark script, lookup.sql, is the customer lookup from above with a random customer each time (the composite index existed). pgbench ran from a postgres:18 container on the same Docker network, so direct and pooled runs use the identical client path:

\set cid random(1, 200000)
SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = :cid ORDER BY created_at DESC LIMIT 20;

docker run --rm --network pgnet -e PGPASSWORD=pw -v "$PWD":/work postgres:18 \
  pgbench -h pgb -U postgres -n -f /work/lookup.sql -c 90 -j 4 -T 15 shop
# -h pgtune for direct; -c = clients, -j = threads, -T = seconds, -n = skip vacuum
# add -C for "new connection per transaction"

Result 1: it absorbs the client count. 400 clients through PgBouncer ran with 0 failed transactions in all three runs, while 150 direct clients failed on connect.

Result 2: it didn't make steady-state queries faster. It made them slower. Each cell is three 15-second runs, persistent connections, cheap indexed queries. (For the direct 90-client runs I stopped the PgBouncer container first: its idle server connections count against max_connections, and my first direct 90-client attempts failed to connect because of it.)

Setup	Clients	TPS, three runs	Avg latency range
Direct to Postgres	50	6,181 / 6,796 / 11,079	4.5 to 8.1 ms
Direct to Postgres	90	5,806 / 9,164 / 5,676	9.8 to 15.9 ms
Via PgBouncer (20 server conns)	50	3,083 / 4,088 / 4,978	10.0 to 16.2 ms
Via PgBouncer (20 server conns)	90	3,774 / 3,866 / 3,806	23.3 to 23.8 ms
Via PgBouncer (20 server conns)	400	3,001 / 3,702 / 4,050	98.8 to 133.3 ms

The run-to-run spread is large (direct at 50 clients: 6,181 to 11,079) and I did not isolate its cause, so read the table as ranges, not as exact figures. What holds in every pair of runs here: PgBouncer's TPS is lower than direct at 50 clients, and its latency is higher.

While a 90-client run through PgBouncer was going (a separate fourth run, 2,570 TPS), docker stats --no-stream showed:

pgb cpu=104.41%
pgtune cpu=271.47%

So the PgBouncer container was using about one core's worth of CPU while Postgres used under three. That fits a proxy that is saturating one core, but I didn't check PgBouncer's threading model, so treat that as a hypothesis. With 20 backends, queueing behind the pool also adds latency at 400 clients. Slower queries or a remote database change this ratio. I only measured this cheap-query case.

Result 3: new connection per request is where it helps. With pgbench -C (reconnect for every transaction, 50 clients, 15 seconds, three runs each):

Setup	TPS, three runs	Avg connection time
Direct	49 / 33 / 46	80 / 120 / 85 ms
Via PgBouncer	105 / 91 / 75	38 / 43 / 53 ms

Pooled was higher in all three pairs, 1.6 to 2.8 times the direct TPS here. Connection cost depends heavily on TLS and network distance, which this setup lacks, and these absolute numbers are small because each transaction pays for a fresh connection.

So the summary from these runs: use a pooler to cap and protect server connections, and for workloads that open a connection per request; I measured it slowing down a healthy workload with persistent connections.

Transaction-mode gotchas I hit

The PgBouncer docs list features that don't work in transaction pooling: SET/RESET, LISTEN, WITH HOLD cursors, PREPARE/DEALLOCATE, session-level advisory locks, and PRESERVE/DELETE ROWS temp tables. I tested the first one with separate psql calls through PgBouncer:

$ psql -h pgb ... -c "SET statement_timeout='1234ms'"
SET
$ psql -h pgb ... -Atc "SHOW statement_timeout"      # a different client connection
1234ms

One client's SET showed up in a different client's session. Use SET LOCAL inside a transaction instead. The config docs describe max_prepared_statements (default 200) for protocol-level prepared statements in transaction mode; check your driver's setting. SQL-level PREPARE is on the docs' unsupported list. A PREPARE p AS SELECT 1 followed by EXECUTE p in a second psql call happened to work in my test, but I wouldn't rely on that given what the docs say.

If you're choosing between poolers, the existing PgBouncer vs Pgpool deep dive covers that comparison. For more server-side options, see PostgreSQL hidden features for better performance.

Where this stops being representative

• One machine, warm cache, no network latency, synthetic uniform data apart from the two skews I added on purpose. I did not test cold-cache reads.
• I didn't test config tuning (shared_buffers, work_mem) beyond setting shared_buffers=512MB.
• The insert measurement is one bulk-insert shape, not a mixed read/write workload.

What to do next

1. Turn on pg_stat_statements (or your host's slow query log) and pick the one query with the highest total time. Run EXPLAIN (ANALYZE, BUFFERS) on it, inside BEGIN; ... ROLLBACK; if it writes.
2. Look at buffers and "Rows Removed by Filter" first. If a seq scan removes millions of rows to return a few, you need an index.
3. For WHERE a = ? ORDER BY b LIMIT n, create (a, b). Check whether your biggest a value has thousands of rows before bothering.
4. For queries that filter on a rare status, try a partial index with a literal predicate and confirm the planner uses it with your real query, bind parameters included.
5. Replace OFFSET pagination on large tables with a keyset cursor and a unique tie-breaker column.
6. Check SHOW max_connections; against (app instances × pool size per instance). If that product is near 100, put PgBouncer in transaction mode in front, audit for SET and session advisory locks first, and use SET LOCAL.