EvaluatedApplications.HoloDb 10.4.0

HoloDb

An embedded SQL database for .NET. Tables are typed columns, held in memory or on disk, and the engine builds the structures that make repeated queries fast from the queries it sees: summaries, sorted and keyed indexes, a tree of exact sums, per-block records of what earlier reads found, and links between blocks. You write SQL. The engine chooses and maintains the access paths, so there is no index design to do, and every answer is exact.

Tables live in typed columns. Everything the engine builds beside them is derived from those columns, so losing a derived structure costs time, never data. Integer sums are held in 128 bits and a statement that would overflow 64 bits throws instead of wrapping. The package adds ACID transactions with a write-ahead log, a NEAREST operator that ranks rows by similarity of their holographic encoding, ClickBench-compatible analytic SQL, and an optional exact aggregate plane built on Phasor's Pleroma stores.

This file documents the public surface of the package, with a working example for each group.

Contents

• Quick start
• What it does
• Storage format and upgrading
• Core concepts
• Opening a database
• SQL reference
• History: AS OF and sealed sums
• Loading data and change batches
• Summary tables
• Durable, larger-than-memory tables
• What the engine builds for itself
• The aggregate plane (Pleroma)
• The Pleroma page store
• Measured
• Costs and limits
• Dependency injection
• EvalApp pipeline integration
• Usage metering
• Parallel fan-out (HoloPar)
• Engine building blocks
• Exceptions
• File layout
• License

Quick start

dotnet add package EvaluatedApplications.HoloDb

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, age INT, name TEXT)");
db.Execute("INSERT INTO users VALUES (1, 30, 'Ada')");

var result = await db.ExecuteAsync("SELECT name FROM users WHERE age >= 18");
foreach (var row in result.Rows) Console.WriteLine(row["name"]);
// Ada

With no WalPath the database is in memory. Set WalPath to make it durable (see Opening a database).

What it does

• SQL over typed columns. CREATE, ALTER ... ADD COLUMN, INSERT (including ON CONFLICT ... DO UPDATE), UPDATE, DELETE, SELECT with joins, predicates, grouping, ordering, limits, transactions, and EXPLAIN.
• Exact aggregates. COUNT, SUM, AVG, MIN, MAX and COUNT(DISTINCT ...). Whole-table totals and single-column groups read maintained totals instead of scanning.
• Self-tuning access paths. Summaries, sorted and keyed indexes, a holographic tree of exact sums, per-block records and learned block-to-block routes are built when the workload has paid for them, and dropped when it stops using them or when writes make them stale. No index is declared, and none has to be.
• Placement that follows use. Rows that the same filters read are written together, by a regroup that runs as upkeep, and an UPDATE or INSERT moves a row to a block the repeated filters already read.
• Deletes that cost what they delete. A DELETE marks rows and moves nothing. Row ids stay stable, and consolidation later fills the holes and gives the freed pages back, so the data file shrinks.
• Durable transactions. A write-ahead log of signed deltas in a fixed ring, group commit, checksums on every log block and every data page, and crash recovery that replays the log.
• Larger than memory. Paged tables read their pages through a buffer pool. A statement larger than the pool spills to a scratch file, and a rollback or a power cut leaves the table as it was.
• History. Every committed change of a paged table is kept as a signed record. SELECT ... AS OF n reads the table as it was after commit n, and Seal(table, asOf: n) returns its seal then. What cannot be answered exactly is refused.
• ClickBench-compatible analytic SQL. Narrow column types, dictionary-coded text, CASE, CAST, EXTRACT, DATE_TRUNC, aliases, positional GROUP BY and ORDER BY, and COUNT(DISTINCT) on large cardinalities.
• Similarity search. NEAREST (col = value, ...) ranks rows by graded closeness, with a score column.
• Change feeds. ApplyChanges applies upserts and deletes atomically, one write lock and one log commit per batch, and KeyDigest compares two key sets in modulus numbers.
• Summary tables. A table that keeps no rows, only an exact aggregate plane, for dashboards over feeds that are too large to keep.

Storage format and upgrading

Paged tables. The write-ahead log of a paged table, wal.log, is a fixed-size ring of checksummed blocks holding signed deltas. It does not grow with history. Its tail is reclaimed as its deltas are written into data.pages, and a writer that finds it full writes some of them first. The table record is table.meta9, which carries the history log's root, sequence and floor. A table with a NULL column is recorded as table.meta10.

Older tables are refused. A paged table written before the history log (format 8 or older) is refused when it is opened. The message names the directory and says to create the table again. There is no in-place conversion. An older binary refuses the newer table record by name.

Commits. Commits that wait for the same fsync are written as one batch. A statement that has returned is on disk.

Derived files. Sorted indexes, keyed summaries, summary sidecars and learned records are trusted at open only when they match the table. A stale or damaged file is discarded and rebuilt from the columns when it is needed. No step is required.

Behaviour you will notice:

• Row order after a DELETE, and after any write that moves a row, is unspecified. Add ORDER BY where order matters.
• Deletes take time in proportion to the rows they delete, not to the table.
• Upkeep runs by default: a paged table consolidates its deleted rows after a statement once the scans that read them have paid for it, and the planner builds and drops structures on its own. Database.AutoConsolidate = false and SET summaries = declared (or off) turn these off.
• HoloDbOptions.CheckpointThresholdBytes governs only the text log of a database that is not in paged mode.

COUNT(*) and AVG count the rows that exist. Deleted places are not rows.

Core concepts

• Columns are the source of truth. A table is typed columns. The planner's structures, the aggregate plane and the similarity encodings are derived from them, and each is trusted only when it matches the table's change counter and the exact totals.
• Exact answers. INT sums are exact 128-bit running totals, and a statement throws if the true total leaves 64 bits. Counts, sums and extremes served from a structure equal the scan's answer.
• Two storage shapes, one SQL surface. In-memory tables (CREATE TABLE on a database without a paged default) and paged tables (CreatePaged/OpenPaged, or whole-database paged mode) take the same Execute calls. Where a capability differs, the relevant section says so.
• Locking. One reader-writer lock per database. SELECT takes it shared. Every mutation, DDL statement and transaction takes it exclusive. Whole-table counts and sums and single-column group-bys are also served from a snapshot republished after each write, without the lock. A structure built in the background is checked under the shared lock; the exclusive lock covers only the O(1) swap that publishes it.
• Transactions. BEGIN copies nothing. Each change is journaled as it happens and ROLLBACK replays the journal. A transaction is a pending delta: a reader sees the base plus its own changes, and commit adds the changes to the log. A multi-statement transaction is a series of Execute calls on the same database. On a paged table, a statement that fails inside BEGIN undoes only itself: the statements before it stay, and COMMIT keeps them. Outside a transaction a failed statement changes nothing.
• Constraints on write. Types, NOT NULL (the default for every column not declared NULL) and primary-key uniqueness are checked before a row is written.

Opening a database

public sealed class HoloDbOptions
{
    public string? WalPath { get; set; }                  // null = in memory
    public long CheckpointThresholdBytes { get; set; }    // text log (not paged mode): fold into a snapshot past this size; 0 = never
    public bool Paged { get; set; }                       // whole-database paged mode for a new database (see below)
}

• HoloDbService is the entry point: Execute(sql[, args]), ExecuteAsync(sql[, args], ct), BulkLoad, BulkLoadColumns, ApplyChanges and ApplyChangesAsync (single table and several tables), ApplyPlaneChanges and its async form, KeyDigest, PlanePosition, PlanePositionBytes, PlaneTotals, ExportPlane, ImportPlane, CreatePlaneOnly, CreatePaged, OpenPaged, PagedBulkLoad, ClosePaged, Consolidate(table), Regroup(table), Checkpoint(), Close(), and .Database, the engine itself.
• Database is the engine. Use it directly for the members not wrapped by the service: Database.Open(walPath = null, checkpointThresholdBytes, paged = false) (a static factory), Execute, Bind, BulkLoad, Checkpoint, Close, WalPath, IsPagedDefault, TableColumns(table), Consolidate(table), AutoConsolidate, PlaceOnWrite, Regroup(table), Summaries and Indexes (the planner modes), StructureBudgetBytes, SetStructureBudget(bytes), RouteTrace, the aggregate plane members (EnablePleroma, DisablePleroma, PleromaMode, PleromaStats, PleromaVerify, PleromaSumExact), and the static per-thread usage counters LastRowsScanned, LastTableRows, LastRowBytes and LastLogicalBytes.

