Pagination

The previous lesson settled on wrapping collection responses in an envelope and left the envelope’s pagination field empty. This lesson fills that field.

The problem starts with numbers. With twenty records in the loan collection, sending all of them in a single response is not a problem; at two hundred thousand records, the same response strains the server’s memory, the network, and the client’s parser together. The collection has to be handed out in pieces. There are two ways to say where a piece starts, and the difference between them shows up once the collection changes between requests.

Two Methods

In offset-based pagination, the client says which page number it wants; the server skips that many rows from the start of the sorted result and returns the next ones. The LIMIT and OFFSET clauses from the SQL Fundamentals course map to this directly. The client writes ?page=3, the server applies OFFSET 10.

In cursor-based pagination, the client reports the position of the last record it saw; the server returns what comes after it. There is no page number; every response carries the starting point for the next request. The criterion is the sort key, so the method only works under deterministic ordering: if the sort key can produce ties, a unique field is added alongside it.

The server below serves both: the same table, the same ordering, two addresses.

// page-server.mjs — serves the same collection with two pagination methods
import { createServer } from "node:http";
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("library.db");
const respond = (response, status, data) => {
  response.writeHead(status, { "content-type": "application/json; charset=utf-8" });
  response.end(JSON.stringify(data));
};

const server = createServer((request, response) => {
  const url = new URL(request.url, "http://127.0.0.1");
  const s = url.searchParams;
  const size = Math.min(Number(s.get("size") ?? 5), 50);

  if (url.pathname === "/offset/loans") {
    const page = Number(s.get("page") ?? 1);
    const rows = db.prepare(
      "SELECT id, issuedAt FROM loan ORDER BY id DESC LIMIT ? OFFSET ?"
    ).all(size, (page - 1) * size);
    return respond(response, 200, {
      data: rows.map((r) => r.id),
      pagination: { page, size },
    });
  }

  if (url.pathname === "/cursor/loans") {
    // No cursor: start from the top; a cursor asks for identities below it.
    const cursor = s.get("cursor");
    const rows = cursor === null
      ? db.prepare("SELECT id FROM loan ORDER BY id DESC LIMIT ?").all(size)
      : db.prepare("SELECT id FROM loan WHERE id < ? ORDER BY id DESC LIMIT ?")
          .all(Number(cursor), size);
    const last = rows.at(-1);
    return respond(response, 200, {
      data: rows.map((r) => r.id),
      pagination: { nextCursor: last ? String(last.id) : null },
    });
  }

  respond(response, 404, { error: "path_not_found" });
});

server.listen(8480, "127.0.0.1", () => console.log("page server 127.0.0.1:8480"));

The page size is taken from the client but capped at an upper bound. This is an inseparable part of the contract: an unbounded size parameter leaves the door open to pulling the entire collection in a single request.

Drift Measurement

The measurement sets up this scenario: the client fetches the first page, the collection changes in the meantime, then it asks for the second page. Two kinds of change are tried separately — inserting a record in between and deleting one. Data is reseeded at the start of every round, so all four scenarios run from the same starting point.

# A twenty-record collection; the first page is fetched, the collection changes, the second page is fetched.
rm -f library.db && sqlite3 library.db < schema.sql
seed() {
  sqlite3 library.db <<'SQL'
DELETE FROM loan;
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 20)
INSERT INTO loan (member, isbn, issuedAt, return)
SELECT 'U-1001', '978-0262033848', date('2026-01-01', '+' || n || ' day'), NULL FROM s;
SQL
}
seed
node page-server.mjs & server=$!
sleep 0.4

insert() { sqlite3 library.db "INSERT INTO loan (member, isbn, issuedAt, return)
  VALUES ('U-1002','978-0201896831','2026-02-01',NULL),
         ('U-1002','978-0201896831','2026-02-02',NULL);"; }
delete() { sqlite3 library.db "DELETE FROM loan WHERE id IN (20, 19);"; }
get() { curl -sS "http://127.0.0.1:8480$1"; echo; }

for effect in insert delete; do
  seed; printf 'offset / %s  page 1 : ' "$effect"; get "/offset/loans?page=1&size=5"
  $effect;  printf 'offset / %s  page 2 : ' "$effect"; get "/offset/loans?page=2&size=5"
  seed; printf 'cursor / %s  page 1 : ' "$effect"; get "/cursor/loans?size=5"
  $effect;  printf 'cursor / %s  page 2 : ' "$effect"; get "/cursor/loans?cursor=16&size=5"
done

kill $server

page server 127.0.0.1:8480
offset / insert  page 1 : {"data":[20,19,18,17,16],"pagination":{"page":1,"size":5}}
offset / insert  page 2 : {"data":[17,16,15,14,13],"pagination":{"page":2,"size":5}}
cursor / insert  page 1 : {"data":[20,19,18,17,16],"pagination":{"nextCursor":"16"}}
cursor / insert  page 2 : {"data":[15,14,13,12,11],"pagination":{"nextCursor":"11"}}
offset / delete  page 1 : {"data":[20,19,18,17,16],"pagination":{"page":1,"size":5}}
offset / delete  page 2 : {"data":[13,12,11,10,9],"pagination":{"page":2,"size":5}}
cursor / delete  page 1 : {"data":[20,19,18,17,16],"pagination":{"nextCursor":"16"}}
cursor / delete  page 2 : {"data":[15,14,13,12,11],"pagination":{"nextCursor":"11"}}

Two of the four scenarios are flawed.

Offset produced repeats on insertion. The first page ran from 20 down to 16. Once two new records entered in between, the start of the ordering shifted by two positions; the second page now starts at 17. The client saw records 17 and 16 twice. On a screen that accumulates pages into a list, the same loan record shows up as two rows.

Offset produced skips on deletion. Once two records were deleted, the ordering pulled up by two positions and the second page started at 13. Records 15 and 14 never appeared on any page. This is more dangerous than a repeat: what is missing goes unnoticed.

Cursor gave the same result in both cases: 15 down to 11. The reason is clear. A cursor carries not which position a page is in, but where the reader left off. Inserting a record at the front of the collection, or deleting one from the front, does not affect the criterion “identities smaller than 16.” Newly inserted records are seen once they come to the front on a later round; that is a result of freshness, not drift.

The Cost of Deep Pagination

There is a second difference independent of drift, and it shows up as the page number grows. Skipped rows are not skipped for free: the engine still has to find them.

# Deep pagination over two million rows: comparing plan and duration.
rm -f deep.db
sqlite3 deep.db <<'SQL'
CREATE TABLE loan (id INTEGER PRIMARY KEY, member TEXT NOT NULL, issuedAt TEXT NOT NULL);
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 2000000)
INSERT INTO loan (member, issuedAt)
SELECT 'U-' || (1000 + n % 500), date('2020-01-01', '+' || (n % 2000) || ' day') FROM s;
CREATE INDEX loan_issued_at ON loan (issuedAt DESC, id DESC);
SQL

