Nº 17Technical writingSpec sample

OFFSET pagination gets slower every page. Keyset doesn't.

A four-page write-up with a benchmark you can rerun. Skipping 1.9 million rows took 345 ms with OFFSET and 0.02 ms with keyset pagination.

Line chart: OFFSET query time climbs to 345 ms while keyset stays near zero
Fig. 1 · Keyset paginationSpec sample

A closer look

What's in it

  1. Reproducible benchmark (keyset_bench.py, needs psycopg2 and a scratch PostgreSQL 12+ database)
  2. Query plans and a results table at seven depths
  3. Bug OFFSET introduces (duplicates after inserts) and a naive keyset bug (missing a unique tie-breaker)
  4. Clear takeaway and limits (no jump-to-page-N)

The code

keyset_bench.pyThe first lines of keyset_bench.py
"""Keyset vs OFFSET pagination benchmark on PostgreSQL.
Reproduce: point DSN at any scratch PostgreSQL 12+ database and run `python keyset_bench.py`.
"""
import json, os, statistics, psycopg2

DSN = os.environ.get("DSN", "host=/tmp port=54329 user=postgres dbname=postgres")
ROWS = 2_000_000
PAGE = 50
DEPTHS = [0, 1_000, 10_000, 100_000, 500_000, 1_000_000, 1_900_000]
RUNS = 7

c = psycopg2.connect(DSN); c.autocommit = True; cur = c.cursor()
cur.execute("SELECT version()"); version = cur.fetchone()[0]
cur.execute("DROP TABLE IF EXISTS orders")
cur.execute("""
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id int         NOT NULL,
  created_at  timestamptz NOT NULL,
  total       numeric(10,2) NOT NULL
)""")
# created_at has deliberate ties: one timestamp per ~4 rows
cur.execute(f"""
INSERT INTO orders (customer_id, created_at, total)
SELECT (random()*50000)::int,
       timestamptz '2024-01-01' + (g/4) * interval '15 seconds',
       round((random()*500)::numeric, 2)
FROM generate_series(1, {ROWS}) g""")
cur.execute("CREATE INDEX orders_created_id ON orders (created_at, id)")
cur.execute("VACUUM ANALYZE orders")

def exec_ms(sql, params=()):

Want something like this?

alexmercer.projects@gmail.com

Email me the job. You get a free sample first, and most jobs are done within 24 hours once the brief and files are ready.

Email Alex