ivmlite v0.1.0-alpha.6

ivmlite v0.1.0-alpha.6

Release date: 2026-10-20.

The sixth technical preview of ivmlite, an experimental
SQLite loadable extension for incrementally maintained grouped aggregates.
This version accepts existing non-STRICT tables and all SQLite storage classes,
and adds exact REAL aggregation and retractable MIN/MAX.

Breaking: shadow-table format 6 cannot read alpha.5 format-5 state. Old views
open as broken views that can be dropped. Drop every old ivmlite view and
recreate it from its SELECT query. There is no automatic migration; keep the
base tables and the view definitions as the source of truth.

What changed

• Base tables may be STRICT or non-STRICT, with any declared type interpreted
  by SQLite affinity. STRICT ANY is accepted. NULL, INTEGER, REAL, TEXT and
  BLOB pass through capture, state and output without storage-class coercion.
• COUNT(column), TOTAL(column), AVG(column), MIN(column) and MAX(column)
  join COUNT(*) and SUM(column). SUM accepts any column, including SQLite's
  numeric classification of TEXT and BLOB values. Extremum state retains live
  multiplicities and survives retractions.
• REAL and BLOB comparison literals are accepted. Literal conversion follows
  comparison affinity, except REAL against TEXT-affinity columns is refused:
  SQLite REAL-to-TEXT rendering varies by version, so use a quoted string.
• Join columns must have the same affinity. Canonical numeric key equality
  makes INTEGER 1 match REAL 1.0; NULL still matches nothing. Group keys use
  the same equality, and output REAL when any live key in the group is REAL.
• FluxFlow's adapted demo uses native REAL amounts; Kener uses REAL latency
  and AVG/MIN/MAX. Their stored buckets and reader-side work still apply.
• Widened mixed-type differential tests compare both the real extension and
  in-memory engines with SQLite and an independent exact reference. Additional
  tests exercise order independence, persisted extremum multiplicities, and
  the actual artifact's new features across refresh, rollback and reopen.

Differences from SQLite

REAL SUM, TOTAL and AVG return the exact result rounded once. SQLite uses
scan-order Kahan–Babuška–Neumaier summation and divides after rounding its sum,
so last bits may differ: AVG of 0.1, 0.2 and 0.3 is 0.2 in ivmlite and
0.19999999999999998 in SQLite. Finite inputs can also overflow SQLite's
intermediate accumulator to infinity, or produce NULL after intermediate
infinity cancellation, while ivmlite's exact final result is finite. The
finite-input fixtures observe infinity on bundled SQLite 3.53.2; synthetic
comparator tests cover NULL compatibility separately. Actual +Inf and -Inf
inputs together return NULL, as in SQLite.

Integer SUM fails refresh with integer overflow in SUM only when the final
exact result is outside signed 64-bit range. SQLite can fail when a prefix of
its scan overflows even if the final exact result fits. MIN returns INTEGER
and MAX REAL for numerically equal INTEGER/REAL inputs; SQLite's choice
depends on scan order.

Workload compatibility

The After M2c census projects ten of
28 application candidates as compatible after their documented adaptations:
four high-confidence and six low-confidence fits. It does not demonstrate
upstream execution, adoption or speedups. M2c alone unlocks no Nexmark query as
written; the hypothetical nine-query bundle still requires non-aggregate
views, SELECT expressions and a richer WHERE.

Task 13 application measurements

The M2c benchmark results
cover native macOS ARM64 runs on October 19–20: 100 ablation, 240 confirmation,
92 demo and 48 write-amplification rows, plus one 40-row confirmation follow-up.
All original ivmlite steady integer ratios are at most 1.038; the eight
unchanged integer demos' maximum is 1.0367. The original naive-recompute
maintenance/combined flags of 1.061/1.062 did not reproduce in the single
follow-up (1.027/1.028). The follow-up's new trigger-control combined flag is
1.053, versus 1.022 originally; it remains reported and no further rerun was
chosen. The original flags do not establish a persistent regression, and no
spec waiver is needed. This does not mean every raw comparison is below 5%.
The host was not isolated, limiting small-effect attribution.

FluxFlow's native REAL amounts and Kener's AVG/MIN/MAX query change the work
being measured. Current combined costs are 6.052 versus 135.890 ms for
FluxFlow's indexed recomputation, and 11.072 versus 572.612 ms for Kener;
Kener's full-result read raises ivmlite end-to-end cost to 166.546 ms. Its
bootstrap costs 30,638.833 ms, and database page allocation is 1,760,784 KiB
versus 255,436 KiB for the same-day old integer query (6.893x). This allocation
includes base tables and database structures, not RSS or pure operator state.
The FluxFlow and
Kener pages retain full
ranges, indexed/unindexed comparisons, trigger limitations and historical
October 10 alpha.3 tables. Changed-query ratios are not engine speedups or
integer regression checks. These synthetic runs do not establish application
adoption or production performance.

