When the data gets big enough to notice

The cliff

Generated code is written and tested against forty rows. Everything is instant, because at forty rows everything is instant, including the approaches that fail at forty thousand. The failure arrives months later, gradually and then suddenly: a page that takes two seconds, then eight, then times out. Nothing changed in the code. The data grew.

Three causes account for nearly all of it, and each is found in minutes once you know to look.

One query per row

const list = await db.select().from(bookings);           // 1 query
for (const b of list) {
  b.customer = await db.select().from(users).where(eq(users.id, b.userId));  // 1 per row
}

That is the N+1 problem, and ORMs make it invisible because the second line looks like a property access. Five hundred rows is 501 round trips to the database, each a few milliseconds, together several seconds. Ten thousand rows is a timeout.

You find it by counting. Turn on query logging in the ORM (log: ["query"] in Prisma, logger: true in Drizzle) load the page once, and count the lines. A page needing more than a handful of queries has one of these somewhere. The fix is one query with a join, or the ORM’s include or with syntax, which fetches the related rows in a single statement. Ask the agent for exactly that: “this page runs 501 queries; make it run one”.

The missing index

A query with WHERE user_id = ? on a table with no index on user_id reads every row in the table to find the matching ones. At a thousand rows that takes a millisecond. At a million it reads a million rows, every time, for every user.

An index is a sorted copy of one column with pointers back to the rows, kept up to date automatically. With it, the same query jumps to the right place in the sorted copy and reads only the rows that match.

The database will tell you which it is doing:

EXPLAIN ANALYZE SELECT * FROM bookings WHERE user_id = '9f3c...';

Seq Scan on bookings means it read the whole table. Index Scan using bookings_user_id_idx means it used the index. The columns that need one are any column in a WHERE, any foreign key, and any column you sort by. Generated schemas often omit them, because the query works either way at forty rows.

Indexes cost write speed and space; do not index everything. Index what queries filter on, and let EXPLAIN settle arguments.

Fetching everything

SELECT * on a table with a text column holding long descriptions moves every description across the network on every list page, whether or not the page shows it. Select the columns the page needs.

And a page that renders every row cannot survive growth at all. Pagination is the answer, and there are two kinds. Offset pagination (LIMIT 50 OFFSET 5000) is simple and gets slower as the offset grows, because the database still has to count past the skipped rows. Cursor pagination (“give me 50 rows after the one with id X, ordered by created_at”) is constant speed at any depth, and is what infinite-scroll feeds use. Offset is fine for an admin table; cursor for anything users scroll through.

The connection limit

Serverless code that opens a new connection per request meets the free tier’s connection limit (often 20 to 100) at the first burst of traffic, and the errors say “too many connections” or “remaining connection slots are reserved”. This is the pooled connection string from the first lesson in this block. If the app was built without it, the fix is to change one environment variable.

Numbers to keep in your head

A single indexed lookup in Postgres: under a millisecond. A round trip from a serverless function to the database: 1 to 5 ms in the same region, 50 to 150 ms across an ocean, which is why the database and the app should be in the same region and why 500 round trips hurt. A page should need fewer than ten queries. A table under a million rows with the right indexes is not a scaling problem for anything in this course.

Test it before users do

The seed script from earlier can produce a hundred thousand rows in a loop. Do that once on the development database, open every list page, and watch the query log. The pages that fall over are the ones to fix now, in an afternoon, rather than during the week the app gets attention and every page is slow at once.