The list endpoint that fetches every row works fine, until the table grows

A "list all the things" endpoint is usually the first route written for any resource, and it usually looks like this:

app.get('/api/orders', async (req, res) => {
  const orders = await db.query('SELECT * FROM orders')
  res.json(orders)
})

With ten test orders in a local database, this returns instantly. It stays in the codebase unchanged as the table grows, because nothing about it breaks. It just gets slower, one row at a time, until the day it doesn't.

Correct at ten rows is not the same claim as correct at scale

At ten thousand rows, this endpoint is already doing more work than it needs to on every request: scanning the whole table, serializing every row to JSON, and sending a response body that no client actually renders in full. At a few hundred thousand rows, that same query is now a meaningful chunk of database CPU and memory per request, and if it's hit by more than one client at a time, those costs stack. A dashboard that polls this endpoint every few seconds, or a script that calls it in a loop, doesn't need to be malicious to take the database down. It just needs to exist.

This is the same resource exhaustion shape as an unbounded request body: nothing about the code is wrong in the sense of returning incorrect data, and no test written against a small dataset will ever catch it, because the failure only exists as a function of table size, and table size is exactly the variable that's smallest during development and largest in production.

Paginate the query, not just the response

app.get('/api/orders', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 50, 100)
  const cursor = req.query.cursor || null

  const orders = await db.query(
    'SELECT * FROM orders WHERE id > ? ORDER BY id ASC LIMIT ?',
    [cursor || 0, limit + 1]
  )

  const hasMore = orders.length > limit
  const page = hasMore ? orders.slice(0, limit) : orders

  res.json({
    orders: page,
    nextCursor: hasMore ? page[page.length - 1].id : null,
  })
})

Fetching every row and slicing the array in application code is a common half-fix that misses the point entirely: the database still does the full scan before your code ever gets to .slice() it. The LIMIT has to be in the SQL, not applied afterward to an array that's already fully materialized in memory.

Cursor-based pagination (WHERE id > ?) scales better than OFFSET-based pagination for large tables, because OFFSET 50000 still requires the database to walk past 50,000 rows to discard them before returning the next page, while a cursor comparison uses the index directly regardless of how deep into the table you are.

Cap the limit the client is allowed to ask for

Math.min(parseInt(req.query.limit) || 50, 100) matters as much as the LIMIT clause itself. Pagination that trusts a client-supplied page size is pagination in name only: ?limit=999999 gets you back to the original unbounded query, just with an extra query parameter making it look intentional.

Check the routes that "just export everything" too

CSV exports, admin reports, and search-with-no-filters endpoints tend to get written with the same "fetch it all" instinct, often because pagination feels like it complicates a feature that's meant to hand back a complete dataset. Streaming the response, or paginating with a much larger page size than the interactive API uses, both work here. An unbounded SELECT * doesn't, no matter which route it's attached to.

Seaworthy flags list endpoints backed by a query with no LIMIT clause and no bound on client-supplied page size.