using HoloDb;

var path = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName() + ".wal");
var db = new HoloDbService(new HoloDbOptions { WalPath = path });
db.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
db.Execute("INSERT INTO t VALUES (1, 100)");
db.Checkpoint();
db.Close();

var reopened = new HoloDbService(new HoloDbOptions { WalPath = path });
var r = reopened.Execute("SELECT v FROM t WHERE id = 1");
Console.WriteLine(r.GetLong(0, 0));
// 100
reopened.Close();

Close() releases the file handles of every open paged table. Call it before you open the same WalPath again in the same process, and on shutdown. Close() also cancels the background builds and waits for them to stop. A build that was cancelled publishes nothing.

Diagnostics. Database.RouteTrace is a thread-static list. Set it to a new List<string> and the engine records the access path and the route each statement on that thread takes. EXPLAIN reports the same decision without running the statement.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE s (id INT PRIMARY KEY, n INT)");
db.Execute("INSERT INTO s VALUES (1, 1), (2, 2)");

Database.RouteTrace = new List<string>();
db.Execute("SELECT count(*) FROM s WHERE n > 1");
foreach (var step in Database.RouteTrace) Console.WriteLine(step);
Database.RouteTrace = null;

foreach (var r in db.Execute("EXPLAIN SELECT count(*) FROM s WHERE n > 1").Rows)
    Console.WriteLine($"{r["kind"]}: {r["route"]}");

SQL reference

Types and tables

Statement	Purpose
CREATE TABLE [IF NOT EXISTS] t (col TYPE [NULL \| NOT NULL] [PRIMARY KEY], ...)	Defines a table. An ordinary table has one PRIMARY KEY, of type INT or TEXT. A GUID is a TEXT value.
CREATE TABLE t (col TYPE, ...) without a primary key, or with analytic types	Defines an analytic table (see Analytic tables). Paged databases only.
ALTER TABLE t ADD [COLUMN] col TYPE [NULL] [DEFAULT <literal> \| NULL]	Adds one column. There is no DROP, rename or type change. A NOT NULL column needs a literal DEFAULT. The default fills the rows that existed when the column was added.
CREATE SUMMARY TABLE [IF NOT EXISTS] t (col TYPE DIMENSION \| MEASURE, ...) [WITH (max_dim_bits = n, max_joint_bits = n)]	Defines a table that keeps no rows (see Summary tables).

Type names: INT (also INTEGER), TEXT (also VARCHAR, STRING), REAL (also FLOAT, DOUBLE, DECIMAL, NUMERIC). Analytic tables also take SMALLINT, INTEGER, BIGINT, DATE, TIMESTAMP, CHAR(n) and VARCHAR(n), each stored at its declared width.

Values are stored exactly or refused by name. An INT column takes a whole number in the 64-bit range (3.0 is stored as 3; 2.5, a number past 64 bits or a text that is not a number throws). A REAL column takes any finite number; NaN and Infinity throw, from SQL, parameters and bulk loads alike. Numeric literals take a point or an exponent (1.5, 1e20, 2.5E-7). Integer arithmetic in a SELECT that would pass the 64-bit range throws for the rows the query keeps.

Indexes, summaries and planner settings

Statement	Purpose
CREATE INDEX [name] ON t (col)	A sorted index on one INT or TEXT column (a TEXT column gets an equality index). CREATE INDEX [name] ON t (textCol, intCol) is a composite index for WHERE textCol = 'v' ORDER BY intCol LIMIT k. USING SORTED is the same as no USING.
CREATE INDEX [name] ON t (dims ...) [INCLUDE (measures ...)] USING SUMMARY [WITH (max_dim_bits = n, max_joint_bits = n)]	Builds the aggregate plane from SQL. Dimensions are INT or TEXT columns, measures are INT columns. A dimension past its cap does not fail the statement: the row in SHOW INDEXES lists it as over the cap, and the queries that need it go to the engine.
CREATE INDEX [name] ON t (intKey [, splitCol]) INCLUDE (measures) USING KEYED	A keyed summary on a paged table: the exact count and sums for each value of an INT key, optionally split by a second column with few distinct values.
CREATE INDEX ... USING NEAREST	Refused. Similarity search needs no index: see NEAREST.
DROP INDEX name [ON t]	Removes an index or summary.
SHOW INDEXES [ON t]	One row per index: table, name, kind, columns, include, rows, bytes, served, declined, over_cap.
SHOW SUMMARIES [ON t]	One row per summary and index the planner knows about, with its origin (declared or automatic), state, size, counts served and declined, and the reason for its state.
SET summaries = auto \| declared \| off	auto (the default) lets the engine build and drop aggregate summaries. declared keeps only the ones you declared. off uses none.
SET indexes = auto \| declared \| off	The same for sorted and text indexes, and for the paged sorted and keyed structures.
EXPLAIN [PLAN [FOR]] SELECT ...	How the statement would be answered, without running it. One row per step: step, kind, route, detail, est_rows.

EXPLAIN and SHOW SUMMARIES say what the planner is waiting for: how much scan time the shape has paid and what the build would cost, and when a structure was built and why.

Writes

Statement	Purpose
INSERT INTO t [(col, ...)] VALUES (v, ...)[, (v, ...)]	Inserts one or more rows. An omitted column is NULL when declared NULL, otherwise its type's zero (0, 0.0 or '').
INSERT INTO t [(col, ...)] VALUES (...) ON CONFLICT (pk) DO UPDATE SET col = EXCLUDED.col[, ...]	Upsert. An existing key changes only the SET columns. EXCLUDED.col must name the same column. In-memory tables only.
UPDATE t SET col = literal[, ...] [WHERE ...]	Updates matching rows. The right-hand side is a literal (NULL for a column declared NULL). The primary key can not be updated.
DELETE FROM t [WHERE ...]	Deletes matching rows.

A plain INSERT of an existing primary key throws HoloException. UPDATE validates every SET value before it changes a row, so a bad value leaves the table as it was.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO p VALUES (1, 'Ada')");
try { db.Execute("INSERT INTO p VALUES (1, 'Dup')"); }
catch (HoloException ex) { Console.WriteLine(ex.Message); }
// duplicate primary key 1
try { db.Execute("UPDATE p SET id = 2 WHERE id = 1"); }
catch (HoloException ex) { Console.WriteLine(ex.Message); }
// cannot UPDATE the primary key

Parameterized queries

Every Execute has an overload that takes ? placeholders followed by arguments. The arguments are bound as correctly quoted SQL literals before the statement is parsed. A string argument has its quotes doubled. A ? inside a string literal is left alone. Supported argument types are string, bool, any integer type, and any floating-point type; a floating-point argument binds with all its digits and reads back exactly. A null or DBNull argument is the SQL literal NULL. An argument-count mismatch, an unsupported type, a ulong past the 64-bit range, NaN or Infinity throws. Database.Bind(sql, args) returns the bound text without running it.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO p VALUES (?, ?)", 1L, "O'Brien");
var r = db.Execute("SELECT name FROM p WHERE id = ?", 1L);
Console.WriteLine(r.Rows[0]["name"]);
// O'Brien

string text = Database.Bind("SELECT * FROM t WHERE id = ? AND name = ?", 1L, "Widget");
Console.WriteLine(text);
// SELECT * FROM t WHERE id = 1 AND name = 'Widget'

NULL

A column holds SQL NULL only when it is declared NULL: CREATE TABLE orders (id INT PRIMARY KEY, region TEXT NULL, amount INT NULL, qty INT NOT NULL). A primary key is never NULL.

On a paged table a column can be declared NULL, and its NULL cells are stored with the table. In this build a statement that reads such a column (a SELECT, a WHERE on it, or an index on it) is refused by name, so read a paged table's nullable columns in memory until that support lands.

