How do you rewrite LIMIT/OFFSET paging as a keyset (seek) query?

## The idea Offset pagination asks a positional question — "which rows are at positions 201 to 220?" — and positions can only be found by counting. Keyset (also called seek) pagination asks a value question — "which 20 rows come after *this* row in the sort order?" — and a value is something the engine can position on directly. That single change makes every page cost the same as the first. ## The rewrite ```sql -- Offset version: cost grows with the page number SELECT id, created_at, title FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 200000; -- Keyset version: constant cost per page SELECT id, created_at, title FROM posts WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20; ``` The parameters are the `created_at` and `id` of the **last row of the previous page**. Everything the next page needs is carried in those two values; the server keeps no state. The first page has no anchor, so it simply omits the `WHERE` clause (or the application builds the statement without it). Do not invent a sentinel value like `'9999-12-31'` for this — omitting the predicate is clearer and cannot go wrong at the type boundary. ## Row-value comparison semantics `(a, b) < (x, y)` is standard SQL and compares **lexicographically**, exactly the way the corresponding `ORDER BY a, b` sorts: it is true when `a < x`, or when `a = x` and `b < y`. It is emphatically *not* `a < x AND b < y`. That distinction is where the classic bug lives: ```sql -- WRONG: silently drops rows WHERE created_at <= :last_created_at AND id < :last_id ``` This demands that *every* returned row have an id below the anchor's id, which has nothing to do with the sort order. Rows older than the anchor whose id happens to be larger vanish from the results entirely — and because the pages still look plausible, the loss is usually discovered much later. The correct expanded form is: ```sql WHERE created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id) ``` This is logically identical to the row-value comparison and is the portable spelling: engines differ in whether they accept row constructors in comparison predicates and in how well they can use an index for them, so check your engine before committing to the compact form. ## The three correctness rules 1. **The ordering must be total.** The last sort column must be unique — usually the primary key. Without it, ties at the page boundary make the anchor ambiguous and rows are skipped or repeated. 2. **The predicate must mirror the ORDER BY.** Same columns, same sequence, same directions. `DESC` ordering pairs with `<`, `ASC` with `>`. If the directions are mixed (`a ASC, b DESC`), a single row-value comparison cannot express the boundary — you must write the expanded `OR` form with the correct operator per column. 3. **The sort columns should be NOT NULL.** Any comparison involving NULL yields UNKNOWN, so the row is not returned; a nullable sort column silently truncates paging at the first NULL. If you must sort on a nullable column, seek on a NOT NULL surrogate or an expression that eliminates the NULL. A fourth practical rule: the filters and the `ORDER BY` must stay identical across every page of the same traversal. The anchor is meaningful only relative to one ordering — change the sort and the cursor must be discarded. ## What the client hands back The anchor is just the sort-key values of the last displayed row. Applications usually opaque-encode them into a single token so callers cannot craft them, but at the SQL level they are ordinary bind parameters. Note that the anchor values must be the ones the query sorted on, not a display-formatted version of them: a timestamp truncated to seconds in JSON will land on the wrong row when the column holds microseconds. ## What you give up Keyset gives next and previous, not random access — there is no way to express "page 57" without counting, which is the very thing you removed. It also does not give you a total row count; that needs a separate aggregate query if the UI insists on it. And it needs a sort key that is stable while a user pages: ordering by a score that changes every few seconds will still shift rows around. For most feeds, activity logs and infinite-scroll lists those are acceptable prices, and the payoff is that page 10,000 costs exactly what page 1 costs.