SQL / INDEXES AND QUERY PERFORMANCE
Pagination that stays fast on large tables
Replace OFFSET paging with a cursor on the last row's sort key, so page 5000 costs the same as page 1 and no rows are skipped or repeated.
What you will learn
- Convert OFFSET paging into a WHERE filter on the sort key of the last row shown
- Add the primary key to ORDER BY so a page boundary never lands inside a tie
- Index the exact ORDER BY tuple so the cursor becomes an index range scan
- Write a previous-page query by flipping the comparison and sort, then re-sorting
Understanding Pagination that stays fast on large tables
OFFSET 100000 does not mean "start at row 100000", because a table has no such address. The engine still has to produce rows in the ORDER BY sequence and discard the first 100000 of them, so the work grows with the page number: page 1 walks 20 index entries, page 5001 walks 100020. Unless the scan can be satisfied from the index alone, each discarded row is also fetched from the table to check whether it is visible to your transaction, which is why deep pages get slow even when a perfect index exists.
The fix is to change what a page boundary means. Instead of a count of rows to skip, remember the sort key of the last row you displayed and ask for everything that sorts after it. A B-tree can descend directly to that key and read forward, so the query touches about LIMIT entries no matter how deep the page is, and the boundary stays correct while other sessions insert and delete rows, because a key does not move when unrelated rows appear.
This only works if the sort key is unique and the index agrees with it. ORDER BY created DESC is ambiguous whenever two rows share a timestamp, so the boundary has to include a tie-break column, normally the primary key. The comparison in the WHERE clause, the ORDER BY, and the index column order must all be the same tuple in the same direction; if they diverge, the engine sorts the matching rows or filters them after reading, and the cursor buys you nothing.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
created TEXT NOT NULL,
label TEXT NOT NULL
);
INSERT INTO events (id, created, label) VALUES
(1, '2026-01-05', 'alpha'),
(2, '2026-01-05', 'bravo'),
(3, '2026-01-06', 'charlie'),
(4, '2026-01-07', 'delta'),
(5, '2026-01-07', 'echo'),
(6, '2026-01-08', 'foxtrot');
CREATE INDEX events_created_id ON events (created, id);
-- Page 1: no cursor yet.
SELECT id, created, label
FROM events
ORDER BY created DESC, id DESC
LIMIT 2;
-- Page 2: the cursor is the last row of page 1, (2026-01-07, 5).
SELECT id, created, label
FROM events
WHERE (created, id) < ('2026-01-07', 5)
ORDER BY created DESC, id DESC
LIMIT 2;A page boundary is the sort key of the last row you showed, not a count of rows to skip.
Worked examples
A new row breaks OFFSET, not the cursor
Shows a row appearing on two consecutive pages because an insert shifted every position by one.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
created TEXT NOT NULL,
label TEXT NOT NULL
);
INSERT INTO events VALUES
(1,'2026-01-05','alpha'), (2,'2026-01-05','bravo'),
(3,'2026-01-06','charlie'), (4,'2026-01-07','delta'),
(5,'2026-01-07','echo'), (6,'2026-01-08','foxtrot');
-- The reader has already seen page 1: foxtrot, echo. Now a newer row arrives.
INSERT INTO events VALUES (7,'2026-01-09','golf');
-- Page 2, by position.
SELECT id, label FROM events
ORDER BY created DESC, id DESC
LIMIT 2 OFFSET 2;
-- Page 2, by cursor.
SELECT id, label FROM events
WHERE (created, id) < ('2026-01-07', 5)
ORDER BY created DESC, id DESC
LIMIT 2;Example explained
Line 1Inserting id 7 at the top of the ordering pushes every earlier row down one position.
Line 2OFFSET 2 therefore starts on echo, which page 1 already showed, so the reader sees it twice.
Line 3The cursor query names the key ('2026-01-07', 5) rather than a position, so the boundary cannot drift.
Line 4A DELETE above the boundary is the mirror image: OFFSET pulls rows up and one is never displayed at all.
Why the cursor needs a tie-break column
Demonstrates a row vanishing from every page when the cursor is only the non-unique sort column.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
created TEXT NOT NULL,
label TEXT NOT NULL
);
INSERT INTO events VALUES
(1,'2026-01-05','alpha'), (2,'2026-01-05','bravo'),
(3,'2026-01-06','charlie'), (4,'2026-01-07','delta'),
(5,'2026-01-07','echo'), (6,'2026-01-08','foxtrot');
-- Broken: the cursor is just the date of the last row shown.
SELECT id, created, label FROM events
WHERE created < '2026-01-07'
ORDER BY created DESC, id DESC
LIMIT 2;
-- Correct: the cursor is (date, id), so it splits the tie at the exact row.
SELECT id, created, label FROM events
WHERE (created, id) < ('2026-01-07', 5)
ORDER BY created DESC, id DESC
LIMIT 2;Example explained
Line 1Page 1 ended on (2026-01-07, 5); delta shares that date but sorts after echo, so it still owes a page.
Line 2created < '2026-01-07' throws away the whole date group, so delta is skipped by every page forever.
Line 3The row-value comparison keeps rows from the boundary date whose id is below 5, which is exactly delta.
Line 4Switching the broken query to <= is not a fix: it returns echo again, duplicating the row instead of losing one.
Paging backwards from the same index
Builds a previous-page query by reversing the comparison and the scan, then restoring display order.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
created TEXT NOT NULL,
label TEXT NOT NULL
);
INSERT INTO events VALUES
(1,'2026-01-05','alpha'), (2,'2026-01-05','bravo'),
(3,'2026-01-06','charlie'), (4,'2026-01-07','delta'),
(5,'2026-01-07','echo'), (6,'2026-01-08','foxtrot');
CREATE INDEX events_created_id ON events (created, id);
-- The current page starts at (2026-01-06, 3). Fetch the two rows before it.
SELECT id, created, label FROM (
SELECT id, created, label
FROM events
WHERE (created, id) > ('2026-01-06', 3)
ORDER BY created ASC, id ASC
LIMIT 2
) AS back
ORDER BY created DESC, id DESC;Example explained
Line 1Flipping < to > and DESC to ASC walks the index away from the first row of the current page.
Line 2LIMIT 2 applied in ascending order picks the two rows nearest the boundary, not the two oldest rows.
Line 3The outer ORDER BY puts those two rows back into the newest-first order the page renders in.
Line 4One index on (created, id) serves both directions, because the leaf level can be read either way.
Important notes
Mixed directions cannot be written as one row-value comparison. For ORDER BY created DESC, id ASC you need created < :c OR (created = :c AND id > :i), and an index whose directions match that ordering.
Cursor paging gives next and previous, not "jump to page 87", and it produces no total row count; if the interface needs page numbers, treat the count as a separate, cacheable query.
Common mistakes
Using only the timestamp as the cursor: every row sharing the boundary timestamp is dropped from all pages, and switching to <= shows the boundary row twice instead.
Putting a nullable column in the tuple: ('created', id) < (NULL, 5) evaluates to NULL rather than true or false, so the page comes back empty and paging dead-ends.
Indexing (id, created) while ordering by (created, id): the comparison can no longer be turned into a range scan, so the engine reads and sorts the whole matching set and the cursor saves nothing.
Try it yourself
Change, predict, then run
Create a messages(id, sent_at, body) table where 12 rows share only three distinct sent_at values, page through it three rows at a time using WHERE sent_at < :last_sent_at, and write down which rows never appear. Then redo it with a (sent_at, id) cursor and confirm all 12 rows show up exactly once.
Open the SQL workspaceCheck your understanding
With an index on (created, id), why does the cursor form of page 5001 beat LIMIT 20 OFFSET 100000?
- Because the index on (created, id) is smaller than the table, so fewer bytes are read
- Because it descends the index straight to the boundary key and reads about 20 entries, instead of walking and discarding 100000
- Because it avoids a sort, while the OFFSET version has to sort the whole table first
- Because LIMIT without OFFSET lets the engine return an approximate page, trading accuracy for speed
Show answer
The entire saving is the discarded prefix: OFFSET must still generate the first 100000 rows in order before it can throw them away, while the cursor turns the boundary into a starting point the B-tree can seek to. Option 3 is tempting but wrong, because with that index in place neither query sorts anything; both read rows in index order, and only the number of entries touched differs.