Offset Pagination in Databases

@h6165

Abhishek Yadav

ரூபீ ப்ரோக்ராமர்

Co-organizer: Chennai.rb

Offset Pagination in dababases

or

Lets stop using OFFSET

The story

TODO: Add ref to twitter discussion

Offset pagination

-- First page
SELECT * FROM comments OFFSET 0 LIMIT 10 ORDER BY created_at

-- Second page
SELECT * FROM comments OFFSET 10 LIMIT 10 ORDER BY created_at

# nth page
run_sql("SELECT * FROM comments OFFSET " + (n-1)*10 + "LIMIT 10")

OFFSET support

• Non standard 
• Supported by some databases

Offset Problems

• inCorrectness
• Poor performace

Offset Problems: inCorrectness

Page-1

Page-2

User1: Show page-1

User2: Remove Fake

User1: Show page-2

users table

Offset Problems: inCorrectness

Page-1

Page-2

User1: Show page-1

User2: Remove Fake

User1: Show page-2

users table

Offset Problems: inCorrectness

Page-1

Page-2

User1: Show page-1

User2: Remove Fake

User1: Show page-2

Abhishek moves to page-1

users table

Offset Problems: inCorrectness

User1: Show page-1

Page-1

Page-2

User2: Remove Fake

User1: Show page-2

User1 missed Abhishek !

users table

Offset Problems: poor performance

-- Last page in a million record table
SELECT * FROM comments OFFSET 999990 LIMIT 10 ORDER BY created_at

-- TODO: Add time taken

• Last page in a table with a million records
• Can take unacceptable amount of time
• Reason: full table scan
• Sql engine has no way of knowing which record will appear at the offset, without actually loading all the records 

The alternatives

• Page-no free lookup
• Window functions

Alternatives: No-pages

• aka: key-set pagination
• Don't look up by page numbers
• Look-up by last record seen
• A lot like infinite scrolls

TODO: add example

Alternatives: window functions

• TODO: Add example

Python libs

• Offset free:
  • django-infinite-scroll-pagination
  • django-chunkator
•