• Three-valued logic. WHERE and HAVING keep a row only when the predicate is true. A comparison, LIKE, IN or BETWEEN on a NULL is unknown. IS NULL and IS NOT NULL are always true or false.
• Aggregates skip NULL. COUNT(*) counts rows. COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN and MAX use the non-NULL values, and are NULL when there are none.
• Grouping and ordering. GROUP BY and DISTINCT make one group of all the NULLs. ORDER BY puts NULL first when ascending and last when descending. An INNER JOIN never matches a NULL key.
• Results. Read a NULL cell with Rows or GetValue, where it is DBNull.Value, or test QueryResult.IsNull. GetLong, GetDouble and GetText throw a HoloException on a NULL.
• Where a NULL is refused. A comparison written with =, !=, <, >, <=, >=, IN, BETWEEN, LIKE, NEAREST or a join ON against NULL throws, with a message that says to write IS NULL.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, region TEXT NULL, amount INT NULL, qty INT NOT NULL)");
db.Execute("INSERT INTO orders VALUES (1, 'north', 10, 1), (2, NULL, 20, 1), (3, 'north', NULL, 2)");
var r = db.Execute("SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM orders").Rows[0];
Console.WriteLine($"{r["count(*)"]} rows, {r["count(amount)"]} amounts, sum {r["sum(amount)"]}, avg {r["avg(amount)"]}");
// 3 rows, 2 amounts, sum 30, avg 15
Console.WriteLine(db.Execute("SELECT COUNT(*) FROM orders WHERE region IS NULL").Rows[0]["count(*)"]);
// 1
Console.WriteLine(db.Execute("SELECT COUNT(*) FROM orders WHERE region != 'north'").Rows[0]["count(*)"]);
// 0
foreach (var g in db.Execute("SELECT region, COUNT(*) FROM orders GROUP BY region ORDER BY region").Rows)
    Console.WriteLine($"{(g["region"] is DBNull ? "(null)" : g["region"])}: {g["count(*)"]}");
// (null): 1
// north: 2

SELECT

SELECT [DISTINCT] item [, item ...] FROM t
    [INNER JOIN t2 ON a.col = b.col [...]]
    [NEAREST (col = value [, ...])]
    [WHERE ...] [GROUP BY col [, ...]] [HAVING ...]
    [ORDER BY col | position | alias | expression [ASC|DESC] [, ...]]
    [LIMIT n] [OFFSET n]

An item is a column, *, an aggregate (COUNT(*), COUNT(col), COUNT(DISTINCT col), SUM, AVG, MIN, MAX), or on an analytic-capable SELECT an expression with an AS alias. Aggregate result columns are named agg(col) in lower case, for example count(*) and sum(amount), and are read by that name or by position. AVG over an INT column returns a double.

• Row order. Without ORDER BY the order of the rows, and so which rows a LIMIT keeps, is unspecified. The count is min(n, matching rows), every row satisfies the WHERE, and no row repeats. n is a non-negative integer; LIMIT 0 returns no rows (an aggregate without GROUP BY refuses it).
• Empty selections. SUM, AVG, MIN and MAX of no rows are NULL. COUNT is 0. An aggregate without GROUP BY always returns one row.
• Refused, not reinterpreted. A statement that would otherwise drop one of its clauses throws HoloException: DISTINCT together with an aggregate (use GROUP BY), and NEAREST combined with a join, GROUP BY, HAVING, ORDER BY, DISTINCT or an aggregate.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, amount INT)");
db.Execute("INSERT INTO sales VALUES (1,'EU',100),(2,'EU',50),(3,'US',10)");

var agg = db.Execute("SELECT region, sum(amount) FROM sales GROUP BY region HAVING sum(amount) > 20 ORDER BY region LIMIT 10");
foreach (var row in agg.Rows) Console.WriteLine($"{row["region"]}={row["sum(amount)"]}");
// EU=150

var distinctRegions = db.Execute("SELECT DISTINCT region FROM sales ORDER BY region");
foreach (var row in distinctRegions.Rows) Console.WriteLine(row["region"]);
// EU
// US

Joins

INNER JOIN t2 ON a = b (JOIN is the same). An in-memory database accepts several joins in one statement. A paged table accepts one. Qualify a column with table.column when both sides share its name.

Joins with LEFT, RIGHT, FULL, CROSS or NATURAL, USING, comma joins, table aliases in a FROM or JOIN, UNION, INTERSECT, EXCEPT, ROLLBACK TO, and a second statement after ; are refused with a HoloException that names what is not supported. A clause the dialect does not have is never silently dropped.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT)");
db.Execute("INSERT INTO users VALUES (1, 'Ada')");
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, userId INT)");
db.Execute("INSERT INTO orders VALUES (1, 1)");
var j = db.Execute("SELECT users.name FROM orders INNER JOIN users ON orders.userId = users.id");
foreach (var row in j.Rows) Console.WriteLine(row["users.name"]);
// Ada

Predicates

AND, OR, NOT and parentheses. Comparisons =, != (or <>), <, >, <=, >=. LIKE (% for any run, _ for one character, case-insensitive). IN (v, ...) and IN (SELECT ...) (a single-column subquery). BETWEEN lo AND hi. IS [NOT] NULL on a declared NULL column. Each of LIKE, IN and BETWEEN takes an optional leading NOT.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT, price INT)");
db.Execute("INSERT INTO p VALUES (1,'Widget',25),(2,'Gadget',75),(3,'Widgetpro',150)");

Console.WriteLine(db.Execute("SELECT name FROM p WHERE name LIKE 'Widget%'").Rows.Count);            // 2
Console.WriteLine(db.Execute("SELECT name FROM p WHERE id IN (1,2)").Rows.Count);                    // 2
Console.WriteLine(db.Execute("SELECT name FROM p WHERE price BETWEEN 50 AND 100").Rows[0]["name"]);  // Gadget
Console.WriteLine(db.Execute("SELECT name FROM p WHERE id IN (SELECT id FROM p WHERE price > 50)").Rows.Count); // 2

Similarity search: NEAREST

SELECT ... FROM t NEAREST (col = value [, col = value ...]) [WHERE ...] [LIMIT k]

NEAREST ranks rows by the cosine similarity of their holographic encoding to a partial key: any subset of columns, of any type. The encoding is derived from the columns, so a row is ranked by the values it holds now: an UPDATE changes its rank. A WHERE filters the rows before they are ranked.

Similarity is graded. A number or date close to the key ranks ahead of a far one, and a text value that is nearly the same string ranks ahead of an unrelated one, by character trigrams. Add score to the projection to read the ranking value (1.0 for an exact match). score is a pseudo-column: SELECT * does not return it, and it can not appear in a WHERE. Above a size threshold, the ranking uses an approximate candidate search (locality-sensitive hashing) instead of comparing every row, so its order can differ from an exact scan.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE products (id INT PRIMARY KEY, category TEXT, price INT)");
db.Execute("INSERT INTO products VALUES (1,'electronics',100),(2,'electronics',120),(3,'furniture',300)");

var near = db.Execute("SELECT id, category, price, score FROM products NEAREST (category = 'electronics', price = 110) LIMIT 2");
foreach (var row in near.Rows) Console.WriteLine($"id={row["id"]} category={row["category"]} price={row["price"]} score={row["score"]:F3}");
// id=2 category=electronics price=120 score=0.989
// id=1 category=electronics price=100 score=0.989

Transactions

BEGIN, COMMIT and ROLLBACK. A rolled-back transaction leaves the same rows, though their order may differ. DDL inside a transaction is allowed. BulkLoad is not.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE p (id INT PRIMARY KEY, name TEXT)");

db.Execute("BEGIN");
db.Execute("INSERT INTO p VALUES (1, 'Temp')");
db.Execute("ROLLBACK");
Console.WriteLine(db.Execute("SELECT count(*) FROM p").Rows[0]["count(*)"]);
// 0

db.Execute("BEGIN");
db.Execute("INSERT INTO p VALUES (1, 'Kept')");
db.Execute("COMMIT");
Console.WriteLine(db.Execute("SELECT count(*) FROM p").Rows[0]["count(*)"]);
// 1

Analytic tables and the extended SELECT

