Skip to content
A hands-on workshop · 8 chapters · 155 minutes

Every row, exactly once.

Pagination looks like two numbers in a URL. Then rows start to repeat, vanish and arrive mid-page. Learn to page through data that will not sit still, one real bug at a time, in one small record shop.

Start chapter 01Read the source
uv run python -m crate run 4
records when the collector started: 60  (#1 to #60)
while they read: 3 new arrivals after pages 1 and 2, 3 sales after page 4

rows read from the feed: 63
shown twice:  6  (#44 to #46, #51 to #53)
never shown:  3  (#24 to #26)

A newest-first feed, read by offset while the shop changes. Six records came round twice. Three were never shown. Chapter 04 fixes it with a cursor.

What you will learn

From the first page to the last row

Pagination has one promise: walk the pages to the end and you see every row exactly once, in order, without the server doing more work than the page needs. Each chapter keeps one part of that promise.

  1. 01Turn a page number into rowsOffsets, limits and the edges: page 0, the last page and the page after it.
  2. 02Make an order totalWhy ties come back in any order, and the one column that fixes it.
  3. 03Page with a cursorRemember the last row, not the count, so new and deleted rows cannot shift the pages.
  4. 04Design the tokenOpaque, versioned cursors that carry the whole question and refuse the wrong one.
  5. 05Know when to stopShort pages, empty pages, limit plus one, and what a total count really costs.
  6. 06Sync what changedWhy a timestamp loses updates, and the value that only goes up.
Try it

The moving feed

Twelve records, newest first, four to a page. Records arrive at the top and sell while you read. Watch what each way of paging shows you.

  1. 1. With Offset chosen, play the busy pattern. Two records come round twice, in blue, and two are missed, in red.
  2. 2. Switch to Cursor and play it again. Every record is seen once.
  3. 3. That one difference is case 04. Fix it in Python, in chapter 04.
  • Green means seen once.
  • Blue means seen twice.
  • Red means missed.
  • Yellow marks where you are.

Offset skips as many rows as you have read.

012 records, newest first. Read them 4 at a time.

  1. #12not read yet
  2. #11not read yet
  3. #10not read yet
  4. #9not read yet
  5. #8not read yet
  6. #7not read yet
  7. #6not read yet
  8. #5not read yet
  9. #4not read yet
  10. #3not read yet
  11. #2not read yet
  12. #1not read yet
Pages read
0
Rows shown
0
Shown twice
0
Missed
0

Pages you got

None yet. Press Next page.

4 records a page. Missed counts records that were there from the start, are still there, and that you have already gone past.

The index

Eight chapters, eight bugs

Each chapter explains one idea in depth, then hands you a program that gets it wrong in exactly that way. You fix it, and the checker walks every page to prove the fix.

  1. 01Pages and offsetsCase: The first page nobody sawoffsets, page numbers, edges15 min
  2. 02LimitsCase: The page with every recordlimits, defaults, page size15 min
  3. 03A stable orderCase: Musical chairstotal order, tie-breakers20 min
  4. 04CursorsCase: The feed that repeated itselfoffset drift, keyset cursors25 min
  5. 05Cursors on a sort with tiesCase: The price that hid recordscomposite keys, deep pages20 min
  6. 06Opaque cursorsCase: The cursor that forgot the questionopaque tokens, versioning20 min
  7. 07Knowing when to stopCase: The client that gave up earlyshort pages, has more, counts15 min
  8. 08Syncing changesCase: The update the sync never sawchange feeds, versions, clocks25 min
How a case works

Run it, fix it, prove it

Every case is a short Python program with one bug in it, and nothing to install beyond uv. Here is case 07 from start to finish.

  1. 1Run it
    uv run python -m crate run 7
    records for sale:   189
records downloaded: 18
requests made:      1
last reply:         18 records, next = '20'

    The server said there was more. The client stopped anyway.

  2. 2Fix it
    lab/cases/case07_stop.py
    if page.next is None:
break  # only the server knows where the end is

    Stop on the one signal the server documents.

  3. 3Prove it
    uv run python -m crate check 7
    PASS  every record for sale is downloaded, once (189 of 189)
PASS  it stops when next is None: 12 requests, no more (12)
PASS  an empty page in the middle does not stop it
PASS  a single short last page takes one request

    Every record, once, and not one request too many.

Measured, not guessed

What an offset costs

The same page, fetched by OFFSET and by cursor, at five depths. And the same feed, read by each while the shop changes between pages.

One page of 20, at each depthSQLite, 200,000 records. Python 3.13.16, SQLite 3.45.1, Linux, 4 CPUs.
  1. 0 rows before the pageOFFSET is 1x
    OFFSET0.014 ms
    cursor0.014 ms
  2. 1,000 rows before the pageOFFSET is 2x
    OFFSET0.030 ms
    cursor0.014 ms
  3. 10,000 rows before the pageOFFSET is 6x
    OFFSET0.093 ms
    cursor0.014 ms
  4. 100,000 rows before the pageOFFSET is 49x
    OFFSET0.917 ms
    cursor0.019 ms
  5. 199,980 rows before the pageOFFSET is 174x
    OFFSET2.151 ms
    cursor0.012 ms
A feed that changes while you read1,000 records, 20 a page, read to the end. Fixed seeds, so these are exact.
Between pagesOffset: twice, missedCursor: twice, missed
nothing changes0000
1 new record52000
5 new records330000
1 seen record sold04700
3 seen records sold012900

From crate try deep and crate try drift. Chapters 04 and 05 explain every number, and the timings on your machine will differ a little.

Quick start

Four commands to the first bug

You need git and uv. uv installs Python 3.13 for you, and the lab uses the standard library only. No accounts, no API keys, no Docker.

shell
git clone https://github.com/jars-demo/pagination-demo.git
cd pagination-demo/lab
uv sync
uv run python -m crate run 1
Take it with you

The checklist

Ten rules, one per idea in the workshop. The last chapter ends with the same list.

  1. 01Count pages from 1 for people and from 0 for code, and convert in one place.
  2. 02Give every list a default limit and a maximum, and refuse or clamp anything outside them.
  3. 03End every ORDER BY with a unique column, so the order is total.
  4. 04Use a cursor, not an offset, for any list that changes while people read it.
  5. 05Make the cursor the whole sort key, and seek strictly after it.
  6. 06Keep cursors opaque, and put the whole query inside them: filters, sort and direction.
  7. 07Return a next cursor, and let only its absence mean the end.
  8. 08Fetch one row more than the page to know if there is a next one. Count only when someone needs the number.
  9. 09Sync changes by a version that only goes up and that no two writes share, never by a timestamp.
  10. 10Test pagination by walking every page while the data changes, and check each row appears exactly once.