Keyset Cursor Pagination for ActiveRecord Instead of OFFSET
module KeysetPaginatable
extend ActiveSupport::Concern
class_methods do
def keyset_page(cursor: nil, limit: 20, direction: :desc)
limit = limit.to_i.clamp(1, 100)
order_op = direction == :desc ? :desc : :asc
scope = order(created_at: order_op, id: order_op)
scope = scope.where(cursor_predicate(cursor, order_op)) if cursor
rows = scope.limit(limit + 1).to_a
has_more = rows.size > limit
rows.pop if has_more
[rows, has_more]
end
private
def cursor_predicate(cursor, order_op)
comparator = order_op == :desc ? "<" : ">"
sql = "(#{table_name}.created_at, #{table_name}.id) #{comparator} (?, ?)"
sanitize_sql_array([sql, cursor.created_at, cursor.id])
end
end
end
class Cursor
attr_reader :created_at, :id
def initialize(created_at:, id:)
@created_at = created_at
@id = id
end
def self.from_record(record)
new(created_at: record.created_at, id: record.id)
end
def self.decode(token)
return nil if token.blank?
payload = JSON.parse(Base64.urlsafe_decode64(token))
new(created_at: Time.iso8601(payload.fetch("t")), id: payload.fetch("id"))
rescue ArgumentError, JSON::ParserError, KeyError
nil
end
def to_token
payload = { "t" => created_at.iso8601(6), "id" => id }
Base64.urlsafe_encode64(JSON.generate(payload), padding: false)
end
end
class ArticlesController < ApplicationController
def index
cursor = Cursor.decode(params[:cursor])
records, has_more = Article.published.keyset_page(cursor: cursor, limit: params[:limit])
render json: {
data: records.map { |a| serialize(a) },
next_cursor: has_more ? Cursor.from_record(records.last).to_token : nil
}
end
private
def serialize(article)
{
id: article.id,
title: article.title,
published_at: article.created_at.iso8601
}
end
end
class AddKeysetIndexToArticles < ActiveRecord::Migration[7.0]
disable_ddl_transaction!
def change
add_index :articles,
[:created_at, :id],
order: { created_at: :desc, id: :desc },
name: "index_articles_on_created_at_id",
algorithm: :concurrently
end
end
This snippet shows how to replace OFFSET-based pagination with keyset (a.k.a. cursor or seek) pagination in a Rails application. Offset pagination is convenient but degrades badly on large tables: LIMIT 20 OFFSET 100000 still forces the database to walk and discard 100000 rows, so deep pages get progressively slower, and concurrent inserts cause rows to shift between pages. Keyset pagination avoids both problems by remembering the last row seen and asking the database for rows strictly after it, using an index-friendly WHERE clause that turns paging into a fast range scan.
The core idea lives in KeysetPaginatable concern, mixed into any model that needs stable cursors. keyset_page orders by a tuple of columns and appends the primary key as a tie-breaker so the ordering is total and deterministic — this matters because a non-unique sort column alone (like created_at) can leave rows ambiguous and skip or repeat records. When a cursor is supplied it builds a row-value comparison via cursor_predicate. Rather than emitting nested OR conditions, it uses Postgres' tuple comparison (created_at, id) < (?, ?), which is both concise and index-usable when a matching composite index exists. The method fetches limit + 1 rows so it can tell whether a next page exists without a second COUNT query, then trims the extra row.
Cursor is a small value object that encodes the boundary values into an opaque, URL-safe token with to_token using Base64.urlsafe_encode64 over JSON, and reverses it in Cursor.decode. Encoding keeps the API contract clean: clients treat the cursor as an opaque string and never construct comparisons themselves. decode is defensive and returns nil on malformed input so a tampered token degrades to the first page rather than raising.
ArticlesController ties it together. It reads an optional cursor param, decodes it, and calls keyset_page on the scoped relation. The response includes the materialized records plus a next_cursor built only when has_more is true, derived from the final record's created_at and id. Clients follow the next_cursor forward; there is no page number, which is the fundamental trade-off — keyset pagination gives cheap forward/backward seeking but cannot jump directly to an arbitrary page N, and it needs a stable sort key.
A few pitfalls are worth noting. The sort columns in the query, the tuple in cursor_predicate, and the values packed into the Cursor must stay in lockstep; a mismatch silently returns wrong pages. Timestamps should be compared at full precision (or the tie-breaker column must be genuinely unique) to avoid boundary duplicates. For descending order the comparison operator flips to <, as shown. Reach for this pattern on high-traffic feeds, infinite-scroll UIs, and export jobs where consistent, index-backed paging matters more than random page access.
Share this code
Here's the card — post it anywhere.
