Missing Pagination

Introduction

Missing pagination is the practice of letting a list query or list endpoint return an unbounded result set. It may look harmless while the dataset is small, but the query's memory, network, and database work grow with every row added.

Mental model: every list response spends a finite budget of database work, application memory, network bytes, and client rendering time. Pagination makes that budget explicit and keeps one request from consuming the whole system.

TL;DR

• SELECT * FROM orders without a LIMIT is unbounded. As your table grows, this query can return millions of rows, exhaust application heap, and crash 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. With a matching index, its work can stay roughly proportional to page_size as page depth grows.
• Missing pagination is a common cause of "works fine in development, explodes in production" database query bugs.
• Enforce a maximum page size on API endpoints. A client requesting 10,000 rows per page can create a self-inflicted DDoS.

---

What It Is

Before: an unbounded list response

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.

A single unbounded query from an internal tool can take down a production API serving 50,000 users. Adding a LIMIT is an immediate guardrail for the minimal case, while large exports usually need streaming or an asynchronous job.

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 risk that may stay hidden until the table grows. When it fails, it can also consume resources needed by unrelated requests.

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. A single request should not assume that an application server has 5 GB of free heap available.
• Network transfer at 100 Mbps: 5 GB takes ~400 seconds to transmit. That's a 7-minute response time.
• Database side: the query may retain an MVCC snapshot and resources for the entire duration; exact lock behaviour depends on the database engine and isolation level.

Even with a connection pool of 20 connections, one unbounded query can consume a connection for minutes. Five users clicking "export all" simultaneously can exhaust the entire pool and affect 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, and the missing bound is easy to overlook because it is hidden behind the API.

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, hasMore, or another documented bound. If the response is just { "items": [...] } with no pagination metadata or hard cap, the endpoint may be unbounded.

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