PostgreSQL Pagination Performance: OFFSET vs Keyset

Pagination looks simple at first.

You run a query, limit the result set, and return the next page when the user asks for more.

But in PostgreSQL, pagination strategy matters a lot once:

• tables get large
• queries involve sorting
• users browse deeper into result sets
• or data changes between requests

That is where the difference between OFFSET pagination and keyset pagination starts to matter.

Many teams begin with LIMIT and OFFSET because it is simple and easy to explain. That is fine for small datasets and back-office tools.

But once the application grows, OFFSET pagination often becomes slower and less reliable than people expect. At that point, keyset pagination usually becomes the better design.

This guide explains how OFFSET pagination and keyset pagination work in PostgreSQL, why their performance differs, and how to choose the right approach for your application.

The Most Important Pagination Rule

Before comparing the two approaches, remember this:

OFFSET pagination is simple, but it gets more expensive as page depth increases. Keyset pagination is more scalable because it continues from a known position instead of skipping rows.

That is the central tradeoff.

OFFSET is easier to implement and matches traditional page-number interfaces. Keyset is usually faster and more stable for large datasets, but it changes how the API and UI are designed.

If you remember that distinction, most pagination decisions become much easier.

1. What OFFSET Pagination Is

OFFSET pagination uses a query pattern like this:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

That means:

• sort the rows
• skip the first 40
• return the next 20

This is the classic page-number model.

Examples:

• page 1 = OFFSET 0
• page 2 = OFFSET 20
• page 3 = OFFSET 40

This style is easy to understand because it maps naturally to numbered pages.

It is common in:

• admin dashboards
• report screens
• search results with direct page navigation
• and basic CRUD interfaces

2. Why OFFSET Pagination Feels Good at First

OFFSET pagination is popular for good reasons.

It is:

• easy to write
• easy to explain
• easy to integrate into APIs
• and easy to connect to page 1, page 2, page 3 UI patterns

It also works perfectly well when:

• tables are small
• users rarely browse deep into results
• query cost is low
• or pagination is mainly for convenience instead of high-throughput performance

That is why many teams start there.

The problem is not that OFFSET is wrong. The problem is that it scales poorly when result sets get deeper.

3. Why OFFSET Pagination Gets Slower

The performance issue comes from what PostgreSQL still has to do.

When you ask for:

LIMIT 20 OFFSET 10000

PostgreSQL does not magically jump to row 10001 for free.

It still has to:

• identify rows in the sorted order
• move past the first 10000 rows
• and only then return the next 20

So deeper pages mean more skipped work.

That means:

• page 1 may be fast
• page 10 may still be acceptable
• page 500 may become much slower

This is why OFFSET cost tends to grow with page depth.

Even with good indexes, PostgreSQL still has to walk through rows it is not returning.

4. OFFSET Pagination Wastes Work on Deep Pages

A useful mental model is this:

OFFSET pagination pays for rows it throws away.

If the user requests page 1000, the database may still process or step through a large number of earlier rows just to reach the desired slice.

That wasted work becomes more noticeable when:

• the table is large
• the sort is expensive
• the query joins other tables
• the filter is selective but still returns many rows overall
• or many users are paginating concurrently

This is why OFFSET pagination often looks fine in development but becomes a real cost in production.

5. What Keyset Pagination Is

Keyset pagination avoids skipping rows by continuing from the last row already seen.

Instead of saying:

• skip 10000 rows

it says:

• continue after this known sort position

Example:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-04-03 10:15:00', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This means:

• return the next 20 rows after the last row from the previous page

That is why keyset pagination is sometimes called:

• cursor pagination
• seek pagination
• cursor-based pagination

The name varies, but the idea is the same: use the last seen sort key as the starting point for the next page.

6. Why Keyset Pagination Is Faster

Keyset pagination is usually faster because PostgreSQL can use the index to continue from a known boundary.

