Somewhere in your PostgreSQL database there is a table that has quietly become the centre of your business. For a UK retailer, it is probably orders — a few hundred rows in staging, four million in production, and growing by the minute. The order history page was instant last year. Now it times out on a Monday morning, and nobody changed a line of code.

Nothing is broken. The data simply outgrew the way you are asking for it. Indexing is the fix, but indexes are not free, and adding the wrong one is a reliable way to make writes slower without making reads any faster. Here is how indexing actually works, using examples you can map onto your own schema.

What a B-tree index really does

When you write CREATE INDEX idx_orders_customer_id ON orders (customer_id);, PostgreSQL builds a separate, sorted structure holding every customer ID alongside a pointer to the row it belongs to. B-tree stands for balanced tree: values live in sorted nodes, and the tree stays shallow — typically three or four levels for millions of rows.

That shallow, sorted structure is why a B-tree handles more than equality checks. Finding one customer is fast. Finding a range is fast. Reading rows in order is fast, because the data is already sorted. What it cannot do is help with pattern matching. A query like WHERE email LIKE '%@gmail.com' cannot use a standard B-tree, because there is no useful starting point in a sorted list for a value that begins with a wildcard.

A B-tree earns its keep when you:

• Filter a large table down to a small number of rows, such as one customer's order history.
• Search a range, such as orders placed in the last thirty days.
• Sort results, so the database can skip an explicit sort step.
• Support uniqueness checks, which is what most primary keys and unique constraints use underneath.

Reading a query plan without guessing

Before you add anything, ask PostgreSQL what it is doing. Run EXPLAIN ANALYZE in front of the slow query — never EXPLAIN alone in production, because ANALYZE is what actually executes it and shows real timings.

Seq Scan on orders (cost=0.00..148320.00 rows=18 width=64) (actual time=412.006..1180.554 rows=17 loops=1)
Filter: ((customer_id = 4821) AND (ordered_at >= '2024-01-01'::date))
Rows Removed by Filter: 3999979

Two details matter. First, Seq Scan means PostgreSQL read the whole table — roughly four million rows — to return seventeen. An index on (customer_id, ordered_at) would have found those seventeen in a handful of page reads.

Second, compare the estimated rows with the actual rows. If the planner expects 10 rows and finds 50,000, it will choose the wrong strategy. That gap usually means stale statistics; ANALYZE orders; often fixes it.

Do not panic at every sequential scan. On a lookup table with 200 rows, a sequential scan is genuinely faster than the overhead of an index. As a rough guide, once a filter matches more than about 5 per cent of a large table, the planner will often prefer one anyway. Reading pages in physical order beats jumping around the disk for a large slice of data — though on SSD-backed infrastructure that trade-off is less dramatic than it used to be.

When indexes hurt performance

Every index must be updated on every INSERT, UPDATE and DELETE. On a write-heavy table — an events log, or an orders table taking thousands of inserts an hour — each new row now has to be written in several places. Updates become noticeably more expensive when they touch an indexed column.

Indexes also consume disk, memory and maintenance time. Autovacuum has more work to do, backups are larger, and pg_dump takes longer. A routine I have seen more than once: a team adds eight indexes while firefighting, six of them never get used by the planner, and the whole table becomes slower to write with no read benefit at all.

Then there are indexes that exist but never get used, usually for one of these reasons:

1. A function wraps the column. WHERE lower(email) = '[email protected]' cannot use an index on email. Create the index on lower(email) instead, so both sides match.
2. The column order is wrong. An index on (ordered_at, status) will not help a query filtering on status alone.
3. Types do not line up. Comparing a varchar column to a number forces a cast, and the index is quietly ignored.
4. The index duplicates a wider one. If you have (customer_id, ordered_at), a lone (customer_id) index is usually redundant.

Index patterns that pay off

Composite indexes follow a simple rule: put equality filters first, then range filters, then sort columns. For an order history page filtering by customer_id and ordering by date, (customer_id, ordered_at DESC) lets PostgreSQL walk the index in the right order and skip the sort entirely.

Partial indexes are underused. If the customer service dashboard only ever looks at pending or failed orders, then CREATE INDEX ON orders (ordered_at) WHERE status IN ('pending', 'failed'); produces a far smaller, faster index than one covering every row.

Covering indexes go one step further. Adding INCLUDE (total_pence, status) lets a query read everything it needs from the index without visiting the table. That is a real win on wide tables, but each included column makes the index bigger, so keep the payload narrow.

For append-only tables — click events, audit trails, delivery pings — consider BRIN. It stores a summary per block range rather than a pointer per row, so it takes a fraction of the space and works well when rows arrive in roughly chronological order.

A practical routine

Start with the slow query, not the schema. Run EXPLAIN ANALYZE on it, note the actual timings and row counts, then create one index and run it again. Keep the index only if the plan and the timings improve. On a production table, build it concurrently so you do not block writes.

Review what you already have. pg_stat_user_indexes shows how often each index has been used since the last statistics reset; anything sitting at zero across a full business cycle is a candidate for removal. Test the drop on a staging copy first — a report run once a quarter still counts as needed.

Finally, write down why each index exists, even if it is just a comment in the migration. Six months from now, the next person to open that schema will thank you, and they will be far less likely to add a seventh index that does the same job as one of the six.

Photo: panumas nikhomkhai / Pexels