SQL Pagination: OFFSET vs Keyset

SQL pagination is one of the most important query design topics in real applications because almost every system needs to show data in pieces instead of returning everything at once.

That matters because users rarely need:

• all orders ever created
• all support tickets in one response
• every product in one page load
• every activity event in a feed
• or every customer record in one table render

Instead, applications usually need:

• page 1 of recent orders
• the next 20 notifications
• the next chunk of search results
• or another slice of a large dataset

That is what pagination is for.

But not all pagination strategies behave the same way.

The two most common SQL pagination approaches are:

• OFFSET pagination
• keyset pagination

At first, OFFSET pagination looks easier and more familiar. And in many cases, it is perfectly fine.

But as data grows, or as concurrency increases, teams often discover that OFFSET pagination starts causing problems like:

• slower queries on deeper pages
• missing rows
• duplicate rows
• unstable ordering
• and inconsistent user experience when new data is inserted between page requests

That is where keyset pagination becomes important.

This guide explains SQL pagination using OFFSET vs keyset clearly, including:

• how each method works
• where each one shines
• where each one breaks down
• what indexing matters
• what API tradeoffs matter
• and how to choose the right pagination strategy for real systems

Why pagination matters so much

Pagination is not only a frontend concern. It is deeply tied to:

• SQL query shape
• database performance
• indexing strategy
• API behavior
• and consistency under real traffic

A poor pagination design can make an otherwise normal endpoint become slow and unreliable as the table grows.

For example:

• page 1 may be fast
• page 2 may still feel fine
• page 1000 may become surprisingly expensive

And even worse:

• users may see repeated rows
• rows may disappear between page loads
• or the data order may feel unstable

That is why pagination is not just:

• add LIMIT 20

The real design question is:

• how should the database move through the dataset over time?

That is what OFFSET vs keyset is really about.

The most important rule

Before anything else, remember this:

Pagination is only as good as the ordering behind it.

That is the single most important idea in this entire topic.

If the result does not have a clear, stable sort order, then pagination becomes unreliable.

That means:

• page boundaries can shift
• results can repeat
• rows can be skipped
• and “next page” stops having a precise meaning

So before choosing OFFSET or keyset, always define:

• what column or columns determine row order?
• is that ordering stable?
• what happens when two rows tie on the same sort value?

If you get that wrong, neither pagination method will behave well.

What OFFSET pagination is

OFFSET pagination is the classic page-based approach that uses:

• ORDER BY
• LIMIT
• and OFFSET

Example:

SELECT
    order_id,
    created_at,
    total_amount
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This means:

• sort the rows by newest first
• skip the first 40 rows
• then return the next 20

That is effectively:

• page 3 if each page has 20 items

This style is very common because it is easy to understand.

Why OFFSET pagination feels intuitive

OFFSET pagination matches how people often think about pages.

Examples:

• page 1
• page 2
• page 3
• jump to page 10

That makes it naturally attractive for:

• admin dashboards
• report tables
• search results with visible page numbers
• exports with numbered chunks
• and simple public listings

It maps well to user interfaces that say:

• show page 7 of results

That is one of its biggest advantages.

Basic OFFSET pagination example

Suppose you want page 1 of recent posts:

SELECT
    post_id,
    title,
    created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

Page 2:

SELECT
    post_id,
    title,
    created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

Page 3:

SELECT
    post_id,
    title,
    created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

This is simple, readable, and widely used.

What keyset pagination is

Keyset pagination does not say:

• skip 40 rows

Instead, it says:

• continue from the last row already seen

That means it uses the sort key itself to move forward.

Example:

SELECT
    order_id,
    created_at,
    total_amount
FROM orders
WHERE created_at < '2026-04-04 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

This means:

• give me the next 20 rows older than this timestamp

That is keyset pagination in its simplest form.

It is also often called:

• cursor pagination
• seek pagination

The exact naming depends on context, but the idea is the same:

• continue from a position in the sorted dataset, not from a row count offset

Why keyset pagination feels different

OFFSET pagination thinks in terms of:

• row count position

Keyset pagination thinks in terms of:

• last seen value in the ordered result

That is why OFFSET is natural for:

• page-number interfaces

and keyset is natural for:

• infinite scroll
• activity feeds
• event streams
• cursor-based APIs
• “load more” buttons
• stable continuation from the last seen row

This difference matters a lot when designing APIs and user interfaces.

A simple mental model

