Offset pagination vs keyset cursors for a Node.js API that needs both deep pages and page jumps

For a PostgreSQL table with tens of millions of rows, neither pure offset nor pure keyset pagination satisfies all your requirements. To support both fast deep pages and arbitrary page jumps, a hybrid approach is the most practical standard.

The Hybrid Strategy

Implement offset pagination for shallow depths (e.g., the first 100 pages) and switch to keyset pagination for deeper traversal. This acknowledges that while users occasionally jump to arbitrary pages, they rarely jump to page 5,000. For the vast majority of users, the linear cost of OFFSET is negligible at shallow depths, but catastrophic at depth.

Implementation Details

• Shallow Pages: Use LIMIT n OFFSET m. Accept that concurrent inserts may cause rows to shift between pages.
• Deep Pages: Transition to keyset pagination using a unique, monotonic tiebreaker (usually the Primary Key). Use row-value comparisons for efficiency: WHERE (sort_column, id) > ($1, $2) ORDER BY sort_column, id LIMIT n.
• The Transition: When a client reaches the offset threshold, the API response should include a next_cursor (a Base64 encoded string of the last row's sort values) to shift the client into keyset mode.

Handling Constraints

Total Counts and UI

Running SELECT COUNT(*) on millions of rows is expensive and will likely bottleneck your API. For the UI, an estimated count is typically acceptable. You can retrieve this from the PostgreSQL planner statistics:

SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'your_table_name';

Note that this estimate can drift between ANALYZE runs, but it provides the "approximate total" needed for page-number calculations without scanning the table.

Memory and Streaming

The pg-cursor library bounds memory on the Node.js side by fetching rows in chunks, but it does not reduce the database's internal work. If you use OFFSET 1,000,000 with pg-cursor, PostgreSQL still scans and discards a million rows before streaming the result. Use pg-cursor for large data exports, but rely on keyset pagination for API request-response cycles to keep database latency low.

Verification Steps

To ensure the implementation is performing as expected, run the following checks in a staging environment with production-scale data:

1. Execution Plans: Run EXPLAIN (ANALYZE, BUFFERS) on both query types. The keyset query should show an Index Scan with constant cost regardless of the cursor value, while the offset query should show increasing Buffers and execution time as the offset grows.
2. Index Alignment: Verify that your composite index exactly matches the ORDER BY clause to avoid a costly Sort node in the query plan.
3. Concurrency Test: Insert rows while paginating. Confirm that keyset pagination remains stable (no duplicates), whereas offset pagination may show the same row on two different pages.

Missing Detail: Does your business logic require a guaranteed exact count for the UI, or is a ±5% estimate acceptable?