Guides

How to add pagination — page by page, without duplicates or deep-page lag

Illustration: a stack of cards handed out in batches from a bookmark

At first a list of thirty rows loads instantly. Then it's a thousand — the page thinks for a second. Then a hundred thousand, and the app just dies on the home screen.

The fix is pagination: serve batches instead of everything. Sounds boring, but it hides two traps that almost everyone hits.

First: OFFSET 10000 doesn't "skip" ten thousand rows — the database honestly reads and throws them away. The deeper the page, the slower the query. Supabase's own docs on pg_graphql pagination put it plainly: as offset values increase, query performance decreases, so use keyset pagination where possible.

The second trap is sneakier — it's at the end, and it's about duplicates.

Step 1. Decide whether you need it at all

If there are obviously few rows — settings, categories, a dozen projects — don't overthink it. Return everything in one query and move on.

But note: an API sitting on top of a database usually has its own ceiling. With Supabase, for example, responses are capped at 1000 rows by default — and that's a silent truncation, not an error. A list that "somehow ends" is very often exactly this limit.

Step 2. Build plain page-number pagination

The simplest thing that works. In SQL:

select id, title, created_at
from posts
order by created_at desc, id desc
limit 20 offset 40;

The same via the Supabase client (.range() is offset/limit, bounds inclusive):

const PAGE = 20
const from = page * PAGE
const { data, error } = await supabase
  .from('posts')
  .select('id, title, created_at')
  .order('created_at', { ascending: false })
  .range(from, from + PAGE - 1)

One detail matters more than everything else here: order is mandatory. Without an explicit sort the database promises nothing about row order — and the same record can show up on page one and page three. That's not mysticism, that's allowed behaviour.

Step 3. Show how many pages there are

"Page 3 of 40" needs a total. Ask for it alongside the data:

const { data, count } = await supabase
  .from('posts')
  .select('id, title', { count: 'exact' })
  .order('created_at', { ascending: false })
  .range(0, 19)

If you only need the number and no rows, use { count: 'exact', head: true }.

Keep in mind: an exact count on a large table costs time by itself — the database has to count the rows. For an infinite feed you usually don't need it at all; knowing whether another batch exists is enough.

Step 4. Switch to a cursor as the list grows

Keyset (cursor) pagination doesn't count how many rows to skip. It says: "give me twenty records after this one."

// first request: no cursor yet
let query = supabase
  .from('posts')
  .select('id, title, created_at')
  .order('created_at', { ascending: false })
  .limit(20)

// next ones: start from the last record of the previous batch
if (cursor) query = query.lt('created_at', cursor)

const { data } = await query
const nextCursor = data.at(-1)?.created_at

The database jumps straight to the right spot via the index and reads exactly twenty rows — whether it's the first batch or the thousandth. How much that matters shows up in a real case: in 2026 the Supabase Storage team rewrote object listing from OFFSET to a cursor and got up to 14.8× faster deep pagination on 60 million rows, with page 1000 as fast as page 1.

The cost is UI. There's no "jump to page 37" with a cursor: it does next and previous, not leaps. For a feed, a chat, or infinite scroll that's exactly right.

Step 5. Check two things, or it falls apart

  • An index on the sort column. Without it a cursor won't save you: the database still scans the table. Sorting by created_at means the index belongs on created_at.
  • A unique tie-breaker. If two rows share a timestamp (and with bulk inserts they will), one of them gets lost between pages. So sort by a pair: order('created_at').order('id'), and keep both values in the cursor.

What you'll get

The list is served in batches of twenty. Page one and page one hundred load in the same time. The user doesn't wait, the database doesn't read anything extra, and your bandwidth and compute bill stops growing with the table.

Why does the same post appear on two pages?

That's the second trap. While the user reads page one, someone inserts a new record and everything shifts by one. A query with offset 20 now starts a row earlier: the last post of page one shows up again, and one post between pages vanishes entirely. A cursor removes this by construction — it's anchored to a record, not to a number.

Pages or infinite scroll?

That's a UI question, not a database one — under the hood both are the same. Page numbers win where people return to results and share them (search, catalogues). Infinite scroll fits a feed, where browsing matters more than finding a specific spot.

Can I paginate in code, without the database?

You can, but only while the data fits in memory and comes from someone else's API with no pagination of its own. As soon as the source is your own database, slice it there: the point of pagination is that extra rows are never read, not that you read everything and show twenty.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →