sql 45 lines · 4 tabs

Cursor-based pagination with stable ordering

Shared by codesnips Jan 2026

4 tabs

CREATE TABLE posts (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    bigint NOT NULL REFERENCES authors (id),
    title        text   NOT NULL,
    body         text   NOT NULL,
    published_at timestamptz NOT NULL DEFAULT now()
);

-- Matches the ORDER BY exactly so pages are a single index range scan.
-- id is the deterministic tie-breaker for rows sharing published_at.
CREATE INDEX idx_posts_feed
    ON posts (published_at DESC, id DESC);

-- Initial fetch: no cursor yet, just seek from the newest row.
-- LIMIT is page_size + 1 (21 for a page of 20) so we can detect
-- whether a next page exists without a separate COUNT(*).
SELECT id, author_id, title, published_at
FROM posts
ORDER BY published_at DESC, id DESC
LIMIT 21;

-- $1 = last row's published_at, $2 = last row's id (the decoded cursor).
-- The row-value comparison expresses "strictly before this cursor"
-- in one clause the planner pushes straight into idx_posts_feed.
SELECT id, author_id, title, published_at
FROM posts
WHERE (published_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY published_at DESC, id DESC
LIMIT 21;

-- Decodes an opaque base64 cursor token into (published_at, id).
-- Keeps clients from crafting arbitrary filters against the columns.
CREATE OR REPLACE FUNCTION decode_cursor(token text)
RETURNS TABLE (published_at timestamptz, id bigint)
LANGUAGE sql IMMUTABLE AS $$
    SELECT
        split_part(convert_from(decode(token, 'base64'), 'UTF8'), '|', 1)::timestamptz,
        split_part(convert_from(decode(token, 'base64'), 'UTF8'), '|', 2)::bigint;
$$;

CREATE OR REPLACE FUNCTION encode_cursor(published_at timestamptz, id bigint)
RETURNS text
LANGUAGE sql IMMUTABLE AS $$
    SELECT encode(
        convert_to(published_at::text || '|' || id::text, 'UTF8'),
        'base64'
    );
$$;

4 files · sql Explain with highlit

Keyset pagination (often called cursor-based pagination) replaces the classic LIMIT/OFFSET approach with a WHERE clause that seeks directly to the row after the last one seen. This matters because OFFSET n forces Postgres to scan and discard n rows on every page, so deep pages get linearly slower and inserts can shift the offset window and cause skipped or duplicated rows. Keyset pagination instead remembers the sort key of the last row and asks for everything strictly after it, giving stable, index-backed page fetches regardless of depth.

The posts schema tab defines the table and, crucially, a composite index on (published_at DESC, id DESC). The id column is included as a deterministic tie-breaker: published_at is not unique, so two posts sharing a timestamp would otherwise have an ambiguous order and a cursor could land mid-tie and drop rows. Ordering by (published_at, id) makes the total order strict, and the index lets Postgres satisfy both the filter and the sort with a single range scan.

The first page query tab shows the initial fetch: it simply orders by the composite key and takes LIMIT 21 when the page size is 20. Fetching one extra row is a cheap way to detect whether a next page exists without a separate COUNT. The next page query tab is the heart of the pattern — the row-value comparison (published_at, id) < ($1, $2) expresses "everything strictly before this cursor" in one clause that Postgres can push into the index. Using the tuple comparison rather than hand-writing published_at < $1 OR (published_at = $1 AND id < $2) is both clearer and lets the planner do an efficient index range scan.

The cursor encoding tab wraps this in a callable function that decodes an opaque cursor into its two components, so the API surface exposes a single string token rather than leaking raw column values. Encoding the cursor (for example base64 of published_at|id) keeps clients from crafting arbitrary filters and lets the schema evolve.

The main trade-off is that keyset pagination only supports next/previous navigation, not jumping to an arbitrary page number, and the cursor columns must exactly match the ORDER BY. When those constraints are acceptable — infinite scroll, feeds, large exports — this approach stays fast at any depth and never skips rows during concurrent inserts.

Related snips

ruby

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

rails turbo hotwire

by codesnips 4 tabs

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

ruby

Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = false
    Bullet.bullet_logger = true
    Bullet.console = true

N+1 query detection with Bullet gem

rails performance activerecord

by Alex Kumar 2 tabs

ruby

# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord

by Sarah Mitchell 3 tabs

ruby

json.array! @posts do |post|
  json.cache! ['v1', post], expires_in: 1.hour do
    json.id post.id
    json.title post.title
    json.excerpt post.excerpt
    json.published_at post.published_at

Fragment caching for expensive JSON serialization

rails caching performance

by Alex Kumar 1 tab

Share this code

Here's the card — post it anywhere.

Cursor-based pagination with stable ordering — share card

Link copied