Offset-based LIMIT/OFFSET vs keyset pagination using primary key for large datasets in Next.js API Routes

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?