Your query was instant at launch.
At 50,000 rows it takes 8 seconds.

I'm a senior backend tech lead and I run Belmo, a small European PaaS. I'll mention it once near the end with a clear disclosure. Everything before that is plain Postgres and works on any host, any framework, and any database you already have running.

The app was fast on launch day. Of course it was. You had forty rows in every table, and Postgres can read forty rows faster than it can finish parsing your query. Then you got users, and the rows piled up, and one day a page that used to load instantly sits there spinning for eight seconds. You did not change the code. You did not get a traffic spike. The data just got bigger, and a query that was always doing the wrong thing finally got large enough for the wrong thing to hurt.

This is the single most common performance problem I see in apps built fast with Claude Code, Cursor, or Lovable, and it is almost never about traffic. It is about a missing index. The generated code wrote a perfectly reasonable WHERE clause, the database had no index to satisfy it, and so Postgres did the only thing it could: it read every row in the table to find the ones you asked for. That works fine at forty rows and falls over at fifty thousand. The fix is usually one line, but you have to know which line, and that is the part nobody hands you.

Why a query gets slower with no code change

A database query has two ways to find rows. It can walk the entire table from top to bottom and check each row against your condition, which is called a sequential scan, or it can use an index, which is a sorted lookup structure that jumps more or less straight to the rows you want. The difference is the difference between flipping through every page of a book looking for a word and using the index at the back.

At small sizes, the sequential scan wins, and this trips people up. Postgres is smart enough to know that reading forty rows in one pass is faster than bouncing through an index, so it does the scan, and your query is fast. As the table grows, the cost of that full scan grows with it, linearly, while the cost of an indexed lookup barely moves. There is a crossover point where the scan becomes the slow option, and your app crosses it without any deploy, any alert, any obvious cause. The query that "suddenly" got slow was always O(n). You just hadn't paid the n yet.

The reason indexes are not free, and the reason your database does not just index everything, is that every index has to be kept up to date on every write. An index speeds up reads and slows down inserts, updates, and deletes, because each one now has to maintain the index too. So indexing is a trade, reads for writes, and the skill is putting them exactly where the reads are and nowhere else.

Find the slow query before you guess

Do not guess which query is slow. Developers are reliably wrong about this, myself included, and the query you suspect is rarely the one burning the time. Postgres will tell you the truth if you ask it.

The single most useful tool here is EXPLAIN ANALYZE. You put it in front of any query and Postgres runs it and reports exactly how it found the rows and how long each step took.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4218;

The output is dense, but you are looking for one word. If you see Seq Scan on orders, Postgres read the whole table. If you see Index Scan using ..., it used an index. On a slow query, the Seq Scan line will also show a large actual time and a row count close to the size of your whole table, which is the tell: it looked at everything to return a handful of rows.

Seq Scan on orders  (cost=0.00..1843.00 rows=12 width=64)
                    (actual time=0.214..58.343 rows=12 loops=1)
  Filter: (customer_id = 4218)
  Rows Removed by Filter: 89988

Read that Rows Removed by Filter line out loud. Postgres looked at ninety thousand rows and threw away all but twelve. That is ninety thousand reads to answer a question that touches twelve. That is the missing index, in plain text.

If you want to find these without checking each query by hand, turn on the slow query log. Set log_min_duration_statement to something like 500 milliseconds and Postgres writes every query slower than that to its log, with the actual parameters. After a day of real traffic you have a ranked list of your worst offenders, sorted by the thing that matters, which is wall-clock time in production, not your guess.

The fix is usually one line

Once you know the column, the index is a single statement.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Run EXPLAIN ANALYZE again and the Seq Scan becomes an Index Scan, and the eight seconds becomes single-digit milliseconds. That is the whole loop: measure, index the column in the WHERE clause, measure again to confirm it actually got used. Always confirm, because Postgres does not always use an index even when one exists, and the only way to know is to look.

A few rules cover most cases. Index the columns you filter on in WHERE, the columns you join on (foreign keys especially, because ORMs generate joins on them constantly and Postgres does not auto-index foreign keys for you), and the columns you sort by in ORDER BY when the sort shows up as a cost in your plan. Those three places are where almost all the time goes.

There is one trap worth one warning. On a large table that is live in production, plain CREATE INDEX locks the table against writes while it builds, which means your app cannot insert or update those rows until it finishes, which on a big table can be minutes. Use the concurrent form instead, which builds the index without taking that lock:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

It is slower to build and it cannot run inside a transaction, but it does not freeze your app, and on a production table that is the version you want.

Multi-column indexes and the order that matters

