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.
A closer look
What's in it
- Reproducible benchmark (
keyset_bench.py, needs psycopg2 and a scratch PostgreSQL 12+ database) - Query plans and a results table at seven depths
- Bug OFFSET introduces (duplicates after inserts) and a naive keyset bug (missing a unique tie-breaker)
- Clear takeaway and limits (no jump-to-page-N)
The code
"""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=()):