An analytic table is for wide, append-mostly data. Declare SMALLINT, INTEGER, BIGINT, DATE, TIMESTAMP, CHAR(n) or VARCHAR(n) columns, or leave out the primary key, and the table is analytic. An analytic table exists only in a paged database, and its columns can not be NULL. Each column is stored at its declared width. Text is dictionary-coded. Load it column by column with BulkLoadColumns (one transaction per call).

A single-table SELECT that the older parser refuses runs on the extended executor, which also runs on in-memory tables. It accepts AS aliases, expressions, CASE, CAST, EXTRACT, DATE_TRUNC, REGEXP_REPLACE, the string and math functions, OFFSET, HAVING without GROUP BY, and GROUP BY or ORDER BY by alias, position or expression. DATE and TIMESTAMP results come back as strings. Its aggregates run on morsels of rows across all cores, with dense per-worker arrays for small key ranges, partitioned hash tables for large ones, and exact COUNT(DISTINCT).

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
Directory.CreateDirectory(dir);
var db = new HoloDbService(new HoloDbOptions { WalPath = Path.Combine(dir, "db.wal"), Paged = true });
db.Execute("CREATE TABLE hits (watch SMALLINT NOT NULL, day DATE NOT NULL, url VARCHAR(200) NOT NULL, n BIGINT NOT NULL)");
db.BulkLoadColumns("hits", new[] { "watch", "day", "url", "n" },
    new object[] { new short[] { 1, 2, 3 }, new int[] { 19000, 19000, 19001 }, new[] { "a", "b", "a" }, new long[] { 10, 20, 30 } }, 3);
var r = db.Execute("SELECT day, COUNT(*) AS visits, SUM(n) AS total FROM hits GROUP BY day ORDER BY day");
for (int i = 0; i < r.RowCount; i++) Console.WriteLine($"{r.GetValue(i, 0)} {r.GetValue(i, 1)} {r.GetValue(i, 2)}");
// 2022-01-08 2 30
// 2022-01-09 1 30
db.Close();

History: AS OF and sealed sums

Paged tables keep each committed change as a signed record, so a past state is the present state with the later changes taken back out. No copy of the table is kept.

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), "holodb-asof-demo");
var db = Database.Open(null);
db.CreatePaged("CREATE TABLE orders (id INT PRIMARY KEY, amount INT)", dir);
db.Execute("INSERT INTO orders (id, amount) VALUES (1, 10), (2, 20)");
long before = db.CommitSequence;                                  // the sequence after the insert
db.Execute("UPDATE orders SET amount = 99 WHERE id = 2");

var now = db.Execute("SELECT COUNT(*), SUM(amount) FROM orders");
var past = db.Execute($"SELECT COUNT(*), SUM(amount) FROM orders AS OF {before}");
Console.WriteLine($"now: {now.GetValue(0, 1)}, as of {before}: {past.GetValue(0, 1)}");

var sealPast = db.Seal("orders", asOf: before);                   // the table's seal as it was after commit `before`
Console.WriteLine($"seal as of {before}: {sealPast.Rows} rows");
db.Close();

• SELECT ... FROM t AS OF n reads the table as it was after commit n. AS OF LATEST is the present state. SHOW SEQUENCE returns the current sequence and the floor; Database.CommitSequence and Database.HistoryFloor(table) return the same numbers.
• Exact at every n at or above the floor: COUNT, SUM, MIN, MAX, GROUP BY, DISTINCT and row reads. COUNT and SUM without a WHERE, GROUP BY on one INT key and MIN/MAX with a sorted index on the column start from the present answer and apply the overlay's signed correction. Other shapes scan the table and apply the same correction to the rows that changed. EXPLAIN names the route and says why a shape scans.
• Seal(table, asOf: n) returns the table's seal as it was after commit n. For an insert-only history it reads no data page.
• Refused by name, never answered approximately: JOIN, NEAREST, an in-memory table, a plane-only table, n above the current sequence, n below the floor, n strictly inside a retained segment, and a read inside a transaction that has written the table.
• Retention is bounded by the structure budget: older history collapses into segments. An ALTER TABLE ... ADD COLUMN is a barrier: it moves the floor, and an AS OF read below the floor is refused by name.
• Seal(table) is maintained as rows change, for paged and in-memory tables, so reading it is O(1). Seal(table, verify: true) re-derives it from the rows and repairs any drift. A key range scans the rows.
• [GATE: the per-commit cost of keeping history, and the page cost of an AS OF read on large tables.]

Loading data and change batches

BulkLoad and BulkLoadColumns

int BulkLoad(string table, IReadOnlyList<string> cols, IReadOnlyList<Array> data, int count[, IReadOnlyList<bool[]?>? nulls])
int BulkLoadColumns(string table, IReadOnlyList<string> cols, IReadOnlyList<object> data, int count)   // analytic and paged
long PagedBulkLoad(string table, IEnumerable<object?[]> rows, int commitEvery)                          // paged

BulkLoad appends typed arrays (long[] for INT, double[] for REAL, string[] for TEXT) without the SQL parser. It validates every key first, so a duplicate primary key leaves the table unchanged. A column left out of cols takes its type's zero, or NULL if declared NULL. The nulls argument gives one mask per column, true for NULL. BulkLoadColumns takes long[], int[], short[], double[], string[] or Utf8Column for analytic tables.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE users (id INT PRIMARY KEY, age INT, name TEXT)");
var cols = new[] { "id", "age", "name" };
var data = new Array[] { new long[] { 10, 11 }, new long[] { 40, 22 }, new[] { "Bo", "Cy" } };
int n = db.BulkLoad("users", cols, data, 2);
Console.WriteLine(n);
// 2

Change batches: ApplyChanges

A replica or a feed sends an ordered stream of upserts and deletes. ApplyChanges(table, changes) applies a batch atomically: one write lock, one log commit, and a reader sees the table before the batch or after it, never part of it. RowChange.Upsert(row) carries a full row, with every column, and a null for a NULL column. RowChange.Delete(key) deletes by primary key, and a missing key is a counted no-op. Changes count in order, the last change of a key wins, and only the net effect is applied. ChangeResult has Inserted, Updated, Deleted, MissingDeletes and Applied. A failure leaves the table as it was.

The SQL form INSERT ... ON CONFLICT (pk) DO UPDATE SET ... changes only the listed columns of an existing row. It is the way to keep the other columns of a row.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE orders (id INT PRIMARY KEY, region TEXT, total INT)");

var r = db.ApplyChanges("orders", new[]
{
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["region"] = "north", ["total"] = 40L }),
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["region"] = "south", ["total"] = 15L }),
    RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["region"] = "north", ["total"] = 45L }),
    RowChange.Delete(2L),
    RowChange.Delete(99L),
});
Console.WriteLine($"{r.Inserted} inserted, {r.Updated} updated, {r.Deleted} deleted, {r.MissingDeletes} missing");
// 2 inserted, 1 updated, 1 deleted, 1 missing

db.Execute("INSERT INTO orders VALUES (1, 'west', 50), (3, 'east', 7) ON CONFLICT (id) DO UPDATE SET total = EXCLUDED.total");
foreach (var row in db.Execute("SELECT id, region, total FROM orders ORDER BY id").Rows)
    Console.WriteLine($"{row["id"]} {row["region"]} {row["total"]}");
// 1 north 50
// 3 east 7

Text keeps every character it was given, including line breaks and backslashes. A batch is one log commit group, so one call with several changes is one fsync.

Several tables in one step

ApplyChanges(IReadOnlyList<TableChanges>) applies the changes of several in-memory tables as one atomic step, with one write lock and one log commit. A reader sees every table before the batch or every table after it. Every table and value is checked before the first change, and a failure leaves all of them unchanged. You get one ChangeResult per table, in list order. It works inside BEGIN and COMMIT.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
db.Execute("CREATE TABLE ledger (id INT PRIMARY KEY, account INT, amount INT)");
db.Execute("INSERT INTO accounts VALUES (1, 100), (2, 100)");

