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