Generated from the experiment markdown files. Each post keeps the engineering record close to the benchmark evidence: problem, hypothesis, implementation, result, and decision.
utf8.encode allocation after exp 113's direct matrix encoder: the accepted fast path writes ASCII code units dir...Exp 125Experiment 125: Wide ASCII batch parameter encodingMay 5, 2026 · In Review · `parameter-encoding-and-binding`Experiment 113 removed the temporary flattened Dart parameter list from executeBatch, and experiment 116 promoted a 10,000-row x 20-parameter mixed batch to the release write suite. That left a n...Exp 122Experiment 122: Concrete reader-pool stream admissionMay 2, 2026 · In Review · `stream-rerun-dispatch`, `measurement-system`Experiment 118 eliminated ReaderPool wake amplification: overloaded dispatch still parks, but dispatcherWakeRetryTotal stays at zero. The remaining stream-shaped pressure is therefore not another...Exp 121Experiment 121: Invalidation traversal cost auditMay 3, 2026 · In Review · `stream-rerun-dispatch`, `measurement-system`Exp 120 closed the over-dispatch path inside StreamEngine._flushQueue and dropped dispatcherParkedTotal and dispatcherMaxParkedConcurrent to zero on every measured...Exp 120Experiment 120: Bounded `_flushQueue` admissionMay 2, 2026 · In Review · `stream-rerun-dispatch`Experiment 119 measured the post-FIFO dispatch-pressure surface and found the surviving signal: A11c overlap creates 3,590 parked dispatchers per 500-write burst and reaches max_parked = 46 parke...Exp 119Experiment 119: Post-FIFO dispatch pressure auditMay 1, 2026 · In Review · `stream-rerun-dispatch`, `measurement-system`Experiment 118 fixed the old shared-completer wake amplification inside ReaderPool._dispatch: overloaded reads still park, but FIFO waiters keep dispatcherWakeRetryTotal at zero.Exp 118Experiment 118: FIFO dispatch waiters with counter gateMay 1, 2026 · In Review · `stream-rerun-dispatch`Experiment 114 showed that the old shared-completer reader-pool wakeup could be structurally wasteful: one worker-free event woke every parked dispatcher, one caller won the slot, and the rest scan...Exp 117Experiment 117: Named parametersMay 1, 2026 · Rejected (deferred for v0.x launch — see Decision) · `parameter-encoding-and-binding`resqlite only accepts positional ? parameters. SQLite supports four placeholder syntaxes natively (:name, @name, $name, ?NNN), all resolved through sqlite3_bind_parameter_index. Peers (...Exp 116Experiment 116: Wide batch insert release coverageMay 1, 2026 · Accepted · `parameter-encoding-and-binding`, `measurement-system`Experiment 113 showed that batch parameter row width is a real write-path dimension. The existing release write suite still only tracked the narrow two-parameter insert shape:Exp 115Experiment 115: Dispatcher park counters for ReaderPoolMay 1, 2026 · Accepted · `stream-rerun-dispatch`, `measurement-system`Exp 105 (raise pool cap 4→8, rejected) and exp 114 (FIFO waiter queue, rejected after the exp 106 rebase) both targeted the parked-dispatche...Exp 114UntitledApr 30, 2026 · Rejected · `stream-rerun-dispatch`Exp 105's profile attributed A11c writer throughput loss to "completion-side microtask churn on the main isolate" — the per-write wall closely matched `pool_round_trip ...Exp 113Experiment 113: Direct batch parameter matrix encodingMay 1, 2026 · In Review · `parameter-encoding-and-binding`, `measurement-system`Experiment 096 rejected direct nested batch parameter encoding because the release suite did not show a reliable win and the implementation duplicated too much encoder logic. Experiment 112 then na...Exp 112Experiment 112: Fixed-length batch parameter flatteningApr 28, 2026 · Rejected · `parameter-encoding-and-binding`, `measurement-system`Experiment 096 rejected direct nested batch parameter encoding because it added a second encoder path and did not move the benchmark suite. Experiment 109 then made the shared allocateParams path...Exp 111Experiment 111: Nested-transaction benchmark + revisit savepoint string cacheApr 28, 2026 · Rejected · `transaction-control-paths`, `measurement-system`Two prior experiments rejected work in the savepoint codepath solely because the benchmark suite couldn't see the modified path:Exp 110Experiment 110: Long-text stream hash benchmark + 8-byte FNVApr 27, 2026 · In Review · `long-text-stream-hashing`, `measurement-system`Experiment 099 found a structurally sound optimization for TEXT/BLOB hashing, but rejected it because the benchmark suite did not contain a stream workload with long enough cells to exercise the by...Exp 109Experiment 109: Inline-packed parameter bufferApr 27, 2026 · AcceptedallocateParams (in lib/src/native/resqlite_bindings.dart) currently issues one native allocation per text/blob parameter on top of the reusable struct buffer:Exp 108Experiment 108: Persistent selectBytes out-parameter slotsApr 26, 2026 · RejectedqueryBytes() allocates two tiny native out-parameter boxes on every selectBytes() call:Exp 106EXP-106: Column-level dependency tracking (re-attempt of [EXP-052](052-column-level-dependencies.md))Apr 25, 2026 · AcceptedThe stream invalidation engine tracks dependencies at table granularity. A stream watching SELECT id, a, b FROM wide re-queries on any write to wide, even if the write only modifies an unrelate...Exp 105Experiment 105: Raise reader pool worker capApr 25, 2026 · RejectedA11c (Many-Streams Writer Throughput) ships measuring writer throughput while N=50 reactive streams are subscribed. resqlite's writer drops from ~50k w/s with no streams to ~4k w/s with 50 streams ...Exp 104Experiment 104: Re-eval of exp 094 (dirty/read table string reuse) under A11c fan-outApr 25, 2026 · RejectedExp 094 (Apr 23, 2026) made read_set_add and dirty_set_add keep the existing strdup buffer when a reused slot already held the same table name, eliminating o...Exp 103Experiment 103: Native nested transaction depth controlApr 25, 2026 · RejectedExperiment 101 accepted cached prepared statements for the top-level transaction controls:Exp 102Experiment 102: Cached SAVEPOINT / RELEASE / ROLLBACK TO stringsApr 25, 2026 · RejectedExp 101 cached the three transaction-control statements (BEGIN IMMEDIATE, COMMIT, ROLLBACK) as persistent prepared stmts, but it explicitly excluded the dynamic savepoint statements:Exp 101Experiment 101: Cached BEGIN / COMMIT / ROLLBACK statementsApr 25, 2026 · Acceptedresqlite ran every transaction-control statement through sqlite3_exec():Exp 100Experiment 100: Bounded stream re-query schedulerApr 25, 2026 · RejectedExperiment 083 fixed the worst stream invalidation backlog by coalescing re-runs before they enter the reader pool. The remaining issue is high fan-out: when many active streams are invalidated by ...Exp 099Experiment 099: 8-byte-chunked FNV for byte-stream cellsApr 25, 2026 · Rejectedfnv_combine_bytes in native/resqlite.c hashes TEXT and BLOB cell payloads one byte at a time during stream change detection (resqlite_query_hash, exp 075). For long stri...Exp 097Experiment 097: One-pass initial stream decode and hashApr 23, 2026 · In ReviewInitial stream registration decodes the query result for subscribers, then replays the same SQLite statement through resqlite_query_hash to establish the baseline result hash used by later `selec...Exp 096Experiment 096: Direct batch parameter encodingApr 23, 2026 · RejectedexecuteBatch flattens List<List<Object?>> into a temporary Dart List<Object?> before encoding native parameter structs. For large batches, that extra list is avoidable.Exp 095Experiment 095: Persistent writer result bufferApr 23, 2026 · RejectedexecuteWrite allocates a 16-byte native result buffer for every write so C can return affectedRows and lastInsertId. The writer isolate is single-threaded, so this buffer could be allocated o...Exp 094Experiment 094: Dirty/read table string reuseApr 23, 2026 · RejectedDirty-table and read-table capture reuse fixed native arrays, but each capture cycle still frees and duplicates the table-name string for the next active slot. Repeated single-table writes and repe...Exp 093Experiment 093: Alias cache entry's read tables instead of copyingApr 22, 2026 · RejectedOn every reader query, resqlite_get_read_tables copied the read-table list out of a per-reader resqlite_read_set scratch buffer. That scratch was populated on each query by `read_set_load_from_...Exp 092Experiment 092: `wal_checkpoint=NOOP` probe in the periodic checkpointerApr 21, 2026 · Rejected (premise invalid — exp-029 is hook-gated, not timer-gated)Exp-029 schedules PASSIVE checkpoints on the writer. SQLite 3.51.0 added SQLITE_CHECKPOINT_NOOP, which probes WAL state (pnLog / pnCkpt) without doing any checkpoint work. The daily-research ...Exp 090Experiment 090: sqlite3mc bump audit — already currentApr 21, 2026 · Rejected (no bump needed — already on newest stable sqlite3mc)SQLite 3.51.0 (2025-11-04) advertised "use fewer CPU cycles to commit a read transaction." The daily-research note flagged a bump as a candidate: if our vendored sqlite3mc is behind, we get across-...Exp 089Experiment 089: Deeply-immutable `ResultSet` for zero-copy isolate transferApr 21, 2026 · Rejected (blocked upstream — re-check when DI typed-data factory ships)Every read crosses an isolate boundary. Below the byte-size sacrifice threshold (exp 039) we pay a SendPort.send copy; above it we pay an Isolate.exit + reader respawn. The ~0.05 ms copy on sub...Exp 088Experiment 088: `SQLITE_ENABLE_SETLK_TIMEOUT` — kernel-blocking WAL waitsApr 20, 2026 · Rejected (multi-run confirmation, downgraded from Accepted)Exp 080 (dispatch budget) surfaced a 34 ms single-insert outlier in the p99/max tail (2000× the 16 μs median). The prime suspect was a passive WAL checkpoint firing mid-insert and blocking the writ...Exp 085Experiment 085: Reserved reader slot for rerunsApr 20, 2026 · Rejected (full-capacity variant); reserved-slot policy keptExperiment 083 introduced two changes together:Exp 084Experiment 084: Late dispatch generation stampApr 20, 2026 · RejectedThe old stream rerun path captured writeGen too early: when the rerun was requested, not when it actually got a reader. That made it plausible that a simpler fix than a bounded scheduler might work:Exp 083Experiment 083: Stream rerun pre-dispatch queueApr 20, 2026 · In ReviewHigh-fan-out stream scenarios (A11, A11b) were still spending a large amount of time on reruns that eventually finished stale.Exp 082Experiment 082: Isolate message-graph benchmark for result payloadsApr 20, 2026 · Rejected (optimization hypothesis disproven; benchmark harness kept)After several result-storage experiments, it was still unclear whether the remaining headroom lived in:Exp 081Experiment 081: Binary row result storage under `select()`Apr 20, 2026 · Rejectedselect() returns row-shaped results as a shared flat List<Object?> backed by ResultSet / Row. That shape is already lean compared with materialized maps, but we suspected there might still ...Exp 080Experiment 080: Dispatch budget research pass (Phase 1)Apr 18, 2026 · Phase 1 complete — measurements taken, candidates identified, no implementation yetDate: 2026-04-18Exp 079Experiment 079: Batch-scoped stream invalidation coalescingApr 18, 2026 · PlannedThe Sync Burst (A7) benchmark exposes a surprising emission-count gap:Exp 077Experiment 077: Cheap-check-first sweep (four small wins)Apr 16, 2026 · Accepted (one real win + three defensible cleanups)Continuing the "don't do work when a cheaper check will do" theme from experiments 068–076, an audit of the hot paths turned up four places that pay a fixed cost on every call even when the eventua...Exp 076Experiment 076: Pre-bound statement cache — rejected during designApr 16, 2026 · Rejected (not implemented — analysis showed no measurable headroom)Date: 2026-04-16Exp 075Experiment 075: Native-buffered hash for `selectIfChanged`Apr 16, 2026 · AcceptedStream re-queries via selectIfChanged go through the full Dart decode path (allocate values list, decode every cell into a Dart String / Uint8List, build RowSchema, return a ResultSet) then has...Exp 074Experiment 074: Bulk `step_many` for the non-streaming read pathApr 16, 2026 · Rejected (memcpy cost exceeds FFI-crossing savings — same wall as exp 018)The read-worker hot path issues one FFI call per row via resqlite_step_row. At ~60-80 ns per isLeaf FFI crossing, a 10,000-row scan pays ~0.8 ms just in crossings — ~15 % of the measured 5.6 ms...Exp 073Experiment 073: Single-slot schema-cache fast-pathApr 16, 2026 · Rejected (no measurable impact)On every call to decodeQuery, the worker does two LinkedHashMap operations against schemaCache for LRU tracking:Exp 072Experiment 072: xxhash64 replacing FNV-1a for result change detectionApr 16, 2026 · RejectedResult change detection for reactive streams (selectIfChanged on the read worker, _hashResult in StreamEngine) is on the hot path for every write that invalidates a stream. The existing imple...Exp 071Experiment 071: MRU-first stmt cache scan + precomputed SQL hashApr 16, 2026 · Rejected (no measurable impact)Scanning stmt_cache_lookup_entry in native/resqlite.c:Exp 070Experiment 070: Zero-row-change commit short-circuit + persistent dirty bufferApr 16, 2026 · Accepted (cleanup + minor allocation elimination)Every write went through getDirtyTables(dbHandle), which:Exp 069Experiment 069: SQL fingerprint in stmt cacheApr 16, 2026 · Deferred (proper normalization requires SQL rewriter)The C statement cache keys on the raw SQL string. Applications that build SQL with string concatenation instead of bind parameters thrash the cache:Exp 068Experiment 068: DDL schema_version watchdogDeferred (correctness idea valid, implementation hit a stmt-cache race that needs more design work)Active streams cache their read tables at registration time via the authorizer hook. When DDL changes the schema — CREATE TABLE, DROP TABLE, ALTER TABLE — the cached dependencies can go stale:Exp 067Experiment 067: Shrink initial values list allocationApr 16, 2026 · Rejected (regressed — Dart VM has fast path for List.filled)decodeQuery pre-allocates List<Object?>.filled(colCount * 256, null, growable: true) for every query. For point queries (colCount ≈ 5, rowCount = 1), this is 1280 null slots for a single-row re...Exp 066Experiment 066: Transparent single-row fast path in select()Apr 16, 2026 · Rejected (insufficient transparent headroom)Experiment 063 added a selectOne(sql, params) API that was 28-48% faster than select() for point queries. User feedback: the API-surface cost wasn't worth it, but the methodology was sound. Cou...Exp 065Experiment 065: JSON1 bulk shapes re-evaluationApr 16, 2026 · Rejected (confirms experiment 031's original conclusion)Experiment 031 rejected SQLite's built-in json_group_array(json_object(...)) approach for bulk JSON output as "mixed and workload-specific." That evaluation was against the pre-Ryu/pre-SWAR seria...Exp 064Experiment 064: Drop redundant sqlite3_clear_bindingsApr 16, 2026 · Accepted (cleanup)The bind_params function in resqlite.c calls sqlite3_clear_bindings before binding each parameter. The comment explained this as defensive code for "reusing a statement with fewer params than...Exp 063Experiment 063: SelectOne fast path (combined single-row FFI with inline text copy)Apr 16, 2026 · Rejected (measured +28-48% win, rejected to preserve lean API)Point queries (single-row lookups by primary key or indexed column) are a common pattern, but the current select() path crosses the FFI boundary four times per query:Exp 061Experiment 061: C-side hash for unchanged stream re-queriesApr 16, 2026 · Skipped (architectural fit issue; benchmark cannot measure)Stream re-queries with unchanged results currently: 1. Decode all cells into Dart objects (String allocations + int/double boxing) 2. Hash the values list via FNV-1a (experiment 031 — worker-side h...Exp 060Experiment 060: Combined single-row FFI callApr 16, 2026 · Rejected (blocked by text pointer lifetime; predecessor of 063)Point queries (SELECT * WHERE id = ?) cross the FFI boundary four times:Exp 059Experiment 059: Row count hint in schema cacheApr 16, 2026 · Rejected (below noise floor)decodeQuery pre-allocates List<Object?>.filled(colCount * 256, null, growable: true) for the result values. This is a compromise: too small, and multi-row queries do unnecessary list doublings;...Exp 058Experiment 058: Short-string value cacheApr 16, 2026 · Rejected (catastrophic regression)The text decode path (fastDecodeText in query_decoder.dart) allocates a new Dart String for every text cell read from SQLite. In CRUD schemas, many text values repeat across rows: status enum...Exp 057Experiment 057: Preupdate hook batching for batch insertsApr 16, 2026 · Rejected (savings below measurement precision after re-evaluation)The preupdate hook fires per-row during batch inserts. For executeBatch with 10,000 rows into one table, that's 10,000 calls to dirty_set_add, each doing a linear dedup scan over the set conten...Exp 055Experiment 055: Columnar typed arrays for resultsApr 15, 2026 · Rejected (memory win real but below time-based benchmark floor)Query results use List<Object?> in row-major layout. Every int and double is boxed — a 64-bit integer costs ~24 bytes (8 pointer + 16 boxed object) vs 8 bytes in an Int64List. For 10,000 rows ×...Exp 054Experiment 054: Profile-Guided Optimization (PGO)Apr 15, 2026 · Rejected (infrastructure limitation on macOS)The -O3 compiler flag makes optimization decisions based on static heuristics (code structure, loop analysis). PGO uses actual execution profiles to make better decisions: branch prediction hints...Exp 053Experiment 053: Page size 8192Apr 15, 2026 · Rejected (performance wins real, but breaks existing databases)SQLite defaults to 4096-byte pages. Modern SSDs and NVMe drives have 4KB-16KB erase blocks. Larger pages mean fewer B-tree nodes for the same data volume, shallower trees, and fewer page reads for ...Exp 052EXP-052: Column-level dependency trackingApr 15, 2026 · Deferred → superseded by [EXP-106](106-column-level-deps.md) (Accepted, 2026-04-25)The stream invalidation engine currently tracks dependencies at table granularity. A stream watching SELECT name FROM users WHERE active = 1 re-queries on any write to users, even if the write ...Exp 051Experiment 051: Lock-free reader pool with atomicsApr 15, 2026 · Rejected (not attempted — optimization target is dead code)The reader pool uses sqlite3_mutex_enter/leave around a linear scan of up to 16 reader slots in acquire_reader (resqlite.c:891). Under contention (8 concurrent reads), the mutex serializes pool...Exp 047Experiment 047: Authorizer opt-out for non-stream queriesApr 15, 2026 · RejectedThe SQLite authorizer callback fires on every column access in every query, recording read table dependencies for stream invalidation. For non-stream reads (select(), selectBytes()), this depen...Exp 046Experiment 046: Synchronous StreamControllerApr 15, 2026 · RejectedEach controller.add(event) in StreamEngine._subscribe schedules a microtask for async delivery. For N subscribers, that's N microtask schedulings per invalidation cycle. `StreamController(sync:...Exp 045Experiment 045: Microtask invalidation coalescingApr 15, 2026 · AcceptedIn StreamEngine.handleDirtyTables (stream_engine.dart:68), each write dispatches re-queries immediately. When multiple rapid db.execute() calls happen synchronously (e.g., a loop of inserts wit...Exp 044Experiment 044: SQLITE_ENABLE_BATCH_ATOMIC_WRITEApr 15, 2026 · AcceptedOn Android devices running F2FS (the default filesystem since Android 9), SQLite writes both journal + database pages (2x I/O) for every transaction. F2FS supports batch atomic writes, which allows...Exp 043Experiment 043: SWAR escape scanning + escape lookup tableApr 15, 2026 · Acceptedjson_write_string in resqlite.c scans each byte individually through 8 if/else if comparisons to check for JSON-escapable characters (", \, \b, \f, \n, \r, \t, control chars < 0x2...Exp 042Experiment 042: Link-Time Optimization (LTO)Apr 15, 2026 · RejectedThe SQLite amalgamation and resqlite.c are compiled as separate translation units. The compiler cannot inline sqlite3_column_int64, sqlite3_column_text, sqlite3_column_double, etc. across t...Exp 041Experiment 041: Ryu double-to-string for JSON serializationRejected (minimal isolated benefit, high maintenance complexity)The selectBytes JSON serialization path uses snprintf(num, sizeof(num), "%.17g", ...) for float formatting. snprintf parses a format string and always emits 17 significant digits even when fe...Exp 040Experiment 040: Reader Slot Event Port CleanupApr 14, 2026 · AcceptedThe reader pool had accumulated protocol complexity that no longer matched the real invariants of the system.Exp 039Experiment 039: Byte-size sacrifice thresholdApr 10, 2026 · AcceptedThe cell-count threshold (rows * cols > 6000) is a poor proxy for SendPort copy cost. A 200-row query with large text blobs can transfer more data than a 2000-row query with tiny ints, yet only t...Exp 038Experiment 038: Stack Allocation for Column Name ArraysApr 9, 2026 · AcceptedDate: 2026-04-09Exp 037Experiment 037: Persistent JSON Buffer Per ReaderApr 9, 2026 · AcceptedDate: 2026-04-09Exp 036Experiment 036: Compiler Hints (Dart + C)Apr 9, 2026 · AcceptedDate: 2026-04-09Exp 035Experiment 035: Reuse Cell Buffer Across QueriesApr 9, 2026 · AcceptedDate: 2026-04-09Exp 034Experiment 034: Per-Worker Schema CacheApr 9, 2026 · AcceptedDate: 2026-04-09Exp 033Experiment 033: FNV-1a Hash for Result Change DetectionApr 9, 2026 · AcceptedDate: 2026-04-09Exp 032Experiment 032: Row Map Facade OverridesApr 9, 2026 · Acceptedresqlite's transport/result shape is strong: one shared RowSchema, one flat values list, and lazy Row wrappers. But Row itself uses MapMixin, and many of the default MapBase operations ...Exp 032Experiment 032: Completer.sync in Pool DispatchApr 9, 2026 · AcceptedDate: 2026-04-09Exp 031Experiment 031: Worker-Side Result Hash for Stream Re-queriesApr 8, 2026 · AcceptedStream re-queries transfer the full ResultSet from worker to main, then hash it on the main isolate for change detection. For unchanged results (common in fanout scenarios where many streams watch ...Exp 031Experiment 031: JSON1 Bulk ShapesApr 8, 2026 · RejectedSQLite's JSON1 extension can trade many bound parameters for one JSON payload. That could help resqlite in two places where host-side overhead still matters:Exp 030Experiment 030: Dedicated Reader AssignmentApr 8, 2026 · AcceptedEach Dart pool worker is assigned a fixed C reader index at spawn time. The worker passes this index directly to new resqlite_stmt_acquire_on() and resqlite_query_bytes() variants that skip the...Exp 029Experiment 029: Periodic PASSIVE CheckpointingApr 8, 2026 · AcceptedThe current writer policy uses:Exp 028Experiment 028: Static Bind for Text and Blob ParamsApr 8, 2026 · AcceptedThe current bind path allocates native memory for text/blob params in Dart, then asks SQLite to copy those same bytes again via SQLITE_TRANSIENT. If we keep the param buffers alive until the stat...Exp 027Experiment 027: Transaction Query Writer CacheApr 8, 2026 · Rejectedtx.select() inside interactive transactions still prepares statements on the writer connection each time. Reusing the writer-side statement cache for those transaction reads should reduce interac...Exp 026Experiment 026: `sqlite3_db_status()` ProbeApr 8, 2026 · Rejected (no follow-on optimization justified)Before attempting a process-global page cache or more aggressive memory tuning, we should check whether sqlite is actually under page-cache or lookaside pressure in resqlite's hot paths.Exp 025Experiment 025: `PRAGMA optimize`Apr 8, 2026 · RejectedSQLite recommends PRAGMA optimize to refresh planner statistics and opportunistically run lightweight analysis. If planner quality is holding back app-shaped reads, running `PRAGMA optimize=0x100...Exp 024Experiment 024: Increase JSON Buffer Initial Size to 16KBApr 8, 2026 · Accepted (negligible impact, but sensible default)Starting the JSON output buffer at 4KB causes 2-3 reallocs (each a full memcpy) for typical results. 16KB covers most results in a single allocation.Exp 023Experiment 023: Fast int64-to-string for JSON SerializationApr 8, 2026 · Acceptedsnprintf(num, sizeof(num), "%lld", value) parses the format string on every call. A hand-rolled int64-to-string avoids format parsing overhead. With 5000 rows × 2 integer columns = 10K calls per ...Exp 022Experiment 022: WAL Autocheckpoint TuningApr 8, 2026 · Accepted (correctness/reliability improvement, not a benchmark win)Raising WAL autocheckpoint from the default 1000 pages (~4MB) to 10000 pages (~40MB) on the writer reduces checkpoint frequency, avoiding fsync-heavy latency spikes during write bursts. Disabling a...Exp 021Experiment 021: SQLITE_DEFAULT_PCACHE_INITSZ=128Apr 8, 2026 · Accepted (slight positive trend)Pre-allocating 128 page cache slots per connection at startup (instead of the default ~20) avoids incremental malloc pressure during the first few queries. With 2-4 readers + 1 writer, each connect...Exp 020Experiment 020: SQLITE_DEFAULT_LOOKASIDE=1200,128Apr 8, 2026 · Accepted (no measurable impact, but zero-cost improvement)Bumping the lookaside allocator from the default 100 small slots to 128 gives SQLite's dual-pool architecture (since 3.31.0) more headroom for transient allocations during query execution. The SQLi...Exp 019Experiment 019: Hybrid Reader Pool (SendPort + Sacrificial Isolate.exit)Apr 8, 2026 · AcceptedA persistent reader pool eliminates the ~80μs isolate spawn overhead per query. For small results, SendPort.send (copy) is cheaper than the spawn cost saved. For large results, the worker sacrifice...Exp 018Experiment 018: Multi-Row Step (Batch N Rows Per FFI Call)Apr 7, 2026 · RejectedStepping 64 rows per FFI call instead of 1 would reduce FFI crossing overhead from 5000 calls to ~78 calls at 5000 rows. At ~50ns saved per call (with isLeaf), that's ~245μs saved.Exp 017Experiment 017: Dart_PostCObject for ReadsApr 7, 2026 · RejectedDart_PostCObject uses a completely different code path (dart_api_message.cc) that bypasses the Isolate.exit validation walk. By building query results as Dart_CObject structs in C and posti...Exp 016Experiment 016: SQLite Compile Flags CleanupApr 7, 2026 · Accepted (correctness improvements, performance within noise)Date: 2026-04-07Exp 015Experiment 015: Cell Buffer Union (48 → 16 bytes)Apr 7, 2026 · Accepted (simplicity win, performance neutral)Shrinking resqlite_cell from 48 bytes to 16 bytes using a C union would improve cache locality for the per-row cell buffer, especially for wide schemas and large result sets.Exp 014Experiment 014: Writer Connection TuningApr 7, 2026 · Partially acceptedThree write-path optimizations could reduce per-write overhead: 1. PRAGMA locking_mode=EXCLUSIVE on the writer — skip shm file operations 2. BEGIN IMMEDIATE instead of BEGIN — avoid lock-upgr...Exp 013Experiment 013: FFI isLeaf AnnotationApr 7, 2026 · AcceptedAdding isLeaf: true to all @ffi.Native bindings will reduce per-call overhead by eliminating safepoint checks and thread state transitions on every Dart-to-C call.Exp 012Experiment 012: SendPort vs Isolate.spawn Deep Dive — Why Persistent Pools Aren't FasterApr 7, 2026 · Investigation complete (original rejection of persistent pool confirmed)Date: 2026-04-07Exp 011Experiment 011: Persistent Reader Worker Pool (with Hybrid Sacrificial Exit)Apr 7, 2026 · Rejected (thoroughly tested — three approaches tried)Every select() call spawns a one-off isolate via Isolate.spawn + Isolate.exit. The spawn cost (~0.07-0.09ms) is paid per query. For high-frequency small queries (single-row lookups, paginatio...Exp 010Experiment 010: ASCII Fast-Path for String DecodingApr 6, 2026 · Rejectedutf8.decode is called for every text column value. Most database strings are ASCII. Could an ASCII fast-path (String.fromCharCodes for all-ASCII bytes) avoid the UTF-8 validation overhead?Exp 009Experiment 009: Batch FFI with resqlite_step_rowApr 6, 2026 · AcceptedThe select() hot loop makes ~16 FFI calls per row: 1 sqlite3_step + 6 sqlite3_column_type + 6 sqlite3_column_xxx (value read) + ~3 sqlite3_column_bytes (text lengths). For 5,000 rows, tha...Exp 008bExperiment 008b: Byte-Backed Lazy Maps (ByteBackedResultSet)Apr 6, 2026 · Rejected as default (informed the flat-list design)Isolate.exit validation walks O(n) Dart objects. Transferring a single Uint8List is O(1). Could we transfer results as bytes and wrap them in lazy Map objects on main that decode from the buf...Exp 008Experiment 008: Flat Value List with Lazy ResultSetApr 6, 2026 · Accepted (the breakthrough)At 20,000 rows, the Isolate.exit() validation walk cost 8.44ms — 38% of total select() time. Investigation revealed the Dart VM's MessageValidator walks every heap object in the transfer grap...Exp 007Experiment 007: C-Level Connection PoolApr 6, 2026 · Acceptedresqlite used a single C connection with a pthread_mutex. Concurrent reads serialized — query 2 waited for query 1 to finish. sqlite_async's Dart-side connection pool (multiple worker isolates, e...Exp 006Experiment 006: String Interning for Isolate.exit OptimizationApr 6, 2026 · RejectedThe Isolate.exit() MessageValidator walks every object in the transfer graph. For 20,000 rows with 4 text columns, that's ~80,000 String objects. Columns like category have only 10 unique value...Exp 005Experiment 005: Dart Binary Codec with TransferableTypedDataApr 6, 2026 · RejectedIsolate.exit() transfers maps zero-copy but kills the isolate. SendPort.send() keeps workers alive but deep-copies maps. Could we encode maps into a Uint8List on the worker, send via `Transfe...Exp 004Experiment 004: NOMUTEX with Per-Query LockingApr 6, 2026 · AcceptedSQLite's default SQLITE_OPEN_FULLMUTEX wraps every API call in a mutex lock/unlock. For a 20,000-row query with 6 columns, that's ~60,000 lock/unlock operations (step + type + value per cell). Ea...Exp 003Experiment 003: C-Level Connection and Statement CacheApr 6, 2026 · AcceptedEach Isolate.run() call opened a fresh SQLite connection — sqlite3_open_v2 + PRAGMA setup (WAL, busy_timeout, synchronous). This cost ~0.5-1ms per query. Additionally, prepared statements were ...Exp 002Experiment 002: C Binary Buffer for MapsApr 6, 2026 · Rejected (for maps path; kept for selectBytes infrastructure)The sqlite3 Dart package crosses the FFI boundary per-column per-row when reading results. For 5,000 rows × 6 columns, that's 30,000+ FFI calls. Each call has ~10-20ns overhead. Could a C function ...Exp 001Experiment 001: C-Native JSON SerializationApr 6, 2026 · AcceptedBuilding an HTTP response from a SQLite query requires: query → Dart maps → jsonEncode → utf8.encode → shelf. At 5,000 rows, jsonEncode + utf8.encode alone costs ~8.6ms on the main isolate. The dat...