Rillence
Login

← All bricks

PostgreSQL · Intermediate

Slow Pagination: Deep OFFSET Meltdown

A production incident, replayed by hand: a paginated listing is instant on page one and times out on deep pages, and every request recomputes the total with count(*). Bring up the same broken database, catch both offenders in pg_stat_statements, read the plans, fix the ordering with one composite index — and learn why OFFSET and exact counts can never be free.

A tenant-scoped listing endpoint melts down: page one is fine, deep pages crawl, CPU spikes, and nothing was deployed. Every page request runs two queries — a count(*) for the pager header and a LIMIT/OFFSET page — and both get slower as users scroll deeper. This book replays the incident on a database you bring up yourself: schema, data and broken state are plain SQL files, nothing hidden. You reproduce the pain with a stopwatch, catch both queries in pg_stat_statements, convict them with EXPLAIN (ANALYZE, BUFFERS) — a Seq Scan feeding a disk-spilling Sort, fifty thousand rows produced just to be thrown away — then fix the ordering with a composite index and see what the index can and cannot fix: a deep OFFSET still walks every skipped row, and an exact count still visits every row. The closing move is the real application fix: keyset pagination, constant work per page at any depth.

What you'll build

  • Reproduce a pagination latency incident on a local PostgreSQL stand
  • Find the offending queries with pg_stat_statements
  • Read an EXPLAIN (ANALYZE, BUFFERS) plan: Seq Scan, Sort spilling to disk, rows produced vs returned
  • Design a composite index that matches an equality filter plus ORDER BY
  • Understand why deep OFFSET and exact count(*) stay expensive even with an index
  • Apply keyset (seek) pagination as the durable application-side fix

Contents

  1. The stand
  2. Bring it up
  3. The schema
  4. Apply the schema
  5. The data
  6. Load it
  7. A clean slate for statistics
  8. Reproduce the pain
  9. Let the traffic flow
  10. Ask the database what hurts
  11. The page under the microscope
  12. The count under the microscope
  13. Design the fix
  14. Apply it
  15. What the index fixed — and what it didn't
  16. Close the ticket