var results = db.ApplyChanges(new[]
{
    new TableChanges("accounts", new[]
    {
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["balance"] = 70L }),
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["balance"] = 130L }),
    }),
    new TableChanges("ledger", new[]
    {
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 1L, ["account"] = 1L, ["amount"] = -30L }),
        RowChange.Upsert(new Dictionary<string, object?> { ["id"] = 2L, ["account"] = 2L, ["amount"] = 30L }),
    }),
});
Console.WriteLine($"{results[0].Updated} updated, {results[1].Inserted} inserted");
// 2 updated, 2 inserted

var both = db.Execute("SELECT SUM(accounts.balance), SUM(ledger.amount) FROM accounts JOIN ledger ON accounts.id = ledger.account");
Console.WriteLine($"{both.GetValue(0, 0)} {both.GetValue(0, 1)}");
// 200 0

Across two statements a batch can still be seen half applied. Only one statement, such as a join, reads one consistent state of several tables.

Comparing key sets: KeyDigest

KeyDigest(table, modulus) returns one KeyBucket (Count and an exact Int128 Sum) per bucket of an INT primary key, in one pass. Bucket b holds the keys whose value modulo modulus is b. Two systems that compute the same buckets can compare modulus numbers instead of every key, and list the keys of the buckets that differ.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
db.Execute("INSERT INTO accounts VALUES (1, 100), (2, 100), (6, 5)");

KeyBucket[] d = db.KeyDigest("accounts", 4);
Console.WriteLine($"bucket 1: {d[1].Count} key, sum {d[1].Sum}; bucket 2: {d[2].Count} keys, sum {d[2].Sum}");
// bucket 1: 1 key, sum 1; bucket 2: 2 keys, sum 8

Summary tables

A summary table keeps no rows. It stores only the aggregate plane, the value dictionaries of its dimensions and the position each feed has reached, so its size depends on the distinct values of its dimensions, not on how many rows went through it.

CREATE SUMMARY TABLE sales (region TEXT DIMENSION, channel TEXT DIMENSION, units INT MEASURE, revenue INT MEASURE)

A dimension is an INT or TEXT column you filter, group or count distinct on. A measure is an INT column you SUM or AVG. The columns are fixed when the table is created. Database.CreatePlaneOnly(...) is the same thing from code.

• Writing. ApplyPlaneChanges(table, source, position, changes) takes PlaneChange.Add(row), PlaneChange.Remove(row) and PlaneChange.Replace(oldRow, newRow), as one atomic batch. A row is an array in the declared column order, or a dictionary with every column. A Remove carries the row's values, because the table has no rows to look them up in.
• Exactly once. Each batch carries the position its source has reached. A batch at or below the position already applied for that source is skipped, and Skipped is true. The position is a number, or opaque bytes compared as unsigned numbers compared lexicographically (for example a 10-byte, big-endian log sequence number). It is saved in the same log commit as the counts. PlanePosition and PlanePositionBytes read it back.
• No negative counts. A Remove of a row that was never added refuses the whole batch, and nothing changes.
• Reading. COUNT, SUM and AVG of the measures. MIN, MAX and COUNT(DISTINCT) of a dimension. WHERE over dimensions. GROUP BY and HAVING over COUNT and SUM. ORDER BY and LIMIT. Anything that needs a row (SELECT *, a filter on a measure, a join, an UPDATE) is refused with the reason in the message.
• Totals and export. PlaneTotals(table) returns the row count and the exact sum of each measure. ExportPlane(table) returns the whole state as one blob with a checksum. ImportPlane(table, blob, replace) checks the blob before it changes anything.

The dimension caps (a maximum number of distinct values per dimension, and a maximum number of flag bits across all dimensions) are named in the refusal when a batch would pass them.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE SUMMARY TABLE sales (region TEXT DIMENSION, channel TEXT DIMENSION, units INT MEASURE, revenue INT MEASURE)");

var batch = new[]
{
    PlaneChange.Add(new object?[] { "north", "web", 3L, 30L }),
    PlaneChange.Add(new object?[] { "south", "shop", 5L, 50L }),
    PlaneChange.Add(new object?[] { "south", "web", 1L, 10L }),
};
Console.WriteLine(db.ApplyPlaneChanges("sales", "orders-feed", 100, batch).Skipped);   // False
Console.WriteLine(db.ApplyPlaneChanges("sales", "orders-feed", 100, batch).Skipped);   // True
db.ApplyPlaneChanges("sales", "orders-feed", 101, new[]
{
    PlaneChange.Replace(new object?[] { "south", "shop", 5L, 50L }, new object?[] { "south", "shop", 6L, 60L }),
});
foreach (var row in db.Execute("SELECT region, SUM(units), SUM(revenue) FROM sales GROUP BY region ORDER BY region").Rows)
    Console.WriteLine($"{row["region"]} {row["sum(units)"]} {row["sum(revenue)"]}");
// north 3 30
// south 7 70

var t = db.PlaneTotals("sales");
Console.WriteLine($"{t.Rows} rows, {t.Sums["units"]} units, position {db.PlanePosition("sales", "orders-feed")}");
// 3 rows, 10 units, position 101

try { db.Execute("SELECT * FROM sales"); }
catch (HoloException e) { Console.WriteLine(e.Message); }

Durable, larger-than-memory tables

A paged table keeps its pages in a buffer pool, and its changes in a write-ahead log. It is durable, and it can be larger than memory: pages that are not in the pool are read from data.pages, through a read-only memory map. Paged tables take the SQL reference with these exceptions: one INNER JOIN per statement, ApplyChanges (for one table or several) and INSERT ... ON CONFLICT are in-memory only, an analytic table has no NULL columns, and a read of a paged NULL column is refused by name (see NULL).

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
db.CreatePaged("CREATE TABLE big (id INT PRIMARY KEY, v INT)", dir);
db.PagedBulkLoad("big", new object?[][] { new object?[] { 1L, 100L }, new object?[] { 2L, 200L } });
Console.WriteLine(db.Execute("SELECT sum(v) FROM big").Rows[0]["sum(v)"]);
// 300
db.ClosePaged("big");

var reopened = db.OpenPaged(dir);
Console.WriteLine(db.Execute($"SELECT count(*) FROM {reopened}").Rows[0]["count(*)"]);
// 2
db.ClosePaged(reopened);

• CreatePaged(createTableSql, dir, bufferFrames) creates a paged table in dir. bufferFrames is the pool size in pages.
• OpenPaged(dir) reopens it and recovers committed state from its log. It returns the table name.
• PagedBulkLoad(table, rows, commitEvery) streams rows and commits every commitEvery rows.
• ClosePaged(table) checkpoints and closes one table.

Integrity

Every page of data.pages has a CRC-32C in data.crc, written at each checkpoint. A page that fails its check throws a HoloException that names the page and the file, so a flipped bit or a bad sector is an error and never a different answer. A database that predates the checksums gets them the first time it is opened.

A statement may change more pages than the pool holds. The pool then writes the open transaction's least recently used pages to a scratch file, txn.spill, and deletes it when the transaction ends. A large UPDATE or DELETE completes, and a ROLLBACK or a power cut leaves the table as it was.

UPDATE and DELETE find their rows through the primary key, a sorted index or a group index when one proves the answer, and otherwise through the block scan. They adjust the stored totals by the difference.

Deletes and consolidation

A DELETE marks its rows and moves nothing. The stored totals, group indexes and learned block records are adjusted by the deleted values, so the cost follows the rows deleted, not the table. Every scan, index and join skips a deleted row. COUNT(*) and AVG count the rows that exist. A deleted key can be inserted again.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
db.CreatePaged("CREATE TABLE events (id INT PRIMARY KEY, g INT, v INT)", dir);
var rows = Enumerable.Range(1, 20000).Select(i => new object?[] { (long)i, (long)(i % 10), (long)i }).ToList();
db.PagedBulkLoad("events", rows);
db.Execute("DELETE FROM events WHERE g = 7");
Console.WriteLine(db.Execute("SELECT count(*) FROM events").Rows[0]["count(*)"]);
// 18000
long slots = db.Consolidate("events");
Console.WriteLine(slots > 0);
// True
Console.WriteLine(db.Execute("SELECT count(*) FROM events WHERE g = 7").Rows[0]["count(*)"]);
// 0
db.ClosePaged("events");