The complete generated report,
scoped benchmark builds,
demo builds,
original capture provenance and
flag follow-up preserve the evidence.
Correctness-suite wall time is separate from application performance; the
validation runtime record does not
serve as a benchmark speed claim.

Known limitations

A nonempty GROUP BY and at least one aggregate are still required. SELECT and
GROUP BY use bare columns; aggregate arguments are bare columns. Views allow
at most one predicate and one two-table inner equi-join. Expressions, AND/OR,
subqueries, CTEs, DISTINCT aggregates, group_concat, HAVING, ORDER BY, LIMIT,
outer/self/multiway joins and retention/sealing remain unsupported. Only
BINARY collation is supported, and the conservative COLLATE check may also
refuse an explicit BINARY declaration. Refresh remains explicit. Supported
SQLite behavior is 3.45 and later; tests use bundled SQLite 3.53.2.

The parent limitations
retain operational boundaries, including host extension availability and
capture restrictions. Workload compatibility is not a promise of an advantage
over indexed recomputation or application-maintained triggers.

Downloads

Download archives from the
alpha.6 GitHub release
and verify them against its
SHA256SUMS.
The release workflow packages Linux x86_64 and macOS arm64 libraries with
README, LICENSE and changelog. Assets appear only after both platform builds
and their extracted-library smoke tests pass. The native macOS evidence above
does not validate Linux; each platform's artifact smoke is a separate gate.

Release verification

Linux x86_64 and macOS arm64 archives passed native extracted-library lifecycle
smoke tests in the release workflow.
After downloading the published assets, SHA256SUMS, archive members, tagged
README/CHANGELOG/LICENSE bytes, and binary architectures were independently
verified. The downloaded macOS library also passed a local lifecycle smoke.
The PR CI passed
848 tests with 0 failures and 3 explicitly ignored unsupported-query gates
on each platform.

ivmlite v0.1.0-alpha.5

ivmlite v0.1.0-alpha.5

The fifth technical preview of ivmlite, an experimental SQLite loadable
extension for incrementally maintained grouped aggregates. This release adds
one SQL function that refreshes every view of a database in one call,
either all together or one by one, and reduces the cost of a refresh with
nothing new.

The shadow-table format is unchanged (5), so databases created with
v0.1.0-alpha.4 work as they are. The supported SQL is unchanged.

What changed

• ivm_refresh_all() refreshes every view in one call. Call it from a
  SELECT:

  SELECT ivm_refresh_all();            -- every view or none: 2 refreshed
  SELECT ivm_refresh_all('continue');  -- one by one: refreshed 0, skipped 2, failed 0

  • Atomic, the default. Every view refreshes, or none does. If one view
    fails, everything is rolled back and the call fails with
    ivm_refresh_all: view <name>: <message>; no view was refreshed.
    Otherwise it returns the number of views it refreshed, not counting the
    views it skipped because they had nothing new.
  • Continue. Each view refreshes on its own. A view that fails is rolled
    back alone, and the result names it with its message, after the counts
    of views refreshed, skipped and failed.
  • Where to call it. From a statement that writes, it fails with a
    message saying so. SQLite refuses it in the database schema's triggers,
    views and CHECK constraints. Inside your own BEGIN … COMMIT it
    works, and a later ROLLBACK undoes it.
  • Waiting for other writers. In autocommit it starts with
    BEGIN IMMEDIATE, so it waits for another connection's write lock under
    busy_timeout, as a refresh statement does. Called as
    SELECT ivm_refresh_all() FROM t, whose read is already under way, it
    cannot wait and fails with database is locked. Inside your own
    transaction, start it with BEGIN IMMEDIATE to wait.
  • Failures that end the transaction. A failure that rolls back the
    whole transaction, such as a trigger's RAISE(ROLLBACK), fails the call
    in either mode, and ends your own transaction if it ran inside one.

  A view's own refresh, INSERT INTO v(v) VALUES ('refresh'), is itself a
  write, and SQLite cannot open a savepoint inside one. So a function called
  from a SELECT is the only way to make several views' refreshes atomic
  together.

• A refresh with nothing new returns early. If no base table has a
  change past a view's last refresh, the refresh stops after its checks. It
  computes, stages and applies nothing. This helps every refresh, not only
  ivm_refresh_all. The checks always run first. A base table that was
  dropped and recreated has lost its capture triggers, so its changes are no
  longer recorded. The checks are what report that view as broken; a
  shortcut around them would leave it silently out of date.

