Self-hosted SQLite backend: index_scan materializes the entire index range (ignores size_hint / no LIMIT), causing "too many system operations" + OOM on large tables

Summary

On the self-hosted SQLite backend, Persistence::index_scan eagerly loads the entire index interval into memory before the query-layer .take(N) is applied. The SQL it runs has no LIMIT and the size_hint argument is ignored. As a result, an indexed read that should touch a handful of documents (e.g. .withIndex(...).take(1)) instead reads the whole range, which on a moderately large table fails with:

SystemTimeoutError: Your request timed out performing too many system operations.

On a small-RAM host it also drives the backend into memory pressure / OOM, because the full range is materialized into a Vec.

This is not version-specific — it reproduces on current main (verified against f760918).

Root cause (with code pointers)

crates/sqlite/src/lib.rs:

• index_scan(...) takes _size_hint: usize — prefixed with _, i.e. unused (~L560).
• _index_scan_inner(...) (~L122) builds the SQL with ORDER BY B.key {order} and no LIMIT, then collects every matching row into let mut triples = vec![] before returning Vec<...>.

So the persistence layer returns the full interval; the .take(N) upstream only trims the already-materialized result. For a by_created_at / by_x index whose interval spans the whole table, that means "read the entire table" for a take(1).

By contrast, the Postgres backend does the right thing — crates/postgres/src/lib.rs index_scan forwards size_hint and reads in paginated chunks with a real SQL LIMIT + cursor (load_index_chunk, page_size), so it does not have this behavior.

Reproduction

1. Self-host the backend on the default SQLite persistence.
2. Populate an append-only table (e.g. a telemetry/event log) with a few hundred thousand rows.
3. From any query, run a tiny indexed read, e.g.:
   typescript复制

   await ctx.db.query("events").withIndex("by_created_at").order("desc").take(1);

4. It fails with SystemTimeoutError: ... too many system operations (and spikes memory), even though only 1 document is requested. The same read is instant once the table is small again.

The same defect makes the snapshot export worker stall/blow up on a large table (it walks tables via the same index-scan path), and makes any retention sweep that reads a wide createdAt < cutoff range unable to ever catch up — the read it relies on hits the same wall, so the table grows unbounded.

Impact

• Any indexed read (including the dashboard/admin reads and convex export) becomes unusable once a single table grows to ~hundreds of thousands of rows, regardless of the .take() limit.
• On constrained hosts the backend OOMs / livelocks rather than returning a clean error.

Suggested fix

Make the SQLite index_scan stream lazily in bounded chunks (cursor + SQL LIMIT) and honor size_hint, mirroring the Postgres backend's load_index_chunk approach, instead of collecting the whole interval into a Vec.

Happy to put together a PR for the SQLite path along these lines if that direction sounds right — wanted to confirm the intended design first (whether SQLite is meant to stay a simple/dev-scale store) before investing in the change.