#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 guide 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.

#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.