Fixes and additional demos

• A refused savepoint close rolls back ivm_refresh_all inside an existing
  application transaction too. Cleanup guards stay armed until the close
  succeeds, and continue mode uses a distinct per-view savepoint. If the host
  persistently refuses rollback, the error reports that refreshes may remain.
• Catalog checks qualify pragma functions with main; TEMP objects with
  matching names cannot shadow them. Catalog validation rejects entries that
  name a table other than an ivmlite virtual table.
• Benchmark preflight replays the actual deterministic trace and rejects
  delete exhaustion even if the expected delete count fits.
• Five additional demand-shaped demos
  cover Zenod, Nicti, Postio, Phoenix and PocketBase. Synthetic fixtures, mixed
  writes and SQLite multiset oracles exercise single-table and join rollups.
  Results include full/covering-index recomputation and local SQL triggers,
  bootstrap and SQLite page allocation. Postio is approximately tied with
  recomputation and PocketBase is slower; triggers win in all five. These
  saved runs used commit 1c61d6e with the pre-bump alpha.4 package version;
  they are workload evidence, not production adoption or new alpha.5 runs.

What the benchmark found

The full results, data and reproduce commands are in
docs/bench/README.md,
"M1b Phase 7". They come from a new Python driver, separate from the matrix,
which times a refresh of every view over 100,000 base rows in 1,000 groups.
The measured results:

• ivm_refresh_all is not faster than your own loop. Against a loop of
  refresh statements the application keeps prepared, it costs 4.0–5.4 µs more
  per view per call with one-row or empty batches: 8.97 against 8.16 ms for
  200 views (1.10x), and twice the loop's time when every view is idle. The
  reason is that it prepares its nested statements on every call. A cached
  statement that names an ivmlite view would keep the connection from
  closing, so the function may not keep them. With 1,000-row batches it read
  2–4% faster than the loop, within the run's ranges. Use it for the
  atomicity and the single call, not for speed.
• An idle refresh is about 4x cheaper. Refreshing 200 views with nothing
  new now takes 0.87 ms instead of 3.75 ms, about 4.3 instead of 18.7 µs per
  view. With half the views idle, refreshing all 200 is 1.33x faster (in every
  round, with overlapping ranges).
• Nothing else moved. Where every view has new changes, both builds do
  the same work, and the loop timings stayed within 0.97–1.02x of the
  previous release.

Known limitations:

• ivm_refresh_all refreshes every view of the database. It cannot refresh
  a chosen subset, or only the views that read one table.
• Each view still reads its own changes. Views over the same table do not
  share one read of it.
• The limitations of v0.1.0-alpha.4 still apply: refresh and writes are
  still several times slower than hand-written triggers, reconnecting many
  views at once is still slow, and each open view keeps about 40 KB of cached
  statements.

These numbers come from in-memory databases with uniformly distributed data,
measured through Python's sqlite3 on a machine that was not fully idle.
Real workloads will differ; reports of real schemas and queries, including
ones ivmlite rejects or makes slower, are the most useful feedback.

Downloads

Linux x86_64 and macOS arm64 archives plus SHA256SUMS. Each archive contains
the loadable library, README, LICENSE, and changelog.

ivmlite v0.1.0-alpha.4

ivmlite v0.1.0-alpha.4

The fourth technical preview of ivmlite, an experimental SQLite loadable
extension for incrementally maintained grouped aggregates. This release
removes the fixed costs that every refresh paid, whatever the size of the
change, and changes the benchmark to measure the steady state.

Breaking: the shadow-table format is now 5. Views created with
v0.1.0-alpha.3 report a format error on every read and refresh. They still
open as broken views, so DROP TABLE <view> works with this release loaded;
drop every alpha.3 view, then create them again. The supported SQL is
unchanged.

What changed

• Statements are compiled once per view. Each view now keeps the
  statements of its refresh prepared for as long as it is open, instead of
  compiling them on every refresh. Rows are staged in a few fixed statement
  shapes, so a steady refresh prepares nothing, even for a one-row change.
  A statement that a schema change makes stale is prepared again
  automatically, and every refresh still checks the schema first.
• One catalog read per schema change, per connection. After any schema
  change, a connection now reads sqlite_schema once and shares that snapshot
  among its views, instead of each view making its own lookups. A base
  table's capture checks are computed once and reused by every view that reads
  it. Error messages for broken views are unchanged.