sqlite3 deep.db <<'SQL'
.echo on
EXPLAIN QUERY PLAN
SELECT id FROM loan ORDER BY issuedAt DESC, id DESC LIMIT 20 OFFSET 1999980;
EXPLAIN QUERY PLAN
SELECT id FROM loan WHERE (issuedAt, id) < ('2020-01-02', 100) ORDER BY issuedAt DESC, id DESC LIMIT 20;
SQL

echo "--- timings ---"
sqlite3 deep.db <<'SQL'
.timer on
SELECT count(*) FROM (SELECT id FROM loan ORDER BY issuedAt DESC, id DESC LIMIT 20 OFFSET 1999980);
SELECT count(*) FROM (SELECT id FROM loan WHERE (issuedAt, id) < ('2020-01-02', 100) ORDER BY issuedAt DESC, id DESC LIMIT 20);
SQL

EXPLAIN QUERY PLAN
SELECT id FROM loan ORDER BY issuedAt DESC, id DESC LIMIT 20 OFFSET 1999980;
QUERY PLAN
`--SCAN loan USING COVERING INDEX loan_issued_at
EXPLAIN QUERY PLAN
SELECT id FROM loan WHERE (issuedAt, id) < ('2020-01-02', 100) ORDER BY issuedAt DESC, id DESC LIMIT 20;
QUERY PLAN
`--SEARCH loan USING COVERING INDEX loan_issued_at (issuedAt<?)
--- timings ---
20
Run Time: real 0.014 user 0.009015 sys 0.005149
20
Run Time: real 0.000 user 0.000068 sys 0.000015

Duration values vary by machine and by run; what stays fixed is the two plan lines. Per the plan glossary read in the Advanced SQL course, the first says SCAN, the second SEARCH. The offset-based query has to scan the index from the start and discard close to two million entries; the cursor-based query lands directly on the point it is looking for. Both return the same twenty rows, both use a covering index, but one grows more expensive with page depth and the other does not.

The measurement showed a two-order-of-magnitude difference. The gap widens as scale grows, because offset’s cost is directly proportional to the page number: the last page means reading the entire table.

Which Method, Where

Both methods have their place, and the selection criterion is the usage pattern.

Offset-based pagination fits admin screens where the user can jump to a page number: “412 records total, 21 pages” can be reported, and page seven can be reached directly. Its condition is that the data set stays largely static between requests and the page count remains reasonable. If the drift risk is accepted, it should be accepted explicitly.

Cursor-based pagination fits lists that move as a stream: infinite-scroll record lists, export jobs, sync jobs. It cannot report a total count or a page number; in exchange, it does not drift and is not affected by depth.

The cursor itself is also a design decision. In the server above, the cursor travels as a raw identity, which tells the outside that identity is the sort criterion. If sorting is by a different field, the cursor needs to carry that field together with a tiebreaker field. Sending the cursor as a single encoded string keeps the client from becoming dependent on its contents: the cursor is a meaningless token to the client, only sent back as given. That old cursors are invalidated and rejected once the sort criterion changes is also part of the contract.

Summary

• Collections are handed out in pieces; where a piece starts is said either with a page number or with a cursor reporting the last record’s position.
• In offset-based pagination, records repeat when one is inserted in between and are skipped when one is deleted; in the measurement, two records appeared twice and two never appeared.
• Cursor-based pagination gave the same result in both scenarios, because it carries not a position number but a place in the collection.
• A cursor only works with deterministic ordering; if the sort key is not unique, a unique field is added alongside it.
• Under deep pagination, the offset-based query scans the index from the start, the cursor-based one lands on the point it seeks; the plan lines split into SCAN and SEARCH.
• Page size is taken from the client but capped at an upper bound; an unbounded size leaves the door open to pulling the entire collection in one request.

Next Step

Pagination settled how much of the collection to give; it did not settle which records to give. The clerk comes with requests like “only overdue loans,” “books at the Central branch,” “sorted by author name.” All of these will travel in the query part, all will turn into SQL, and two traps will appear during that conversion: how a client-supplied value travels into the query, and how a non-unique sort breaks pagination. The next lesson takes on query parameter design together with both of these traps.