Deleted rows keep their place until the table is consolidated. The engine consolidates by itself, after a statement, once the scans that read the dead places have paid for the rewrite (Database.AutoConsolidate, on by default), or you call Consolidate(table), which returns the number of places removed. A consolidation moves rows into the holes and cuts the end off. A moved row keeps its id, and no sorted index or keyed summary is made stale by it. Rows come back in a different order afterwards unless you ORDER BY.

Pages that a consolidation, a regroup or an emptied table frees go to a free map, and new rows take the lowest free page first. Free pages at the end of data.pages are cut off at the same checkpoint, and their checksums with them. The free map is derived from the table's pages when the table opens, so a crash leaves nothing to repair.

Whole-database paged mode

HoloDbOptions.Paged = true (or Database.Open(walPath, checkpointBytes, paged: true)) puts a new database on the paged engine. A plain CREATE TABLE then creates a paged table, and no CreatePaged call is needed. The option is for a database with no existing files at WalPath. Opening over an existing text log with paged: true throws. Database.IsPagedDefault reports the mode.

using HoloDb;

var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
Directory.CreateDirectory(dir);
var walPath = Path.Combine(dir, "db.wal");
var pd = new HoloDbService(new HoloDbOptions { WalPath = walPath, Paged = true });
pd.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
pd.Execute("INSERT INTO t VALUES (1, 42)");
Console.WriteLine(pd.Database.IsPagedDefault);
// True
Console.WriteLine(pd.Execute("SELECT v FROM t WHERE id = 1").Rows[0]["v"]);
// 42
pd.Close();

What the engine builds for itself

Nothing here is declared. The engine builds a structure when the scans it would answer have paid for its build and for the writes that would keep it current, and it drops a structure when its maintenance costs more than the scans it saves, or when a write makes it stale. Every structure takes its bytes from one budget that scales with the data's own size. The cap is hard. A candidate that does not fit evicts the structures that save the fewest reads per byte. A declared structure that still does not fit is refused, with the amount it needs. Declared structures are never evicted. Answers are the same with any mix of structures, or none.

• Aggregate summaries. A table's small-domain columns, summarised so that filters, grouping, COUNT(DISTINCT) and equi-joins are answered from the summary. The planner picks the dimensions and measures from the statements it sees. A summary on a paged table is built in the background and saved beside the table.
• Sorted index on a paged table. An ordered list of the live rows' values, read in checksummed blocks. A range COUNT, MIN and MAX, and ORDER BY col DESC LIMIT k, are answered from it without reading the table's pages. SUM over a range is not served from it.
• Keyed summary. The exact count and sums for each value of a high-cardinality INT key, optionally split by a second column. It answers GROUP BY key with SUM and COUNT, ORDER BY ... LIMIT, and an equality filter on the split column, and a single join with a dimension table on its primary key. For each join shape the planner has paid for, it keeps pre-grouped totals, which appended rows and new dimension rows update.
• Holographic tree. A tree of exact counts and sums over key ranges, for a SUM over a key range or a GROUP BY on a key whose values are too spread for a keyed summary. It is built when a range sum or a group-by on that key has paid for it.
• Row indexes in memory. A sorted index on an INT column, or an equality index on a TEXT column, for a selective point or range predicate on a large in-memory table.
• Learned block records. For each WHERE clause the engine has seen, the number of matching live rows in each block. A block with no match is not read. Records are exact: an UPDATE or DELETE changes them by the rows it touches, and a move of rows takes the counts of the blocks it changed out of them. Each block also keeps the bounds of its columns, so a block outside a range is skipped.
• Learned routes. A link is the set of blocks that held a match for a conjunction of filters on an earlier read. A later query follows the links whose filters it repeats, and reads only the blocks they name. Every link is checked when it is followed: a block whose first row id or checksum does not match is a stale hop, and the statement reruns without routing. A query that has no link is an ordinary scan.
• Gravity. Rows that satisfy the same repeatedly-scanned predicates are written into the same blocks, by a regroup. Rows keep their ids. A regroup runs as upkeep, when the reads it would save are worth more than the rewrite.
• Placement on write. An UPDATE or INSERT that gives a row new values moves it into a free slot of a block that the repeated filters already read, when that saves more reads than the move costs. The row keeps its id, and the learned records follow it exactly.

Control and inspection. SET summaries and SET indexes take auto, declared or off. declared keeps the structures you created and drops the ones the planner built. off keeps every structure but uses none. EXPLAIN names the structure that answered a statement, or the reason it did not, and SHOW SUMMARIES reports the state of each structure and what it is waiting for. Database.Regroup(table) runs a regroup now. Database.PlaceOnWrite turns placement on writes off.

Keyed summary, sorted index and summary files on a paged table are saved beside the table, and the learned records (routes, predicate counts and block zones) are saved in learned.idx. A saved file is trusted only when it matches the table's change counter and exact totals. learned.idx is trusted at open only when the table has not changed since it was written. A stale or damaged file is discarded, or for a declared structure rebuilt from its saved definition, and SHOW SUMMARIES gives the reason.

using HoloDb;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, tier INT, qty INT)");
db.Execute("INSERT INTO sales VALUES (1,'north',1,10),(2,'north',2,-4),(3,'south',1,7),(4,'east',3,5),(5,'south',2,9)");
db.Execute("SET summaries = declared");
db.Execute("CREATE INDEX by_region ON sales (region, tier) INCLUDE (qty) USING SUMMARY");
foreach (var r in db.Execute("EXPLAIN SELECT SUM(qty) FROM sales WHERE region = 'north' OR tier = 3").Rows)
    Console.WriteLine($"{r["kind"]}: {r["route"]}");
foreach (var r in db.Execute("SHOW SUMMARIES ON sales").Rows)
    Console.WriteLine($"{r["name"]} {r["origin"]} {r["state"]}");
db.Execute("DROP INDEX by_region");

The aggregate plane (Pleroma)

An optional aggregate plane, held in Phasor Pleroma stores, summarises the small-domain columns of one table. It is derived from the columns, updated by every write, and answers WHERE aggregates over any combination of AND, OR, NOT, LIKE, IN and BETWEEN on its dimensions, multi-column GROUP BY with HAVING, COUNT(DISTINCT dimension), MIN and MAX of a dimension, and one equi-join COUNT or SUM between two tables that both have a plane. Sums are exact past 64 bits: the plane raises an error rather than wrap, and PleromaSumExact returns the exact 128-bit total.

A plane that is attached with EnablePleroma, or built by the planner, answers only what its dimensions and measures cover. Anything else goes to the engine, so a statement returns the same rows with the plane on or off.

using HoloDb;

var db = Database.Open();
db.Execute("CREATE TABLE sales (id INT PRIMARY KEY, region TEXT, tier INT, qty INT)");
db.Execute("INSERT INTO sales (id, region, tier, qty) VALUES (1,'north',1,10),(2,'north',2,-4),(3,'south',1,7),(4,'east',3,5),(5,'south',2,9)");

db.EnablePleroma("sales", new PleromaPlaneOptions(new[] { "region", "tier" }, new[] { "qty" }));
db.PleromaMode = PleromaMode.Always;

var r = db.Execute("SELECT COUNT(*), SUM(qty) FROM sales WHERE region = 'north' OR tier = 3");
Console.WriteLine($"{r.Rows[0]["count(*)"]} {r.Rows[0]["sum(qty)"]}");
// 3 11
r = db.Execute("SELECT COUNT(DISTINCT region) FROM sales");
Console.WriteLine(r.Rows[0]["count(distinct region)"]);
// 3
db.Execute("DELETE FROM sales WHERE id = 4");
Console.WriteLine(db.Execute("SELECT COUNT(DISTINCT region) FROM sales").Rows[0]["count(distinct region)"]);
// 2
Console.WriteLine(db.PleromaVerify("sales") ?? "plane equals a rebuild from the columns");
// plane equals a rebuild from the columns
Console.WriteLine(db.PleromaSumExact("SELECT SUM(qty) FROM sales"));
// 22

