OFFSET pagination and its limits

Capsule overview

Almost every pagination tutorial starts the same way: "add LIMIT 50 OFFSET (page-1)*50 and you're done, pagination solved." It works perfectly in dev, it works perfectly in QA, it works perfectly for the first 6 months in production. And one day a user reaches page 800, the query takes 6 seconds, your pager goes off, and you discover that OFFSET isn't a free trick — it's O(n) over the page's position.

This capsule takes OFFSET apart down to its pieces. You're going to see how PostgreSQL executes LIMIT 50 OFFSET 50000 step by step, understand why even with a perfect index it's still O(n), measure the real degradation with EXPLAIN ANALYZE, and come out with an honest decision tree: when OFFSET is still the right decision and when you need to move to cursor or keyset.

The goal isn't to convince you that OFFSET is always bad. It's for you to know exactly when it breaks and why — so you make informed decisions, not dogmatic ones.

How OFFSET works internally

PostgreSQL executes LIMIT N OFFSET M like this:

1. It starts producing rows according to the ORDER BY.
2. It counts and discards the first M rows.
3. It starts returning from M+1.
4. It stops once it has returned N rows.

Step 2 is where the problem lives: PostgreSQL has to read and discard M rows to get to the right position. With OFFSET 50000, it reads 50,050 rows from the index and discards 50,000. There's no way to "skip" without reading — even with a perfect index.

Mental model: the elevator with no floor buttons

Imagine you're in an elevator that only has a "go up one floor" button. You want to go to floor 50. You have to press the button 50 times. The elevator goes up one at a time. If you want to go to floor 5,000, you have to press it 5,000 times.

OFFSET is like that. PostgreSQL can't "jump to floor 5,000" — it has to walk the floors one by one until it gets there.

┌──────────────────────────────────────────────────────────────────────┐
│  Table with an index on created_at DESC                              │
│                                                                      │
│  Row 1       ← LIMIT 50 OFFSET 0:      returns 1-50                  │
│  Row 2                                                               │
│  ...                                                                 │
│  Row 50                                                              │
│  Row 51      ← LIMIT 50 OFFSET 50:     reads 100, discards 50,       │
│  ...                                     returns 51-100              │
│  Row 50,000                                                          │
│  Row 50,001  ← LIMIT 50 OFFSET 50000:  reads 50,050,                 │
│  Row 50,002                              discards 50,000,            │
│  ...                                     returns 50,001-50,050       │
│  Row 50,050                                                          │
└──────────────────────────────────────────────────────────────────────┘
                       ↑
         To get here, it read 50,000 rows from the index

This is O(n) over the depth of the page. Page 1 reads 50 rows. Page 1,000 reads 50,050 rows. Page 100,000 reads 5,000,050 rows. Latency scales linearly.

The SQL you see vs the work it does

-- What you write
SELECT id, title, created_at
FROM tasks
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

-- What PostgreSQL does internally
-- 1. Index Scan Backward over idx_tasks_created_at
-- 2. Reads row 1, 2, 3 ... until it gets to row 50,001
-- 3. Starts producing output from row 50,001
-- 4. Returns rows 50,001 to 50,050
-- 5. Done

The execution plan shows it to you explicitly. You'll see it in the worked example.

Worked example: measuring OFFSET with EXPLAIN ANALYZE

We're going to set up a 1M-row table and measure the difference between page 1 and page 10,000 with OFFSET.

Note: exact figures vary by hardware. What matters is the shape of the degradation: linear with the depth of the page.

Setup

-- setup_offset_demo.sql
CREATE TABLE IF NOT EXISTS tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed 1M tasks with created_at spread across the last year
INSERT INTO tasks (title, created_at)
SELECT
  'task_' || g,
  now() - (random() * interval '365 days')
FROM generate_series(1, 1000000) g;

