Why OFFSET and LIMIT Pagination Breaks at Scale and What to Use Instead

Using OFFSET and LIMIT to paginate queries in PostgreSQL or MySQL forces the database to scan and discard all skipped rows before returning the next page. That’s fine for small datasets, but for large ones, the database does a lot of work just to throw most of it away.

• In PostgreSQL, OFFSET and LIMIT queries must process each skipped row, inflating buffer reads and CPU usage even with indexes. Execution plans may look clean, but performance deteriorates as the offset increases.
• Similarly, the engine must count and discard rows up to the offset in MySQL. Performance degrades sharply as the dataset grows without a covering index or aligned sort order.

What to use instead:

• Keyset Pagination (Seek Method): Use a stable, indexed column (like created_at or id) with an ORDER BY clause and paginate using WHERE column > last_seen_value. This avoids full scans and ensures consistent performance even on large datasets.
• Cursor-Based APIs: Modern APIs (e.g., GraphQL with Relay-style cursors) align well with keyset-style pagination, offering speed and consistency.
• Materialized Views or Caching Layers: For expensive joins or queries with complex filters, precomputing the result set and paginating over that data is often more efficient. However, traditional materialized views have trade-offs: they require resource-heavy refreshes or risk serving stale data. A query caching system like Readyset avoids this by keeping Incremental Materialized Views updated in memory, enabling fast, consistent reads without manual refreshes.

Offset-based pagination has its place, but better options exist for high-traffic, latency-sensitive, or large-scale workloads. Check out Optimizing SQL Pagination in Postgres and Optimizing SQL Pagination in MySQL for SQL patterns and real-world examples.

🔍 What We’re Watching in the Database Ecosystem

Understanding transaction visibility in PostgreSQL clusters with read replicas PostgreSQL read replicas can show transactions in different orders, a quirk that breaks snapshot isolation and is sometimes called “Long Fork.” AWS confirms it affects RDS Multi-AZ clusters but not Aurora or Single-AZ. Until a fix lands, replica reads come with caveats.

Search Query Understanding with LLMs: From Ideation to Production Yelp integrated LLMs into their search query understanding, tackling tasks like query segmentation and review highlighting. This initiative required fine-tuning models, precomputing results, and developing new infrastructure, transforming an optimization effort into a substantial engineering project.

Redis is now available under the AGPLv3 open source license Redis 8 introduces AGPLv3 as a licensing option, returning to an OSI-approved open source license. It’s a notable move amid ongoing debates about the future of open source infrastructure.

In Case You Missed It

⚡️Readyset Private on Azure VM: You can now deploy Readyset privately on an Azure VM for performance gains from milliseconds to microseconds. Setting up takes just minutes and a few commands, with no replicas, rewrites, or added complexity required. Read the guide for details.