I added my first index thinking it was basically a free speedup — like flipping a switch. Nobody had told me indexes cost anything, so it didn't occur to me they might not always be worth it. It took looking at a genuinely slow write path, and eventually tracing it back to a table with five indexes on columns nobody actually queried by, to understand that an index is a trade-off, not a strict upgrade.
The problem indexes actually solve
Without an index, finding rows that match a WHERE condition means Postgres reads every single row in the table and checks each one — a sequential scan. On a table with a few hundred rows, that's basically instant, not worth worrying about. On a table with millions of rows, it's the difference between a query that returns in milliseconds and one that takes seconds, every single time it runs.
That's the entire motivation. An index gives Postgres a shortcut — a separate structure it can search instead of scanning the whole table — for columns you filter, join, or sort by often enough that the sequential scan actually becomes a problem.
Creating one
CREATE INDEX idx_venues_city ON venues(city);
CREATE UNIQUE INDEX idx_venues_name ON venues(name);
Nothing exotic in the syntax itself. The UNIQUE version does double duty — it speeds up lookups on name and enforces that no two rows can share the same value, functioning as a constraint as well as a performance tool.
The columns worth indexing are the ones that actually show up in your WHERE clauses, your join conditions, or your ORDER BY — not every column "just in case it's useful someday." That instinct, indexing defensively, is exactly what leads to the situation I mentioned above.
When an index doesn't actually help
This is the part that gets skipped in a lot of explanations, and it's arguably more useful to know than when to index, since the "when to" case is fairly intuitive once you understand sequential scans.
Small tables. A sequential scan on a few hundred rows is genuinely faster than using an index, once you account for the overhead of looking the index up and then jumping back to the table to fetch the actual row. The index doesn't help; it's just extra bookkeeping.
Columns that are rarely queried. An index that never gets used in a WHERE or join condition is pure cost with no corresponding benefit — it still has to be maintained on every write, for nothing.
Columns updated extremely often. Every INSERT or UPDATE that touches an indexed column also has to update the index itself, not just the table row. On a write-heavy table, having several indexes on frequently updated columns adds up to real overhead, and it's the kind of thing that's easy to not notice until write latency creeps up over time.
Low-cardinality columns. Something like a boolean is_closed column only has two possible values. A plain B-tree index on it doesn't narrow things down much, since roughly half the table matches either value — the index isn't selective enough to be worth using over a sequential scan in most cases.
The index types worth being able to name
| Type | Best for |
|---|---|
| B-tree (default) | equality and range queries — the default 90% of the time |
| GIN | JSONB, arrays, full-text search |
| GiST | geospatial data (what PostGIS relies on), also full-text |
| BRIN | huge tables with naturally sequential data, like timestamps in an append-only log |
| Hash | pure equality lookups only, rarely used over B-tree in practice |
You'll mostly use B-tree without ever thinking about it, since it's the default CREATE INDEX gives you. The others matter once you're dealing with specific data shapes. For a geospatial project, this is worth knowing specifically: PostGIS relies on GiST indexes, which support things like "nearest neighbor" and bounding-box style queries efficiently. A plain B-tree fundamentally can't do that kind of spatial lookup — it's built for ordering along a single dimension, not for two-dimensional proximity.
Actually checking if your query is using an index
EXPLAIN ANALYZE SELECT * FROM venues WHERE city = 'Bhopal';
EXPLAIN on its own shows you the planned execution — what Postgres intends to do, without actually running the query. EXPLAIN ANALYZE actually executes it and shows real timing alongside the plan. The thing to look for in the output is whether it says Seq Scan or Index Scan. A sequential scan on a large, filtered table is usually a sign something's missing — either the index doesn't exist yet, or it exists but isn't being used for some reason, which is its own separate rabbit hole worth investigating if it happens.
This is genuinely one of the highest-value habits to build early. Before assuming a slow query is "just how Postgres is" or reaching for some other fix, checking EXPLAIN ANALYZE first tells you whether the problem is actually about indexing or something else entirely — a bad join order, an unnecessary sort, or something else the plan reveals if you know what to look for.
Why OFFSET gets slow, and what to use instead
SELECT * FROM venues ORDER BY id LIMIT 20 OFFSET 10000;
This looks harmless — you're only asking for 20 rows. But Postgres still has to scan through and discard the first 10,000 rows before it can return the 20 you actually wanted. As the offset grows, the query gets slower, even though the result set size never changes. On a table that only ever has a few pages, nobody notices. On a table with deep pagination — page 500 of search results, say — it becomes a real problem.
The alternative is keyset pagination:
SELECT * FROM venues WHERE id > 10000 ORDER BY id LIMIT 20;
Instead of counting through rows to skip, this jumps directly to where the last page left off, using the last seen id as the starting point. With an index on id — which a primary key already has by default — this is fast regardless of how deep into the results you are, because it's not scanning and discarding anything.
Deciding what to index on a real table
Say the venues table from earlier in this series is now getting queried in a few different ways: filtering by city, checking is_closed, and occasionally sorting by created_at.
city is a solid candidate — it's a genuine filter condition, likely used often, and has enough distinct values to actually be selective. is_closed is the low-cardinality case from above — a boolean with two values, and indexing it alone probably doesn't help much on its own. created_at, if the table grows into millions of rows and queries often sort by recency, is a reasonable candidate too, potentially even a BRIN index specifically, since timestamps in a table like this tend to be naturally close to sequential as new rows get inserted.
The way I'd actually verify any of this, rather than guessing, is running the real queries through EXPLAIN ANALYZE before and after adding a candidate index, and comparing the plan and the timing directly instead of assuming.
Where this leaves you
An index isn't a free performance upgrade — it's a genuine trade: faster reads for slower writes and more storage, and it's only worth making on columns that are actually queried often enough for the sequential scan to be a real cost. EXPLAIN ANALYZE is the tool that turns this from guesswork into something you can actually verify, and it's worth reaching for before adding or removing any index, not just when something's already obviously slow.
Next up: transactions, ACID, and MVCC — the part of Postgres that explains why concurrent reads and writes mostly don't get in each other's way, and why VACUUM exists at all.
Top comments (1)
Nice walkthrough, and the point that an index is a trade-off rather than a free upgrade is the part most intros skip.
One refinement on the low-cardinality boolean case: it depends on which value you're querying. If
is_closedis true for, say, 0.1% of rows, an index on it does get used forWHERE is_closed, because that value is highly selective. The index only fails to help for the common value. For that skewed case a partial index (CREATE INDEX ON venues (id) WHERE is_closed;) is smaller and cheaper to maintain, since it only contains the rare rows. I checked this on a 200k-row table, andEXPLAINswitched from Seq Scan to Index Scan.On keyset pagination: it needs a unique, stable sort key. If you paginate by something non-unique like
created_at, rows that share a timestamp get skipped or repeated across pages. The fix is a tie-breaker:WHERE (created_at, id) < ($last_created_at, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20, backed by an index on(created_at, id). Postgres handles the row comparison natively and still uses the index.