Pagination without a gem

Rails pagination has an answer in the framework already, and the answer is two methods that have been on ActiveRecord::Relation since before any of the gems existed. The interesting question is never which gem to install. It is which of the two pagination techniques a given list needs, because the obvious one has a performance failure and a correctness failure, and the other one costs you page numbers.

Offset pagination in plain Active Record

Twelve lines, no dependency:

class ArticlesController < ApplicationController
  PER_PAGE = 25

  def index
    page = params[:page].to_i.clamp(1, 10_000)

    @articles = Article.order(published_at: :desc, id: :desc)
                       .limit(PER_PAGE)
                       .offset((page - 1) * PER_PAGE)
    @page = page
  end
end

That produces ORDER BY published_at DESC, id DESC LIMIT 25 OFFSET 50 for page three. The view needs a link to page: @page + 1 and a link to page: @page - 1, and for a great many lists the work is now finished.

The clamp is not decoration. params[:page] is attacker-controlled, "-1".to_i is -1, and PostgreSQL 17.7 answers SELECT 1 OFFSET -1 with ERROR: OFFSET must not be negative, which reaches your users as a 500. At the other end, ?page=90000000 is an OFFSET of 2.25 billion, and the next section is about what that costs.

Page numbers need one more query, Article.count, which is a SELECT COUNT(*) over the filtered set. PostgreSQL has no stored row count to read for it, so the count scans. Caching that number is the usual fix and a fine one, though on a Rails 8 default cache store the cached total is itself a row in PostgreSQL, which is the trade Running Rails 8 without Redis pulls apart.

Where offset is the right answer

An admin list of 3,000 orders, sorted by whatever column the operator clicked, with a footer reading "page 12 of 120" and a jump to the last page. Offset is correct here and a cursor would be a downgrade. The operator wants to jump, the sort changes on every click so no single index serves it, the count is cheap at that size, and a row appearing mid-session matters to nobody.

Do not rewrite this list. Most lists in most applications are this list.

What a large OFFSET actually costs

The PostgreSQL manual states the mechanism in one sentence: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient." Skipped is not the same as not read. The server produces every row up to the offset in order, then throws them away.

Here is the plan, on PostgreSQL 17.7, against a million row temp table with the matching index:

CREATE TEMP TABLE articles AS
  SELECT g AS id, timestamp '2020-01-01' + (g * interval '1 minute') AS published_at
  FROM generate_series(1, 1000000) g;
CREATE INDEX ON articles (published_at DESC, id DESC);
ANALYZE articles;

LIMIT 25 OFFSET 0:

 Limit (actual rows=25 loops=1)
   Buffers: local hit=1 read=3
   ->  Index Only Scan using articles_published_at_id_idx on articles (actual rows=25 loops=1)
         Heap Fetches: 25

LIMIT 25 OFFSET 500000, same table, same index, same statement otherwise:

 Limit (actual rows=25 loops=1)
   Buffers: local hit=705 read=6369
   ->  Index Only Scan using articles_published_at_id_idx on articles (actual rows=500025 loops=1)
         Heap Fetches: 500025

The index is used in both. It does not help, because the index is what the server walks 500,025 entries of to reach the 25 you asked for. The work is linear in the page number, so the page nobody visits is free and the page a crawler reaches on its four hundredth request is the expensive one.

No timings appear above on purpose. Buffers and row counts reproduce on your machine; a millisecond figure from mine would not.

Why page two repeats a row you already saw

Offset counts positions in a result set, and positions move. A reader loads page one of a feed sorted newest first and gets rows 1 to 25. Somebody publishes an article. The reader asks for page two, OFFSET 25, and the row that was position 25 a moment ago is now position 26, so it is the first row of page two. The reader sees it twice.

A delete does the mirror image. Every row after the deleted one shifts up one position, the row that was at position 26 is now at 25 and inside page one, which the reader has already read, so it is never rendered at all. No exception, no log line, nothing to grep for.

For an admin list this is a curiosity. For an infinite feed it is the normal case rather than an edge case, because the entire point of the feed is that new rows arrive at the top while the reader is scrolling through the bottom. Every scroll event is a fresh request against a set that has moved underneath it, so the duplicate is not bad luck, it is the design. The rendering half of that feed, appending a page of rows to a list without re-rendering the page, is Turbo Stream actions; the query half is the rest of this post.

Your specs will not catch any of it. A request spec creates its fixtures, asks for page one, asks for page two, and the set does not move between the two requests because nothing is inserting concurrently. Green test, broken feed.

Keyset pagination: ask for what comes after

Stop counting positions. Remember the last row you rendered and ask for the rows after it.

scope = Article.order(id: :desc).limit(PER_PAGE)
scope = scope.where("id < ?", params[:after]) if params[:after].present?

The SQL is WHERE id < 8412 ORDER BY id DESC LIMIT 25. On the primary key index the server descends to 8412 and reads 25 entries, and the cost of that does not depend on how many rows came before. Page 400 costs what page 1 costs. Inserts at the top of the feed no longer shift anything, because "after row 8412" means the same thing whatever else happened.