• Refresh no longer scans every view's progress. __ivm_progress gets an
  index, so the garbage collection in a view's refresh is a search instead of
  a scan over one row per view. This is the format change above. A database
  without that index reports its views as broken; creating any view recreates
  it.
• The benchmark measures the steady state. Every benchmark cell now
  applies one warm-up batch and then five measured batches, and reports the
  first refresh separately. Earlier releases timed only the first refresh
  after CREATE, which paid one-time costs a later refresh does not; their
  numbers are not directly comparable with this release's.

What the benchmark found

The full results, data and reproduce commands are in
docs/bench/README.md,
"M1b Phase 6". In short:

• with one-row changes, a steady refresh is 3.50x faster than in alpha.3 at
  200 views, from 23.5 to 6.7 ms for all of them (from about 118 to 34 µs per
  view), and 3.05x faster at 10 views, from 1.12 to 0.37 ms. The first
  refresh after creating 200 views is 9.6x faster (220.6 to 22.9 ms). With
  batches of 100 rows or more, refresh time moved by 0.92–1.07x, about the
  measurement's noise floor;
• ivmlite beats full recomputation by more than 2x in 62 of the 68 measured
  configurations, including all 48 at 100,000 base rows and more. It still
  loses in the small-table corner (10,000 rows with 1,000-row batches);
• on total write + refresh time it is still slower than special-purpose
  hand-written triggers: 1.56x at best (10 groups, large batches), and
  6.65–8.01x in the other confirmed configurations. With one-row changes the
  gap is now tens of times, not thousands, and the cost per view no longer
  grows with the number of views. What remains is the maintenance itself:
  writing the state and output tables, and running the operator tree;
• in the four demo workloads, steady-state refresh is 1.06–1.37x faster,
  and end-to-end time moves by 0.98–1.06x, because each demo refreshes one
  view after a batch of hundreds of rows;
• SQLite's memory use is higher, because each view keeps its statements: about
  40 KB per view, 38.3 to 46.3 MB at 200 views in one probe;
• a few measurements got slower, each near the run's noise floor: steady
  refresh at 10 views with 1,000-row batches (0.96x and 0.92x), bootstrap (up
  to 8%), and the first refresh in three demos (Kener's 0.67x is noisy).

Known limitations:

• Reconnecting many views at once is still slow: about 2.5 s for 200 views
  over 20,000 rows, after a schema change made by another connection or
  rolled back. This predates this release.
• A user's trigger on an internal __ivm_ table that reads an ivmlite view
  keeps that view's statements alive, so closing the connection returns
  SQLITE_BUSY. Triggers on ivmlite's internal tables are not supported.
• Writes to a tracked table still cost more as views are added. No change in
  this release touched the write path's code, and the write-amplification
  benchmark was not re-run; one benchmark cell's one-row write did move
  between builds, unexplained (see the "M1b Phase 6" section).

These numbers come from in-memory databases with uniformly distributed data
and one refresh per batch, measured on a machine that was not fully idle.
Real workloads will differ; reports of real schemas and queries, including
ones ivmlite rejects or makes slower, are the most useful feedback.

Downloads

Linux x86_64 and macOS arm64 archives plus SHA256SUMS. Each archive contains
the loadable library, README, LICENSE, and changelog.

ivmlite v0.1.0-alpha.3

ivmlite v0.1.0-alpha.3

The third technical preview of ivmlite, an experimental SQLite loadable
extension for incrementally maintained grouped aggregates. This release makes
refresh cheaper, and measures each change against alpha.2 with the same
protocol as before.

Correction (2026-10-10). These notes first said that the Kener demo's
steady-state refresh got slower than in alpha.2, by 0.80x to 0.90x. That was
wrong. The demo benchmark timed one refresh per repeat, which is too noisy to
resolve a change of that size. It now times ten steady-state batches per run,
and with it Kener's refresh is 1.22x faster than in alpha.2 (2.872 → 2.354 ms
median), and steady-state refresh is 1.19–1.47x faster in all four demos. The
corrected results are in the "M1b Phase 5" section of
docs/bench/README.md.
The release's code is unchanged.

Breaking: the shadow-table format is now 4. Views created with
v0.1.0-alpha.2 report a format error on every read and refresh. They still
open as broken views, so DROP TABLE <view> works with this release loaded;
drop every alpha.2 view, then create them again. The supported SQL is
unchanged.

What changed

• One set-based apply per refresh. A refresh used to apply its staged
  changes through a trigger that fired once per staged row. It now arms one
  sentinel row, and a trigger fires once and applies the whole stage with
  set-based statements. It is still a single statement, so state, output and
  watermarks still commit together or not at all.
