Use LIMIT for Pagination Instead of OFFSET

While OFFSET is a common way to implement pagination in SQL, it performs poorly as datasets grow. This is because the database must scan and skip over every row before your desired page—creating a performance penalty that compounds with each request. For example, paginating with OFFSET 10000 forces the engine to examine and discard the first 10,000 rows before returning anything.

Instead, you can use keyset pagination, also known as indexed pagination. This method uses a WHERE clause to bookmark your last-seen ID (or timestamp), allowing the query to jump directly to the starting point for the next page. It’s faster, more scalable, and works well with indexes.

For instance, rather than using OFFSET 10000, you could store the last ID returned (e.g., last_seen_id) and filter with WHERE id < last_seen_id ORDER BY id DESC LIMIT 10. This allows the query to perform a targeted index scan instead of a full traversal, maintaining consistent performance even on datasets with millions of rows.

Galaxy’s optimizer detects inefficient OFFSET usage and recommends a keyset-based alternative automatically. For high-volume pagination—especially in logs, timelines, or customer lists—this change alone can turn a sluggish 5-second query into a 50-millisecond response.