Why pagination hurts

Most of us have shipped an API that returns a page of results from a table with millions of rows. The naïve approach — SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 100000 — works fine on a few thousand rows but collapses once the offset grows. The engine still has to scan and sort the first 100,020 rows before discarding 100,000 of them. I’ve seen production latency jump from 30 ms to several seconds just because a client asked for page 5000.

The CTE + window function pattern

A common table expression (CTE) combined with a window function lets you compute a stable row number once, then filter on that number. The trick is to materialise the ordering in a CTE, assign ROW_NUMBER(), and finally select the slice you need. Because the row number is calculated in a single pass, the optimizer can push the filter down and avoid the massive offset scan.

WITH ordered AS (
    SELECT
        id,
        customer_id,
        total,
        created_at,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM orders
    WHERE status = 'completed'
)
SELECT id, customer_id, total, created_at
FROM ordered
WHERE rn BETWEEN 100001 AND 100020;

Notice the WHERE clause inside the CTE. It reduces the working set before the window function runs, which is crucial when you have a selective predicate like a status filter.

Putting it together

In a real service you’ll usually expose page and page_size parameters. Translating those to the BETWEEN bounds is straightforward:

-- pseudo‑code for the service layer
page      := 5001;
page_size := 20;
lower     := (page - 1) * page_size + 1;   -- 100001
upper     := page * page_size;             -- 100020

Then you bind lower and upper as parameters in the final query. Using parameters (instead of string interpolation) keeps the plan cache happy and prevents SQL injection.

Performance notes

  • Index support: An index on (status, created_at DESC) lets the engine satisfy the ordering without a separate sort step.
  • Partitioned tables: If you partition by month, the CTE still works; the planner will prune partitions based on the status predicate.
  • Memory: Window functions need a sort buffer. For very wide rows consider selecting only the columns you need in the CTE, then joining back to the base table for the final projection.

Tip: If you only need the primary keys for the page, run the CTE on the PK column alone, then join to fetch the full rows. This reduces the sort width dramatically.

When to reach for it

Use this pattern whenever you have:

  1. Large result sets that require deep pagination.
  2. A stable ordering column (timestamp, auto‑increment id, etc.).
  3. Optional filters that can be applied before the window function.

It’s not a silver bullet — keyset pagination (a.k.a. seek method) is still faster for infinite‑scroll UIs because it avoids the ROW_NUMBER() calculation entirely. But for traditional page‑number navigation, the CTE + window function approach gives you predictable, index‑friendly performance with only a few lines of SQL.

Next time you see a query with a huge OFFSET, try rewriting it with a CTE and ROW_NUMBER(). You’ll often cut latency by an order of magnitude and keep the query plan simple enough for the optimizer to do its job.