Question
Offset-based LIMIT/OFFSET vs keyset pagination using primary key for large datasets in Next.js API Routes
Ari Dey
0 reputation · 16 Mar 2025, 03:57 UTC
135.2K views1
Loading…
0 reputation · 16 Mar 2025, 03:57 UTC
Goal: build a paginated API endpoint in a Next.js Jamstack application that returns a subset of rows from a large PostgreSQL table without degrading response time as the dataset grows.
Constraint: the simplest approach uses LIMIT/OFFSET via req.query, but OFFSET forces the database to scan and discard preceding rows, which becomes costly for deep pages. An alternative keyset pagination uses a stable cursor (e.g., primary key) to fetch the next set, requiring a deterministic ORDER BY and additional WHERE logic, yet avoids scanning skipped rows.
Which approach provides better scalability for deep pagination? How can consistent ordering be guaranteed when using keyset pagination? What are the trade‑offs in implementation complexity versus query performance?
26525 reputation · 16 Mar 2025, 11:26 UTC
Offset‑based LIMIT/OFFSET forces the database to scan and discard N rows before returning results. Latency grows linearly with offset depth, which collapses performance on deep pages of large tables.
Keyset pagination uses a primary‑key seek (WHERE id > :last_id ORDER BY id LIMIT N), touching only the target range. Execution time stays constant regardless of dataset size.
Consistent ordering with keyset pagination requires a monotonically increasing, uniquely indexed primary key. The ORDER BY clause must reference that key, and the cursor must be the last observed key value from the previous page.
jamstack
nextjs
postgresqlid) that auto‑increments and is indexed.id from the returned row as the cursor parameter in the next request.SELECT * FROM rows WHERE id > :cursor ORDER BY id LIMIT :pageSizeTo refine the recommendation for your environment, could you confirm whether your table’s primary key is monotonically increasing and fully indexed?
Use comments to ask for clarification. Post a solution as an answer.
2,180 reputation · 16 Mar 2025, 04:15 UTC
While keyset pagination solves the performance degradation of OFFSET, a common implementation hurdle occurs when sorting by non-unique columns (e.g., created_at or price). If multiple rows share the same value, a simple WHERE created_at > :cursor may skip records that occur at the exact same timestamp as the last item of the previous page.
To maintain deterministic ordering and prevent data loss, you must use a composite cursor that includes a unique identifier (typically the primary key) as a tie-breaker. In PostgreSQL, this can be handled efficiently using row value comparisons:
SELECT * FROM products
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at ASC, id ASC
LIMIT 20;For this to remain performant, a composite index on (created_at, id) is required. Without this index, the database may revert to a sequential scan, negating the performance benefits of the seek method.