Instead of stepping through thousands of skipped rows, it can seek into the right part of the ordered data and continue from there.

That makes performance much more stable across page depth.

In practical terms:

• page 1 can be fast
• page 10 can still be fast
• page 1000 can also remain efficient

The query cost does not grow the same way OFFSET does, because the database is not repeatedly discarding earlier rows.

This is the main performance reason teams switch to keyset pagination.

7. Keyset Pagination Needs a Stable Sort Order

Keyset pagination works best when the sort order is:

• explicit
• stable
• indexed
• and unique enough to avoid ambiguity

For example, sorting only by:

• created_at DESC

may not be enough if multiple rows share the same timestamp.

A safer pattern is:

• ORDER BY created_at DESC, id DESC

That way the order is deterministic.

The cursor can then use both values:

• the timestamp
• and the unique row ID as a tie-breaker

This matters because pagination breaks down if rows have no reliable order.

8. The Index Must Match the Pagination Pattern

This is one of the most important PostgreSQL pagination rules.

If the query sorts by:

• created_at DESC, id DESC

then the index should support that access pattern.

A matching index might look like:

• (created_at DESC, id DESC)

or the equivalent ordering PostgreSQL can use effectively.

Likewise, if the query is tenant-scoped or filtered, the index often needs to begin with the filter columns that shape the result set.

For example:

• (tenant_id, created_at DESC, id DESC)

for multi-tenant queries

Without the right index, both OFFSET and keyset pagination can struggle. But keyset only delivers its full benefit when the index supports the actual sort and filter path.

9. OFFSET Pagination Has Consistency Problems Too

Performance is not the only issue.

OFFSET pagination can also produce unstable user experiences when rows are inserted or deleted between requests.

Imagine a user loads page 1. Then new rows are inserted at the top. Now the user loads page 2 using OFFSET.

Because the row positions shifted, the user may:

• see duplicate rows
• miss some rows entirely
• or get inconsistent page contents

Deletes can cause similar problems.

This happens because OFFSET pagination depends on row position in a moving ordered set.

If the set changes, the meaning of skip the first 20 changes too.

10. Keyset Pagination Is Usually More Stable Under Change

Keyset pagination is usually better when the data changes frequently between requests.

That is because it asks:

• continue after this last seen row

instead of:

• skip this many rows from the current version of the dataset

This makes it much more stable for:

• activity feeds
• event timelines
• logs
• inbox-style lists
• and user-facing infinite scroll interfaces

If rows are added at the top after the first page was loaded, the next page can still continue from the previous boundary instead of drifting unpredictably.

That does not make it perfect for every use case, but it is generally much better for changing datasets.

11. When OFFSET Pagination Is Still Fine

OFFSET is not obsolete. It still makes sense in several situations.

Small datasets

If the table is small, the simplicity may be worth more than the optimization.

Shallow browsing

If users rarely go beyond a few pages, OFFSET may be totally acceptable.

Page-number interfaces

If the UI needs direct navigation to page 7 or page 42, OFFSET fits that model naturally.

Internal tools and admin screens

Back-office systems often prioritize implementation simplicity over maximum scalability.

Reporting screens where exact position matters

Some workflows still want traditional numbered pages.

In these cases, OFFSET is often the correct tradeoff.

12. When Keyset Pagination Is Usually Better

Keyset pagination is usually the better choice when:

The table is large

Deep OFFSETs get expensive on big datasets.

Users scroll continuously

Infinite-scroll or load more interfaces map naturally to keyset.

New rows are inserted frequently

Feeds and timelines benefit from more stable page continuation.

Performance consistency matters

Keyset avoids the growing cost of deep pages.

The sort order is stable and indexable

This is where keyset performs best.

It is especially good for:

• activity feeds
• audit logs
• messaging lists
• transaction history
• product catalogs with load more
• and any list where users usually move forward sequentially

13. Keyset Pagination Has Tradeoffs Too

Keyset pagination is usually better for performance, but it is not free of downsides.

