Database.Middle.What is the Seek Method?

The Seek Method paginates by using a stable, indexed column value (like id or created_at) from the last row of the previous page, instead of counting and skipping rows like OFFSET does.

🧠 Core Idea

Instead of saying:

-- Offset method: skips rows
LIMIT 10 OFFSET 20;

You say:

-- Seek method: starts "after" a known value
WHERE created_at < '2024-01-01 10:00:00'
ORDER BY created_at DESC
LIMIT 10;

🧭 You’re saying: “Give me the next 10 posts older than this timestamp.”

✅ Example

Table: posts

id	content	created_at
105	Hello world	2024-06-10 10:00:00
104	Post 2	2024-06-10 09:55:00
103	Post 3	2024-06-10 09:45:00
…	…	…

First Page:

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

You get the latest 10 posts. The last post’s created_at is 2024-06-10 09:00:00.

Next Page (Seek):

SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;

→ Returns the next 10 posts older than that date.

Repeat by tracking the last created_at on each page.

✅ Advantages

Benefit	Why it Matters
🔄 Consistent results	No shifting from inserts/deletes on other rows
⚡ Very fast	Especially for deep pages (no OFFSET scan)
💪 Works with indexes	Optimized for indexed columns

⚠️ Limitations

Drawback	Description
❌ No page numbers	You can’t jump directly to page 10
❌ Need state	The client must remember the last seen value

🧰 Pro Tips

• Use unique values in ORDER BY, like created_at, id, to avoid duplicates:

ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < (?, ?)

ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < (?, ?)

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

🔁 Summary

Feature	OFFSET	Seek Method (Keyset)
Speed	Slower on deep pages	Fast even for page 1000
Result Stability	Can shift on inserts	Stable and consistent
Page jumping	✅ Yes	❌ No (next/prev only)
Requires bookmark?	❌ No	✅ Yes (last seen value)