PLANETSCALE 2022-08-16 Tier 3

PlanetScale — Introducing FastPage: Faster offset pagination for Rails apps¶

Narrow application-tier Rails post from PlanetScale's Ruby backend (not the MySQL/Vitess data-plane product). Mike Coutermarsh introduces fast_page, a new ActiveRecord gem that mechanically applies the MySQL "deferred join" optimisation documented in High Performance MySQL (Schwartz / Zaitsev / Tkachenko, 3rd ed. 2021) to any ORDER BY … LIMIT … OFFSET … query a Rails app happens to generate. The gem is a direct port of Caleb Porzio / Aaron Francis's hammerstonedev/fast-paginate Laravel gem, which was itself the implementation companion to Aaron Francis's widely-cited blog post Efficient Pagination Using Deferred Joins (2022).

Summary¶

LIMIT 25 OFFSET 100 becomes catastrophically slow at depth. The default ActiveRecord pagination path — issued as SELECT posts.* FROM posts ORDER BY created_at DESC LIMIT 25 OFFSET 100 — forces InnoDB to walk the secondary index on created_at, follow the primary-key pointer from each index leaf back to the clustered index to fetch the full row, and throw away the first 100 full rows it just materialised. The deeper the page, the more rows it wastes. On a ~1M-row table, the unoptimised query runs 1,228.7 ms to return 25 rows at offset 100.

The deferred-join rewrite splits the work in two. First, a thin index-only query reads just the primary keys in the correct order with the same LIMIT … OFFSET …:

SELECT id FROM posts ORDER BY created_at DESC LIMIT 25 OFFSET 100;

No clustered-index lookups, no row materialisation — the created_at secondary index already contains everything this query needs.

Second, the 25 primary keys that actually survive are used to fetch full rows:

SELECT * FROM posts
WHERE id IN (1271528, 1271527, 1271526, …)
ORDER BY created_at DESC;

Only 25 clustered-index lookups, not OFFSET + LIMIT of them. On the same 1M-row table the rewritten query runs in 456.9 ms for the id pluck + 0.4 ms for the hydrate = ~457 ms total — a 2.7× speedup at offset 100. Coutermarsh's benchmark chart on a 2000-page walk shows the gap widening superlinearly with page depth: FastPage stays near-flat while the ActiveRecord baseline climbs steeply.

"This technique works 'because it lets the server examine as little data as possible in an index without accessing rows.'" — quoting High Performance MySQL.

The gem exposes the rewrite as a single method chain: any ActiveRecord::Relation with offset/limit becomes .fast_page and the rewrite is applied transparently. Because the rewrite always issues two round-trips instead of one, Coutermarsh explicitly warns it is slower for shallow pages and recommends conditional application:

posts = Post.all.page(params[:page]).per(25)
# Use fast page after page 5, improves query performance
posts = posts.fast_page if params[:page] > 5

Key takeaways¶

