Answer to the Question
SurrealDB’s current query engine scans the entire result set up to the OFFSET before applying the LIMIT. Consequently, query latency increases roughly linearly with the offset value. There is no built‑in cursor or key‑set pagination API; to avoid the cost of large offsets you need to implement your own cursor logic, typically by using a monotonically increasing field (e.g., an auto‑increment id or a timestamp).
How High OFFSETs Affect Latency
- For small tables or modest offsets, the overhead is negligible.
- When the offset reaches hundreds of thousands or millions, the engine must traverse and discard that many rows, which can turn a sub‑second query into several seconds or more.
- Because the engine does not perform an index seek for arbitrary offsets, the cost is essentially
O(offset) in time.
Why a Cursor Is Safer
A cursor‑style query uses a filter on a unique, indexed field:
SELECT * FROM my_table WHERE id > $last_seen_id ORDER BY id ASC LIMIT 100;
This approach lets the engine jump directly to the starting point via the index, making the latency independent of how many rows precede the cursor.
Verification Steps for Your Environment
Run a baseline query with a large offset (e.g., OFFSET 1_000_000) and record the execution time using SurrealDB’s PROFILE command or an external timer.
Identify a unique, indexed column that can serve as a cursor (often the primary key).
Execute a cursor query using that column and compare its latency to the offset query.
If SurrealDB provides a query plan or logs, inspect them to confirm that the offset query performs a full scan while the cursor query uses an index seek.
Missing Detail That May Change the Recommendation
Do you have a unique, indexed field you can use for cursor‑based pagination? If not, consider adding one or using a composite key that guarantees monotonic ordering.
Practical Takeaway
For production workloads that require paging through large tables, replace OFFSET with a cursor query. Monitor latency differences and adjust the page size to balance throughput and memory usage.
surrealdb