Last Tip of 2025 That Will Boost Your PostgreSQL Performance OFFSET vs KEYSET

PostgreSQL OFFSET/LIMIT pagination gets slower as pages get deeper. Here’s how to use keyset (seek/cursor) pagination with the right index for consistent performance.

Dec 31, 2025

60 second Tip

Stop Using OFFSET in SQL Pagination (Use Keyset Pagination Instead)

If your app has infinite scroll, deep dashboards, or reports with page 200… and it gets slower the deeper users go, there’s a high chance you’re using PostgreSQL OFFSET/LIMIT pagination.

You think you’re paginating.
You’re actually committing slow-motion D.O.A. (Dead On Arrival) on your database.

Thanks for reading The Sev-1 Database! This post is public so feel free to share it.

Share The Sev-1 Database

Why `OFFSET` Pagination Gets Slower in PostgreSQL

OFFSET pagination looks like this:

SELECT *
FROM events
ORDER BY created_at DESC
OFFSET 10000
LIMIT 50;

What PostgreSQL has to do:

read rows up to the offset
apply ordering (ORDER BY)
skip/discard the first 10,000 rows
return the last 50

So the deeper the page, the more work PostgreSQL does.

Result: OFFSET query time grows roughly linearly with the offset. That’s why PostgreSQL OFFSET LIMIT performance gets worse as your dataset grows.

This becomes painful in:

admin dashboards
reporting tools
mobile apps with deep pagination
infinite scroll feeds

The Fix:

Keyset Pagination (a.k.a. Seek / Cursor Pagination in PostgreSQL)

Keyset pagination (also called seek pagination or cursor pagination) tells PostgreSQL:

Start after this known row and give me the next page.

Instead of skipping 10,000 rows, you use a cursor from the last row of the previous page.

Step 1: Write the Keyset Query

-- Next page (use the last row from the previous page as the cursor)
SELECT *
FROM events
WHERE (created_at, id) < ('2025-10-13 10:22:51', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Why use (created_at, id) as a composite cursor?
Because created_at isn’t always unique. Adding id guarantees deterministic ordering and prevents duplicates/missing rows.

Step 2: Add the Supporting Index

CREATE INDEX ON events (created_at DESC, id DESC);

This lets PostgreSQL seek directly to the next page using the index.

OFFSET vs Keyset Pagination (What Changes)

With OFFSET: PostgreSQL does more work the deeper you go (scan + skip).
With keyset pagination: PostgreSQL uses the index to jump to the cursor (fast, consistent).

Bottom line: OFFSET is simple but doesn’t scale. Keyset pagination is slightly more work but is the correct approach for large datasets and high-traffic apps.

FAQ

Is OFFSET bad in PostgreSQL?
Not always small offsets are fine. But large offsets become slow because PostgreSQL must still process and skip rows up to the offset.

What is the best pagination method in PostgreSQL for performance?
For deep pagination and infinite scroll, keyset/seek (cursor) pagination is usually the best option.

👉 Paid members get instant access to:

Premium content with Deep dive into full RCA analysis, get actionable scripts, and Ask Me Anything via Email for Mentorship.

Catch you next week with another tip!

If you found this helpful, go ahead and share it with your network.

If you’re interested in becoming a premium member, feel free to support my publication!