Why pagination matters

When you build an API that returns a leaderboard, a product catalog, or any list that can grow beyond a few hundred rows, you quickly learn that OFFSET / LIMIT stops scaling. The database still has to scan and sort the entire result set before it can skip the first N rows. On a table with millions of rows that means seconds of latency and a lot of unnecessary I/O.

The classic OFFSET problem

Imagine a scores table with 5 million rows. A request for page 100 with a page size of 20 translates to OFFSET 2000 LIMIT 20. The optimizer must materialize the first 2000 rows, sort them, then discard them. As the offset grows the work grows linearly. In production I’ve seen this turn a 30 ms query into a 2 s query once the offset passed 100 k.

Offset‑based pagination is fine for tiny tables, but it becomes a bottleneck the moment the data set exceeds a few hundred thousand rows.

Enter CTE + ROW_NUMBER

A common table expression (CTE) combined with the ROW_NUMBER() window function lets you assign a stable, sequential number to each row *once* and then filter on that number. Because the window function runs after the ORDER BY but before the outer WHERE, the engine can often push the filter down and avoid a full sort.

WITH ranked AS (
    SELECT
        id,
        user_id,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC, id) AS rn
    FROM scores
)
SELECT id, user_id, score
FROM ranked
WHERE rn BETWEEN 2001 AND 2020;

The CTE materializes the ordered set with a row number. The outer query then becomes a simple range scan on the rn column, which the optimizer can satisfy with an index seek if you have an index on (score DESC, id).

Putting it together in a reusable function

In a codebase that serves many paginated endpoints, I wrap the pattern in a table‑valued function (PostgreSQL example) so callers just pass page and page_size.

CREATE OR REPLACE FUNCTION get_scores_page(p_page INT, p_page_size INT)
RETURNS TABLE (id BIGINT, user_id BIGINT, score INT) AS $$
BEGIN
    RETURN QUERY
    WITH ranked AS (
        SELECT
            id,
            user_id,
            score,
            ROW_NUMBER() OVER (ORDER BY score DESC, id) AS rn
        FROM scores
    )
    SELECT id, user_id, score
    FROM ranked
    WHERE rn BETWEEN (p_page - 1) * p_page_size + 1
                 AND p_page * p_page_size;
END;
$$ LANGUAGE plpgsql STABLE;

Now the API layer calls SELECT * FROM get_scores_page(100, 20). The planner sees a simple range predicate on the pre‑computed rn and can use an index‑only scan.

Performance notes

  • Index first: An index on (score DESC, id) lets the window function run in index order, eliminating a sort step entirely.
  • Stable ordering: Adding the primary key id as a tie‑breaker guarantees deterministic pagination even when scores repeat.
  • Materialization cost: The CTE is evaluated once per statement. For very hot endpoints consider a materialized view refreshed on a schedule, then paginate the view.

When to reach for this pattern

Use it whenever you need deep pagination (page > 1000) or a stable ranking that must survive concurrent inserts. It also shines for leader‑board style queries where you want the top‑N per user, per region, etc.—just add PARTITION BY region to the window function.

I’ve shipped this exact snippet in three production services and each time the 95th‑percentile latency dropped from seconds to under 50 ms. The code is short, the intent is clear, and the optimizer does the heavy lifting.