module Keysettable
extend ActiveSupport::Concern
included do
scope :keyset_page, ->(after: nil, limit: 20) do
relation = order(created_at: :asc, id: :asc).limit(limit + 1)
if after.present?
relation = relation.where(
"(#{table_name}.created_at, #{table_name}.id) > (?, ?)",
after[:created_at],
after[:id]
)
end
relation
end
end
end
class Cursor
def self.encode(record)
return nil if record.nil?
payload = {
created_at: record.created_at.iso8601(6),
id: record.id
}
Base64.urlsafe_encode64(payload.to_json, padding: false)
end
def self.decode(token)
return nil if token.blank?
json = Base64.urlsafe_decode64(token)
data = JSON.parse(json)
{
created_at: Time.iso8601(data.fetch("created_at")),
id: Integer(data.fetch("id"))
}
rescue ArgumentError, KeyError, JSON::ParserError
nil
end
end
class ArticlesController < ApplicationController
PER_PAGE = 20
def index
after = Cursor.decode(params[:cursor])
records = Article.published.keyset_page(after: after, limit: PER_PAGE).to_a
has_next = records.size > PER_PAGE
page = has_next ? records.first(PER_PAGE) : records
next_cursor = has_next ? Cursor.encode(page.last) : nil
render json: {
articles: page.map { |a| ArticleSerializer.new(a) },
page_info: {
next_cursor: next_cursor,
has_next_page: has_next
}
}
end
end
class AddKeysetIndexToArticles < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_index :articles,
[:created_at, :id],
name: "index_articles_on_created_at_and_id",
algorithm: :concurrently
end
end
Offset pagination (LIMIT ... OFFSET ...) degrades badly on large tables: the database still has to scan and discard every skipped row, and rows shifting between requests cause duplicates or gaps. Keyset pagination, also called cursor pagination, fixes both problems by remembering the last row seen and asking for rows strictly after it in a stable sort order, which lets an index seek jump straight to the next page in roughly constant time.
The Keysettable concern mixes a keyset_page scope into any model. It sorts by a tuple of columns — here (created_at, id) — where id acts as a tiebreaker so the order is total and deterministic even when two records share a timestamp. The heart of it is the row-value comparison (created_at, id) < (?, ?), a SQL feature (well supported on PostgreSQL) that compares tuples lexicographically. This expresses "everything after the cursor" in a single indexable predicate instead of a tangle of nested OR conditions. The scope fetches one extra row (limit + 1) so the caller can tell whether a further page exists without a second COUNT query.
The Cursor value object encodes and decodes the opaque cursor. It packs the two key values into JSON, then Base64-URL-encodes them so the client treats it as a meaningless token. Cursor.encode turns a record into a string; Cursor.decode parses it back, tolerating a blank or malformed cursor by returning nil so a bad token just starts from the beginning rather than raising.
In ArticlesController, the index action decodes the incoming params[:cursor], calls keyset_page, then splits the results: if more than per_page rows came back, the extra row is dropped and a next_cursor is built from the last kept record. That cursor is returned in the JSON payload so the client can request the following page. The next_cursor is nil on the final page, giving clients a clean stop condition.
The main trade-off is that keyset pagination only supports next/previous traversal, not random "jump to page 50" access, and the sort columns must be backed by a composite index ((created_at, id)) to stay fast. For infinite-scroll feeds and large API result sets, that trade is almost always worth it.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
class Post < ApplicationRecord
belongs_to :author, class_name: 'User'
has_many :comments, dependent: :destroy
scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
scope :draft, -> { where(published_at: nil) }ActiveRecord scopes for reusable query logic
payload = {
sub: user.id,
iss: 'https://auth.example.com',
aud: 'codesnips-api',
exp: 15.minutes.from_now.to_i,
iat: Time.now.to_i,JWT issuance and verification without common footguns
module Api
module V1
class UsersController < BaseController
def show
user = User.includes(:profile).find(params[:id])ETags for conditional requests and caching
class PostsController < ApplicationController
def index
@posts = Post.includes(:author)
.order(created_at: :desc)
.page(params[:page])
.per(10)Turbo Frames: infinite scroll with lazy-loading frame
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
Share this code
Here's the card — post it anywhere.