When you filter on two columns together, a single index over both can beat two separate ones, but the column order inside the index is not arbitrary. An index on (status, created_at) can serve a query that filters on status alone, or on status and created_at together, because it is sorted by status first. It cannot efficiently serve a query that filters on created_at alone, because created_at is only sorted within each status group. The rule of thumb: put the column you filter for equality first, and the column you filter as a range or sort by second.

-- Serves: WHERE status = 'paid'
-- Serves: WHERE status = 'paid' AND created_at > '2026-01-01'
-- Does NOT efficiently serve: WHERE created_at > '2026-01-01'
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

This is the part that the generated code will not do for you, because it requires knowing how your app actually queries, which is a thing only you know. Look at your real WHERE clauses, group the ones that travel together, and build the composite index in the order that matches.

When an index does nothing, and the other slow things

Sometimes you add the perfect index and the query is still slow, and EXPLAIN ANALYZE still shows a sequential scan. The usual reason is that you wrapped the column in a function, and an index on the raw column cannot help once you transform it. WHERE lower(email) = '[email protected]' cannot use an index on email, because the index stores the original values, not the lowercased ones. You either index the expression itself with CREATE INDEX ... ON users (lower(email)) or you store the value already normalized. Same problem with leading wildcards: LIKE '%term' cannot use a normal index because there is no sorted prefix to jump to.

Indexes also are not the only cause of slowness, and it is worth naming the others so you do not index your way into a wall. The N+1 query, where your code runs one query to fetch a list and then one more query per item in a loop, will flood your database with hundreds of fast queries that add up to a slow page, and no index fixes that. The fix there is to fetch in one query or use your ORM's eager-loading. And a query that genuinely returns fifty thousand rows is slow because it returns fifty thousand rows, so paginate it rather than indexing it. Reach for EXPLAIN ANALYZE first precisely so you spend your effort on the real cause instead of the assumed one.

What I built

I run Belmo, a small European PaaS, and the reason indexing is on my mind is that the log monitoring I built keeps surfacing the same shape of problem in the logs of customer apps: query latency that creeps up week over week with no deploy attached, the same endpoint getting slower as a table fills, the pattern of a sequential scan quietly scaling with the data. That is the disclosure, that is the one mention. Belmo can flag the climb in your logs and alert you over Telegram before a user emails you about it, but it cannot write the index, because only you know which queries matter to your product and how your app actually filters. The platform can point at the creeping slowness. Choosing the right index, and deciding it is worth the write cost, stays your call.

What to do tonight, regardless of your platform

1. Turn on the slow query log. Set log_min_duration_statement to 500 milliseconds and let it run through a normal day. You want a list of your actual worst queries ranked by real production time, not a hunch.
2. Take your single slowest query and run EXPLAIN ANALYZE on it. Look for Seq Scan and for a Rows Removed by Filter number that dwarfs the rows you actually wanted. That gap is your proof.
3. Add an index on the column in that query's WHERE clause, using CREATE INDEX CONCURRENTLY if the table is live and large. Then run EXPLAIN ANALYZE again and confirm the plan switched to an index scan. Never assume it worked, check.
4. Index your foreign keys. Postgres does not do this automatically, and your ORM joins on them constantly, so this one change often speeds up pages you did not even know were slow.
5. For queries that filter on two columns at once, build one composite index and put the equality column first, the range or sort column second. Match the order to how you actually query.
6. Scan your slow queries for functions wrapped around columns and for LIKE '%...' leading wildcards, because those silently defeat indexes, and fix them with expression indexes or normalized columns.
7. Resist indexing everything. Each index taxes every write, so add them where the slow query log tells you to and nowhere else. More indexes is not a strategy, just a different bill.

One open question

The part I keep going back and forth on is when to stop. You can index your way to fast reads right up until your write-heavy tables start to feel the tax, and there is no clean universal answer for where that line sits, because it depends on your read-to-write ratio, which is a product fact, not a database fact. On a table that is read constantly and written rarely, I will happily stack several indexes. On a high-churn table that is mostly inserts, I get stingy fast and I would rather paginate and restructure the query than add a fourth index that every insert now has to maintain.

So I will put it to people running these apps for real. How do you decide an index is not worth it? Do you watch write latency and back off when it climbs, do you set a hard cap on indexes per table, or do you just add them reactively when the slow query log complains and never think about the write cost until it bites? I suspect most small teams, mine included, are more reactive here than they would admit, and I would like to hear from anyone who has a discipline that actually holds up.

Related reads on this blog: Your Postgres will die at 50 concurrent users, not 50,000. Here's the connection pooling guide nobody handed you. · Your next migration is going to lock the users table and take the app down. Here is how to ship schema changes nobody notices. · Your indie SaaS has zero working Postgres backups. Here's the 20-minute fix (and the drill you need to run before you sleep tonight).