Menu

Offset vs cursor pagination in SQL
📰

Offset vs cursor pagination in SQL

DEV Community: postgres·Israa·5 days ago
#OSjVpHRL
#devtopostgresql#devto#dev#rows#offset
Reading 0:00
15s threshold

Pagination means splitting a long list into smaller pages, like showing 10 posts at a time instead of every row in the table. It's a favorite backend interview topic, and the question usually sounds like this: offset or cursor, and why?

Every example here is plain SQL for PostgreSQL 18, the current version. You can run it in psql, the command-line tool that comes with Postgres, or in any SQL editor. You'll fill a table with 100,000 posts, watch offset pagination slow down and repeat rows, then fix both problems with a cursor. It takes about 10 minutes to read and try.

Offset pagination says "skip 20 rows and give me the next 10," while cursor pagination says "give me the 10 rows after this one."

1.…

Continue reading — create a free account

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