Why OFFSET Pagination Slows You Down: Switch to Keyset and Cut Latency from Seconds to Milliseconds

Problem with OFFSET pagination

Initial API query returned results in ~200 ms. As the transactions table grew, the same query took 2–3 seconds. Execution‑plan analysis showed the cause was the use of OFFSET, which forces the database to read and discard all preceding rows. For example:

SELECT *
FROM transactions
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

The engine reads 10 020 rows, drops the first 10 000, and returns the last 20, so performance degrades as the offset grows.

Solution 1: Keyset (Seek) pagination

Rewrite the query to start from a concrete cursor value instead of skipping rows:

SELECT *
FROM transactions
WHERE user_id = 42
  AND created_at < '2024-05-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

This lets the planner use the created_at index directly, reducing latency from ~2.6 s to under 200 ms.

When multiple rows share the same created_at (e.g., bulk uploads), add a secondary sort key to guarantee a stable order:

WHERE (created_at, id) < ('2024-05-01 10:00:00', 98765)
ORDER BY created_at DESC, id DESC;

The composite condition ensures deterministic pagination without duplicates or gaps.

Other explored approaches

Cursor‑based pagination – the same keyset logic wrapped in an opaque cursor string, e.g. "2024-05-01T10:00:00Z_98765". Clients send the cursor back for the next page. Used by Instagram, Twitter, etc.

Index‑only OFFSET – create a covering index on the queried columns and force the planner to read only the index. Example index:

CREATE INDEX idx_user_created_id_amount
ON transactions(user_id, created_at DESC, id, amount);

This does not speed up OFFSET itself but eliminates table lookups, improving query time.

Materialized view for reporting – pre‑aggregate daily transaction sums:

CREATE MATERIALIZED VIEW user_summary AS
SELECT user_id, DATE(created_at) AS day, SUM(amount) AS total
FROM transactions
GROUP BY user_id, DATE(created_at);

Refresh the view every few minutes via a cron job. Reporting queries then read from the pre‑computed view, dropping response time to 50–100 ms.

Benchmark results

OFFSET 10000 – average response ~2600 ms

OFFSET + index‑only optimization – average response ~1300 ms

Keyset / Seek pagination – average response ~180 ms

Keyset + cursor – average response ~190 ms

Materialized view – average response ~50–100 ms

The measurements demonstrate that replacing OFFSET pagination with keyset‑based techniques (or a materialized view for heavy reporting) yields order‑of‑magnitude speedups without hardware changes.