Missing pagination anti-pattern

TL;DR

• SELECT * FROM orders without a LIMIT is unbounded. As your table grows, this query eventually returns millions of rows, exhausts application heap, and crashes the process.
• OFFSET-based pagination (LIMIT 20 OFFSET 1000) looks safe but isn't: the database must read and discard 1,000 rows to find the start of your page. At OFFSET 100,000, you've read 100,020 rows to return 20.
• Cursor-based pagination (keyset pagination) uses an opaque cursor (e.g., the last row's ID or timestamp) to fetch the next page directly without scanning skipped rows. It's O(page_size) regardless of page number.
• Missing pagination is the most common cause of "works fine in development, explodes in production" database query bugs.
• Always enforce a maximum page size on API endpoints. A client requesting 10,000 rows per page is a self-inflicted DDoS.

---

The Problem

It's Monday morning. Your admin dashboard has an "export all orders" feature. In development, the orders table has 500 rows. The query runs in 10ms and returns a nicely formatted list. You ship it.

Six months later, production has 12 million orders. A team member clicks the export button. The query runs for 40 seconds, returns a result set of 12 million rows, and the application server runs out of memory and crashes, taking down every other user's requests with it.

I've been on the receiving end of this exact scenario. A single unbounded query from an internal tool took down a production API serving 50,000 users. The fix was adding one word to the query: LIMIT.

The same thing happens with API endpoints that don't paginate. A client calls /api/users expecting a page of users. Your system returns all 8 million users in one response. The API server OOMs. The network transfer takes 3 minutes. The client-side JavaScript freezes rendering 8 million rows.

The bottom line: an unbounded query is a production time bomb. It works fine until it doesn't, and when it fails, it takes everything else down with it.

The failure math

Consider an orders table with 10 million rows, 500 bytes per row:

• Unbounded SELECT *: 10M rows x 500 bytes = ~5 GB result set. No application server has 5 GB of free heap for a single request.
• Network transfer at 100 Mbps: 5 GB takes ~400 seconds to transmit. That's a 7-minute response time.
• Database side: the query holds a read lock (or at minimum, an MVCC snapshot) for the entire duration. Other queries queue behind it.

Even with a connection pool of 20 connections, one unbounded query can consume a connection for minutes. Five users clicking "export all" simultaneously exhausts the entire pool. I've seen a single admin tool query bring down a production database serving 200,000 active users.

---

Why It Happens

Four reasonable-sounding decisions lead to this anti-pattern:

1. "The dataset is small." In development and early production, the query is fast. Nobody adds pagination because there's no performance problem yet. By the time the table hits millions of rows, the unbounded query is buried deep in the codebase and nobody remembers it exists.

2. "The ORM handles it." ORMs like Django, Rails, and Sequelize make it easy to write Model.findAll(). The generated SQL has no LIMIT. Developers trust the ORM without checking the generated query. In my experience, the most dangerous unbounded queries come from ORMs, not hand-written SQL, because the missing LIMIT is invisible.

3. "We use OFFSET, so we're paginated." OFFSET-based pagination looks correct. It works at page 1. It degrades silently at page 5,000. Nobody notices until a power user scrolls deep, an automated tool iterates through all pages, or a search engine crawler tries to index every page.

4. "Internal tools don't need pagination." Admin dashboards, reporting tools, and data export features skip pagination because "only the team uses it." But "the team" includes scripts, cron jobs, and that one person who clicks "export all" on a 12-million-row table.

The common thread: none of these decisions seem wrong at the time. The anti-pattern reveals itself only when the data grows, which is exactly the point when fixing it becomes urgent.

---

How to Detect It

Code smell: Any SQL query or ORM call that returns a list without a LIMIT clause. Search your codebase for .findAll(), .find({}), or SELECT * FROM without LIMIT.

Architecture smell: API endpoints that return arrays without nextCursor or hasMore fields. If the response is just { "items": [...] } with no pagination metadata, the endpoint is unbounded.

ORM audit: Search for common unbounded patterns in your framework: