SQL
Implementing Efficient Keyset Pagination for Large Datasets
Optimize pagination for large datasets using keyset pagination (seek method) in SQL, significantly improving performance over traditional OFFSET/LIMIT for web applications.
-- Assume 'products' table with 'id' (primary key) and 'created_at' (indexed datetime)
-- We want to paginate by 'created_at' then 'id' for uniqueness, ascending.
-- Initial query for the first page (page 1, limit 20 items):
SELECT id, name, created_at, price
FROM products
ORDER BY created_at ASC, id ASC
LIMIT 20;
-- To get the next page (page 2, limit 20 items):
-- Use the 'created_at' and 'id' of the LAST item from the previous page as anchor points.
-- Let's say the last item from page 1 had created_at = '2023-10-26 10:30:00' and id = 123.
SELECT id, name, created_at, price
FROM products
WHERE (created_at > '2023-10-26 10:30:00')
OR (created_at = '2023-10-26 10:30:00' AND id > 123)
ORDER BY created_at ASC, id ASC
LIMIT 20;
-- For backward pagination (previous page):
-- Use the 'created_at' and 'id' of the FIRST item from the current page as anchor points.
-- If the first item on current page had created_at = '2023-10-27 11:00:00' and id = 456.
SELECT id, name, created_at, price
FROM products
WHERE (created_at < '2023-10-27 11:00:00')
OR (created_at = '2023-10-27 11:00:00' AND id < 456)
ORDER BY created_at DESC, id DESC -- Note the DESC for going backward
LIMIT 20;
-- Then reverse the results in the application layer if needed for display order.
How it works: Keyset pagination, also known as the seek method, is a highly efficient way to paginate through large datasets, especially when compared to traditional `OFFSET`/`LIMIT` which can become very slow on large offsets. It works by "seeking" the next page directly based on the values of the last item from the previous page (e.g., `created_at` and `id`). This approach avoids scanning previous records, making it ideal for scalable web APIs and applications dealing with millions of records.