Postgres best practices
Pagination in Supabase: use keyset, not offset
Offset pagination makes Postgres read and throw away every row before the page you want, so page 500 is slow. Keyset pagination asks for rows after the last one you saw, such as `created_at < cursor`, and stays fast at any depth with a matching index.
Last updated
Why is offset slow?
`limit 20 offset 10000` reads 10,020 rows to return 20. Pages also shift when rows are inserted, so users see duplicates or miss rows. Supabase's `.range()` uses offset under the hood.
How do I write keyset pagination?
Sort by a unique, stable key. If `created_at` can repeat, add `id` as a tiebreaker and compare both.
select id, title, created_at
from public.posts
where (created_at, id) < ($1, $2)
order by created_at desc, id desc
limit 20;
create index posts_created_id_idx on public.posts (created_at desc, id desc);const { data } = await supabase
.from("posts")
.select("id, title, created_at")
.lt("created_at", cursor)
.order("created_at", { ascending: false })
.limit(20);Does pagination matter for security?
Yes. Scrapers page through tables fast and deep. A hard `limit` on every query, a max page size in your API settings and an index-backed cursor make bulk export slower and more visible. Supahound flags an actor that pages through a table far deeper or faster than your app does.
Frequently asked questions
- Can I jump to page 50 with keyset pagination?
- Not directly. Keyset suits infinite scroll and next/previous buttons. If you need numbered pages on large tables, show approximate counts and limit how deep users can jump.
- How do I get a total count cheaply?
- Exact counts on large tables are slow. Use `count: 'estimated'` in supabase-js, which reads the planner's estimate.
- Does Supabase cap rows per request?
- Yes. The API has a max rows setting (1,000 by default) in the project's API settings. Keep it as low as your app allows.