Efficient pagination

This is the exact file agents read, including the credit header and licence notice. Save it as efficient-pagination/SKILL.md.

---
name: efficient-pagination
description: >
  Design fast, correct SQL pagination that stays quick on deep pages, across PostgreSQL, MySQL and MariaDB, SQLite and SQL Server: keyset (seek) pagination with row-value comparison, the matching index, multi-column and mixed-direction sorts, stable ordering, and when OFFSET is still acceptable. Use it when someone says "pagination is slow on later pages", "OFFSET is slow", "page 500 takes seconds", "infinite scroll keeps duplicating or skipping rows", "how do I paginate a large table", "cursor pagination", "keyset or seek pagination", or "load more is slow". Do NOT use it for the index column-order rules in general (see database-indexing), for diagnosing an unrelated slow query (see query-optimization), for join logic (see sql-joins), or for window-function ranking (see window-functions).
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/efficient-pagination/.
---

# Efficient pagination

## When to use this, and when not to

Use this skill to paginate a large result set so every page is fast, including deep ones, and so pages do not duplicate or skip rows when the data changes underneath. The triggers are slow pagination on later pages, a slow `OFFSET`, an infinite scroll that repeats or drops rows, or a question about cursor, keyset or seek pagination.

Do not use it for the general rules of index column order (that is `database-indexing`, which this skill calls for the supporting index), for diagnosing an unrelated slow query (`query-optimization`), for join logic (`sql-joins`), or for window-function ranking (`window-functions`). The defining feature of this skill is paging through an ordered result by remembering the last row seen, not by counting rows to skip.

The core idea: `OFFSET n` makes the database read and throw away the first `n` rows on every page, so deep pages get slower and slower, and rows shifting between requests make it skip or repeat. Keyset pagination remembers the last row and asks for rows after it, so every page costs the same.

## Inputs the agent must have before starting

1. The SQL engine and version (row-value comparison and `FETCH` syntax differ; see the appendix).
2. The sort order the user sees: the exact columns and directions the list is ordered by. This is what the cursor is built from.
3. Whether that sort is unique. If it is not (sorting by `created_at`, which repeats), you need a unique tiebreaker column, usually the primary key.
4. The page size.
5. Whether the interface needs numbered pages with jump-to-page, or only next and previous (infinite scroll, "load more").
6. The filters applied to the list, since the supporting index must cover them too.

If the sort column is not unique and no tiebreaker is given, ask for one. Without it, keyset pagination can skip or repeat rows at the boundary between pages, and the order is not reproducible.

## The workflow, step by step

### 1. Decide whether OFFSET is good enough

`OFFSET`/`LIMIT` is fine when the table is small, or the user never pages deep (a few pages at most), or you truly need jump-to-arbitrary-page navigation. Decision rule: if the list is large and users scroll or page far, or it is an infinite scroll or "load more", use keyset. If pages stay shallow and numbered jumps are required, `OFFSET` is acceptable; just know it degrades with depth.

If numbered pages are a hard requirement on a large table, you can still cut the cost of `OFFSET` with a deferred join: page over the primary key alone in a subquery, where the deep scan can stay inside a narrow index, then join back for the full row. The skipped rows are then read as thin index entries rather than full heap rows, which is cheaper even though the engine still counts past them:

```sql
SELECT t.*
FROM posts t
JOIN (
    SELECT id FROM posts
    WHERE author_id = :author
    ORDER BY created_at DESC, id DESC
    LIMIT :n OFFSET :off
) k USING (id)
ORDER BY t.created_at DESC, t.id DESC;
```

This keeps page numbers while reading narrow index rows for the skipped portion. It still grows with depth, just more slowly, so it is a halfway house, not a substitute for keyset.

### 2. Fix the sort so it is total and stable

Keyset pagination needs a deterministic order. If the visible sort is not unique, append a unique tiebreaker (the primary key) as the last sort column. `ORDER BY created_at DESC` becomes `ORDER BY created_at DESC, id DESC`. Every column in the cursor comparison must appear in `ORDER BY` in the same order and direction.

### 3. Write the first page with no cursor

The first request has no cursor, so it is just the ordered, limited query. Select the sort columns so the client can send them back as the cursor:

```sql
SELECT id, created_at, title
FROM posts
WHERE author_id = :author           -- any filters
ORDER BY created_at DESC, id DESC
LIMIT :page_size;
```

### 4. Write later pages with a row-value comparison on the last row seen

Each later request carries the last row's sort values. Compare the whole sort tuple at once with a row-value comparison, which is exactly "rows ordered after the last one seen":

```sql
SELECT id, created_at, title
FROM posts
WHERE author_id = :author
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size;
```

The row-value form `(created_at, id) < (:c, :id)` is equivalent to `created_at < :c OR (created_at = :c AND id < :id)`, and it handles the ties in `created_at` correctly. Use `<` for descending order and `>` for ascending.

### 5. Handle mixed sort directions

A row-value comparison only works when every column sorts the same way. For `ORDER BY price ASC, id DESC`, the directions differ, so expand the comparison by hand:

```sql
WHERE price > :last_price
   OR (price = :last_price AND id < :last_id)
ORDER BY price ASC, id DESC
```

### 6. Watch for NULLs in the sort columns

A row-value comparison and `>`/`<` do not behave intuitively when a sort column can be `NULL`, because comparisons with `NULL` are unknown, and `NULL`s sort first or last depending on the engine and the `NULLS FIRST`/`NULLS LAST` clause. Decision rule: build the cursor only from columns that are `NOT NULL`. If you must order by a nullable column, make the order explicit with `NULLS LAST`, keep a `NOT NULL` tiebreaker, and expand the comparison so the `NULL` boundary is handled by hand rather than by a tuple comparison.

### 7. Build the supporting index

The query is fast only if an index matches the filters and the full sort. Build an index on the filter columns followed by the sort columns in the same directions, and hand the column order to `database-indexing`. For the example: `(author_id, created_at DESC, id DESC)`. Without it, the engine still sorts the whole set and the benefit is lost.

### 8. Return the cursor, detect the next page, and support previous

Return the last row's sort values as an opaque cursor: encode the tuple and its direction into a single token (base64 of a small JSON object, for instance) rather than exposing raw column internals, and validate it on the way back in. To know whether a "next" link should appear, fetch one more row than the page size (`LIMIT :n + 1`): if `n + 1` rows come back, there is another page, so drop the extra row and show the first `n`. For a previous-page link, run the query with the comparison and the `ORDER BY` both reversed, take `n` rows, then reverse them back in the application so they display in the original order.

### 9. Deal with the total count separately

Keyset pagination has no page numbers and no natural total. If the interface needs a count, run a separate `COUNT(*)` with the same filters, but on a large table that is itself a slow scan, so cache it, recompute it on a schedule, or show an approximate count. A cheap estimate is available per engine: PostgreSQL exposes `reltuples` in `pg_class` and a planner estimate through `EXPLAIN`, and most engines keep an approximate row count in their catalog. Do not try to compute a total from the paged query itself, and prefer "load more" or "showing results 1 to 50" over an exact total when the table is large.

### 10. Verify

Check that the `ORDER BY` is unique (a tiebreaker is present), the cursor columns match the `ORDER BY` columns and directions exactly, the supporting index exists so the plan is an index scan and not a sort, paging forward then backward returns to the same rows, and no row is skipped or repeated when a row is inserted between two page loads.

## Worked example, end to end

A feed shows posts newest first, 50 per page, filtered by author, on a large `posts` table. The first attempt uses `OFFSET`:

```sql
SELECT id, created_at, title FROM posts
WHERE author_id = :author
ORDER BY created_at DESC
LIMIT 50 OFFSET 4950;     -- page 100
```

