Why OFFSET Pagination Slows Your API and How Keyset Pagination Restores Speed

The Problem with OFFSET Pagination

Our backend team built a straightforward API to fetch a user's transaction history with pagination. Initially the query returned results in about 200 ms, but as the table grew, the same query began taking 2–3 seconds.

The original SQL looked clean:

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

Behind the scenes the database scanned 10,020 rows, discarded the first 10,000, and finally returned the last 20. Each additional page increased the OFFSET, causing the scan to become slower and slower.

Keyset (Cursor) Pagination Solution

After several ineffective tweaks we rewrote the query to use keyset pagination, which asks the database for rows after a specific timestamp (and optionally an ID) 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 change reduced the API response time from 2.6 seconds to under 200 ms without any infrastructure changes or caching—just smarter SQL.

Handling Duplicate Timestamps

When multiple transactions share the same created_at value, pagination can repeat or skip rows. We solved this by adding the primary‑key id as a tie‑breaker:

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

Including the id makes the ordering deterministic and pagination stable.

Other Approaches Considered

Cursor‑based pagination : essentially the same as keyset pagination but the timestamp and ID are encoded in a token, e.g. {"next_cursor":"2024-05-01T10:00:00Z_98765"}. This is how many modern APIs (Instagram, Twitter) implement scrolling.

Covering index : we added an index on (user_id, created_at DESC, id, amount) so the query can be satisfied entirely from the index.

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

Materialized view : for a reporting dashboard that repeatedly runs the slow query, we created a materialized view summarizing daily transaction totals. <code>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);</code> We refresh the view with a cron job every few minutes, dramatically reducing load on the live table.

Performance Results

Average query latency after each optimization:

OFFSET 10,000 → ~2600 ms

OFFSET + covering index → ~1300 ms

Keyset pagination → ~180 ms

Keyset + cursor token → ~190 ms

Materialized view → ~50–100 ms

The simple rewrite of the SQL query produced a dramatic speedup, proving that thoughtful query design can outweigh hardware upgrades.

Final Takeaways

If your application relies on OFFSET‑based pagination and you notice degrading performance, switch to keyset (cursor) pagination, add appropriate indexes, and consider materialized views for heavy reporting workloads. The solution is simple, elegant, and highly efficient.