Why OFFSET Slows Your API and How Keyset Pagination Boosts Speed

Problem with OFFSET Pagination

Our API originally used a simple SQL query with LIMIT 20 OFFSET 10000 to fetch a page of user transaction records. When the table was small, the query returned in about 200 ms, but as the data volume grew the same request took 2–3 seconds because the database first retrieved over 10 k rows and then discarded the first 10 k.

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

Each additional page increased the OFFSET value, causing the query to scan more and more rows, leading to progressively worse performance.

Solution: Keyset (Seek) Pagination

We replaced the OFFSET query with a keyset pagination query that uses a deterministic ordering column (and optionally a secondary column) to jump directly to the next slice of data.

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

Instead of telling the database to skip rows, we tell it to start reading from a specific timestamp, allowing the index on created_at to locate the start point instantly.

Handling Duplicate Timestamps

When multiple rows share the same created_at value, pagination can produce duplicate or missing rows. Adding a secondary sort key ( id) guarantees a stable order.

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

Other Approaches Explored

Cursor‑Based Pagination

Keyset values can be encoded into an opaque cursor string that the client sends back for the next page. "next_cursor": "2024-05-01T10:00:00Z_98765" This pattern is used by modern APIs such as Instagram and Twitter, offering a stateless and clean interface.

Index‑Only OFFSET

For scenarios that require jumping to an arbitrary page number, we created a covering index and limited the selected columns to those stored in the index.

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

While this does not make OFFSET itself faster, it reduces the need for costly table lookups.

Materialized Views

For reporting workloads that repeatedly aggregate daily transaction totals, we built a materialized view refreshed every few minutes.

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

The view allows the reporting system to read pre‑aggregated data in 50–100 ms, dramatically lowering load on the live transaction table.

Performance Comparison

OFFSET 10000 – ~2600 ms

OFFSET + index optimization – ~1300 ms

Keyset/Seek pagination – ~180 ms

Keyset + cursor – ~190 ms

Materialized view – ~50–100 ms

Final Thoughts

The experience shows that performance bottlenecks often stem from data‑access patterns rather than hardware limitations. By switching from OFFSET to keyset pagination (or using cursors, covering indexes, or materialized views where appropriate), we achieved order‑of‑magnitude speedups without any infrastructure changes.

If your application still relies on OFFSET pagination and suffers from latency as data grows, consider adopting keyset pagination—it is simple, elegant, and highly efficient.