-- Page 1
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;
Let’s say the last row returned has:
created_at = '2024-06-10 09:00:00'
Now for Page 2, we run:
-- Page 2
SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;
This query says: “Give me the next 10 rows older than what I already saw.”
❓What Happens If Rows Were Deleted?
🧼 If a previously returned row is deleted:
- It won’t affect the next page.
- The Seek query still returns “the next 10 older rows” — just skipping over the now-missing row.
✅ No duplication
✅ No skipping unintended rows
✅ Pagination still correct
🧪 Example
Posts table (simplified):
| id | content | created_at |
|---|---|---|
| 1 | A | 2024-06-10 10:00:00 |
| 2 | B | 2024-06-10 09:45:00 |
| 3 | C | 2024-06-10 09:00:00 ← last row from page 1 |
| 4 | D | 2024-06-10 08:30:00 |
| 5 | E | 2024-06-10 08:00:00 |
Now suppose row with id = 3 (created_at = 09:00) is deleted.
You rerun page 2:
SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;
You get:
D | 08:30
E | 08:00
✅ Works fine! The deleted row is just skipped.
🛑 BUT: If You Delete the Seek Anchor Row Itself
If you store the last-seen value (like '2024-06-10 09:00:00'), and then that row is deleted, your anchor still works:
WHERE created_at < '2024-06-10 09:00:00'
You’re not relying on the actual row — just its timestamp.
✅ Seek still works even if the anchor row is deleted.
✅ Summary
| Case | Does it break Seek Pagination? |
|---|---|
| Row deleted from previous page | ❌ No — it’s skipped |
| Anchor row deleted | ❌ No — value still valid |
| Massive deletions causing gaps | ❌ No — just fewer results |