Four properties are required of the column you cursor on, and dropping any one of them breaks it quietly rather than loudly. It must be ordered, so the comparison operator means something. It must be unique, or the cursor is ambiguous. It must be indexed, or you have replaced a scan with a scan. It must be static, because a value that changes moves the row relative to a cursor already handed out.

Ordering by two columns without dropping rows

A feed is rarely sorted by id. Sorted by published_at DESC, id DESC, the naive cursor WHERE published_at < ? is wrong: every row sharing the boundary timestamp is discarded, including the ones you have not rendered yet.

The fix is to compare the whole sort key at once, which SQL has a syntax for:

WHERE (published_at, id) < ('2020-12-13 05:20:00', 500000)
ORDER BY published_at DESC, id DESC
LIMIT 25

The PostgreSQL manual defines the semantics: "the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found." Left to right is exactly the tiebreak you want, so the comparison says "earlier than that timestamp, or at that timestamp with a smaller id".

Note the null clause in that sentence. ROW(1,2,NULL) < ROW(1,3,0) is true only because the third pair is never examined; a null that does get examined makes the whole comparison null, and a null comparison excludes the row. Cursor columns want NOT NULL on them.

In Active Record the row comparison goes in as a fragment, since there is no relation API that emits one:

Article.where("(published_at, id) < (?, ?)", cursor_time, cursor_id)
       .order(published_at: :desc, id: :desc)
       .limit(PER_PAGE)

Against the index from earlier, PostgreSQL 17.7 pushes it down:

 Limit (actual rows=25 loops=1)
   Buffers: local hit=2 read=2
   ->  Index Only Scan using articles_published_at_id_idx on articles (actual rows=25 loops=1)
         Index Cond: (ROW(published_at, id) < ROW('2020-12-13 05:20:00'::timestamp without time zone, 500000))
         Heap Fetches: 25

Four buffers against 7,074, and 25 index rows against 500,025, for the same 25 rows of output.

The cursor form that quietly stops using the index

The row comparison has an equivalent everyone writes instead, because it is portable and looks more like Ruby: a < x OR (a = x AND b < y). Logically identical. Not the same plan.

 Limit (actual rows=25 loops=1)
   Buffers: local hit=578 read=4044
   ->  Index Only Scan using articles_published_at_id_idx on articles (actual rows=25 loops=1)
         Filter: ((published_at < '2020-12-13 05:20:00'::timestamp) OR ((published_at = '2020-12-13 05:20:00'::timestamp) AND (id < 500000)))
         Rows Removed by Filter: 500001
         Heap Fetches: 500026

Index Cond became Filter, and Rows Removed by Filter: 500001 is the OFFSET behaviour you adopted keyset pagination to escape. The query returns the right 25 rows, the response is correct, and the only visible symptom is that deep cursors are as slow as deep pages were.

One planner version on one table shape proves nothing about every planner version, so do not take the plan on trust. What does generalise is the instruction: run EXPLAIN on your own cursor query and look for the word Filter.

Active Record already writes this query, for batches

Keyset pagination is not exotic in Rails. It is what find_each has always done, and the code is worth reading before writing your own. In activerecord 8.1.3.1, ActiveRecord::Batches#batch_condition builds the cursor predicate:

def batch_condition(relation, cursor, values, operators)
  cursor_positions = cursor.zip(Array(values), operators)

  first_clause_column, first_clause_value, operator = cursor_positions.pop
  where_clause = predicate_builder[first_clause_column, first_clause_value, operator]

  cursor_positions.reverse_each do |column_name, value, operator|
    where_clause = predicate_builder[column_name, value, operator == :lteq ? :lt : :gt].or(
      predicate_builder[column_name, value, :eq].and(where_clause)
    )
  end

  relation.where(where_clause)
end

That is the expanded OR form from the previous section, built up right to left: the last cursor column gets a strict comparison, every column before it gets "strictly beyond, or equal and the rest holds". For batching it is the right call, because find_each walks from one end and its cursor is never deep, so the Filter plan costs nothing. For a feed handing out cursors into the middle of a million rows, it is the plan you just saw discard half a million rows.

The signature is find_each(start: nil, finish: nil, batch_size: 1000, error_on_ignore: nil, cursor: primary_key, order: DEFAULT_ORDER), with DEFAULT_ORDER = :asc, and cursor: takes a column name or an array of them.

Two guards in that file are worth stealing outright. The first refuses to batch on something that cannot identify a row:

if (Array(primary_key) - cursor).any?
  indexes = model.schema_cache.indexes(table_name)
  unique_index = indexes.find { |index| index.unique && index.where.nil? && (Array(index.columns) - cursor).empty? }

  unless unique_index
    raise ArgumentError, ":cursor must include a primary key or other unique column(s)"
  end
end

Rails checks the schema for a real unique index and raises when it does not find one. The second is ORDER_IGNORE_MESSAGE = "Scoped order is ignored, use :cursor with :order to configure custom order.", which by default only warns, so Article.order(:published_at).find_each silently batches by id instead.

The bite: an ORDER BY that is not unique