On a test of this shape, the deep `OFFSET` page read about 5,000 index entries before returning 50 and took roughly 1.19 ms, while the keyset version below read about 50 and took roughly 0.16 ms; the gap widens as the page number grows (Vlad Mihalcea, SQL seek pagination). `OFFSET` also has a correctness problem: if a new post is inserted while the user pages, the window shifts and a row can repeat or be skipped.

The fix adds a unique tiebreaker and pages by the last row seen:

```sql
-- First page
SELECT id, created_at, title FROM posts
WHERE author_id = :author
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Next page, cursor = last row's (created_at, id)
SELECT id, created_at, title FROM posts
WHERE author_id = :author
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;
```

With the index `(author_id, created_at DESC, id DESC)`, every page is an index scan that reads about 50 rows regardless of depth, and an insert between requests cannot shift a page, so no row repeats or is skipped. The trade-off is that the user cannot jump straight to page 100, which suits an infinite scroll.

In practice the endpoint fetches one extra row, `LIMIT 51`, returns the first 50, and sets a `has_next` flag from whether the 51st row came back. The cursor it returns is the 50th row's sort values, say `('2026-10-12 09:14:02', 88213)`, encoded as an opaque token rather than sent as raw columns. The next request decodes the token back into `:last_created_at` and `:last_id` for the comparison. For a previous-page link, the endpoint runs the mirror query from the first row's values with `>` and ascending order, takes 50 rows, and reverses them in the application so the page still reads newest first. None of these pages ever counts past rows it does not return.

## Variations you will meet in practice

- **Search boxes and filters.** The cursor query must repeat every `WHERE` filter the list applies, and the supporting index must put those filter columns before the sort columns, or the plan falls back to a scan. A cursor is only valid for the filter it was created under: when the user changes the search term or a filter, reset to the first page rather than reusing the old cursor.
- **Three or more sort columns.** The row-value comparison extends naturally when every column shares a direction: `(a, b, c) < (:a, :b, :c)`. With mixed directions, expand it into nested groups, comparing the most significant column first and only moving to the next on equality, with the unique tiebreaker last. Keep the index column order and directions matching the full sort.
- **A sort column the user can edit.** If you page by a mutable value (a "priority" the user reorders, a score that updates), a row can move between pages and appear twice or disappear. Page by an immutable pair such as `(created_at, id)` and order the mutable column only within a page, or state plainly that the order can drift while editing.
- **Rough navigation without page numbers.** When users want to jump around a long list but not to an exact page, offer a seek by the sort key itself, for example a date picker that pages from `created_at <= :chosen`. That is still a keyset seek with a user-supplied cursor, and it stays fast at any depth.

## Templates you can use verbatim

```sql
-- Keyset, single or same-direction multi-column sort (row-value comparison)
-- First page
SELECT cols FROM t
WHERE filters
ORDER BY c1 DESC, id DESC
LIMIT :n;
-- Next page
SELECT cols FROM t
WHERE filters
  AND (c1, id) < (:last_c1, :last_id)   -- '>' for ASC
ORDER BY c1 DESC, id DESC
LIMIT :n;

-- Mixed directions: expand by hand
WHERE (c1 > :last_c1)
   OR (c1 = :last_c1 AND id < :last_id)
ORDER BY c1 ASC, id DESC

-- Previous page: reverse comparison and ORDER BY, then reverse rows in the app
WHERE (c1, id) > (:first_c1, :first_id)
ORDER BY c1 ASC, id ASC
LIMIT :n;

-- Supporting index (hand column order to database-indexing)
CREATE INDEX idx_t_feed ON t (author_id, c1 DESC, id DESC);
```

## Failure modes and anti-patterns