PleromaMode has three values. Auto (the default) leaves whole-table counts and sums, and single-column GROUP BY, to the engine's maintained totals. Always serves every shape the plane covers. Off bypasses the plane while it is still kept current. PleromaVerify rebuilds the plane from the columns and compares it, and returns null when they agree. PleromaStats reports sizes and the number of statements served and declined.

A plane created from SQL with CREATE INDEX ... USING SUMMARY, and one the planner builds, is logged and saved with the database, and survives a restart. A plane attached with EnablePleroma is not saved: call EnablePleroma again after Database.Open.

The Pleroma page store

PleromaPageStore keeps many exact Pleroma pages of one shape as the data itself, durably and larger than memory. PleromaSparsePageStore does the same for sparse pages over a large vocabulary. Each page carries a Phasor check prime, so a damaged record is detected, and records are grouped with exact-sum parity records, so a lost page or one damaged record per group is rebuilt by Repair. Readers do not wait for a writer that is working on other pages.

using HoloDb;
using Phasor;

var shape = new HologramShape { KeyCapacity = 2, KeyCycle = 110, CompleteKeys = true };
var dir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());

using (var store = PleromaPageStore.Open(dir, shape))
{
    store.AddBatch(new[] { new PageAdd(42, 5, 3), new PageAdd(42, 77, -2), new PageAdd(7, 5, 1) });
    store.AppendEvent("context 42 seen"u8);
    Console.WriteLine($"{store.Counts(42)[5]} {store.Counts(42)[77]}");
    // 3 -2
}

using (var store = PleromaPageStore.Open(dir, shape))
{
    Console.WriteLine($"{store.Counts(42)[5]} {store.Counts(7)[5]}");
    // 3 1
    store.ReplayEvents((seq, bytes) => Console.WriteLine($"{seq} {System.Text.Encoding.UTF8.GetString(bytes)}"));
    // 2 context 42 seen
    Console.WriteLine(store.Verify().Clean);
    // True
}

• Writes are journaled before they are applied, and a page remembers the last batch applied to it, so a crash replays each logged add exactly once. A torn journal tail is discarded, so a batch survives whole or not at all.
• Restore points. Checkpoint() returns a point and RestoreTo(id) returns the store to exactly that state, without a recount. A restore point is copy-on-write.
• Events. AppendEvent stores an opaque record in the same sequence as the batches. ReplayEvents returns them in order, and TrimEvents drops an absorbed prefix.
• Repair. Verify checks every record and parity group. Repair rebuilds a lost page or a damaged record from its group. A rebuilt page must pass its own check before it is written.

The store's shape must be CompleteKeys and admissible for a check prime. One process opens a store at a time, and a batch is applied page by page, so a reader can see a batch on some pages and not on others.

Measured

Machine and conditions for every figure on this page. An 11th Gen Intel Core i9-11950H (8 cores, 16 threads), 31.7 GB of memory visible to the operating system, an NVMe drive, Windows 11. Each figure is one run, or the median of repeated calls in one session, on a machine that was not otherwise idle. Figures on different machines or in different sessions are not compared with each other. Each one names the rows and the statement it was measured on.

Deletes on paged tables

Warm, on a paged table of 3,000,000 rows:

DELETE on 3,000,000 rows	Time
one row at a random key (median)	1.0 ms
the first 100,000 rows	57 ms
the rows where g = 7 (2%)	94 ms

At 10,000,000 rows: deleting the first 1,000 rows takes 156 ms, because it removes the minimum of four columns and those columns are read again. Deleting one row from the middle takes 40 ms. Deleting the 2% of rows where g = 7, scattered through the table, takes 308 ms, which is the scan that finds them.

Learned records and routes

Repeated clustered predicate, 60,000 rows. Blocks read by a repeated WHERE clause: 59 on the first run, 8 once the aggregate has been learned, and 1 for COUNT(*). On 10,000,000 rows, COUNT(*) with a repeated predicate fell from 5.7 ms to 1.0 ms.

Repeated workload, 10,000,000 rows. Each round looks up 10 customers, runs two status filters within the blocks the lookup finds, and joins the customer dimension with a status filter on the fact side. The same ten customers repeat each round. Blocks of the fact table read per round:

Round	No learned links	With learned links
first	19,366	2,032
later rounds (steady)	6,691	625

With the structure budget reduced to 20 MB, the first round is 23,766 blocks without links and 6,425 with them, and the steady rounds are 6,691 and 625. The block counts are exact. Timings on that machine varied by about 20% between runs, so they are not given here. A first lookup of a customer no read has seen is a full scan in both arms.

ClickBench-compatible analytic SQL

The 43 ClickBench queries run on the official hits table of 99,997,497 rows and 105 columns. Results are checked against the output of an independent engine on the same file.

• Correctness. [GATE: ClickBench correctness on this release at 1,000,000, 10,000,000 and 100,000,000 rows]
• Load and size. [GATE: ClickBench load time and disk size at 100,000,000 rows on this release]
• Timings. [GATE: ClickBench hot and cold totals, and the slowest query, at 100,000,000 rows on this release]

These are not leaderboard submissions and the relative-score formula was not run.

100,000,000 rows: writes, mixed work and a regroup

[GATE: single-row DELETE and UPDATE, autocommit, p50 and p99 at 100,000,000 rows]

[GATE: a transaction of 100 statements, p50 and p99 at 100,000,000 rows]

[GATE: mixed workload at 100,000,000 rows: group-by, point read, single write, filtered sum, top-10 customers]

[GATE: repeated scattered-predicate workload at 100,000,000 rows, first round and steady rounds]

[GATE: the one-time regroup at 100,000,000 rows]

Costs and limits

This is the one place the package states its costs. Each is a current behaviour.

• Deleting the last row that holds a column's minimum or maximum re-reads that column. A sorted key or group index that already knows the next value avoids the re-read.
• A DELETE or UPDATE of a column that a sorted index or keyed summary reads is applied to it as a signed delta, so the structure stays current. A write the structure cannot follow drops it: a key outside a keyed summary's range, or more deleted rows than a sorted index holds as deltas. ALTER TABLE ... ADD COLUMN drops a sorted index. Until the planner has paid for a rebuild, the scans run without it.
• Learned records and routes help when a repeated predicate matches rows that sit together. A predicate whose rows are spread evenly over every block gains nothing: on a test of uniform data the repeated predicate read 59 blocks with the records and 59 without them.
• The first lookup of a literal that no earlier read has seen is a full scan, unless the dictionary or the column's range proves no row can match: a text value absent from the column, or a number outside its range, reads no block. A number inside the range is still a full scan on the first read.
• Learned records and routes are saved in learned.idx beside the table. A reopen keeps them only when the table has not changed since the file was written. After any later write they are learned again by use. The planner's usage ledger is memory only, and it is not written to the log.
• Consolidation and regroup run as upkeep, in slices. A statement that arrives during a slice waits for that slice, not for the whole job. Scattered holes cost the page each one sits in. [GATE: the longest read wait during a consolidation of scattered holes at 10,000,000 and 100,000,000 rows]
• The holographic tree is built in slices under the shared lock. Its final merge is one pass that is not sliced, and only the publish takes the exclusive lock.
• Paged tables take one INNER JOIN per statement. ApplyChanges, the upsert form and summary tables are in-memory only. A primary key loaded out of order is held in a dictionary in memory.
• Without ORDER BY the row order is unspecified. After a DELETE, or any write that moves a row, it can differ from the order before it.
• Sums past 64 bits throw. Text GROUP BY is ordinal and case-sensitive, and the groups of a coded group-by come back in code order unless you add ORDER BY.
• NEAREST from a few thousand rows up is approximate, and its ranking is not guaranteed to order every pair of far values by distance.
• The aggregate plane declines what it does not cover. A plane attached with EnablePleroma is not kept across a restart.

Dependency injection

public static IServiceCollection AddHoloDb(this IServiceCollection services, Action<HoloDbOptions>? configure = null)

AddHoloDb registers a singleton HoloDbService. It needs only IServiceCollection, from Microsoft.Extensions.DependencyInjection.Abstractions. ServiceCollection and BuildServiceProvider come from the full Microsoft.Extensions.DependencyInjection package, which ASP.NET Core and the generic host already include. A console project adds it with dotnet add package Microsoft.Extensions.DependencyInjection.

