Offset vs Cursor Pagination

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
1	2	Understanding Database Indexes	2026-09-05 15:45
2	3	REST API Best Practices	2026-09-04 09:20
3	4	Introduction to PostgreSQL	2026-09-03 18:10

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
1	2	Understanding Database Indexes	2026-09-05 15:45
2	5	This is just a TEST	2026-09-05 14:00
3	3	REST API Best Practices	2026-09-04 09:20
4	4	Introduction to PostgreSQL	2026-09-03 18:10

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.

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.