Offset pagination's cost grows with offset, not with page size. LIMIT 25 OFFSET 100 is not "fetch 25 rows starting at position 100"; it's "fetch 125 rows, hydrate all of them from the clustered index, then discard the first 100." The work scales with OFFSET + LIMIT, not LIMIT. At offset N=100,000 the database has hydrated 100,025 full rows to return 25 to the user. (Source: sources/2026-04-21-planetscale-introducing-fastpage-faster-offset-pagination-for-rails-apps.)
The deferred-join rewrite is two queries, not one, but the first is index-only. The first query reads only primary keys from the secondary index — no clustered-index lookups, no row hydration. It can walk the index at index-scan speed. The second query hydrates exactly the 25 surviving rows via primary-key point lookups. Two round-trips, but OFFSET × row_size bytes of wasted clustered-index I/O eliminated.
The speedup scales with offset depth. Coutermarsh's benchmark on a ~1M-row AuditLogEvent table (both owner and created_at indexed) shows the FastPage line stays near-flat across 2000 pages while the ActiveRecord baseline climbs steeply. A fixed 2.7× at offset 100 becomes much larger at offset 10,000 or 100,000 — the gap widens superlinearly.
Deferred join is slower for shallow pages. Two queries instead of one means two round-trips and two planner invocations. For OFFSET 0 … OFFSET 100 the raw path is faster because there's almost nothing to throw away. Coutermarsh's explicit recommendation: apply fast_page only past page N, where N is the crossover point you measure on your own data.
Deferred join vs cursor pagination — different trade-offs. The canonical escape hatch for deep pagination is cursor (keyset) pagination — WHERE created_at < $last_seen ORDER BY created_at DESC LIMIT 25 — which is O(1) regardless of depth but cannot skip to page N. Deferred join preserves the skip-to-arbitrary-page UX of offset pagination while reducing its cost constant. Choose based on whether the UX needs "page 47" or just "next / previous."
ORDER BY is load-bearing. Coutermarsh: "fast_page works best on pagination queries that include an ORDER BY." Without an ORDER BY, InnoDB can return rows in arbitrary clustered-index order and the first query has no reason to prefer a secondary-index walk over a table scan. The deferred join's speedup comes from letting the first query use a secondary index as a covering index for the sort — that requires an ORDER BY clause whose columns are indexed.
Cross-ecosystem pattern replication. The same deferred join is independently packaged in three ecosystems: the Laravel hammerstonedev/fast-paginate gem (Aaron Francis et al., 2022), this Rails planetscale/fast_page gem (Coutermarsh, 2022), and directly in PostgreSQL / MySQL SQL by application engineers who read High Performance MySQL. The packaging cost is small — a handful of ORM integration lines — but the win for ORM-using apps is uniform.

Systems¶

MySQL — the database engine the optimisation targets. Coutermarsh's benchmark is against MySQL specifically; the deferred-join rewrite exploits InnoDB's clustered-index layout where secondary indexes store primary-key pointers and full-row fetches require a second B+tree descent.
InnoDB — the MySQL storage engine whose clustered-index architecture makes the optimisation matter. Non-clustered engines (e.g. MyISAM) see less dramatic wins because their secondary indexes already point directly at row offsets.
Ruby on Rails — application framework. The gem integrates at the ActiveRecord::Relation layer so any Rails query chain with .offset.limit can opt in via .fast_page.
fast_page gem — the PlanetScale- authored gem canonicalised by this post. GitHub: planetscale/fast_page.

Concepts¶

Offset pagination cost — new canonical wiki concept introduced by this ingest. The cost of LIMIT N OFFSET M is proportional to M+N, not N — a property derived from how OFFSET is implemented (fetch-then-discard, not seek).
Deferred join — new canonical wiki concept introduced by this ingest. The SQL rewrite that replaces a wide SELECT * FROM t ORDER BY … LIMIT … OFFSET … with an inner index-only key-selection query joined back to the table by primary key. Primary citation in High Performance MySQL.
Cursor pagination — new canonical wiki concept. The alternative mechanism — WHERE key < $last ORDER BY key DESC LIMIT N — that sidesteps the OFFSET cost entirely at the price of losing arbitrary-page random access.
Secondary index — the two-step lookup mechanism is the load-bearing detail. A secondary index walk returns primary keys; hydrating full rows requires a second walk of the clustered index. The deferred join isolates the first step.
Clustered index — InnoDB's table storage layout. Full-row reads require a clustered- index descent; the deferred-join first query avoids them.
Composite index — relevant when pagination predicates filter by multiple columns (e.g. WHERE owner = ? ORDER BY created_at DESC). A well-chosen composite index like (owner, created_at) makes the deferred-join first query a pure index-only walk.

Patterns¶

Deferred join for offset pagination — new canonical wiki pattern introduced by this ingest. The two-step SQL-rewrite recipe at the application / ORM layer.
Conditional optimisation by page depth — new canonical wiki pattern introduced by this ingest. The meta-recipe: a cost transformation that is a win in one regime (deep pages) and a loss in another (shallow pages) should be applied conditionally by measuring the crossover empirically, not unconditionally. Coutermarsh's if params[:page] > 5 is the canonical form.