No natural page numbers

You do not easily jump to page 87.

More API complexity

The client has to carry cursor values or encoded tokens.

Sort order must be deliberate

You need a stable ordering strategy.

Bidirectional pagination is more complex

Going backward may need extra logic or reversed queries.

UX expectations may change

Keyset fits next/previous or load more better than classic numbered pages.

So the decision is not just about SQL performance. It also affects product design and API shape.

14. OFFSET Versus Keyset in Real Product Terms

A good way to choose is to think about the user experience.

OFFSET fits:

• Go to page 5
• Show me 50 results per page
• admin tables
• static result sets
• simple back-office browsing

Keyset fits:

• Load more
• Next results
• infinite scroll
• activity streams
• large, changing datasets
• feeds where users move forward sequentially

This is why many modern product interfaces naturally move toward keyset pagination even before the database team gets involved.

The UX and performance goals align well.

15. A Practical Example

Imagine a posts table with millions of rows.

A classic OFFSET query:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Even with good indexing, PostgreSQL still has to work through a large number of preceding rows.

Now compare that with keyset:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-04-03 10:15:00', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The second query continues from a precise boundary. That is why it scales much better for deep traversal.

16. Do Not Forget Filtering

Pagination performance is not only about LIMIT, OFFSET, or cursor design.

It also depends on filters.

For example:

• tenant scoping
• status filtering
• category filtering
• date range filtering

all shape how PostgreSQL can use indexes.

A keyset query becomes much more powerful when the index matches both:

• the filter pattern
• and the ordering pattern

For example:

• (tenant_id, status, created_at DESC, id DESC)

may be more useful than indexing the sort columns alone if that matches the real access path.

This is why pagination performance is really a schema-and-query-design problem, not just a syntax choice.

17. OFFSET Is Often Good Enough Until It Is Not

Many systems do not need keyset pagination on day one.

That is important.

If:

• the data is small
• the UI is admin-only
• the pages are shallow
• and performance is already fine

then OFFSET may be the right answer for now.

But teams should know the warning signs that it is becoming a problem:

• slow deep pages
• rising database cost for list endpoints
• inconsistent pagination under inserts and deletes
• large scans for page N access
• and user-facing feeds that keep growing

At that point, keyset usually becomes the better long-term design.

Common Pagination Mistakes

Using OFFSET on very deep pages of huge tables

This creates avoidable work and latency.

Sorting without a stable tie-breaker

Pagination becomes inconsistent when ordering is ambiguous.

Forgetting the index

Even the best pagination approach depends on the right index support.

Using keyset when the product needs arbitrary page jumps

That creates awkward UX or extra complexity.

Treating pagination as only an API concern

In PostgreSQL, pagination is tightly connected to indexing and sort design.

Ignoring data churn

Frequently inserted or deleted rows make OFFSET less stable.

FAQ

Is keyset pagination faster than OFFSET in PostgreSQL?

Usually yes, especially on large datasets and deeper pages. OFFSET pagination makes PostgreSQL skip more rows as page depth increases, while keyset pagination uses the last seen sort key to continue efficiently.

Should I stop using OFFSET pagination completely?

Not always. OFFSET is still useful for simple interfaces, small tables, and cases where users need direct page numbers. The right choice depends on dataset size, UX needs, and consistency requirements.

Conclusion

OFFSET pagination and keyset pagination both have valid use cases in PostgreSQL.

OFFSET is simple, familiar, and often good enough for:

• small datasets
• admin tools
• page-number interfaces
• and shallow browsing

Keyset pagination is usually better for:

• large datasets
• high-performance list endpoints
• changing data
• and user-facing feeds where people move forward through results

That is why the best choice is usually not ideological.

It is practical.

Use OFFSET when simplicity and page numbers matter more. Use keyset when scale, consistency, and deep traversal matter more.

And whichever model you choose, make sure the query pattern, sort order, and index design all line up.