using HoloDb;
using Microsoft.Extensions.DependencyInjection;

var services = new ServiceCollection();
services.AddHoloDb(o => o.WalPath = null);
var provider = services.BuildServiceProvider();
var db = provider.GetRequiredService<HoloDbService>();
db.Execute("CREATE TABLE ping (id INT PRIMARY KEY)");
db.Execute("INSERT INTO ping VALUES (1)");
Console.WriteLine(db.Execute("SELECT count(*) FROM ping").Rows[0]["count(*)"]);
// 1

EvalApp pipeline integration

public static IAppBuilder WithHoloDb(this IAppBuilder builder, HoloDbService service)

WithHoloDb sets a HoloDbContext, an EvalApp global context that wraps the service, as the app's context. A step that derives from ContextPureStep<HoloDbContext, ...> reaches the service through global.Service. An app has one global context. When the app needs the database and other things too, put a HoloDbContext inside your own context object and pass that to WithContext.

using EvalApp;
using HoloDb;

var service = new HoloDbService(new HoloDbOptions());
service.Execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT)");
service.Execute("INSERT INTO users VALUES (1, 'Ada')");

var services = new Services(new HoloDbContext(service));

Eval.App("Catalog")
    .WithContext(services)
    .DefineDomain("Catalog")
        .DefineTask<LookupQuery>("LookupUser")
            .AddStep<LookupUserStep>("Lookup")
        .Run(out ICompiledPipeline<LookupQuery> pipeline)
    .Build();

var outcome = await pipeline.RunAsync(new LookupQuery(1));
if (outcome is PipelineResult<LookupQuery>.Success ok) Console.WriteLine(ok.Data.Name);
// Ada

public sealed record LookupQuery(long Id, string Name = "");

public sealed class Services(HoloDbContext db) : GlobalContext
{
    public HoloDbContext Db { get; } = db;
}

public sealed class LookupUserStep : ContextPureStep<Services, LookupUserStep.NoDomain, LookupQuery>
{
    public sealed class NoDomain : DomainContext { }
    protected override ValueTask<LookupQuery> TransformAsync(LookupQuery data, Services global, NoDomain domain, CancellationToken ct)
    {
        var r = global.Db.Service.Database.Execute($"SELECT name FROM users WHERE id = {data.Id}");
        return ValueTask.FromResult(data with { Name = (string)r.Rows[0]["name"] });
    }
}

HoloDbService.ExecuteAsync and ApplyChangesAsync run the synchronous engine on a thread-pool thread, and do not go through the pipeline. Inside a step, call the synchronous Database as above.

Usage metering

MeteredDatabase wraps a Database and reports, for each query, the work it did: the rows and bytes it read, and whether it was served from a maintained total without reading rows.

public readonly record struct QueryUsage(long RowsScanned, long TableRows, long RowBytes, long BilledBytes, double ElapsedMs)
{
    public long BytesActuallyRead { get; }   // RowsScanned * RowBytes
    public bool ConstantTime { get; }         // true iff RowsScanned == 0
}
public sealed class MeteredDatabase
{
    public MeteredDatabase(Database db, HoloPricing? pricing = null);
    public (QueryResult Result, QueryUsage Usage) Run(string sql);
    public double OurCostUsd(QueryUsage u);
    public double ScanCostUsd(QueryUsage u, ScanPricing engine);
}

ScanPricing and HoloPricing are records that hold a price per tebibyte and a minimum bill per query. The ScanPricing names are BigQuery, Athena and All. Database.LastRowsScanned, LastTableRows, LastRowBytes and LastLogicalBytes are the per-thread counters Run reads.

using HoloDb;
using HoloDb.Analytics;

var db = new HoloDbService(new HoloDbOptions());
db.Execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)");
db.Execute("INSERT INTO t VALUES (1, 100), (2, 200)");
var metered = new MeteredDatabase(db.Database);
var (res, usage) = metered.Run("SELECT sum(v) FROM t");
Console.WriteLine(usage.ConstantTime);
// True
Console.WriteLine(metered.OurCostUsd(usage) <= metered.ScanCostUsd(usage, ScanPricing.BigQuery));
// True

Parallel fan-out (HoloPar)

HoloPar.Cpu is the parallel primitive the engine fans out through: Map(chunks, body) runs the body once per chunk. You do not need to call it, since every query path that benefits already does. Each call site learns its own cost, and runs inline when a fan-out would not pay. Nothing in this package is gated.

using HoloDb;

var sum = 0; var gate = new object();
HoloPar.Cpu.Map(4, i => { lock (gate) sum += i; });
Console.WriteLine(sum);
// 6

Engine building blocks

These types are public in the assembly, but none of them is needed to use the SQL surface. They are the building blocks the engine is made of.

Type	What it is
Holo (static)	The row encoding: EncodeRow(Schema, row) binds the columns into a holographic vector, and Similarity(a, b) is the cosine similarity. NEAREST ranks with these.
HoloLsh	The locality-sensitive index that NEAREST builds over row encodings once a table is large enough: Add, Approx and Exact top-k.
GrowColumn<T>	An append-only column array with a published count, which readers can see without a lock.
Table, Schema, Column, ColType, LiveMetrics, GroupRow	The in-memory table primitives Database builds on. Build tables through SQL, not with new Table(schema).

using HoloDb;

var schema = new Schema(new[] { new Column("id", ColType.Int, true), new Column("tag", ColType.Text, false) });
var v1 = Holo.EncodeRow(schema, new Dictionary<string, object?> { ["tag"] = "red" });
var v2 = Holo.EncodeRow(schema, new Dictionary<string, object?> { ["tag"] = "red" });
Console.WriteLine(Holo.Similarity(v1, v2) > 0.99);
// True

Exceptions

Every error the engine raises on purpose is a HoloException. Catch that type.

public sealed class HoloException : Exception
{
    public string? Code { get; }     // set for a plane-only cap, a refused plane blob, or a data page that fails its checksum
    public string? Dimension { get; }
    public int? Cap { get; }
    public int? Needed { get; }
}

Code takes the values in HoloErrorCodes: PlaneDimensionCap, PlaneJointCap, PlaneBlobCorrupt and PlaneBlobVersion. A damaged data page reports the code data-page-checksum, which is not one of those constants.

File layout

• In memory (WalPath = null): nothing is written to disk.
• Text log (WalPath set, an in-memory database): WalPath is an append-only log of committed statements, and WalPath + ".snap" is the latest snapshot. Checkpoint() folds the log into a new snapshot and empties the log.
• Paged table (CreatePaged(sql, dir), or whole-database paged mode): one directory per table.
  • Keep these together. They are the data and its record: data.pages (the pages), wal.log (the ring of deltas not yet written into data.pages), table.meta9 (the table record; table.meta10 when the table has a NULL column), and the stubs table.meta8, table.meta7 and table.meta, which make older binaries refuse the table.
  • Declarations, to keep with the table: summary.decl, sorted.decl and keyed.decl record the indexes you created with CREATE INDEX ... USING SUMMARY, CREATE INDEX (sorted) and USING KEYED. Back them up with the table.
  • data.crc: one CRC-32C per data page. It is rebuilt at open when it is missing or damaged.
  • txn.spill and stmt.spill: scratch space for a transaction or a statement larger than the pool.
  • Derived, and safe to delete (the engine rebuilds them or earns them again): derived.idx (the group indexes and the sorted key), summary.auto (planner-built aggregate summaries), sorted.<column>.idx (sorted index files), keyed.<name>.pks and keyed.<name>.joins (keyed summary files and their join totals), and tree.list with its files (the holographic tree). learned.idx (routes, predicate counts and block zones) is derived too: a file that does not match the table is discarded.

License

Proprietary: this package ships the compiled library only; see LICENSE for the full terms. Installing and using unmodified copies for internal evaluation and non-commercial development or testing is licensed; commercial, production, or revenue-generating use requires a separate commercial licence from the owner. No feature on this page is gated behind a tier or a licence key: every capability described works identically with or without one. Copyright (c) 2026 Dongyang Stephen Chen, trading as Evaluated Applications. All rights reserved.