• No catalog checks on an unchanged schema. A refresh skips its schema
  checks while SQLite's schema cookie is unchanged. Any schema change, made
  from any connection, makes the next refresh check everything again. At 200
  views and one-row batches, this alone cut refresh by about a third.
• Smaller new views. Bootstrap now empties its stage table, so a freshly
  created view no longer holds a second copy of its initial state until its
  first refresh. With 100,000 groups, that about halves the database right
  after CREATE VIRTUAL TABLE.
• Fewer staging statements. A refresh stages up to 64 rows per
  INSERT, within the connection's live limit on SQL variables.
• New demo benchmarks. FluxFlow, noop, Zcash and Kener now run through
  one protocol runner, documented with their results in
  docs/demos/.

What the benchmark found

The full results, data and reproduce commands are in
docs/bench/README.md,
"M1b Phase 5", as published with this tag; its Kener demo finding is
superseded by the correction above. In short:

• in the ablation, write + refresh is 1.35–1.53x faster than alpha.2 in four
  of the five measured configurations, and 3.52x in the fifth (200 views,
  100,000 groups). There, most of the gain is the first refresh after
  CREATE no longer draining the bootstrap stage, because the benchmark times
  that first refresh. Steady-state refresh in the demos, measured separately,
  is 1.19–1.47x faster;
• ivmlite beats full recomputation by more than 2x in 57 of the 68 measured
  configurations (alpha.2: 55), including all 48 at 100,000 base rows and
  more. It still loses in the small-table corner (10,000 rows);
• on total write + refresh time it is still far from special-purpose
  hand-written triggers: 1.81x slower at best (10 groups), and 6.64–17.55x
  slower in the other confirmed configurations with batches of 100 or more,
  and thousands of times slower with one-row batches at 200 views. What
  remains is the maintenance itself: writing the state and output tables,
  and running the operator tree;
• in all four demo workloads, steady-state refresh is 1.19–1.47x faster
  (corrected on 2026-10-10; see above). In Kener it is 1.22x faster, while
  its end-to-end time barely moves, because reading its 550,000 groups
  dominates it;
• write cost is unchanged.

These numbers come from in-memory databases with uniformly distributed data
and one refresh per batch, measured on a machine that was not fully idle.
Real workloads will differ; reports of real schemas and queries, including
ones ivmlite rejects or makes slower, are the most useful feedback.

Downloads

Linux x86_64 and macOS arm64 archives plus SHA256SUMS. Each archive contains
the loadable library, README, LICENSE, and changelog.

ivmlite v0.1.0-alpha.2

ivmlite v0.1.0-alpha.2

The second technical preview of ivmlite, an experimental SQLite loadable
extension for incrementally maintained grouped aggregates. It is the first
release measured against hand-written triggers and full recomputation.

Breaking: the shadow-table format is now 3. Views created with
v0.1.0-alpha.1 report a format error on every read and refresh. They still
open as broken views, so DROP TABLE <view> works with this release loaded;
drop every alpha.1 view, then create them again. The supported SQL is
unchanged.

What changed

• Retractions are index searches. Each view's output table gets an index
  over its output columns. Before, every retracted output row scanned the
  whole output table; at 100,000 groups that turned a 0.13 s refresh into
  17.6 s.
• Cheaper writes with many views. The REPLACE-capture check reads
  sqlite_schema once per written row instead of four times: about 1.4–1.5x
  less write cost at 200 views than in alpha.1. Databases stay readable and
  writable by SQLite versions older than 3.44.

What the benchmark found

The full results, data and reproduce commands are in
docs/bench/README.md.
In short:

• ivmlite beats full recomputation by more than 2x in 55 of the 68 measured
  configurations, and by 17–909x at one million base rows;
• it loses in the small-table corner (10,000 rows), especially with large
  batches;
• on total write + refresh time it stays 4–15x slower than special-purpose
  hand-written triggers in most confirmed configurations;
• its write cost grows with the number of views, to about 60–70 µs extra per
  written row at 200 views, yet from 50 views up it is lower than maintaining
  that many hand-written summary tables.

These numbers come from in-memory databases with uniformly distributed data
and one refresh per batch. Real workloads will differ; reports of real schemas
and queries, including ones ivmlite rejects or makes slower, are the most
useful feedback.

Downloads

Linux x86_64 and macOS arm64 archives plus SHA256SUMS. Each archive contains
the loadable library, README, LICENSE, and changelog.

ivmlite v0.1.0-alpha.1

Full Changelog: https://github.com/SFARL/ivmlite/commits/v0.1.0-alpha.1