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
Postgresql | √ |
Mysql | √ |
Oracle | No |
SQLServer | No |
- Non standard
- Supported by some databases
Offset Problems
- inCorrectness
- Poor performace
Offset Problems: inCorrectness
Fake |
Abhishek |
Page-1
Page-2
User1: Show page-1
User2: Remove Fake
User1: Show page-2
users table
Offset Problems: inCorrectness
Fake |
Abhishek |
Page-1
Page-2
User1: Show page-1
User2: Remove Fake
User1: Show page-2
users table
Offset Problems: inCorrectness
Fake |
Abhishek |
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
Abhishek |
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
Offset Pagination in Databases
By Abhishek Yadav
Offset Pagination in Databases
- 1,185