At some point in every junior developer's career, a query that ran fine on your laptop turns into a query that times out in production. You didn't change the logic. You didn't change the data model. The only thing that changed is the data got big.
Nine times out of ten, the fix is an index. This post is the explanation I wish someone had given me instead of "just add an index, it'll be fine."
The problem: full table scans
Say you've got an orders table with 50 million rows, and you run this:
SELECT *
FROM orders
WHERE customer_id = 48213;
Without an index on customer_id, the database has no way to know where rows for customer 48213 live. So it does the only thing it can do: it reads every single row in the table, checks whether customer_id matches, and keeps the ones that do. That's called a full table scan.
On a 500-row table, a full scan is instant and nobody notices. On a 50-million-row table, it can mean seconds or minutes instead of milliseconds — which is exactly the kind of thing that turns into a Slack message asking why the dashboard is frozen.
What an index actually is
An index is a separate data structure — most commonly a B-tree — that stores a sorted copy of one or more columns, along with a pointer back to where the full row lives on disk.
The usual analogy is a phone book. If you want everyone with the last name "Smith," you don't read the book cover to cover — you flip straight to the S section because the names are sorted. An index gives your database the same shortcut: instead of scanning every row, it can jump straight to the matching entries and then go fetch just those rows.
Once you add an index:
CREATE INDEX idx_orders_customer
ON orders (customer_id);
the same SELECT query above no longer needs a full scan. The database checks the index, finds exactly which rows have customer_id = 48213, and reads only those. In Postgres and MySQL, this happens automatically — the query planner picks up the new index the next time that column is used in a WHERE, JOIN, or ORDER BY clause, no code changes needed on your end.
Clustered vs. non-clustered indexes
In most row-oriented relational databases (Postgres, MySQL, SQL Server), you'll run into two flavors:
Clustered index — this determines the physical order the table's rows are stored in on disk. Because the data can only be physically sorted one way, a table can have exactly one clustered index. It's usually built on the primary key.
Non-clustered index — a separate structure, stored apart from the table, that holds a sorted copy of the indexed column(s) plus a pointer back to the actual row. A table can have several non-clustered indexes, which is what lets you optimize for more than one type of lookup.
A practical way to think about it: the clustered index is the order the book's pages are physically bound in. A non-clustered index is more like the index at the back of the book — a separate list that tells you which page to flip to.
Indexes are not free
It's tempting, once you learn this trick, to start indexing everything. Don't. Every index comes with real costs:
-
Writes get slower. Every
INSERT,UPDATE, orDELETEhas to update not just the table, but every index on that table too. Five indexes means five extra structures to maintain on every write. - Storage goes up. An index is a real, separate structure taking up real disk space — sometimes a significant fraction of the table's own size.
-
Low-cardinality columns rarely benefit. An index on a boolean column (like
is_active) usually doesn't help much, because there are only two possible values — the database still ends up reading a large chunk of the table either way.
A reasonable rule of thumb: index the columns you actually filter, join, or sort on frequently in real queries — not every column "just in case." If you're not sure, look at your slow query logs or EXPLAIN output before adding an index, not after.
The Snowflake exception
This is the part that trips people up if they learned SQL on Postgres or MySQL and then moved to Snowflake (which is the stack this blog mostly covers): Snowflake does not have traditional indexes. There's no CREATE INDEX statement in Snowflake at all.
Instead, Snowflake automatically breaks every table into micro-partitions — contiguous chunks of roughly 50–500MB of uncompressed data — and stores metadata about the min/max values in each one. When you run a query with a filter, Snowflake uses that metadata to skip micro-partitions that can't possibly contain matching rows, a process called pruning. You get a lot of the same benefit as an index (skip the data you don't need) without creating or maintaining anything yourself.
For very large tables where Snowflake's natural micro-partitioning isn't lining up well with how you query the data, you can define a clustering key to influence how the data gets physically organized — but that's a different mechanism solving a similar problem, not an index. (We covered clustering keys in an earlier post if you want the deeper dive.)
The short version: learn indexes for Postgres/MySQL and interviews — plenty of systems still use them, and understanding the concept makes you a better engineer regardless of platform. Just don't go looking for CREATE INDEX in a Snowflake worksheet.
Recap
- No index means a full table scan on every query that filters on that column.
- An index is a sorted structure that points back to the real rows — it trades a bit of write overhead for much faster reads.
- Clustered indexes sort the table itself (one per table); non-clustered indexes are separate lookup structures (you can have several).
- Indexes aren't free — they slow down writes and cost storage, so index what you actually query on, not everything.
- Snowflake skips traditional indexing entirely in favor of automatic micro-partitioning, with clustering keys as the manual lever for huge tables.
If you found this useful, I post daily breakdowns like this on @techqueen.codes on Instagram — SQL, Python, and Snowflake, explained for junior developers. Drop a comment with the slowest query you've ever had to debug; I might turn it into the next post.
Top comments (1)
tr.ee/dev-to