How to Paginate ClickHouse Results in TypeScript

Paginating ClickHouse results is straightforward at small scale and becomes a real problem at large scale. This post covers the three patterns you'll encounter — offset/limit, cursor-based, and keyset — and shows how to implement each with hypequery.

Offset / Limit Pagination

The simplest approach. Ask for rows 0–49, then 50–99, and so on.

This works fine for small tables or when users only browse the first few pages. The problem surfaces at scale.

Why OFFSET is expensive on ClickHouse: ClickHouse is a columnar database optimised for full-scan aggregations, not point lookups. When you write OFFSET 100000 LIMIT 50, ClickHouse still reads and processes the first 100,000 rows — it just discards them before returning results. The deeper into the dataset you page, the more work gets thrown away. On a table with hundreds of millions of rows, page 10,000 is genuinely slow.

Use offset pagination when:

• The dataset is small (< 1M rows in the result set)
• Users won't browse past the first few pages
• You need to jump to an arbitrary page number

Cursor-Based Pagination

Instead of tracking a page number, you track the last item you saw. Each response includes a cursor (usually the ID or timestamp of the last row), and the next request uses that cursor as a WHERE condition.

This is better for infinite scroll and feed-style UIs. You can't jump to page 47, but you don't need to for most analytics use cases.

Keyset Pagination

Keyset pagination is the most performant pattern for large datasets. It works by filtering on an indexed column — typically a timestamp or auto-incrementing ID — so ClickHouse can use the primary key or sort key to skip directly to the right place in the data.

The key difference from cursor pagination is precision: you filter on both the timestamp and the ID together to handle ties correctly.

Keyset pagination is fast because ClickHouse can evaluate the WHERE condition against indexed columns without reading discarded rows. hypequery's .where() and .orderBy() make it composable — you can build the base query once and add the cursor condition conditionally.

Choosing the Right Pattern

| Pattern | Use case | Performance at scale | |---|---|---| | Offset/limit | Small datasets, arbitrary page jumps | Degrades with depth | | Cursor | Infinite scroll, chronological feeds | Good | | Keyset | High-volume analytics, large result sets | Best |

For most analytics dashboards built on ClickHouse, keyset or cursor pagination is the right default. If you're building a simple admin table with a few thousand rows, offset/limit is fine and easier to implement.

hypequery's fluent API makes all three patterns clean to write. The .limit(), .offset(), and .where() methods chain naturally, and the TypeScript types ensure your cursor values match the schema columns you're filtering on.