The Seek Method avoids “shifting” caused by inserts or deletes because it doesn’t depend on row positions like OFFSET does—it uses stable, deterministic values (like timestamps or IDs) to continue pagination.
Let me explain it clearly:
🔁 The Problem with OFFSET
Imagine this:
-- Get page 2, 10 per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;
You fetch rows 11–20, assuming rows 1–10 came before.
But if someone inserts or deletes rows between queries, the row positions shift:
| Before | After Insert (newer post) |
|---|---|
| Row 10 = Post A | Row 10 = new post inserted |
| Row 11 = Post B | Row 11 = Post A (was 10) |
| Row 12 = Post C | Row 12 = Post B (was 11) |
Your query might:
- Skip Post A
- Repeat Post B
- Return inconsistent pages
⚠️ This makes
OFFSET-based pagination unstable if data changes.
✅ Why Seek Method Doesn’t Shift
Seek pagination uses the value of the last item seen:
-- Get first 10
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;
You get the latest 10. Let’s say the last one has:
created_at = '2024-06-10 09:00:00'
Then the next page uses:
SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;
Even if new rows are inserted:
- They’ll have newer timestamps
- So they don’t affect this page
- This page starts after the last-seen value → no skipping or duplication
🧠 Summary
| Behavior | OFFSET | SEEK |
|---|---|---|
| Rows shift on INSERT/DELETE | ❌ Yes | ✅ No |
| Based on row position | ✅ Yes | ❌ No |
| Based on known column values | ❌ No | ✅ Yes |
| Consistent across changes | ❌ No | ✅ Yes |