A very useful way to compare the two is:

OFFSET pagination

“Skip this many rows, then give me the next batch.”

Keyset pagination

“Start after this last known row, then give me the next batch.”

That is the clearest simple difference.

Why OFFSET pagination becomes slower

This is one of the biggest practical issues.

Suppose you ask for:

LIMIT 20 OFFSET 100000

The database usually still has to:

• identify the ordered result
• move through the first 100000 rows
• discard them
• then return the next 20

That means page depth matters a lot.

The deeper the page:

• the more work the database may have to do before returning the result

This is why OFFSET pagination often feels fine on:

• page 1
• page 2
• page 5

but starts feeling expensive on:

• page 500
• page 5000
• or large scrolling datasets

That is one of the biggest reasons teams switch to keyset pagination.

Why keyset pagination is usually faster

Keyset pagination is usually faster because the query says:

• continue from this known point in the sort order

That often allows the database to use the index more directly.

For example:

WHERE created_at < '2026-04-04 10:00:00'
ORDER BY created_at DESC
LIMIT 20

This lets the engine continue from a known boundary instead of:

• scanning and discarding many earlier rows

That is why keyset pagination tends to scale better on:

• large tables
• active feeds
• audit logs
• transaction histories
• messaging systems
• and any API where users keep moving forward through data

OFFSET pagination and shifting data

Another major issue with OFFSET pagination is instability when the underlying data changes between page requests.

Suppose a user loads page 1:

• newest 20 orders

Then a new order is inserted.

Now the user requests page 2 using:

• OFFSET 20

But the rows have shifted.

That can cause:

• one row to appear again on page 2
• or one row to be skipped entirely

This is one of the biggest real-world problems with OFFSET pagination on live data.

Example of shifting rows with OFFSET

Imagine the sorted rows are:

1. A
2. B
3. C
4. D
5. E

Page 1 with LIMIT 2 OFFSET 0 returns:

• A
• B

Now a new row X arrives at the top. The new sorted order becomes:

1. X
2. A
3. B
4. C
5. D
6. E

Now page 2 with LIMIT 2 OFFSET 2 returns:

• B
• C

But B was already seen on page 1.

That is a duplicate caused by shifting rows.

A similar issue can cause missing rows when deletions happen.

This is one of the strongest arguments for keyset pagination on live systems.

Why keyset pagination is more stable under inserts and deletes

Keyset pagination says:

• continue after the last item I already saw

So if the last seen row had:

• created_at = 2026-04-04 10:00:00
• order_id = 123

then the next query continues from that boundary.

This is usually much more stable because it is not based on:

• how many rows existed before

It is based on:

• where the user actually was in the sorted result

That makes keyset pagination much better for:

• continuously changing datasets
• feeds
• notifications
• event logs
• financial ledgers
• and high-write applications

The importance of stable ordering

Keyset pagination works best when the ordering is both:

• meaningful
• and stable

For example, sorting only by:

ORDER BY created_at DESC

may be fine if timestamps are always unique.

But in many real systems, multiple rows can share the same timestamp.

That creates ambiguity.

So a better pattern is often:

ORDER BY created_at DESC, order_id DESC

Now the ordering is more stable because ties on created_at are broken by order_id.

This is a very important design detail.

Keyset pagination with tie-breakers

If your ordering uses multiple columns, the continuation condition usually needs to reflect that.

Example stable sort:

ORDER BY created_at DESC, order_id DESC

Then the next page condition must usually say:

• rows older than this timestamp
• or rows with the same timestamp but smaller order_id

Conceptually:

WHERE
    created_at < :last_created_at
    OR (
        created_at = :last_created_at
        AND order_id < :last_order_id
    )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

This is one of the most important real keyset pagination patterns.

It ensures the next page continues cleanly through tied sort values.

OFFSET pagination is simpler to implement

One of the biggest advantages of OFFSET pagination is simplicity.

It is easy to:

• understand
• implement
• expose through APIs
• map to page numbers
• and debug

The client can request:

• page 1
• page 2
• page 3

or:

• limit 20, offset 40

This makes it attractive for:

• small internal tools
• simple admin grids
• low-volume dashboards
• report pages with explicit page numbers
• and small-to-medium datasets where deep pagination is rare

This simplicity is a real advantage. Not every system needs keyset pagination.

Keyset pagination is harder to implement, but usually stronger at scale

Keyset pagination is more complex because you need:

• a stable sort key
• a cursor or last-seen value
• careful tie-break handling
• and sometimes more API design thought

You also usually cannot jump directly to:

• page 100

because keyset pagination is position-based, not page-number-based.

That makes it a little harder for:

• classic page number UIs
• arbitrary jumping between pages
• or some report-style experiences

But in exchange, you usually get:

• better performance on large datasets
• more stable results
• fewer duplicates and missing rows
• and better user experience for “load more” or infinite-scroll flows

That is why high-scale applications often prefer it.

When OFFSET pagination is a good choice

OFFSET pagination is usually a good choice when:

• the dataset is small or moderate
• page numbers matter to the UI
• users need to jump to arbitrary pages
• deep pages are rare
• implementation simplicity matters
• and minor shifting under concurrent writes is acceptable

Examples:

• internal admin tables
• low-volume reporting pages
• documentation lists
• product catalogs with limited depth
• dashboards where the first few pages are most common

In these cases, OFFSET pagination may be completely fine.

When keyset pagination is a good choice

Keyset pagination is usually a good choice when:

• the table is large
• the result set changes frequently
• users scroll forward through time or sequence
• consistent traversal matters
• performance on deep pages matters
• and the UI does not require arbitrary page jumps

Examples:

• notification feeds
• activity streams
• event logs
• audit trails
• transaction history
• chat or messaging history
• API cursor pagination
• admin tables on high-volume systems

These are classic keyset pagination workloads.

OFFSET pagination and user experience

OFFSET pagination works especially well for UIs that show:

• page numbers
• total pages
• jump to page
• go to last page

That is because it maps cleanly to:

• page_number
• page_size

It is conceptually friendly for users when the dataset is relatively stable and not extremely large.

So although OFFSET has technical limitations, it still fits many user experiences well.

Keyset pagination and user experience

Keyset pagination works especially well for UIs that show:

• next
• previous
• load more
• infinite scroll
• continue from here

It maps naturally to:

• cursor-based APIs
• mobile feeds
• event browsing
• and timeline-based interfaces

It is less natural for:

• “go to page 17”

but often much better for:

• “show me the next 20 most recent items”

That is why product UX should influence pagination choice too.

Counting total rows is easier with OFFSET-style page UIs

A lot of page-based interfaces want to show:

• total results
• total pages

That often goes naturally with OFFSET pagination.

Keyset pagination is usually more focused on:

• efficient traversal forward and backward

not on:

• total page count

This does not mean total counts are impossible with keyset pagination. It just means they are not as central to the model.

So if the UI strongly depends on:

• page numbers
• page counts
• jumping around

OFFSET may still be the easier fit.

Indexing matters for both methods

Pagination performance depends heavily on indexing.

For OFFSET pagination, a good index can help:

• the sort
• the filter
• and the early result pages

But deep offsets still become costly because skipped rows still matter.

For keyset pagination, a good index is even more important because the whole strategy depends on continuing efficiently from a known sort position.

A strong pagination index usually matches:

• the filter conditions
• and the sort order

Example:

WHERE customer_id = 42
ORDER BY created_at DESC, order_id DESC

A helpful index might reflect:

• customer_id
• created_at
• order_id

in a compatible order.

This is one of the biggest practical reasons pagination and indexing should be designed together.

Keyset pagination usually needs deterministic sort keys

A good keyset pagination design often needs:

• a unique or near-unique ordering path

Why?

Because if the order is ambiguous, the cursor position is ambiguous too.

Good keyset sort patterns often use:

• timestamp plus ID
• score plus ID
• created_at plus primary key
• name plus ID if alphabetical traversal is needed
• event_time plus event_id

The tie-breaker matters a lot.

Without it, users may see:

• repeated rows
• skipped rows
• or unstable continuation

That is why deterministic sorting is not optional in serious keyset pagination.

Backward pagination with keyset

Forward keyset pagination is the most common case.

But sometimes APIs also need:

• previous page
• backward scrolling
• reverse traversal

This is possible, but it usually requires:

• reversing the comparison logic
• reversing the sort
• or careful cursor management

That is one reason keyset pagination can feel more complex than OFFSET.

It is not impossible. It just requires more deliberate API design.

Cursor pagination in APIs

A lot of modern APIs expose keyset pagination through cursors.

Instead of asking for:

• page=3

the client sends:

• a cursor token
• or the last seen key values

The cursor may encode things like:

