We will preface what was mentioned in the short reel to set the context first. Context: High quantity of data, content management systems often have too many rows of data to display, for UX/UI purposes and frontend optimizations, we want to use pagination to reduce the latency to seeing content, while also preventing duplicates Real World Example: Instagram, Facebook posts whereby you want to see posts quickly and scroll, however, you do not ever want to see duplicate posts for user experience In a quick minimum viable product setup, you would use \`offset pagination\` Example table | Row Index (Not in SQL table) | id | title | created_at | | --- | --- | --- | --- | | 0 | 1 | Getting Started with SQL | 2026-09-06 10:30:00 | | 1 | 2 | Understanding Database Indexes | 2026-09-05 15:45:00 | | 2 | 3 | REST API Best Practices | 2026-09-04 09:20:00 | | 3 | 4 | Introduction to PostgreSQL | 2026-09-03 18:10:00 | ```plain SELECT id, title, created_at FROM posts ORDER BY created_at DESC LIMIT 2 OFFSET 2; ``` The key idea is to just go through your DB and get rows based on their offset as a first naive solution. So this query above will be fetching `Index 2 (REST API) ` and `Index 3 (Introduction to PostgreSQL) ` . So in order to fetch everything from DB, you would have to do `OFFSET 0` and `OFFSET 2` and display it to the user. Problem: 1. Database will scan every skipped row. If you have 500,000 rows in your DB then this query by itself will scan all rows -> Slow Deep page queries 2. It is possible to find multiple examples where rows are either skipped or duplicated -> Unstable pagination in inserts and Deletes For (1), it is based on how database are structured. They are structured on B trees and Linked Lists and not arrays, thus its not easy to find memory offsets since OFFSET can be applied to multiple kinds of conditional queries. For (2), suppose a new post is inserted while user is scrolling, it is possible that the insertion occurs at the boundary. In the example below, we created a new row ( for example backfilling a post). This results in the example table below. | Row Index (Not in SQL table) | id | title | created_at | | --- | --- | --- | --- | | 0 | 1 | Getting Started with SQL | 2026-09-06 10:30:00 | | 1 | 2 | Understanding Database Indexes | 2026-09-05 15:45:00 | | 2 | 5 | This is just a TEST | 2026-09-05 14:00:00 | | 3 | 3 | REST API Best Practices | 2026-09-04 09:20:00 | | 4 | 4 | Introduction to PostgreSQL | 2026-09-03 18:10:00 | In this case, if the UI was rendered for original `INDEX 2 (REST API) and 3 (INTRO to Postgres)` -> Insertion occurs, this led to the rows below from being pushed down -> `Index 4 (INTRO to Postgres` is now return as part of the new query for `LIMIT 2 OFFSET 4` It is possible, to also construct the case where a row is deleted. Then it is possible that the DB rows move up, and a post is skipped. lets say `LIMIT 2 OFFSET 2` query -> User deletes `Index 2(This is just a test`) -> next query is `LIMIT 2 OFFSET 4` , we have succesfully skipped over `Introduction to PostgreSQL (Index 4 -> Index 3) ` Remember that you are able to construct multiple kinds of conditional query in SQL `Where` which can have such a side effect, depending on the logic, sorting order and more Solution: Cursor Pagination Instead of **using a query that is like skipping n rows**, you can use the idea of `give me everything after this item` , using some sort of cardinality that does not rely on the table's natural offset. Examples are `id` or `created_at` timestamp. ```plain SELECT id, title, created_at FROM posts WHERE created_at < cursor ORDER BY created_at DESC LIMIT 2; ``` What this does underneath the hood, is allowing B+ Index trees to seek for the pages immediately through traversal and return you a page thus preventing a full table scan, solving (1), and if we use a `stable and immutable key` for pagination, we solve (2). Immutable prevents the anomalies. **Important: Why stable and immutable key?** Cursor pagination assumes a **stable, deterministic ordering**: once a row has been passed by the cursor, its position relative to that cursor does not change. New inserts and deletes may change the dataset, but they **must not move existing rows across the cursor boundary** Assumption thus far: There must exist a Index in the DB using `created_at` such that it acts as a `composite key` for efficient search. Else, it does not really solve (1). Extended: **As a junior dev**, I think something that screams out at you, might be caching. Why do we focus on the DB, when most reads occur from cache in large scalable systems, I think below is a table that was generated from AI that tells us pagination is solving a different problem that cache does not solve. Additionally, the stronger idea is to pre-compute into the cache the list of id you would want to fetch (sort of like a queue) and use cursor pagination to actually fetch this id (ChatGPT Table) | Problem | Pagination | Cache | | --- | --- | --- | | Too much data transferred | Yes | No | | Huge DB result set | Yes | No | | Reduce response size | Yes | No | | Avoid repeatedly hitting DB | No | Yes | | Reduce latency | Yes | Yes | | Handle continuously changing data | Cursor pagination helps | Cache invalidation is hard | | Keep memory/storage bounded | Yes | No | \`\`\`plantuml @startuml actor User rectangle "Where does the ordered list come from?" { rectangle "Simple System" { database "Posts DB" as DB rectangle "B+ Tree Index" as Index DB --> Index Index --> "Ordered Results\n[91, 82, 73, 61, 55, ...]" } rectangle "Large-Scale Feed" { rectangle "Feed Generation\n+ Ranking" as FeedGen database "Materialized Feed Cache" as FeedCache FeedGen --> FeedCache note right of FeedCache Alice: [91, 82, 73, 61, 55, ...] end note } } @enduml \`\`\` Signed, Sean Disclaimer: I use AI for diagrams and some knowledge, however, majority of this is written and vetted by me. Source: Youtube videos on cursor pagination vs offset, AI on understanding assumptions surrounding such choices.