UltraHard Scenario BasedSQL#1847 min readJul 11, 2026
Pagination for 100M rows
indexing
On 100M rows, OFFSET is like asking a librarian to count 99 million books before handing you one page.
Question: How do I paginate 100M rows in SQL without page 100,000 becoming painfully slow?
Answer: Use keyset pagination, which means “start after the last row I already saw” instead of “skip N rows.” I sort by a stable key, usually something like created_at DESC, id DESC, and fetch the next page with a WHERE clause on that last key. This stays fast on deep pages because the database can seek into an index instead of scanning and discarding millions of rows.
Interview-Ready Answer: I would not use deep OFFSET pagination on 100M rows. I’d use keyset pagination with a deterministic sort order and a composite index, for example created_at DESC, id DESC. The first page uses LIMIT, and every next page uses the last row as a cursor: WHERE (created_at, id) < (:last_created_at, :last_id). That keeps latency stable, avoids skipping millions of rows, and prevents duplicates or missed rows when data changes between requests.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EKeyset%20Pagination%3C%2Ftext%3E%3C%2Fsvg%3E)
How it works under the hood
Think in terms of a bookmark, not a page number.
- Pick a stable sort key. A good key is usually a time column plus a unique tie-breaker, like
created_at DESC, id DESC. The tie-breaker matters because many rows share the same timestamp. - Create a matching composite index. If you also filter by tenant or status, put that filter first: for example
(tenant_id, created_at DESC, id DESC). This lets the optimizer use the leftmost part of the index efficiently. - Fetch the first page with
ORDER BY ... LIMIT 50. Fifty or one hundred rows is a common page size because it keeps payloads small and the query cheap. - Return the last row as a cursor token. A cursor is just a bookmark that stores the last seen sort values, often encoded as JSON or base64 in an API.
- For the next page, ask for rows after that bookmark:
WHERE (created_at, id) < (:last_created_at, :last_id)for descending order. The database can seek directly into the index, then read the next 50 rows. - For previous pages, reverse the comparison and sort, then flip the rows back in application code if needed.
Why OFFSET falls over
OFFSET 10,000,000 LIMIT 50 means the engine still has to find and discard ten million rows before it can hand you the next 50. With a good index this is still better than a full table scan, but the cost grows with the page number, so page 1 is fast and page 200,000 is not.
Compare the options
| Method | How it works | Best for | Problem |
|---|---|---|---|
| OFFSET + LIMIT | Skip N rows | Small lists | Deep pages slow |
| Keyset | Bookmark last row | Feeds, APIs | No random page jump |
| DB cursor | Keep session state | Batch jobs | Ties up connection |
Performance notes
- Offset pagination is roughly
O(offset + page_size). - Keyset pagination is roughly
O(log N + page_size)with the right index. - Without an index that matches the filter and sort, both approaches can become slow.
- A covering index can avoid table lookups, but on 100M rows the index itself may be several GB, so choose columns carefully.
Edge cases
- Duplicate timestamps: always add a unique tie-breaker such as
id. - Changing filters: the cursor becomes invalid if the user changes sort or filter criteria.
- Concurrent writes: keyset avoids most duplicates caused by shifting offsets, but it is not a full snapshot unless you hold a stable transaction snapshot. In PostgreSQL, the default isolation level is
READ COMMITTED, so new rows can appear between requests. - Exact page numbers: if the business insists on “jump to page 4000,” use bookmarks every N pages or a hybrid approach; pure keyset is optimized for next/previous navigation.
A common production case is an admin dashboard for a checkout service that stores 100M payment events. The first version used ORDER BY created_at DESC LIMIT 50 OFFSET 5000000 for the audit log. Page 1 was fine, but deeper pages took 20 to 40 seconds, support agents saw endless spinners, and logs showed huge buffer reads plus many rows being skipped. When new payments arrived, page numbers shifted, so the same event could appear twice or disappear between refreshes.
The fix was keyset pagination with a composite index on (created_at DESC, id DESC) and a cursor token built from the last row on each page. After that, latency stayed predictable, the UI felt instant, and the audit trail stopped drifting under live traffic.
What goes wrong when you misunderstand it: the app looks fast in testing with page 1, then falls apart in production on deep pages. Symptoms include timeouts on large offsets, hot CPU from repeated scans, log lines with huge row counts, and user reports like “I saw this order already” or “page 200 never loads.”
SQL
DROP TABLE IF EXISTS audit_log;
CREATE TABLE audit_log (
id BIGINT PRIMARY KEY,
created_at TIMESTAMP NOT NULL,
action TEXT NOT NULL
);
-- Duplicate timestamps are intentional: real feeds often have ties.
-- The id tie-breaker makes the order deterministic when created_at matches.
INSERT INTO audit_log (id, created_at, action) VALUES
(10, TIMESTAMP '2026-01-01 10:00:00', 'signup'),
(9, TIMESTAMP '2026-01-01 10:00:00', 'login'),
(8, TIMESTAMP '2026-01-01 09:59:30', 'view'),
(7, TIMESTAMP '2026-01-01 09:58:00', 'add_to_cart'),
(6, TIMESTAMP '2026-01-01 09:57:00', 'checkout'),
(5, TIMESTAMP '2026-01-01 09:56:30', 'payment'),
(4, TIMESTAMP '2026-01-01 09:55:00', 'refund'),
(3, TIMESTAMP '2026-01-01 09:54:00', 'logout'),
(2, TIMESTAMP '2026-01-01 09:53:00', 'support'),
(1, TIMESTAMP '2026-01-01 09:52:00', 'archive');
-- Match the index to the ORDER BY used by the page query.
-- On a large table, this lets the engine seek directly into the sorted data.
CREATE INDEX idx_audit_log_created_id_desc
ON audit_log (created_at DESC, id DESC);
-- Page 1: fast, simple, and what every pagination scheme starts with.
SELECT id, created_at, action
FROM audit_log
ORDER BY created_at DESC, id DESC
LIMIT 4;
-- Page 2 with keyset pagination:
-- The last row of page 1 becomes the bookmark for the next request.
WITH first_page AS (
SELECT id, created_at, action
FROM audit_log
ORDER BY created_at DESC, id DESC
LIMIT 4
),
last_seen AS (
SELECT created_at, id
FROM first_page
ORDER BY created_at ASC, id ASC
LIMIT 1
)
SELECT a.id, a.created_at, a.action
FROM audit_log a
CROSS JOIN last_seen s
WHERE (a.created_at, a.id) < (s.created_at, s.id)
ORDER BY a.created_at DESC, a.id DESC
LIMIT 4;
-- OFFSET still works, but it gets more expensive as the page number grows.
-- On 100M rows, this is the pattern that becomes painful.
SELECT id, created_at, action
FROM audit_log
ORDER BY created_at DESC, id DESC
LIMIT 4 OFFSET 4;
-- Edge case: cursor past the end returns no rows, which is correct.
SELECT id, created_at, action
FROM audit_log
WHERE (created_at, id) < (TIMESTAMP '2025-12-31 00:00:00', 0)
ORDER BY created_at DESC, id DESC
LIMIT 4;Follow-up & Tricky Questions:
- How do you paginate backward? Use the opposite comparison and reverse the sort. For descending order, previous-page queries usually use
>on the first visible row, then you reverse the result order if the UI wants the same direction. - What index would you create? Match the query pattern exactly. If the page is filtered by tenant, use something like
(tenant_id, created_at DESC, id DESC)so the engine can use the filter and the sort together. - How do you jump to page N? Pure keyset is not built for random access. A common fix is to store anchor bookmarks every 1,000 rows or every N pages, then keyset from the nearest anchor.
- What if the sort column is not unique? Add a unique tie-breaker, usually the primary key. Without that, rows with equal timestamps can repeat, disappear, or change order between requests.
- What happens when rows are inserted or deleted between requests? Offset pages shift and can duplicate or skip rows. Keyset is much more stable because it uses the last seen key, but it is still not a perfect snapshot unless you keep a stable transaction view.
- Is keyset pagination just a SQL cursor? No. A SQL cursor usually keeps database state and often an open session; a keyset cursor is just an application token that remembers the last sort key.
- Is
OFFSETokay if I have an index? It is fine for small offsets, but the database still has to walk past the skipped rows. The index helps, but it does not change the fact that deep offsets get slower as they grow. - Can I paginate on
ORDER BY name? Yes, if the order is deterministic and indexed, but be careful with collation and duplicate names. Add a tie-breaker so the order never depends on luck.
Common gotchas: The big trap is assuming page 1 performance means page 10,000 will also be fine. It will not.
Common Mistakes:
- Using only the timestamp. Fix: add a unique tie-breaker like
idso the order is stable. - Building the wrong index order. Fix: match the
WHEREclause andORDER BYtogether, not just the table columns. - Using deep
OFFSETin production. Fix: reserve it for shallow pages or admin-only small tables. - Changing filters without changing the cursor. Fix: invalidate the cursor when sort or filter criteria change.
Memory Hook: “Don’t count to the page; bookmark the shelf.” If you already know the last row, ask for the rows after it.
Cheat Sheet:
- Deep pagination on huge tables: prefer keyset.
- Use a stable sort key: usually
created_atplusid. - Create a composite index that matches filter + sort.
OFFSETis easy but gets slower as the offset grows.- Keyset is best for next/previous navigation, not random page jumps.
- Expect duplicate timestamps, live inserts, and empty pages at the end.
Practice Tasks:
- Write a first-page query for a 50-row feed sorted newest-first.
- Rewrite it as a next-page keyset query using the last row from page 1.
- Add a filter like
tenant_id = 42and update the index to match it.