1. **Deep OFFSET on a large list.** Spot it when later pages get slower, or a "page 500" is slow while page 1 is fast. Fix: switch to keyset; the database stops reading and discarding the skipped rows.
2. **No tiebreaker on a non-unique sort.** Spot it when rows near a page boundary are occasionally skipped or repeated, or the order changes between runs. Fix: append the primary key to the `ORDER BY` and to the cursor comparison.
3. **Cursor columns that do not match the ORDER BY.** Spot it when pages overlap or miss rows even with a tiebreaker. Fix: the comparison tuple and the `ORDER BY` must list the same columns in the same order and direction.
4. **Row-value comparison used with mixed sort directions.** Spot it when `(a, b) < (:a, :b)` returns wrong rows because `a` and `b` sort opposite ways. Fix: expand the comparison into the explicit `OR` form.
5. **Expecting totals or jump-to-page from keyset.** Spot it when the UI needs page numbers or an exact total that keyset cannot give. Fix: run a separate, cached `COUNT(*)`, show an approximate total, or move to "load more"; use `OFFSET` only if true random-page access is required.

## Quality bar

- The `ORDER BY` is total: a unique tiebreaker is present wherever the visible sort can repeat.
- The cursor comparison lists the same columns, in the same order and directions, as the `ORDER BY`.
- A supporting index covers the filters and the full sort, and the plan is an index scan with no separate sort.
- Mixed-direction sorts use the expanded `OR` comparison, not a plain row-value one.
- Paging forward and then backward returns the same rows, and an insert between pages neither skips nor repeats a row.
- Totals and page numbers, if needed, come from a separate count, not from the paged query, and the jump-to-page limitation was stated.

## Data handling and safety

Pagination reads data and returns a page at a time, so it is low risk on its own. The cursor should be opaque to the client: encode the last row's sort values rather than exposing raw column internals, and never put secrets or another user's identifiers in it. The pagination query must keep the same `WHERE` filters as the list, including any permission or tenant filter, so that paging cannot walk into rows the user may not see. When a paginated query feeds a bulk action (for example, acting on every row across all pages), that is an irreversible action: show the count and require explicit human confirmation before it runs.

## Appendix: engine-specific notes (verify before relying on these)

- **PostgreSQL**: supports row-value comparison `(a, b) < (:a, :b)` directly, and `FETCH FIRST n ROWS ONLY` or `LIMIT n`. A matching B-tree index (with the columns in the sort's directions) gives an index scan with no sort. Row-value comparison requires the compared columns to share a direction.
- **MySQL and MariaDB**: support row-value (tuple) comparison and `LIMIT n`. InnoDB's clustered primary key makes `id` a natural, cheap tiebreaker. For mixed directions, use the expanded `OR` form; older optimisers handle it better than a tuple comparison in some versions.
- **SQLite**: supports row-value comparison and `LIMIT n`. Confirm the plan with `EXPLAIN QUERY PLAN` shows `SEARCH ... USING INDEX` and no `USE TEMP B-TREE` for the sort.
- **SQL Server**: has no row-value comparison, so use the expanded `OR` form, or `OFFSET ... FETCH` for classic paging. A filtered or covering index on the sort columns supports the seek.

## Sources

- Use The Index, Luke (Markus Winand), "No offset": why `OFFSET` reads and discards skipped rows and drifts when data changes, and the seek pattern `WHERE ... AND id < :last ORDER BY id DESC FETCH FIRST n ROWS ONLY`, and that it cannot jump to an arbitrary page. Verified 30 October 2026.
- Vlad Mihalcea, "SQL Seek / Keyset Pagination": the row-value comparison `(sort_col, id) < (:c, :id)` and its expansion to `created_at < :c OR (created_at = :c AND id < :id)`, the matching composite index `(created_at DESC, id DESC)`, the first-page versus later-page forms, and the measured deep-page comparison (about 5,000 index entries at roughly 1.19 ms for OFFSET versus about 50 rows at roughly 0.16 ms for keyset). Verified 30 October 2026.
- SQLite documentation, "EXPLAIN QUERY PLAN": confirming a paginated query uses `SEARCH ... USING INDEX` and does not fall back to `USE TEMP B-TREE` for the sort. Verified 30 October 2026.
- Existing agent-skill implementations reviewed for the keyset pattern and its index (community SQL and query-optimization SKILL.md files): the `(created_at, id) < (?, ?)` cursor form with a supporting index and a stable ordering. Verified 30 October 2026.