1---
2title: Use Cursor-Based Pagination Instead of OFFSET
3impact: MEDIUM-HIGH
4impactDescription: Consistent O(1) performance regardless of page depth
5tags: pagination, cursor, keyset, offset, performance
6---
7 
8## Use Cursor-Based Pagination Instead of OFFSET
9 
10OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).
11 
12**Incorrect (OFFSET pagination):**
13 
14```sql
15-- Page 1: scans 20 rows
16select * from products order by id limit 20 offset 0;
17 
18-- Page 100: scans 2000 rows to skip 1980
19select * from products order by id limit 20 offset 1980;
20 
21-- Page 10000: scans 200,000 rows!
22select * from products order by id limit 20 offset 199980;
23```
24 
25**Correct (cursor/keyset pagination):**
26 
27```sql
28-- Page 1: get first 20
29select * from products order by id limit 20;
30-- Application stores last_id = 20
31 
32-- Page 2: start after last ID
33select * from products where id > 20 order by id limit 20;
34-- Uses index, always fast regardless of page depth
35 
36-- Page 10000: same speed as page 1
37select * from products where id > 199980 order by id limit 20;
38```
39 
40For multi-column sorting:
41 
42```sql
43-- Cursor must include all sort columns
44select * from products
45where (created_at, id) > ('2024-01-15 10:00:00', 12345)
46order by created_at, id
47limit 20;
48```
49 
50Reference: [Pagination](https://supabase.com/docs/guides/database/pagination)
51