A SaaS app feels instant with a hundred users and crawls with ten thousand. Nothing in the code changed — the data grew, and queries that scanned a small table now scan a huge one. The fix is almost always indexes, and the gains are dramatic for how little work they take.
What an index actually does
An index is a lookup structure that lets the database find rows without reading the whole table. Without one, finding a user by email in a million-row table means checking up to a million rows. With one, it’s a handful. The slowdown is invisible until the table is big — which is exactly why it surprises growing apps.
Index what you filter and join on
Any column in a WHERE clause, a JOIN, or an ORDER BY on a large table is a candidate. The classic wins are foreign keys (the user_id you join on constantly) and lookup fields like email or slug. Use your database’s EXPLAIN on slow queries — it tells you whether a query uses an index or scans the table. A full table scan on a large table is the signal to add one.
Don’t over-index, though. Every index makes writes slightly slower and uses space, so indexing every column is its own problem. Index what you actually query often. Most “we need a bigger server” fires are really three missing indexes.