Offset vs Cursor Pagination

Question

I am trying to compare any performance gain(s) from moving a current offset-based pagination endpoint to a cursor based one.

I have the following ruby code:

@limit = 25
@max_pages = 200

def query
  @q ||= MyDataBaseClass.where(status: "open").order(:id)
end

def offset_paginatiion
  # count is currently an implementation for this appraoch 
  total = query.count
  ctr = 0
  page_size = 1
  while page_size < @max_pages
    query.limit(@limit).offset(ctr).load
    ctr += @limit
    page_size += 1
  end
end

def keyset_pagination
  current_page = query.limit(@limit)

  page_size = 1
  while true
    break if page_size >= @max_pages
    page_size += 1
    last_id = current_page.last.id
    current_page = query.where("id > ?", last_id).limit(@limit).load
  end
end

require 'benchmark/ips'

Benchmark.ips do |x|
  x.report("offset_pagination") { offset_paginatiion }
  x.report("keyset_pagination") { keyset_pagination }
  x.compare!
end

I've seeded the database with 1 million rows but you can see here I'm only trying to page up to 200 pages (or 5,000 results).

The two methods are returning the exact same data in the same order so they are functionally equivalent and after running the benchmark are performing nearly the same:

│Calculating -------------------------------------
│   offset_pagination      0.009  (± 0.0%) i/s -      1.000  in 110.100327s
│   keyset_pagination      0.009  (± 0.0%) i/s -      1.000  in 112.732942s
│
│Comparison:
│   offset_pagination:        0.0 i/s
│   keyset_pagination:        0.0 i/s - 1.02x  slower

I was under the impression performance (from numerous articles) was a benefit to moving to cursor pagination but have yet to been able to reproduce - what am I doing worng?

Answer 1

I'm not surprised at your results, bar a few minor details that any decent database will optimize away your implementations are the same. You are also basically just re-implementing the find_each method.

Note, you might find a real difference if you used database cursors but that is a completely different thing.

The real difference between the two is that cursor based pagination responds better when the underlying data changes. i.e. using offset based pagination you might have a record included twice or skipped if a record is inserted or deleted by another process.