---
title: Keyset Pagination Quick Reference
description: Row-constructor keyset pagination, Top-K rewrite, storage-level OFFSET pushdown
build: default (no feature flag required)
---

# Keyset Pagination Quick Reference


---

## 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

```sql
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**:

```text
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` (older releases, no pushdown) | O(N) materialise + skip | ~80 ms | No (drift on insert) |
| `LIMIT 20 OFFSET 100000` (storage-level 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.

```sql
-- 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:

```sql
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`). 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.

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

```text
 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.), `scan_table_with_offset_limit` skips offset rows at the **RocksDB iterator** — no bincode decode, no decrypt, no dictionary resolve.

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

```text
 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 path without pushdown.

---

## Recipe: Web API With Cursor Bookmarks

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
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

```sql
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:

```sql
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](https://www.heliosdb.com/docs/nano/guides/tutorials/explain_and_optimization_tutorial/) — read the plans this quickref produces.
- [WINDOW_FUNCTIONS_QUICKREF](https://www.heliosdb.com/docs/nano/guides/quickstart/window_functions_quickref/) — `ROW_NUMBER() OVER (…)` is keyset-friendly too.
- [BAAS_REST_API_TUTORIAL](https://www.heliosdb.com/docs/nano/guides/tutorials/baas_rest_api_tutorial/) — REST surface uses keyset under the hood when filters match.

---

*See the [changelog](https://www.heliosdb.com/docs/nano/releases/changelog/) for the release history of these paths (`LogicalExpr::Tuple` row-constructor compare, `TopKOperator`, `scan_table_with_offset_limit`).*