AdvancedAdvanced PostgreSQL

Efficient pagination with keyset (cursor-based)

The query

SQL

-- Traditional OFFSET pagination (SLOW for large offsets):
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 10000; -- slow!

-- Keyset pagination (FAST regardless of page):
-- First page:
SELECT order_id, order_date, total
FROM orders
ORDER BY order_id
LIMIT 20;

-- Next page (using last order_id = 500 from previous page):
SELECT order_id, order_date, total
FROM orders
WHERE order_id > 500  -- last_seen_id
ORDER BY order_id
LIMIT 20;

Tested against PostgreSQL 16

Note

“Keyset pagination uses a WHERE clause instead of OFFSET. O(log n) vs O(n). Essential for production APIs.

Tables referenced

orders6 cols