Almost every API paginates like this:

SELECT * FROM posts ORDER BY created_at DESC , id DESC LIMIT 20 OFFSET 40 ; -- page 3

Enter fullscreen mode

Exit fullscreen mode

It works great on page 1. On deep pages it gets thousands of times slower , and it can show users the same item twice. Here's the proof, and the fix.

Video version:

Measured: 1,000,000 rows in PostgreSQL 17

I filled a table with a million posts ( id , title , a 200-character body , created_at ), added an index on (created_at, id) , and timed the same 20-row page query at different offsets. These are medians of 7 runs:

OFFSET 0 0.022 ms OFFSET 1000 0.041 ms OFFSET 10000 0.319 ms OFFSET 100000 6.217 ms OFFSET 500000 32.102 ms OFFSET 999980 63.890 ms 🐌 (the last page) keyset (last) 0.006 ms ⚡

Enter fullscreen mode

Exit fullscreen mode

The cost grows linearly with the page number .…