ActiveRecord and PostgreSQL: Offset pagination vs keyset pagination for large datasets under concurrent writes

Direct answer

For tables over 100 k rows with high insert/update concurrency, use keyset (cursor) pagination as the default. OFFSET pagination should only be kept for the very first page (or pages 1‑3) if deep‑linking to a page number is a hard requirement.

Confirmed facts

• OFFSET forces PostgreSQL to scan and discard all preceding rows → O(N) cost and can skip or duplicate rows when concurrent writes occur.
• Keyset pagination uses a WHERE clause on indexed columns (e.g., WHERE created_at < :ts OR (created_at = :ts AND id < :id)) → O(log N) index seek and a stable result set regardless of inserts/deletes.
• When the primary sort column is not unique, a composite cursor that adds a unique column (usually the primary key) is required to guarantee deterministic ordering.
• ActiveRecord has no built‑in cursor API, but the logic can be written manually or abstracted with gems such as pagy (keyset extension) or ari__ord_pagination.

Steps to implement keyset pagination in ActiveRecord

1. Choose an immutable, indexed sort order, e.g., order(created_at: :desc, id: :desc) and ensure a matching composite index CREATE INDEX ON posts (created_at DESC, id DESC).
2. After fetching a page, capture the last row’s values: last_ts = posts.last.created_at, last_id = posts.last.id.
3. For the next page, query:

   posts = Post.where(
     '(created_at < ?) OR (created_at = ? AND id < ?)',
     last_ts, last_ts, last_id
   ).order(created_at: :desc, id: :desc).limit(20)
   

4. Encode the cursor (e.g., Base64 of '#{last_ts.iso8601}-#{last_id}') and send it to the client; decode it on the next request to rebuild the condition.
5. If you want to keep existing pagination helpers, wrap the above logic in a small concern or use pagy with its :keyset option:

   pagy, posts = pagy_keyset(Post.order(created_at: :desc, id: :desc), params[:cursor])
   

Handling non‑unique sort columns

• When sorting only by created_at, rows with identical timestamps would be collapsed, causing skips.
• Add the primary key (or another unique column) to the ORDER BY and to the WHERE clause as shown above.
• API contract shifts from ?page=2 to an opaque ?cursor=… token; document that the token is opaque and should be treated as a single value.

Combining OFFSET for shallow pages with keyset for deep pages

A hybrid approach is possible but adds complexity and breaks the notion of a stable page number. If you still need it:

• Detect the requested page number; for page ≤ 3 use Post.limit(20).offset((page-1)*20).
• For page > 3, convert the page number to a cursor by fetching the last row of page 3 (or store a pre‑computed cursor) and then apply the keyset query.
• Because the cursor depends on the actual data state, deep‑linking to an arbitrary page number (e.g., “page 500”) is not reliable; you must either abandon deep‑linking or accept possible drift.

Missing diagnostic detail: Do you already have a composite index matching your ORDER BY columns (e.g., created_at and id)? If not, adding that index is required for keyset performance; otherwise you would need to create it before adopting the cursor approach.