-- Index on created_at DESC (the ORDER BY we're going to use)
CREATE INDEX idx_tasks_created_at_desc ON tasks (created_at DESC);

-- Force fresh statistics
ANALYZE tasks;

createdb pagination_demo
psql -d pagination_demo -f setup_offset_demo.sql
# CREATE TABLE
# INSERT 0 1000000
# CREATE INDEX
# ANALYZE
# Took ~12s on my MacBook Air M1

Measure page 1

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, title, created_at
FROM tasks
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

Expected output (abridged):

Limit  (cost=0.42..2.28 rows=50 width=27) (actual time=0.025..0.215 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_tasks_created_at_desc on tasks
        (cost=0.42..37204.42 rows=1000000 width=27)
        (actual time=0.024..0.205 rows=50 loops=1)
        Buffers: shared hit=4
Planning Time: 0.092 ms
Execution Time: 0.245 ms

Reading it: 0.25ms. It read 4 buffers. It returned 50 rows. Perfect.

Measure page 1,000

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, title, created_at
FROM tasks
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Expected output:

Limit  (cost=1860.82..1862.68 rows=50 width=27) (actual time=18.450..18.502 rows=50 loops=1)
  Buffers: shared hit=1862
  ->  Index Scan using idx_tasks_created_at_desc on tasks
        (cost=0.42..37204.42 rows=1000000 width=27)
        (actual time=0.018..15.823 rows=50050 loops=1)
        Buffers: shared hit=1862
Planning Time: 0.085 ms
Execution Time: 18.530 ms

The critical read: the line (actual time=0.018..15.823 rows=50050 loops=1) tells you it read 50,050 rows to return 50. Latency: 18.5ms. Buffers read: 1,862 (vs 4 on page 1).

Measure page 10,000

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, title, created_at
FROM tasks
ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

Expected output:

Limit  (cost=18602.21..18604.07 rows=50 width=27) (actual time=185.230..185.290 rows=50 loops=1)
  Buffers: shared hit=18602
  ->  Index Scan using idx_tasks_created_at_desc on tasks
        (cost=0.42..37204.42 rows=1000000 width=27)
        (actual time=0.020..158.420 rows=500050 loops=1)
        Buffers: shared hit=18602
Planning Time: 0.091 ms
Execution Time: 185.385 ms

The critical read: it read 500,050 rows to return 50. Latency: 185ms. Buffers: 18,602.

Degradation table

Summarizing the three measurements:

The pattern: latency and buffers grow linearly with OFFSET. Every 10x deeper into the page = 10x more latency. That's O(n).

For deep diagnosis of plans with EXPLAIN ANALYZE (what each line means, how to read Index Scan vs Seq Scan, buffers vs disk reads), review module 2 of guide #12 (Database Performance & Query Tuning) — this capsule assumes you already know how to interpret it at a basic level.

The detail almost nobody mentions: WITH TIES and a page with no stable order

If your ORDER BY only uses a non-unique column (e.g. created_at without id as a tiebreaker), OFFSET has an extra problem: rows with the same created_at can be returned in any order. Page 1 might give you task A. Page 2 might give you task A again if it happened to land later in the sort.

-- Bad: non-deterministic order when there are duplicate created_at values
ORDER BY created_at DESC LIMIT 50 OFFSET 50;

-- Good: deterministic order with a PK tiebreaker
ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 50;

This is a silent OFFSET trap. You won't see it in dev with small data. You'll see it in production when a customer says "I see this item on page 5 and page 6." The PK tiebreaker fixes it.

When OFFSET is still the right decision

OFFSET isn't an absolute anti-pattern. There are cases where it's the pragmatically correct choice:

Case 1: small dataset (<5,000 rows total)

If your table has 200 products and the user is never going past page 4 (of 4), OFFSET is trivial, simple to implement, and the degradation is invisible. Cursor would be over-engineering.

Case 2: an admin table with page numbers

[Page 1] [Page 2] [Page 3] ... [Page 47] ... [Page 250]
            ↑
       direct click

This requires OFFSET. You can't implement it with cursor (a cursor only gives you next/previous, not "go to 47"). If your UI has this affordance, OFFSET is the right answer.

Case 3: you need a total COUNT(*) to show "Page 47 of 250"

If the UI says "Page 47 of 250", you need to know the total. An exact COUNT(*) on a large table is expensive, but it's what the case requires. With a cursor, you don't have a "total page count" — you have "next / previous."

Case 4: internal APIs with controlled consumers

If the API is internal and the consumers are your own frontend, the decision is more flexible. If the frontend always calls pages 1-3 (search with filters), OFFSET is fine. If the frontend does infinite scroll that loads page 100+, cursor is better.

A concrete decision tree

Does the UI need to "go to page N" directly?
├─ YES → OFFSET (the only reasonable option)
└─ NO → next question

Can the dataset grow past 10k rows?
├─ NO → OFFSET (simplicity wins)
└─ YES → next question

Are deep pages (>page 50) plausible?
├─ NO → OFFSET (you won't see the degradation)
└─ YES → next question

Do you need "Page 47 of 250" in the UI?
├─ YES → OFFSET + COUNT (accept the cost)
└─ NO → CURSOR / KEYSET (capsules 03-05)

What matters about this tree: two of the four decisions end in OFFSET. That's not a minority. It's the most common pagination case in internal admin tables.

Why does this matter in real work?

1. Production diagnosis. When a paginated endpoint gets slow, the first thing you need to know is whether the slowness scales with the depth of the page. If it does, it's OFFSET. If latency is constant at any page, it's a different problem (missing index, heavy JOIN, N+1). Without understanding OFFSET's linear degradation, you'll waste hours looking in the wrong place.

2. Critical code review. When someone on your team adds OFFSET to a new public API, you'll be able to ask the right questions: "how big can this table get? does the UI have page numbers or is it infinite scroll? what page depth is plausible?" That's senior level.

3. Early architectural decisions. Public APIs are hard to migrate. If you ship ?page=1&limit=50 and a year later you have to switch to ?cursor=abc, you break all your consumers. Deciding well at the start (with the decision tree above) saves you that cost.

Traps and common mistakes

Mistake 1 (conceptual): "the index fixes OFFSET"

Symptom: "I have a perfect index on created_at, so OFFSET 50000 should be O(1)."

Why it's wrong: the index helps find ordered rows efficiently, but it doesn't let you skip rows. PostgreSQL still has to walk the index position by position until it reaches rowid 50,001. The index turns OFFSET from "Seq Scan + sort of the whole table" into "Index Scan that walks 50,050 rows," which is much better — but it's still O(n) over the depth of the page.

How to tell: look at actual rows=50050 loops=1 in the EXPLAIN ANALYZE. That's the number of rows it actually read. With or without an index, that number grows with OFFSET.

Mistake 2 (practical): not adding a PK tiebreaker to the ORDER BY

Symptom: a customer reports "I see this item on page 5 and page 6." You say "are you sure?" You confirm they are. The pagination is duplicating or skipping items.

Why it happens: your ORDER BY created_at isn't deterministic when there are rows with the same created_at. PostgreSQL can return them in any order, and it can differ between two calls. Add the OFFSET problem on top (rows can land on shifting boundaries) and you get duplicated or lost items.

How to fix it: always add the PK as a tiebreaker:

-- ❌ Bad
ORDER BY created_at DESC LIMIT 50 OFFSET 50;

-- ✅ Good
ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 50;

This applies to cursor pagination too (capsule 05) — the tiebreaker is universal.

Mistake 3 (conceptual): assuming COUNT(*) is free

Symptom: "I need to show 'Page 47 of 250', so I run SELECT COUNT(*) FROM tasks WHERE tenant_id = $1. It takes 4 seconds."

Why it happens: an exact COUNT(*) requires visiting every row that matches the filter. Without a specific covering index, it's a Seq Scan. With an index it can be an Index Only Scan, but it's still O(n) over the filtered subset. On a table with millions of rows, it's expensive.

How to tell: EXPLAIN ANALYZE SELECT COUNT(*) FROM tasks WHERE tenant_id = $1. If you see "Seq Scan" or latency >100ms, your COUNT is expensive.

How to mitigate:

• If you need an approximate number, use pg_class.reltuples (the planner's estimate, nearly instantaneous):

  SELECT reltuples::bigint AS approx_count FROM pg_class WHERE relname = 'tasks';

• If you need a count per filter, consider a materialized counts table updated with triggers.
• If you need the exact exact exact count, accept the cost and cache it (Redis with a TTL).

Mistake 4 (practical): OFFSET and a hidden N+1

Symptom: the query with OFFSET is fast (50ms on page 1,000), but the endpoint takes 800ms.

Why it happens: after the SELECT * FROM tasks LIMIT 50 OFFSET 50000, the code iterates and does task.author.name (the classic N+1). The problem isn't OFFSET — it's an N+1 over the 50 returned rows.

How to tell: measure the raw SQL query with pgbench or psql and compare it with the full endpoint. If the difference is large, the problem lives in the ORM.

How to fix it: selectinload or joinedload in SQLAlchemy. This is covered in guide #12 module 4 (N+1).

Mistake 5 (edge case): OFFSET with a dataset that changes between requests

Symptom: between the client asking for page 5 and page 6, someone inserts a row at the top. Page 6 shows a row the client already saw on page 5.

Why it happens: OFFSET is positional, not value-based. Position 250 is different before and after the INSERT. A new row at the top "pushes" everything down and the page boundaries shift.

How to tell: a reproducible test — open two psql sessions, in the first run SELECT * FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 50, in the second insert a row with created_at = now(), in the first run SELECT * FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 100. You'll see a repeated row.

How to fix it: this problem isn't fixed with OFFSET — it's in the model. Cursor pagination is stable for insertions (new rows appear at the top, they don't wedge themselves between pages the client already saw). You'll see it in capsule 03.

Exercises

Exercise 1: measure OFFSET's degradation on your machine

Set up the example table (1M rows). Run EXPLAIN ANALYZE with OFFSET at 0, 5,000, 50,000, and 500,000. Report actual rows, Buffers: shared hit, and Execution Time in each case. Does latency grow linearly with OFFSET on your hardware?

psql -d pagination_demo

\timing

EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 5000;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 50000;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tasks ORDER BY created_at DESC LIMIT 50 OFFSET 500000;

Exercise 2: identify whether a slow query is because of OFFSET

You're handed an endpoint with this query and told it's slow on page 200+:

SELECT * FROM events
WHERE workspace_id = 42
ORDER BY occurred_at DESC
LIMIT 100 OFFSET 20000;

How do you verify the problem is OFFSET and not something else (a missing index, a badly optimized filter)?

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE workspace_id = 42 ORDER BY occurred_at DESC LIMIT 100 OFFSET 0;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE workspace_id = 42 ORDER BY occurred_at DESC LIMIT 100 OFFSET 20000;

Exercise 3: decide between OFFSET and cursor for a real case

For each case, decide whether you'd use OFFSET or cursor pagination, and justify it:

a) An admin endpoint /admin/users that shows a table with page numbers. ~50,000 users total. The support team rarely goes past page 5.

b) A public API /v1/transactions so customers can see their payment history. Each customer has between 100 and 500,000 transactions. The UI is infinite scroll in the mobile app.

c) A /feed endpoint for a Twitter-like product. Each user sees their personalized feed, infinite scroll, they can "scroll to infinity" (weeks back).

d) A /products endpoint for an e-commerce store. ~2,000 products in the catalog. UI with page numbers, sort by price, filter by category.

Exercise 4: add a PK tiebreaker and demonstrate the difference

Insert 100 rows into tasks with the exact same created_at:

INSERT INTO tasks (title, created_at)
SELECT 'duplicate_' || g, '2026-04-15 10:00:00+00'::timestamptz
FROM generate_series(1, 100) g;

Run the following pagination twice in a row:

SELECT id, title FROM tasks
WHERE created_at = '2026-04-15 10:00:00+00'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- And again
SELECT id, title FROM tasks
WHERE created_at = '2026-04-15 10:00:00+00'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

Is it always the same order? Then add , id DESC to the ORDER BY. Does anything change?

SELECT id, title FROM tasks
WHERE created_at = '2026-04-15 10:00:00+00'
ORDER BY created_at DESC
LIMIT 10;

   id   |     title
--------+--------------
 1234567 | duplicate_42
 1234568 | duplicate_43
 1234569 | duplicate_44
 ...

   id   |     title
--------+--------------
 1234599 | duplicate_74
 1234600 | duplicate_75
 ...

SELECT id, title FROM tasks
WHERE created_at = '2026-04-15 10:00:00+00'
ORDER BY created_at DESC, id DESC
LIMIT 10;

Exercise 5: measure the cost of a total COUNT(*)

To implement "Page X of Y" with OFFSET, you need COUNT(*). Measure what it costs:

\timing

SELECT COUNT(*) FROM tasks;
SELECT COUNT(*) FROM tasks WHERE created_at > now() - interval '30 days';

-- Compare with the planner's estimate
SELECT reltuples::bigint AS approx_count FROM pg_class WHERE relname = 'tasks';

How long does each one take? When would you accept the pg_class.reltuples approximation?

Time: 95.234 ms                              -- COUNT(*) total
Time: 88.110 ms                              -- COUNT(*) with WHERE
Time: 0.452 ms                               -- pg_class.reltuples

Summary and next step

In this capsule you learned:

• OFFSET is O(n) over the depth of the page. PostgreSQL reads and discards M rows to reach the right position. The index helps it walk efficiently, but it doesn't let it skip.
• The degradation is linear and measurable with EXPLAIN ANALYZE: every 10x deeper = 10x more buffers read and 10x more latency.
• OFFSET is still correct on small datasets, UIs with direct page numbers, and internal APIs with controlled consumers. It's not an absolute anti-pattern.
• The PK tiebreaker in ORDER BY is universal — without it, OFFSET can skip or duplicate rows when there are duplicate values in the sort column.
• An exact COUNT(*) is expensive and it's often what makes "Page X of Y" slow, not OFFSET itself. Approximating with pg_class.reltuples is a valid tool in many cases.
• The decision tree tells you when to switch to cursor: the dataset can grow past 10k, deep pages are plausible, the UI has no direct "go to page N".

Before moving on you should be able to:

• Explain to a colleague why LIMIT 50 OFFSET 50000 reads 50,050 rows even with a perfect index
• Decide between OFFSET and cursor for a given case using the decision tree
• Spot OFFSET degradation in a slow endpoint by looking at the EXPLAIN ANALYZE
• Add a PK tiebreaker to the ORDER BY by reflex

Next capsule — Cursor pagination: fundamentals. You're going to understand exactly what a cursor is, how it differs from keyset pagination, how it's encoded (base64 with timestamp + id), and how to model the paginated response with Pydantic. Capsule 03 is conceptual; the end-to-end implementation with SQLAlchemy + FastAPI comes in 04.

Resources

1. Markus Winand — "We need tool support for keyset pagination" — the canonical manifesto against OFFSET. If you read only one resource from the module, make it this one.
2. Markus Winand — "Paging Through Results" (use-the-index-luke.com) — a chapter of the online book on why OFFSET is problematic in SQL generally (not just Postgres).
3. PostgreSQL Documentation — LIMIT and OFFSET — the official docs. Note that the warning about rows getting "skipped" with a large OFFSET is in the docs themselves.
4. Joe Nelson — "Faster Pagination in Rails" — an analysis of the cost of COUNT and alternatives for "Page X of Y".
5. Brandur Leach — "API Paginations Design" — an explicit account of the OFFSET vs cursor trade-off from Stripe's perspective.
6. Stack Overflow Engineering — "Why we use OFFSET pagination" — a reasoned defensive case for when OFFSET is correct in a real API with a numbered UI.
7. Postgres Wiki — Slow Counting — the official explanation of why COUNT(*) is expensive and patterns to mitigate it.
8. Citus — "Lessons learned from running PostgreSQL at scale" — references to OFFSET optimizations on large datasets (including pg_class.reltuples).

Module 1 — SQL Patterns for Production APIs Guide

Next capsule: Cursor pagination — fundamentals.