• last timestamp
• last ID
• sort direction
• filter state

That makes the API cleaner because the client does not need to build the SQL logic itself. It just says:

• give me the next page after this cursor

This is one of the most common production uses of keyset pagination.

Common mistakes with OFFSET pagination

There are a few very common problems.

1. Using OFFSET on huge tables without realizing deep pages get expensive

This is the biggest one.

2. Paginating without a stable ORDER BY

This creates inconsistent results.

3. Sorting by a non-unique column without a tie-breaker

This causes unstable page boundaries.

4. Using OFFSET in live rapidly changing feeds

This often causes duplicates and skipped rows.

5. Assuming performance on page 1 reflects performance on page 5000

It often does not.

Common mistakes with keyset pagination

There are also some classic keyset mistakes.

1. Using an unstable sort key

This breaks continuation.

2. Forgetting tie-breakers

Rows with equal sort values become ambiguous.

3. Designing a cursor that does not match the ORDER BY

This leads to wrong page boundaries.

4. Trying to support arbitrary page numbers with keyset as if it were OFFSET

That is usually the wrong fit.

5. Forgetting that filter conditions and cursor conditions must work together

The cursor logic should be consistent with the full query shape.

Practical examples

Example 1: OFFSET pagination for an admin table

SELECT
    user_id,
    email,
    created_at
FROM users
ORDER BY created_at DESC, user_id DESC
LIMIT 25 OFFSET 50;

This is good for:

• simple page-number admin UIs
• moderate data volume
• easy implementation

Example 2: keyset pagination for recent orders

SELECT
    order_id,
    created_at,
    total_amount
FROM orders
WHERE
    created_at < '2026-04-04 10:00:00'
    OR (
        created_at = '2026-04-04 10:00:00'
        AND order_id < 12345
    )
ORDER BY created_at DESC, order_id DESC
LIMIT 25;

This is good for:

• large datasets
• live recent-order views
• “load more” style interfaces

Example 3: keyset pagination within one customer’s orders

SELECT
    order_id,
    created_at,
    total_amount
FROM orders
WHERE customer_id = 42
  AND (
      created_at < '2026-04-04 10:00:00'
      OR (
          created_at = '2026-04-04 10:00:00'
          AND order_id < 12345
      )
  )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

This shows that keyset pagination works well even with additional filters, as long as the ordering and cursor logic stay aligned.

A practical decision framework

If you are deciding between OFFSET and keyset pagination, ask:

1. Is the dataset small enough that deep offsets are not a real issue?

If yes, OFFSET may be fine.

2. Does the UI need page numbers or jump-to-page behavior?

If yes, OFFSET may be a better fit.

3. Is the dataset large, active, or constantly changing?

If yes, keyset is often better.

4. Does the user experience look like infinite scroll, next page, or load more?

If yes, keyset is often a natural fit.

5. Do stable continuation and consistency matter more than arbitrary page navigation?

If yes, keyset usually wins.

6. Do you already have a strong indexed stable sort key?

If yes, keyset becomes much easier to implement well.

This framework usually gets you to the right choice.

FAQ

What is the difference between OFFSET pagination and keyset pagination?

OFFSET pagination skips a number of rows and then returns the next page, while keyset pagination uses the last seen sort key to continue from a specific position in the result set.

Why is keyset pagination usually faster than OFFSET pagination?

Keyset pagination is usually faster because the database can continue from a known indexed position instead of scanning and discarding a large number of earlier rows.

When should I use OFFSET pagination?

Use OFFSET pagination when the dataset is small, page numbers matter to the user experience, or simplicity is more important than maximum scalability and consistency.

When should I use keyset pagination?

Use keyset pagination for large tables, high-traffic APIs, activity feeds, admin grids, event logs, and any system where stable ordering and consistent page traversal matter.

Final thoughts

SQL pagination is one of those topics that seems simple at first but becomes very important as systems grow.

The key difference is straightforward:

• OFFSET pagination says “skip this many rows”
• keyset pagination says “continue after this last row”

That sounds small, but it affects:

• performance
• consistency
• indexing
• API design
• and user experience

The best practical rule is:

• use OFFSET when simplicity and page-number navigation matter, and the dataset or workload can support it
• use keyset when scale, stability, and efficient forward traversal matter more

If you understand that tradeoff clearly, you will make much better pagination decisions in real applications, especially once the data stops being small and the traffic stops being gentle.