Stoolap Files

What's New in v0.4.2

v0.4.2 builds out the analytic side of the v0.4.0 storage engine: sealed volumes get their own secondary indexes, tables can keep their cold rows in a declared order, volumes are read from their files a row group at a time, and cold reads decode only the columns they need. It also carries a round of correctness and durability fixes found by review and fault injection. The volume format stays V4; see Upgrading for what was checked against v0.4.1, and Behaviour changes for what a Rust API user has to adapt to.

Storage

• Secondary indexes for sealed rows. Each volume can carry a side file (.sidx) that indexes a column for the rows sealed into it. It is built with the volume under a budget and owned through the volume's life. An equality, or a lower and an upper bound, on an indexed column is answered from the side file, decided per volume before the volume is touched (#205, [#206], [#207], [#208]). For volumes sealed before an index existed, a background pass builds side files one volume at a time, only while no other build runs and within half the build budget; PRAGMA INDEX_BACKFILL runs a pass now and reports what it built, refused, discarded, failed and left. Contention or a small budget can leave volumes uncovered, and those are scanned as before (#211). Limited joins probe the hot index and the side files (#212, [#213]). PRAGMA INDEX_CACHE_MB, INDEX_BUILD_MB, INDEX_STATS and INDEX_READ_STATS set and report the budgets and the reads.
• Coverage in this release: side files cover single-column B-tree indexes on INTEGER and TIMESTAMP columns. HNSW indexes keep their own path. Other index types, and IN, EXISTS, GROUP BY and top-k over sealed rows, still scan the volumes with zone map, bloom and dictionary pruning.
• Clustered tables. CREATE TABLE ... CLUSTER BY (a, b, c) and ALTER TABLE ... CLUSTER BY keep sealed and compacted rows in key order, and volumes sealed before the key are reclustered through compaction, a bounded number per cycle (#170, [#171], [#172], [#173]). A selective query over a clustered key decodes far less.
• Volumes are read from their files. A volume opened from disk keeps its metadata resident and reads its column blocks from the file by position, one row group at a time, instead of loading the whole file into memory at open and at every cold reload. No file descriptor is held between reads. PRAGMA VOLUME_STATS reports such a volume's tier as file (#183).
• Cold reads decode less. Row groups are ruled out by their zone maps before dictionary filters decode them, a cold read decodes only the columns its projection, expressions and filter name, and dictionary equalities are tested over a row group's raw ids in one pass (#112, [#134], [#175], [#186], [#187]). Decoded row groups are kept in a byte-budgeted cache, 64 MB by default (PRAGMA GROUP_CACHE_MB, GROUP_CACHE_STATS, [#107]).
• Compaction streams its outputs to their files in bounded batches with the unique indexes built from the batches, merges clustered inputs in key order, and decides publication visibility outside the segment locks (#181, [#183], [#184], [#188], [#189]). A size merge no longer rewrites a near-target volume for much smaller ones (#180), and a transfer keeps the row groups it will read again (#194). At a million rewritten rows the heap peak went from 231.6 MB to 218.9 MB and allocation traffic from 2,010 MB to 592 MB (#194, [#195]). Seal removes sealed index entries by row id, 4.1 s to 2.5 s for 2M rows (#110); it still builds each volume in memory.
• Hot store limits. hot_max_rows (default 262,144) makes a commit that takes a table past it request a seal; hot_max_bytes (default off) makes such a commit wait, up to ten seconds, until the seal brings the table back under. Both are DSN keys and PRAGMAs. PRAGMA MEMORY_STATS reports exact hot byte counts per table (#111).
• Checkpoint and WAL stalls. A publication's syncs are ordered with barriers and made durable by the manifest's last sync (#190). The checkpoint holds the commit fence only for the cut and publishes the cut only after the catalog copies are durable, with one WAL sync outside the fence: the largest INSERT stall at a checkpoint went from 37 to 52 ms to 7 to 16 ms in the measured shape (#191). WAL truncation starts a new file with the swap the only work under the lock: 13 to 16 ms of lock hold to 0.2 ms (#193).

Correctness

• A snapshot keeps the row version it reads when a chain passes its history limit (#224).
• A row updated often enough to reach its version history limit no longer reads as absent while its next update commits. Read committed readers and snapshots read the version before (#233).
• A snapshot's protection is taken with its begin sequence, and it keeps its isolation when the default changes later (#234).
• Two databases in one process no longer share committed-transaction answers: an uncommitted or aborted transaction in one database could read as committed because another database, or an earlier open of the same one, had committed the same transaction id on that thread (#236).
• A comment inside a statement is whitespace. Before, SELECT 1 -- note followed by + 1 on the next line ran as two statements, a comment before WHERE in UPDATE or DELETE failed to parse, and an unterminated block comment was dropped: UPDATE t SET v = 0 /* no end updated every row. It is now a parse error and nothing runs (#230).
• The parsed query cache kept queries apart only up to whitespace, including whitespace inside string literals: SELECT 'a b' could return 'a b' (#229).
• A commit whose WAL marker fails takes back everything it applied: versions, row count, index updates, cold tombstones and sealed rows' index keys. A commit that fails on a later table takes back what the earlier tables applied instead of leaving them visible until reopen (#109, [#215]).
• SELECT ... FROM t AS OF TRANSACTION n WHERE id = k returned the current row instead of the historical one (#161).
• A scan could read a newer row payload after a concurrent UPDATE reused its arena slot (#147).
• A GROUP BY answered from an index inside a transaction missed the transaction's own inserts, deletes and moved keys (#197). A HAVING clause could test the wrong aggregate (SUM(v) and SUM(w) in one select list), GROUP BY ROLLUP lost its total row on that path, and HAVING SUM(DISTINCT v) used the non-distinct sum (#198).
• Adding or dropping a column lays out the hot rows as the schema says (#218). Sealed volumes resolve renamed and dropped columns through the table's whole schema history: a column dropped and added back could be pruned by the old column's bloom filter, a column renamed twice was lost, and an anti-join over a sealed inner table let every outer row through (#175, [#176], [#177]). MIN and MAX no longer answer from sealed extrema while the transaction holds its own writes (#177).
• Cold reads stay coherent under compaction, schema changes, file retirement and seals, including LIMIT reads and fetches by row id (#199, [#200], [#201], [#202], [#217]). A scanner no longer crashes when a volume's columns turn resident under a concurrent seal (#179).
• HNSW indexes stay true to the table across sealed updates, older snapshots, reopen and NULL vectors, including keyless tables (#209, [#220]). DROP INDEX removes the side files it no longer needs (#219).
• An index join inside a transaction, a materialized CTE join included, sees the rows the transaction inserted or moved (#216). Numeric IN members and primary key IN lists match as the comparison does, and NOT IN on the primary key walks the keys that exist (#210, [#222]). NOT IN over a subquery holding a NULL answers per SQL (#169). A non-literal LIKE ESCAPE is evaluated (#144).
• ROLLBACK TO SAVEPOINT restores sealed rows deleted or updated after the savepoint (#99). Failed DML statements restore the transaction's state, and a failed RETURNING rolls back the autocommit write (#124, [#128]). Storage and cold-read errors reach SQL instead of being swallowed, and malformed volume metadata is refused instead of panicking (#129 to [#142]).
• An explicit read-only transaction released its per-table stores only on rollback, so each committed one kept them for the life of the engine (#164).

Durability

• Opening a WAL whose last write a crash cut drops the torn bytes and keeps the commits after it; transaction ids are not reused after recovery (#225).
• After a failed WAL write or sync, the file is cut back to its durable length on every platform. On Windows the cut did nothing before, so a refused commit could replay (#227).
• A file database whose persistence cannot start now fails to open, instead of opening without persistence (#226).

Performance

• The first partial-key query after a bulk load, and a seal of those rows, no longer take quadratic time in the rows per key: 47.8 s to 338 ms for the query, and a seal that held its lock for 105 s leaves a reader a 1.8 s worst case (#104).
• The latest N rows of a series are read by walking the multi-column index in key order: 36 ms to 0.011 ms in the measured shape (#109). ORDER BY one column + LIMIT reads from the rows near the bound, and a sorted volume is walked from the bound (#105, [#106]).
• An absent key on a hot index answers from the index instead of scanning: 1.4 ms to 0.05 ms at 262,144 rows (#196). A GROUP BY with HAVING and LIMIT walks the index on a file database too: 984 to 48 microseconds (#198).
• UPDATE and DELETE by primary key on a sealed table resolve by id: 1.1 ms to 2.7 microseconds at 40,000 sealed rows (#162). The PK fast path covers plain column projections: 1.47 to 0.20 microseconds (#161).
• The Transaction API keeps the parsed statement of single-statement SQL it runs again, up to 64 per transaction, admitting SQL on its second run through a 16-entry ring. A repeated PK tx.query went from about 1.5 to 0.9 microseconds, the speed of a prepared statement; a transaction cycling through more than 16 distinct SQL texts keeps parsing (#231).
• A DELETE on a file table with nothing sealed no longer gathers every hot row id on each statement: a DELETE that removes no row over 10,000 hot rows went from about 30 to 8 microseconds (#239).
• Several per-statement allocations are gone: single-row INSERT from 32 to 18 allocations in the benchmark suite (#163), and index join and NOT EXISTS work that was repeated on every execution (#167, [#168]).

SQL and API

• CLUSTER BY in CREATE TABLE and ALTER TABLE.
• PRAGMAs: HOT_MAX_ROWS, HOT_MAX_BYTES, MEMORY_STATS, GROUP_CACHE_MB, GROUP_CACHE_STATS, INDEX_CACHE_MB, INDEX_BUILD_MB, INDEX_STATS, INDEX_READ_STATS, INDEX_BACKFILL. DSN keys: hot_max_rows, hot_max_bytes.
• RELEASE SAVEPOINT parses, savepoints stack, and savepoint statements run on a Transaction and through stoolap_tx_exec (#99). Non-reserved keywords are accepted as names in every statement, and after AS as a table alias (#103).

Behaviour changes

• STDDEV and VARIANCE are the sample forms, as documented (STDDEV_SAMP, VAR_SAMP), matching PostgreSQL and DuckDB. One non-NULL input now gives NULL, where the population form gives 0. Use STDDEV_POP or VAR_POP for the population forms (#102).
• DEFAULT is a reserved word, as in SQLite and PostgreSQL. A column named default must be quoted (#103).
• An unterminated block comment is a parse error (#230).
• A file database that cannot start its persistence fails to open (#226).
• On a file database a table seals early once it holds more than 262,144 hot rows (hot_max_rows), instead of waiting for the checkpoint interval (#111).
• After a checkpoint under writes, the WAL directory holds two files until the next checkpoint covers the older one (#193).
• A statement can return SchemaChanged. On a file database, a statement that overlaps a column change (ADD, DROP, RENAME or MODIFY COLUMN) while it captures the schema gets it and can run again once the change completes (#200). A transaction that wrote rows to a table before another connection's ADD or DROP COLUMN on it gets it at COMMIT: its writes laid out for the old columns are rejected, and the column change stays (#218). Drivers report it as a database error and do not retry.
• Rust API:
• Error::SchemaChanged { table } is a new variant; an exhaustive match on Error needs an arm for it (#200).
• The Table trait's row_count, fast_row_count, sum_column, avg_column, min_column, max_column, get_partition_values and get_partition_count return Result, so storage errors reach the caller (#130 to [#138]). The trait gains methods with default implementations.
• StreamingResult, AggregationScanner, VisibleRowInfo, RowIndex, the version store's row index and sorted-limit methods, and Table::collect_rows_sorted_with_limit are removed (#150).

Upgrading

• The volume format stays V4. Checked with v0.4.1: a v0.4.1 database with sealed rows, a secondary and an HNSW index, an added column and an unflushed WAL tail opens in v0.4.2 with the tail replayed; a v0.4.2 database with those shapes and side files opens and is written in v0.4.1, which ignores the side files, and reopens in v0.4.2.
• Newer features are not promised to survive a downgrade: v0.4.1 has no clustering key in its schema and does not know the ALTER TABLE ... CLUSTER BY WAL record. v0.4.0 was not tested.
• Volumes written before an index existed are covered by budgeted background passes after open; PRAGMA INDEX_BACKFILL runs a pass at once and reports what is left.
• Dependencies: the lockfile takes the anyhow, rand, memmap2 and rustls advisories, and lru moves to 0.18 (#221). The repository has a security policy and a daily cargo audit workflow (#151).
• Memory: the per-thread cache of committed transactions is now 1 MiB, up from 512 KiB, built on the heap by the first thread that uses it (#236).