#Performance and query plans

Silt keeps persistent documents out of a permanent JavaScript collection cache. That reduces JS heap pressure; it does not guarantee that a compiled query is faster than Mingo or a hand-written SQL query. Measure the actual filters, joins, result sizes, and write load your application uses.

#Inspect the executed plan

const orders = db.collection('orders');
await orders.createIndex({ tenant: 1, status: 1, updatedAt: -1 });

const plan = await orders
  .find({
    tenant: 'team-1',
    status: 'open',
    updatedAt: { $gte: 100 },
  })
  .explain();

console.log(plan.sql);
console.log(plan.params);
console.table(plan.queryPlan);

Awaited .explain() loads current index metadata and shows the SQL plan execution would use. .compile() is synchronous and can use older cached metadata. A supported index supplies candidate rows, and the exact SQL predicate still checks their Mongo-shaped semantics.

Create indexes for selective equality/range filters and eligible foreign lookup keys. Put selective $match stages early when doing so preserves pipeline semantics. Return only needed fields and use cursor iteration when you do not need the entire result in an array. See the index compatibility reference for specifications and optimizer boundaries.

#Costs to account for

The default 8 MiB page-cache target and temp_store=FILE setting are configuration requests, not measured memory ceilings. SQLite execution, JS result decoding, native allocation, and the operating-system page cache all contribute to resource use.

#Sort placement around correlated lookup

A small documentation example with three customers and four orders exposed severe SQL-plan expansion when sorting outer rows after a correlated lookup pipeline. Putting the same independent outer _id sort before the lookup avoided that expansion. This is a known compiler/planner case, not a claim that every lookup is slow.

When the sort depends only on fields that the lookup leaves unchanged, place that outer sort before $lookup and verify equivalent output. A sort inside the foreign pipeline controls foreign matches and cannot simply be moved outside. Inspect and time representative pipelines, especially when combining correlated lookups with structural or compound-value ranking; even generating a full EXPLAIN QUERY PLAN can be expensive for an expanded plan. The reproduction and follow-up are tracked in internal/follow-ups.md.

#What the exploratory measurements show

Two captured experiments answer different questions. They use local development-container storage, and are not production throughput guarantees or mobile-device benchmarks. Complete methods, samples, hashes, SQL, and query plans live in internal/benchmarks.

#Persistence and JavaScript memory

A Node 24.19.0 experiment used 20,000 documents, each with its own roughly 1 KiB payload. SQLite inserted them into a temporary file without accumulating a JS input array; Mingo retained the same documents in an array. Separate child processes checked equivalent results and forced GC for heap snapshots.

This run illustrates lower retained JS heap with persistence, and also shows expensive unindexed grouping/joining. It had no repetitions, warmup rounds, secondary indexes, concurrent writers, or cold-cache control. SQLite's file was 26.79 MiB after a WAL checkpoint. Process RSS is broader than JS heap and excludes some system-wide filesystem cache.

Reproduce from the repository root:

node scripts/benchmark.mjs 20000

The runner verifies all query results and the document count after reopening. Counts are bounded to 1,000–100,000; temporary databases are removed afterward.

#Selective indexes and write cost

A separate captured run used better-sqlite3 13.0.3, SQLite 3.53.4, and Node 24.19.0, with WAL, synchronous=FULL, an 8 MiB page cache, and temp_store=FILE. Reads used 20,000 orders, one warmup, and five measured executions; compilation and statement preparation were outside timing. Both variants checked identical result hashes and retained the exact residual predicates.

These very selective fixtures favor indexed probes. The compound filter used { tenant: 1, status: 1, date: -1 }; the lookup used a foreign { joinKey: 1 } index. Both indexes took about 996 ms to build and each held 20,000 key rows.

For 100-document transaction batches, median insert time increased from 0.148 ms without these indexes to 5.097 ms with them; updating their indexed fields increased from 0.148 ms to 4.972 ms. Trigger maintenance is a real write cost. The container's temporary filesystem makes these absolute write times unsuitable for predicting a deployment's durable-storage latency.

node scripts/benchmark-indexes.mjs
SILT_INDEX_BENCH_ADAPTER=node node scripts/benchmark-indexes.mjs

SILT_INDEX_BENCH_DOCS and SILT_INDEX_BENCH_REPEATS override defaults. The script prints JSON with samples, confirmed PRAGMAs, hashes, build time, and query plans, then removes its temporary files. Consider cold caches, less selective filters, multikey expansion, large joined output, and concurrent clients when designing a representative application benchmark.

#Concurrent WAL readers

A September 2026 experiment compared the direct better-sqlite3 adapter with the server worker engine, using two WAL readers, one writer, eight concurrent clients, 3,000 documents, two repetitions, and 36 isolated measured runs. Both used SQLite 3.53.4, WAL/FULL durability and the same data. Timings include API work and queueing; startup and warmup are separate. Each connection had an 8 MiB cache target, so the worker configuration had a larger total cache allowance.

Workers improved the CPU-heavy fixtures and reduced main-thread timer stalls: maximum lateness was 80–823 ms direct, versus at most 1.1 ms for server cases except one 14.3 ms run. The test explicitly measured timer lateness because the native event-loop histogram missed the direct adapter's initial synchronous burst.

Three-worker startup took 231–268 ms versus 9–12 ms direct. Observed process RSS ranges were 115–169 MiB server and 64–104 MiB direct. These are small warm-container workloads, not deployment capacity promises. Very short queries can cost more to send between workers than to execute. Document query metadata RPCs are a measured optimization candidate; fresh snapshot metadata preserves index correctness.

A separate cursor test showed the WAL tradeoff: after 40 writes, the server reader retained a 164,832-byte WAL until cursor closure; the portable SQL spool allowed checkpoint progress during iteration. Both truncated successfully after closure.

SILT_CONCURRENCY_REPEATS=2 npm run benchmark:concurrency

The complete report and raw measurements include p95 latency, result/counter validation, source hashes, environment, memory, settings, and all samples. See server configuration before selecting pool and cursor limits.

#JSON text versus JSONB

An additional storage-format experiment found faster scan-heavy operations with SQLite JSONB but slower complete-document reads and committed insertion. Production storage remains JSON TEXT. Index selection can change which of these costs matters most, so a raw json_extract microbenchmark alone is insufficient to choose a storage format.