Picture yourself at a busy airport gate. The agent announces: “We’ll now board passengers in rows 20 through 30.” Simple, efficient, everyone knows whether it’s their turn. Now imagine instead they said: “We’ll board passengers 200 through 300 by check-in order.” Chaos. People counting, confusion, delays. That’s the difference between seek-based and offset-based pagination.
The Offset Boarding Problem
Offset pagination works like this:
Gate Agent: “Now boarding passengers 51-100 in check-in order!”
Problems:
- Everyone starts counting from the beginning
- Person #73 waits for 72 people to be counted
- If person #65 doesn’t show up, everyone shifts
- Late arrivals mess up the entire count
- The agent literally counts: “1, 2, 3… 50, okay NOW: 51!”
This is offset pagination: SELECT * FROM passengers LIMIT 50 OFFSET 50
The database counts through all those first 50 records just to skip them. Boarding passenger 10,000 means counting through 9,999 first.
The Seek Boarding Solution
Now the efficient approach:
Gate Agent: “Now boarding everyone with seat numbers after 14B!”
Advantages:
- Look at your boarding pass - instant decision
- No counting required
- Missing passengers don’t affect others
- Late arrivals slot in naturally
- The agent just checks: “Is your seat after 14B?”
This is seek pagination: SELECT * FROM passengers WHERE seat > '14B' LIMIT 50
The database uses an index to jump directly to seat 14B. No counting.
The Boarding Pass is Your Cursor
This diagram requires JavaScript.
Enable JavaScript in your browser to use this feature.
Your boarding pass (the cursor) contains all the information needed to find your position. No counting from the start required.
Real-World Airport Scenarios
Scenario 1: VIP Cuts the Line
Offset: Everyone’s position changes, count disrupted Seek: No problem, VIP has seat 1A, boards first regardless
Scenario 2: Family Doesn’t Show
Offset: Positions 145-149 now empty, everyone shifts Seek: Seats 23A-23E empty, but 24A still knows they’re next
Scenario 3: International Flight (10,000 passengers)
Offset: “Now boarding passengers 9,951-10,000” [agent counts for an hour] Seek: “Now boarding seats 95A and higher” [instant]
The Technical Reality
When you use offset pagination:
The database query says: “Give me 20 results, but first skip 19,980 rows”
- The database starts at row 1
- Counts through rows 1 to 19,980
- Returns rows 19,981 to 20,000
- All that counting takes time
With seek pagination:
The query says: “Give me 20 results starting after ID 487291”
- The database uses its index to jump directly to ID 487291
- Returns the next 20 rows immediately
- No counting required
When Offset Makes Sense
Sometimes you do need offset pagination:
Fixed Catalogs: “Jump to page 50 of 100”
- Product catalogs
- Photo galleries
- Search results with relevance scoring
Small Data: When counting is cheap
- User lists (hundreds)
- Blog posts (thousands)
- Settings pages
The Compound Boarding Pass
Sometimes your “seat” is complex. When paginating by multiple criteria (like Section-Row-Seat):
- First, check if the date is after March 15, 2024
- If the date is exactly March 15, then check if ID is greater than 1234
- This ensures proper ordering when you have compound keys
- Results are sorted by date first, then by ID within each date
It’s like boarding instructions that say: “Board if you’re in Section A, Row 16 or higher, OR if you’re in Section A, Row 15, Seat B or later.” The compound condition ensures nobody gets skipped.
Implementing Smart Boarding
Here’s how seek pagination works:
-
If given a cursor (previous position):
- Decode it to find the last seat seen
- Query for all seats after that position
- Limit to the requested number of results
-
If no cursor (first page):
- Start from the beginning
- Get the first batch of results
-
After getting results:
- Take the last item’s position
- Encode it as a cursor for the next request
- Return both the results and the cursor
The cursor acts like a bookmark, telling you exactly where to continue reading without counting pages from the beginning.
The Cursor Contents
A good cursor (boarding pass reference) contains:
- Last seen ID/seat
- Sort direction
- Any filter state
- Timestamp (for consistency)
Encoded as an opaque token: eyJzZWF0IjoiMTRCIiwidHMiOjE2MjM0NTY3ODl9
Performance at Scale
The difference is measurable:
| Page | Offset Time | Seek Time |
|---|---|---|
| 1 | 1ms | 1ms |
| 100 | 10ms | 1ms |
| 1000 | 100ms | 1ms |
| 10000 | 1000ms | 1ms |
Offset boarding gets slower with each page. Seek boarding stays fast.
Decision Rules
Use seek pagination when:
- Your dataset exceeds a few thousand rows
- Users need to paginate through large result sets
- You’re iterating over changing data (new items can appear mid-iteration)
Use offset pagination when:
- You genuinely need random access to any page
- Working with small, static datasets
- Building UI that shows “page X of Y”
Your database indices are the gate agents who can instantly locate seat 47B without counting through seats 1A-47A. Give your queries boarding passes, not passenger numbers.
