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
- Self-host the backend on the default SQLite persistence.
- Populate an append-only table (e.g. a telemetry/event log) with a few hundred thousand rows.
- From any query, run a tiny indexed read, e.g.:
await ctx.db.query("events").withIndex("by_created_at").order("desc").take(1);
- 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.
Summary
On the self-hosted SQLite backend,
Persistence::index_scaneagerly loads the entire index interval into memory before the query-layer.take(N)is applied. The SQL it runs has noLIMITand thesize_hintargument 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: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 againstf760918).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 withORDER BY B.key {order}and noLIMIT, then collects every matching row intolet mut triples = vec![]before returningVec<...>.So the persistence layer returns the full interval; the
.take(N)upstream only trims the already-materialized result. For aby_created_at/by_xindex whose interval spans the whole table, that means "read the entire table" for atake(1).By contrast, the Postgres backend does the right thing —
crates/postgres/src/lib.rsindex_scanforwardssize_hintand reads in paginated chunks with a real SQLLIMIT+ cursor (load_index_chunk,page_size), so it does not have this behavior.Reproduction
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 < cutoffrange unable to ever catch up — the read it relies on hits the same wall, so the table grows unbounded.Impact
convex export) becomes unusable once a single table grows to ~hundreds of thousands of rows, regardless of the.take()limit.Suggested fix
Make the SQLite
index_scanstream lazily in bounded chunks (cursor + SQLLIMIT) and honorsize_hint, mirroring the Postgres backend'sload_index_chunkapproach, instead of collecting the whole interval into aVec.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.