Menu

Why page 800 of your list is slow (and the one-line fix)
📰

Why page 800 of your list is slow (and the one-line fix)

DEV Community: postgres·Bedirhan Elibol·10 days ago
#4x6o5tzt
#devtopostgresql#devto#dev#offset#desc
Reading 0:00
15s threshold

A list endpoint with LIMIT 20 OFFSET 20000 looks harmless. On a table with a few hundred rows in development it is instant, so nobody notices.

In production the database reads the first 20,000 rows in your sort order, discards them, and returns the next 20. Page 1 touches 20 rows. Page 1000 touches 20,000. If the table grows, the same URL gets slower every month, and the first complaint is usually an export or an admin screen that times out.

What it looks like

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20000 ;

Enter fullscreen mode

Exit fullscreen mode

Run EXPLAIN ANALYZE on this and on the same query with OFFSET 0 . The plan shows the row count the scan produced, and the gap is the wasted work.

In Prisma the same thing is skip :

await prisma . order .…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.