Is there a recommended way to fetch a very large table in a streaming/off-memory manner?

[hksdpc255]

I'm looking for ways to process all records from a very large table incrementally without loading everything into memory. Ideally, I want to fetch small batches (e.g., a few hundred rows) at a time, in any order, and process them sequentially.

Some approaches that come to mind:

1. Paging with LIMIT + START

   • In many databases, using large offsets may result in degraded performance because the engine still needs to scan skipped rows.
   • I'm not sure if SurrealDB behaves the same way, but I suspect similar trade-offs could apply.
   • (Reference: PostgreSQL Keyset Pagination vs OFFSET – not SurrealDB-specific, but highlights the general issue.)

2. Keyset pagination

   • An alternative pattern is WHERE field > $last_value ORDER BY field LIMIT N.
   • This avoids large offsets in other systems and might be more efficient in SurrealDB. One concern is that repeatedly querying with WHERE conditions could incur extra index lookups each time.

3. Using id for range queries

   • An alternative pattern is FROM [last_id]>.. ORDER BY id LIMIT N.
   • Since record IDs are unique, ordering by id and paging based on the last-seen ID might allow a linear scan.
   • However, I'm not sure if SurrealDB guarantees order without explicit sorting, so I'd like to know if this is a recommended pattern.

Questions:

• Is keyset pagination considered the best practice for large-table traversal in SurrealDB?
• Does SurrealDB have internal optimizations for LIMIT + START, or should we assume the same caveats as in traditional databases?
• Would ordering by id and paging with WHERE id > $last_id be the most reliable way to linearly walk a table?
• Are there any cursor-like or streaming query features planned?

Any insights or recommended patterns would be greatly appreciated!