ActiveRecord offset pagination performance degradation on large datasets

Offset and keyset pagination: primary trade-offs

ActiveRecord’s .limit and .offset provide a simple pagination model, but as the offset grows, the database must scan and discard every row before the target page. This yields O(n) latency growth, where n is the offset value. In contrast, keyset pagination (also called the seek method) leverages an indexed column in a WHERE clause to jump directly to the desired row, achieving O(log n) or even O(1) seek time when the index is covering. In Rails 5 and later, ActiveRecord consistently emits these clauses as expected, but the underlying database engine bears the scan cost.

API consistency

Offset pagination supports random page access: a client can request page 5, page 10, or jump to the last page without sequential dependencies. This is intuitive for UI components that display page numbers. Keyset pagination is inherently sequential: navigation is limited to “next” and “previous” based on the last retrieved key. Random access is possible but requires re‑querying from the beginning or storing state externally, which complicates the API.

Caching implications

Offset‑based result sets are fragile when the underlying data changes. A new insert or delete shifts row identifiers, causing the same offset to return different entities or skip rows entirely. This makes HTTP‑level or fragment caching difficult without additional versioning or checksums. Keyset pagination caches more stably because the seek key (typically a unique ID or timestamp) remains constant; however, only the most recent page cache is useful, and stale keys can cause duplicates or gaps if the sort order column is not immutable.

Typical scale threshold

Performance degradation becomes noticeable when the offset exceeds 10,000 to 50,000 rows, depending on hardware, row width, and index coverage. This range is a common empirical observation but not a hard limit: wide rows (many columns or large text/​blob fields) amplify the cost because the engine must materialize more data per scanned row, while narrow rows with a covering index can push the threshold higher. The linear scan remains a fundamental bottleneck regardless of dataset size.

Verification steps

To confirm the degradation curve for your deployment, run the following scoped command:

EXPLAIN ANALYZE SELECT * FROM posts ORDER BY created_at DESC LIMIT 1 OFFSET 10000;

Compare the “Rows Removed” count against “Rows Returned”. A ratio near 1:10,000+ indicates the scan cost. For a benchmark, iterate pages 1, 10, 100, 1000 and record execution times on identical hardware.

Missing diagnostic detail

To refine the exact transition point for your dataset, I need one detail: what is the average row size and does a covering index exist on the primary sort column? This determines whether the 10k–50k rule of thumb applies or if the threshold is higher/lower. Providing this will let me adjust the recommendation precisely.