Operational numbers¶

All from the post body, unverified outside:

Baseline (1M-row table, LIMIT 25 OFFSET 100 by created_at DESC): Post Load = 1,228.7 ms for 25 rows.
With fast_page: Post Pluck (id-only) = 456.9 ms
Post Load (25-row hydrate via IN (...)) = 0.4 ms = ~457.3 ms total. 2.7× speedup at offset 100.
Benchmark table: AuditLogEvent, ~1 million records, indexed on both owner and created_at.
Benchmark sweep: 2000 pages — FastPage line near-flat, ActiveRecord baseline climbs steeply (chart in post). No explicit p50 / p99 numbers at the sweep extrema.
Per-page size: 100 rows per page in the benchmark (.per(100)), 25 rows per page in the pedagogical example (.limit(25)).
Recommended crossover threshold: "page 5" as the author's ballpark — explicitly a measure-your-own-data recommendation, not a universal cutoff.
Test query predicate: .where(owner: org), i.e. the benchmark is not a pure index scan but a filtered scan — making the deferred-join first query's reliance on a composite (owner, created_at) index the load-bearing optimisation.

Caveats¶

Application-tier, not PlanetScale product internals. This is a Rails gem that rewrites queries the application ORM generates — it has nothing to do with PlanetScale's Vitess proxy, VTGate planner, or sharding substrate. The optimisation works against any MySQL instance (RDS, Aurora, self-hosted, PlanetScale) because the rewrite happens in the client.
Two round-trips > one for shallow pages. The pattern's explicit wart: small offsets pay a round-trip tax. Coutermarsh's params[:page] > 5 heuristic acknowledges this but doesn't quantify it — he delegates the measurement to the reader.
Requires an indexed ORDER BY. Without an index covering the sort order the first query can't be index-only and the optimisation's premise collapses. For unusual sort orders (e.g. ORDER BY some_random_column with no index) the rewrite may be slower than the baseline.
Not applicable to queries without LIMIT. The optimisation is specifically for LIMIT/OFFSET — it doesn't help full-table scans or top-N aggregations without offset.
ORDER BY created_at DESC with ties. If the created_at column has ties and no tiebreaker, the first and second query can see different row orderings if writes happen between them. The post doesn't discuss this — in practice you'd add ORDER BY created_at DESC, id DESC to both phases for stability.
Cursor pagination is strictly better where applicable. For next/previous UX without skip-to-page-N, cursor pagination is O(1) at any depth and has none of the LIMIT/OFFSET waste. Deferred join is the offset-preserving middle ground, not a replacement for cursor pagination where cursor is an option.
IN (...) list size limits. The second-phase query passes the primary keys as an IN list. For very large page sizes this could hit MySQL max_allowed_packet or query-parser limits; the typical pagination size (25-100) is well inside safe bounds.
2022-era post, 2026 re-fetch. The gem has been unchanged in public API since 2022 (per its GitHub repo). MySQL 8.0+ has improved some edge cases of the baseline query planner but the underlying asymmetry — index walk vs clustered-index hydrate — remains the dominant cost factor and the optimisation continues to be a clean win at depth.
Tier-3 + batch-skip override. Raw file originally carried ingested: true + skip_reason: batch-skip — marketing/tutorial slug pattern (url: /fastpage-faster-offset-pagination-for-rails-apps), 0 arch signals in body from tools/batch_skip_marketing.py. Override justified by user explicit full-ingest request + novel canonical content: three new concept pages (concepts/offset-pagination-cost, concepts/deferred-join, concepts/cursor-pagination) + two new pattern pages (patterns/deferred-join-for-offset-pagination, patterns/conditional-optimization-by-page-depth) + one new system page (systems/fastpage-gem). Although titled "Introducing…" the post is a ~80%-body quantitative walkthrough of the deferred-join mechanism with a 2.7× benchmark, SQL rewrites shown byte-for-byte, and pedagogy quoting High Performance MySQL; the product-announcement envelope is <20%. Borderline-case rule ("Only skip if architecture content is <20% of the body") clears.