Taming Postgres at scale: the queries that bit us

Postgres will happily run for years without complaint — until the day your tables cross a threshold and a query plan quietly flips. Here are three incidents that taught us the most, and the fixes that stuck.

1. The missing covering index

A dashboard query timed out once the transactions table passed 40 million rows. EXPLAIN told the whole story: a sequential scan where we expected an index.

EXPLAIN ANALYZE
SELECT status, created_at
FROM transactions
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;
-- Seq Scan on transactions ... (actual time=8123ms)

The fix was a covering index that includes every column the query touches, so Postgres never has to visit the heap:

CREATE INDEX idx_txn_account_created
ON transactions (account_id, created_at DESC)
INCLUDE (status);
-- Index Only Scan ... (actual time=1.4ms)

From 8 seconds to under 2 milliseconds. The INCLUDE clause is the hero: it keeps status in the index so the query is satisfied without touching the table.

2. Connection exhaustion under load

During a traffic spike, every request started failing with remaining connection slots are reserved. Each serverless function was opening its own connection, and Postgres has a hard ceiling.

The answer is a pooler in front of the database:

[databases]
clara = host=db.internal port=5432 dbname=clara

[pgbouncer]
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 20

transaction pooling lets thousands of clients share a small pool of real backend connections. We went from 500 saturated connections to 20 comfortable ones serving 10× the traffic.

The database can only do so many things at once. A pooler’s job is to make sure the things it is doing are the ones that matter.

3. SELECT * and the TOAST tax

A hot endpoint got slow for no obvious reason. The culprit: a SELECT * on a table with a large JSONB metadata column. Postgres was de-TOASTing and shipping kilobytes per row that the endpoint never used.

-- Slow: drags the big JSONB blob out of TOAST storage every row
SELECT * FROM accounts WHERE region = 'MX';

-- Fast: fetch only the columns you actually render
SELECT id, name, status FROM accounts WHERE region = 'MX';

Selecting three columns instead of everything cut the endpoint’s p95 latency by 70%. SELECT * is convenient in a REPL and a liability in production.

The through-line

None of these were exotic. They came down to the same discipline every time:

1. Reach for EXPLAIN ANALYZE before theorizing.
2. Give the database only the work it truly needs — the right index, the right columns, the right number of connections.
3. Re-check plans as tables grow; the plan that was optimal at 1M rows may be a trap at 40M.

Postgres is an extraordinary database. Most of the time, “Postgres is slow” really means “we asked it to do more than we needed.”