# How do you write efficient pagination for millions of rows?

Efficient pagination is **absolutely critical** when dealing with **millions of rows** in PostgreSQL — especially if you care about performance, index usage, and UX for APIs or UI pages.

## 1\. OFFSET/LIMIT Pagination (a.k.a. “Skip-Limit”)

```sql
SELECT * FROM orders
ORDER BY created_at
OFFSET 10000
LIMIT 50;
```

### Pros:

*   Simple to implement
    
*   Page numbers are intuitive for UIs
    

### Cons:

*   **Slower as OFFSET increases** — PostgreSQL must **scan and discard** the skipped rows
    
*   **Non-deterministic** under concurrent updates (rows can shift across pages)
    
*   Doesn’t scale beyond 100k rows
    

### When to use:

*   Small datasets (or <100 pages)
    
*   Admin dashboards or static data
    
*   Temporary/internal tooling
    

## 2\. **Keyset Pagination (a.k.a. Seek Method)**

### Description:

Use a **cursor** (like `created_at, id`) to fetch the next page.

```sql
-- First page
SELECT * FROM orders
ORDER BY created_at, id
LIMIT 50;

-- Next page (cursor = last row)
SELECT * FROM orders
WHERE (created_at, id) > ('2024-07-01 10:00:00', 'uuid-123')
ORDER BY created_at, id
LIMIT 50;
```

### Pros:

*   **O(1)** pagination — always uses index seek
    
*   Safe under high concurrency
    
*   Deterministic (no skipping/overlaps)
    
*   Ideal for **real-time apps**
    

### Cons:

*   Can't jump to arbitrary page (e.g., page 10)
    
*   Cursor management required
    

### Index recommendation:

```sql
CREATE INDEX ON orders (created_at, id);
```

## 3\. **Hybrid Keyset + Offset (for Jumping to Specific Pages)**

### Description:

Use **OFFSET** for initial jump + **keyset** after that.

> E.g., cache the `(created_at, id)` of the first row on page 10 → use that as keyset.

### Pros:

*   Allows page jumping
    
*   Still uses index efficiently from that point onward
    

### Cons:

*   Adds complexity (requires cursor cache)
    
*   Still slow on very high page numbers if not cached
    

### Use case:

*   UIs where both page numbers and infinite scroll are required
    

## 4\. **Cursor-based Pagination (Encoded Cursors)**

This is **commonly used in APIs** (GraphQL, REST with cursor tokens).

### Description:

Return a **base64-encoded cursor** like:

```json
{
  "next_cursor": "created_at=2024-07-01T10:00:00&id=abc-123"
}
```

In the next query:

```sql
SELECT * FROM orders
WHERE (created_at, id) > (:created_at, :id)
ORDER BY created_at, id
LIMIT 50;
```

### Pros:

*   API-friendly, stateless pagination
    
*   Easy to cache cursors
    
*   Works with dynamic filters
    

## How to Handle **UPDATEs / Concurrent Changes**

| Challenge | Problem |
| --- | --- |
| Rows inserted/deleted during pagination | Causes "row shifting" on OFFSET pagination |
| Rows updated (e.g., created\_at changed) | Might move between pages if paginating on that column |

## For Large Scale Dataset pagination

| Tip | Why it Matters |
| --- | --- |
| Use composite indexes | `(created_at, id)` is better than `created_at` alone |
| Avoid OFFSET over 10k | degrades performance fast |
| Consider materialized views or temp tables | for heavy analytical pagination |
| Expose cursor in your API layer | makes UX smooth & stateless |
| Cache row cursors by page | hybrid offset-keyset model |
| Use `REPEATABLE READ` isolation | if pagination happens within a transaction to avoid data shifts |

## When to Use Each Method

| Use Case | Recommended Pagination |
| --- | --- |
| Admin dashboard < 10k rows | OFFSET/LIMIT |
| Real-time web/mobile UI | Keyset pagination |
| Reporting with stable pages | Window function with `ROW_NUMBER()` |
| APIs with infinite scroll | Cursor-based (encoded cursor tokens) |
| UI with both pages & infinite scroll | Hybrid keyset + cursor caching |