Why Your Feed Shows Duplicate Posts
44sRelatable bug that many developers have faced, explained with a clear real-world scenario.
▶ Play ClipAI Summary
This video explains the difference between offset and cursor-based pagination, highlighting the problems of offset pagination at scale and how cursor pagination solves them. It uses a real-world example of duplicate posts to illustrate the issues and provides a clear recommendation for production systems.
[00:01]
Offset pagination problem
Offset pagination (e.g., LIMIT 10 OFFSET 1000) skips N rows, but the database still scans every skipped row, causing performance degradation at scale.
[00:28]
Duplicate posts bug
When new posts are inserted while a user scrolls, rows shift, causing the same post to appear on multiple pages. Deletions can also cause posts to be skipped entirely.
[00:42]
Cursor pagination solution
Cursor pagination uses a cursor (timestamp or ID) to fetch items after a specific point, avoiding scans and shifting issues.
[00:55]
Index seek efficiency
Cursor pagination uses an index seek to jump directly to the position on disk, eliminating the need to scan and skip rows.
[01:09]
Recommendation
Offset pagination is fine for admin dashboards or small scale, but cursor-based pagination is non-negotiable for feeds with real traffic.
Cursor-based pagination is essential for scalable, reliable APIs, preventing duplicate or missing items and improving performance.
Mentioned in this Video
Study Flashcards (3)
What are the two main problems with offset pagination at scale?
easy
Click to reveal answer
What are the two main problems with offset pagination at scale?
easy
Click to reveal answer
1. The database scans every skipped row, causing performance issues. 2. New insertions or deletions can cause duplicate or missing items.
00:13
How does cursor pagination avoid the duplicate posts bug?
medium
Click to reveal answer
How does cursor pagination avoid the duplicate posts bug?
medium
Click to reveal answer
It uses a cursor (timestamp or ID) to fetch items after a specific point, so new insertions don't shift the results.
00:42
What is the recommended pagination method for high-traffic feeds?
easy
Click to reveal answer
What is the recommended pagination method for high-traffic feeds?
easy
Click to reveal answer
Cursor-based pagination is non-negotiable for feeds with real traffic.
01:09
💡 Key Takeaways
💡
Duplicate posts bug explained
Illustrates a real-world consequence of offset pagination that developers often encounter.
00:28
🔧
Index seek efficiency
Explains the performance advantage of cursor pagination in a clear, technical way.
00:55Full Transcript
[00:01] testing, but then in your production and users started to see duplicate posts. Why? Well, you were using what's called offset pagination. A query like limit offset pagination. A query like limit 10, offset by 1,000. It skips N rows and
[00:13] simple and it's the default for most systems, but it has two big problems at scale. First, the database still scans every skipped row. At offset 500,000, you're throwing away half a million rows on every request. Second, this is where
[00:28] our duplicate bug came in. If a new post gets inserted while a user is scrolling, every row shifts down by one. So, the post that was at position 10 is now at position 11. Next page starts at offset 10 and you've served them the same post
[00:42] again. Or, if a row got deleted and you end up skipping one entirely. Cursor pagination fixes both of these issues. Instead of skip N rows, you say, "Give me everything after this item." Usually using the timestamp or the ID from the
[00:55] last item the client saw. So, the query becomes something more like this. The index seeks straight to the spot on disk, no scans, no shifting, and no fine for admin dashboards or small scale, but for any feed with real
[01:09] traffic, cursor-based pagination is a non-negotiable. Check out our full breakdown of pagination and API design over at hellonext.com.
