Keyset Pagination Quick Reference

Available since: v3.6.0 (base scan path), v3.12.0 (row-constructor (a, b) < (x, y), Top-K, storage-level OFFSET pushdown). Verified against: v3.19.1.

UVP

OFFSET 10000 reads 10000 rows you’ll throw away. Keyset pagination passes the last row seen instead — WHERE (created_at, id) < ($1, $2) — and the engine descends straight to the next page. On a 1M-row table HeliosDB Nano returns a 20-row page in ~30 µs (334× faster than PostgreSQL 13) because the row-constructor compare planner-rewrites to a btree range seek and Top-K caps the heap at the LIMIT. Same SQL, same connection, same client — just trade OFFSET for a tuple.

The Pattern

SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ($1, $2)        -- pass last row's (created_at, id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

For the first page, drop the WHERE. For each subsequent page, pass the last row of the previous page:

page_1 = SELECT … LIMIT 20                          -- no WHERE
last_row = page_1[-1]                               -- (created_at, id)
page_2 = SELECT … WHERE (created_at, id) < (last_row.created_at, last_row.id)
                  ORDER BY created_at DESC, id DESC LIMIT 20

Why Keyset Beats OFFSET

Approach	Cost shape	1M rows, page 5000	Page-bookmark stable?
`LIMIT 20 OFFSET 100000` (pre-3.12)	O(N) materialise + skip	~80 ms	No (drift on insert)
`LIMIT 20 OFFSET 100000` (v3.12+ pushdown)	O(N) RocksDB iterator skip, no decode	~5 ms	No (drift on insert)
Keyset `WHERE (a, id) < (…, …)`	O(log N) seek + 20 rows	~30 µs	Yes

Keyset wins on:

Speed — bounded work per page, regardless of depth.
Stability — concurrent INSERTs at the head don’t shift your cursor.
Memory — no materialised buffer.

The 334× number is from tests/pagination_tests.rs against PostgreSQL 13 on the same hardware.

Composite Keys (the General Form)

Pagination keys must be unique and ordered. Use (timestamp, id) — the timestamp gives you the desired sort order, the id breaks ties.

-- Descending by created_at, ties broken by id
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Ascending
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;

The row-constructor (a, b) < (x, y) is evaluated lexicographically by evaluate_tuple_compare (src/sql/evaluator.rs). Equivalent to:

WHERE a < x OR (a = x AND b < y)   -- but written cleaner

Both forms work. Prefer the tuple form — the planner recognises it as a single predicate.

Top-K Rewrite (Free Speedup)

Limit(Sort(…)) is rewritten to TopK k=LIMIT automatically (src/sql/executor/topk.rs, v3.12.0). Memory drops from O(N) to O(k), time from O(N log N) to O(N log k). No hint needed — kicks in whenever LIMIT has a concrete bound.

EXPLAIN ANALYZE
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20;

 Limit (Top-K)  k=20
   ->  TopK
         Sort Key: created_at DESC
         ->  Seq Scan on articles

Storage-Level OFFSET Pushdown (When You Must Use OFFSET)

If you can’t restructure to keyset (because the client is a generated SDK, e.g.), v3.12 added scan_table_with_offset_limit which skips offset rows at the RocksDB iterator — no bincode decode, no decrypt, no dictionary resolve.

EXPLAIN ANALYZE
SELECT * FROM articles ORDER BY id LIMIT 5 OFFSET 990;

 Limit
   ->  Seq Scan (offset-pushdown)  actual rows=5
         OFFSET-skipped: 990

The actual rows=5 (not 995) is the diagnostic — only 5 rows fully deserialised. ~1 ms on a 1000-row table for OFFSET 990. Still slower than keyset, but a 16× win over the pre-3.12 path.

Recipe: Web API With Cursor Bookmarks

-- First page
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 21;
-- (Fetch 21 to detect "has next page"; show 20.)

Encode the cursor as a base64-of-JSON {"created_at":"…","id":…} and round-trip it:

-- Next page (cursor decoded)
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

If the result has 21 rows, drop the last and emit next_cursor = encode(rows[19]). If 20 or fewer, no next page.

Recipe: Bidirectional (Forward + Back)

Both directions use the same row-constructor pattern, just flipped:

-- Forward (next page)
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;

-- Backward (previous page)
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC LIMIT 20;
-- (Then reverse the result client-side to render top-down.)

Recipe: Multi-Tenant Pagination

Add the tenant filter; the engine still picks the right scan strategy:

SELECT id, created_at, title
FROM articles
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

For best performance, define the index (tenant_id, created_at, id).

Verifying With EXPLAIN

EXPLAIN ANALYZE
SELECT id, created_at FROM articles
WHERE (created_at, id) < ('2026-04-25', 1000)
ORDER BY created_at DESC, id DESC LIMIT 20;

You want to see:

Limit (Top-K) at the root
A sort key that matches your ORDER BY
Filter: ((created_at, id) < (…, …)) on the leaf scan
Tiny actual time on EXPLAIN ANALYZE

If the actual time is large, your sort columns aren’t indexed. Add the composite index:

CREATE INDEX articles_created_id ON articles (created_at DESC, id DESC);

Common Pitfalls

Pitfall	Symptom	Fix
Sort key not unique	Rows skipped or duplicated across pages	Always include `(…, id)` as the tail
Mixing ASC/DESC inside the tuple	Lexicographic semantics break	Use a uniform direction; flip the operator instead
Forgetting LIMIT	Full scan returned	Always cap with `LIMIT N`
Using `OFFSET` for deep pages	Slow on every paginate-N regardless of pushdown	Switch to keyset
Floating-point sort keys	Tie-breaks unstable	Use timestamp + id, not float + id

Where Next

EXPLAIN_AND_OPTIMIZATION_TUTORIAL — read the plans this quickref produces.
WINDOW_FUNCTIONS_QUICKREF — ROW_NUMBER() OVER (…) is keyset-friendly too.
BAAS_REST_API_TUTORIAL — REST surface uses keyset under the hood when filters match.

CHANGELOG references: v3.6.0 (fast paths, 21/21 vs PG13), v3.12.0 (LogicalExpr::Tuple row-constructor compare, TopKOperator, scan_table_with_offset_limit), v3.19.1 (current verified release).