Cursor on created_at alone and the day it breaks is the day two rows share a timestamp. An importer writes 4,000 rows inside the same second, or the column is a datetime with second precision, and now a whole block of rows has one cursor value.

Both available operators are wrong. WHERE created_at < ? skips every remaining row that shares the boundary timestamp, and the reader never learns those rows existed. WHERE created_at <= ? returns the tied rows again on the next page, and a paginator that always returns the same page is an infinite scroll that never advances.

The PostgreSQL manual states the rule for LIMIT in general, not just for cursors: "When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order." Active Record's batches documentation adds the property people forget: "When using custom columns for batching, they should include at least one unique column (e.g. primary key) as a tiebreaker. Also, to reduce the likelihood of race conditions, all columns should be static (unchangeable after it was set)."

Static is the second trap, and it is the one that survives code review. Cursor a feed on updated_at and every edit moves a row: a record touched while somebody is scrolling jumps back to the top of the sort and gets served again on a later page, or moves behind the cursor and disappears from that reader's session entirely. The column looks unique enough and is not stable at all.

The fix is the same in both cases and costs one column in the sort and one column in the index: append the primary key. ORDER BY created_at DESC, id DESC, index on (created_at DESC, id DESC), cursor as a row comparison on both.

Your fixtures will not reproduce any of this, because a factory that creates 30 records gives each of them a distinct created_at from the clock. To test the tie, write the timestamps explicitly and make several rows share one.

What cursor pagination costs

Page numbers, and everything built on them. No "page 12 of 120", no jump to page 50, no last page, and no total count unless you run a separate COUNT(*) and pay for it on its own terms. Pagy's keyset documentation states the limit plainly: "You can only paginate from one page to the next: no jumping to arbitrary pages", and "You don't know the previous and the last page; you only know the first and next pages".

A previous link is not free either. Going backwards means flipping the comparison and the sort, running the query, and reversing the result array before rendering, which is why several libraries just tell you to call reverse_order on the set and paginate forward.

The URL changes meaning too. ?page=3 is a position and ?after=8412 is an anchor, so a shared cursor link stays pointed at the same rows forever, which is better for a reader and worse for a crawler that wanted a finite, enumerable set of index pages.

When the gem is the right answer

Pagination without a gem is about fifteen lines for offset and about thirty for keyset, so the gem is not buying you an algorithm. What it buys is the view layer: page links with a windowed number range, first and last, i18n, the ARIA attributes, and the Turbo compatible markup. If you are about to render a numbered page bar on six different admin screens, write none of that by hand.

Two candidates, and they are not in the same condition.

Pagy 43.6.3, released 2026-09-23, MIT, Ruby 3.3 or newer, and it describes itself as "agnostic pagination in plain ruby". Actively developed to the point of turbulence: version 43 is, in the upgrade guide's own words, "a complete redesign of the legacy code. Its improvements make pagination a lot simpler and powerful, but require a quite different way to use it." It ships a :keyset paginator, which is the thing most of this post is about, and it states the same constraint: "The set must be uniquely ordered. Add the primary key (usually :id) as the last order column to be sure."

Kaminari 1.2.2, released 2021-12-25, MIT, "A Scope & Engine based, clean, powerful, customizable and sophisticated paginator for modern web app frameworks and ORMs". The repository is not abandoned: the most recent commit on master at the time of writing is "CI against Ruby 4.0" from 2026-02-20, and Rails 8.1 went into the CI matrix in October 2025. Nothing has been released in nearly five years, though, so what bundle install gives you is the 2021 gem and the README's supported list stops at Rails 8.0. Its without_count mode drops the SELECT COUNT(*) and leaves you with prev and next links, which is offset pagination wearing a cursor's limitations.

The call, and what would flip it

Write offset yourself, because it is a limit, an offset and a view helper you will still understand in a year. Reach for Pagy when the page-number UI is the actual work, or when you want keyset without maintaining a cursor encoder yourself. Skip Kaminari in a new Rails 8.1 application, not because it is bad, since it is battle tested and hugely deployed, but because adopting it means pinning a five year old release to a framework version its README does not list.

The cost of the recommendation is honest to state: hand-written offset pagination gives you no page bar, no i18n, and one more place where somebody will forget the clamp. Pagy's cost is a dependency that rewrote its entire API at version 43 and can do it again.

What would flip it: a Kaminari 1.3 naming Rails 8.1 would make the maturity argument beat the recency one, and a Pagy release that stabilised the API would remove the only real objection to reaching for it first.

What this post does not cover

The Rails boilerplate this site sells does not paginate anything. No pagination gem appears in its Gemfile, and no controller in it calls limit or offset for paging. Everything above comes from Active Record's source, the PostgreSQL manual, and the two gems' own documentation, which is the honest provenance for a post that would otherwise be tempted to sell you something.

Also absent: benchmarks with timings, for the reason given above; MySQL, where row constructor comparison exists but the plans are not the ones measured here; cursor encoding, signing and the question of whether exposing id in a URL leaks your row count; and Relay-style GraphQL connections, which are keyset pagination with a base64 costume and a spec to follow.