{"course":{"slug":"building-apps-with-ai","title":"Building Apps with AI","url":"https://addaly.com/learn/building-apps-with-ai","level":"beginner","track":"build"},"lesson":{"slug":"when-the-data-gets-big-enough-to-notice","title":"When the data gets big enough to notice","url":"https://addaly.com/learn/building-apps-with-ai/when-the-data-gets-big-enough-to-notice","position":57,"of":87,"minutes":8,"answer":"A page that was instant at a hundred rows and takes thirty seconds at a hundred thousand is usually one query per row or a missing index.","keyIdea":"A page that was instant at a hundred rows and takes thirty seconds at a hundred thousand is usually one query per row or a missing index, and both are found by counting queries and reading the plan."},"body":{"format":"markdown","text":"## The cliff\n\nGenerated 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.\n\nThree causes account for nearly all of it, and each is found in minutes once you know to look.\n\n## One query per row\n\n```typescript\nconst list = await db.select().from(bookings);           // 1 query\nfor (const b of list) {\n  b.customer = await db.select().from(users).where(eq(users.id, b.userId));  // 1 per row\n}\n```\n\nThat 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.\n\nYou 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\".\n\n## The missing index\n\nA 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.\n\nAn 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.\n\nThe database will tell you which it is doing:\n\n```sql\nEXPLAIN ANALYZE SELECT * FROM bookings WHERE user_id = '9f3c...';\n```\n\n`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.\n\nIndexes cost write speed and space; do not index everything. Index what queries filter on, and let `EXPLAIN` settle arguments.\n\n## Fetching everything\n\n`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.\n\nAnd 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.\n\n## The connection limit\n\nServerless 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.\n\n## Numbers to keep in your head\n\nA 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.\n\n## Test it before users do\n\nThe 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."},"check":{"question":"A list of 500 bookings takes eight seconds to load. The server log shows 501 SQL queries for the one page. What is happening?","options":[{"text":"One query fetched the bookings and then the code ran a separate query for each booking's customer, so the page cost grows with the row count.","correct":true},{"text":"The database is under-provisioned for this many rows and needs a paid tier with more memory and a faster disk before the page will be quick again.","correct":false,"tempting":"Five hundred tiny queries are slow because of five hundred round trips, not because any one of them strains the database."},{"text":"The bookings table has no primary key.","correct":false,"tempting":"A missing primary key slows lookups; it does not multiply the number of queries."},{"text":"The ORM is logging too much and the logging itself is slow.","correct":false,"tempting":"Logging revealed the count; it did not cause it."}]},"prev":{"slug":"the-database-and-the-agent","title":"The database and the agent","url":"https://addaly.com/learn/building-apps-with-ai/the-database-and-the-agent"},"next":{"slug":"the-key-never-goes-to-the-browser","title":"The key never goes to the browser","url":"https://addaly.com/learn/building-apps-with-ai/the-key-never-goes-to-the-browser"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'When the data gets big enough to notice', in Building Apps with AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/building-apps-with-ai/when-the-data-gets-big-enough-to-notice (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"When the data gets big enough to notice","courseTitle":"Building Apps with AI","module":{"index":6,"slug":"the-parts-that-persist","title":"The parts that persist: data, accounts and files"},"answer":null,"keyIdea":"A page that was instant at a hundred rows and takes thirty seconds at a hundred thousand is usually one query per row or a missing index, and both are found by counting queries and reading the plan.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-cliff","runs":[{"text":"The cliff"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Three causes account for nearly all of it, and each is found in minutes once you know to look."}]},{"t":"h2","id":"one-query-per-row","runs":[{"text":"One query per row"}]},{"t":"code","lang":"typescript","label":"TypeScript","text":"const list = await db.select().from(bookings);           // 1 query\nfor (const b of list) {\n  b.customer = await db.select().from(users).where(eq(users.id, b.userId));  // 1 per row\n}"},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"You find it by counting. Turn on query logging in the ORM ("},{"text":"log: [\"query\"]","code":true},{"text":" in Prisma, "},{"text":"logger: true","code":true},{"text":" 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”."}]},{"t":"h2","id":"the-missing-index","runs":[{"text":"The missing index"}]},{"t":"p","runs":[{"text":"A query with "},{"text":"WHERE user_id = ?","code":true},{"text":" on a table with no index on "},{"text":"user_id","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"The database will tell you which it is doing:"}]},{"t":"code","lang":"sql","label":"SQL","text":"EXPLAIN ANALYZE SELECT * FROM bookings WHERE user_id = '9f3c...';"},{"t":"p","runs":[{"text":"Seq Scan on bookings","code":true},{"text":" means it read the whole table. "},{"text":"Index Scan using bookings_user_id_idx","code":true},{"text":" means it used the index. The columns that need one are any column in a "},{"text":"WHERE","code":true},{"text":", any foreign key, and any column you sort by. Generated schemas often omit them, because the query works either way at forty rows."}]},{"t":"p","runs":[{"text":"Indexes cost write speed and space; do not index everything. Index what queries filter on, and let "},{"text":"EXPLAIN","code":true},{"text":" settle arguments."}]},{"t":"h2","id":"fetching-everything","runs":[{"text":"Fetching everything"}]},{"t":"p","runs":[{"text":"SELECT *","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"And a page that renders every row cannot survive growth at all. Pagination is the answer, and there are two kinds. Offset pagination ("},{"text":"LIMIT 50 OFFSET 5000","code":true},{"text":") 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."}]},{"t":"h2","id":"the-connection-limit","runs":[{"text":"The connection limit"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"numbers-to-keep-in-your-head","runs":[{"text":"Numbers to keep in your head"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"test-it-before-users-do","runs":[{"text":"Test it before users do"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A list of 500 bookings takes eight seconds to load. The server log shows 501 SQL queries for the one page. What is happening?"}],"options":[{"text":[{"text":"One query fetched the bookings and then the code ran a separate query for each booking’s customer, so the page cost grows with the row count."}],"correct":true,"tempting":null},{"text":[{"text":"The database is under-provisioned for this many rows and needs a paid tier with more memory and a faster disk before the page will be quick again."}],"correct":false,"tempting":[{"text":"Five hundred tiny queries are slow because of five hundred round trips, not because any one of them strains the database."}]},{"text":[{"text":"The bookings table has no primary key."}],"correct":false,"tempting":[{"text":"A missing primary key slows lookups; it does not multiply the number of queries."}]},{"text":[{"text":"The ORM is logging too much and the logging itself is slow."}],"correct":false,"tempting":[{"text":"Logging revealed the count; it did not cause it."}]}],"order":[1,2,0,3],"why":null},"prev":{"slug":"the-database-and-the-agent","title":"The database and the agent","minutes":8},"next":{"slug":"the-key-never-goes-to-the-browser","title":"The key never goes to the browser","minutes":8}}}