Chronos DB 0.1.0
A misspelled column suggests the nearest one; COPY ... FROM STDIN (FORMAT binary); COPY streams into its INSERT
The first release: signed binaries for macOS and Linux (x86_64 and arm64), installed by curl -fsSL https://github.com/Abhishekxdg/chronosdb/releases/latest/download/install.sh | sh.
Getting started
- Prebuilt binaries with SHA-256 sums and keyless cosign signatures (
CHRONOS_VERIFY=1checks them), published at github.com/Abhishekxdg/chronosdb with the Studio's source (MIT), the clients and the docs; the docs lead with the one-line install. - A misspelled column suggests the nearest one:
column "nme" does not exist; did you mean "name"?(one or two edits away). chronos studio,serve,mcpandimportwithout a folder say so and how to give one, and a file given as the folder says it's a file (it wasFile exists (os error 17)).- The shell and its help say worlds throughout;
worldslists them (branchesstill works). - Issue and pull request templates, CONTRIBUTING.md, and Report a problem in the Studio's Help menu.
Bulk loads
COPY ... FROM STDIN (FORMAT binary), as bulk loaders send it: every type binary parameters take, pgvector's vectors included (psycopg:cur.copy("copy items from stdin (format binary)")).COPY ... TOin binary is still refused.- COPY streams into its INSERT: the rows go in a part at a time (16,384 rows or 32 MB) as the client sends them, instead of all being kept (on disk past a quarter of
CHRONOS_WORK_MEM) until the data ends. The data is written once fewer, and a COPY holds one part. - Vectors are stored 7 bits to a character instead of base64's 6: a 1,536-dimension vector takes 7,022 characters instead of 8,192. Rows written before still read.
- Measured on 76,424 × 1,536 embeddings: a binary COPY writes 1,057 MB instead of 1,816 MB, with 8–9 s of server CPU instead of 14–26 s, into a 536 MB database instead of 625 MB; an INSERT load writes 1,323 MB instead of 1,477 MB.
- Loading them on a cloud VM is bound by its disk, not by the protocol (binary COPY reads and decodes them all in 0.7 s).
bench/ingest.pytimes INSERT and binary COPY on Chronos DB and Postgres, and LanceDB, whose ~2 s load doesn't wait for the disk (no fsync).
Scans stay small at any table size
- Scans split a table into parts of about 4,096 rows however big it is. They were cut at the tree level with at least 256 subtrees, so each time a table outgrew a level (the tree fans out 32 ways) every part became 32 times bigger: a
count(*)over 10 million rows needed +63 MB of heap, now +8 MB (1 and 4 million rows: +5 and +6 MB, as before), and RSS went from 250 MB to 107 MB. What still grows with a database is the page directory it keeps open, about 3 MB per million narrow rows.examples/scanmem.rsmeasures it.
Chronos Studio
- The layout of a desktop data tool: menus, a search that opens the command palette, an icon rail, a sidebar with the folder, worlds, tables opening to their columns, a timeline and an outline (it collapses: ⌘B), pill tabs, and a status bar; floating panels on a quiet ground, Schibsted Grotesk and Martian Mono.
- The worldline: every world drawn as a lane off its parent at the moment it was forked; click a lane to switch world, drag across it to read the tables as they were.
- Search: words, filters and nearness to a row's vector in one
findcall, beside a map of the table's vectors (PCA, coloured by a field with few values). Vectors show as a strip of bars in the grid and the inspector, with Find similar rows. - Diagram: a card per table with its columns and types, foreign keys drawn between columns, drag, pan, zoom and fit.
- Settings (⌘,): theme, density, sidebar, worldline, times in the local zone or UTC, the session's key, the database's facts and an integrity check.
- Checkpoints (save one, view as of it, restore to it), Export CSV of a table's rows in view, CSV downloads of SQL results, and SQL colouring in the editor.
- Edit a cell in place: double-click (or F2), Enter saves, Esc cancels; one
UPDATE ... RETURNING, and the grid shows the stored row without reloading. On main it asks first. - Import rows from CSV, JSON or NDJSON: checked against the table's columns, previewed, and written all at once or not at all.
- Simulations: fork N worlds, run the same SQL in each, score them, keep the best; open, review, replay or discard what's kept.
- Merges on the worldline: a merge into main rejoins it, and a world merged and gone is drawn from its fork to its merge.
chronos studio <folder> [--port N] [--no-open]: a web UI for the whole database, built into the binary (a React + TypeScript app instudio/, built by Vite and embedded bybuild.rs; without a build, a page says how to make one, socargo buildneeds no Node). Worlds as lineage (each world's color down the page edge, amber for main), tables and schemas (JSON tables too), a virtualized row grid that pages through 250,000 rows with filters, sorting and time travel, a row inspector with editing that defaults to a new world on main, a SQL console (SQLSTATE errors,EXPLAIN, history), the Changes view (the paged diff with only changed columns, a merge preview settling conflicts row by row, merge, discard, undo), history with restore, agents and the audit log, status, a ⌘K command palette and keyboard shortcuts, light and dark themes, and phone layouts. See docs/studio.md.- It listens on 127.0.0.1 only, on its own port, and works beside a running
chronos serve,mcpor shell on the same folder by running its calls in that process over the local socket (a newapisession mode). The API needs a per-run 256-bit key carried in the link's#(never sent to a server) and sent back asX-Studio-Token; Host must be127.0.0.1:<port>and any Originhttp://127.0.0.1:<port>, exactly. Responses areno-storewith a strict CSP (no inline script), no framing and no referrer. It offers no backups (a path on the machine).chronos serve's own API is unchanged.
DISTINCT aggregates bounded
count(DISTINCT x)and every otherDISTINCTaggregate on one table no longer hold the table or all their values. They were kept off the part-by-part grouping (two parts' distinct values couldn't be merged), so the whole table was read into memory first, and each group kept every value it had seen:count(DISTINCT k)over 10 million rows peaked at 4.0 GB. A DISTINCT state now keeps its values so two parts' states merge, the values count againstCHRONOS_WORK_MEM, and past it rows spill by group and argument (see operations). On the VM, 100,000 distinct values over 4 and 10 million rows took 94 and 295 MB (main: 1.5 and 3.3 GB) with a 32 MB cache and 64 MB budget, and at the defaultscount(DISTINCT k)of 10 million values 672 MB (main: 4.0 GB). Same answers and speed while they fit (1 million rows: 2.1 s on both).- A grouping's next wave of parts is sized by what one part's groups hold, not only by the merged groups: parts sharing groups or distinct values had let a whole table's parts be held at once.
Statements bigger than memory
- A big
INSERT(VALUES,SELECT,COPY),UPDATEorDELETEno longer holds its writes. Past half ofCHRONOS_WORK_MEMit goes on in parts: each part is checked against the table as the statement found it, and its rows and index entries go to sorted, lz4-compressed run files in<database>/spill(sealed when the database is encrypted), with marks for keys and unique values that depend on other parts. At the end the runs are merged: a key or unique value two rows take, or one the table holds for a row the statement leaves alone, fails the statement (23505) and nothing is written, as before. The merged changes are written into a new tree beside the world's, and one checkpoint makes it the world's state: all of the statement or none of it, for readers and after a crash (the manifest is the commit record; a newOp::Bulklog note, manifest format 7, records it for history and replay). Worlds, merges, history and time travel see one write, and nothing is left in memory afterwards. - Under a 30 GB cap on the 31 GB VM,
INSERT ... SELECTs of 540-byte rows into a table with two indexes peaked at 61, 67 and 78 MB for 200,000, 1 million and 2 million rows withCHRONOS_WORK_MEM=32MB(before: 439 MB, 2.3 GB, 4.5 GB), and at 405, 406 and 411 MB for 1, 5 and 10 million rows with the defaults (main at 3 million: 6.8 GB). They were also faster (1 million rows: 56 s against 79–86 s), and small statements weren't slower. AnUPDATEof all 5 million rows peaked at 548 MB, andCOPYs of 0.5, 2 and 4 million rows at 433, 440 and 419 MB. A kill -9 while one spills, or while its commit writes pages, leaves the table as it was and no spill files; one after its checkpoint keeps all of it. COPY ... FROMis one statement however big (it went in as a transaction of 20,000-row inserts), taking its rows a part at a time as they arrive.INSERT ... SELECTreads a one-table orgenerate_seriesSELECTa part at a time (a function inFROMcounted asLATERAL, so everygenerate_serieswas computed whole first).- Still held in memory:
ON CONFLICT,RETURNING, tables with triggers or a foreign key to themselves, aDELETEor key-changingUPDATEof a table others' foreign keys cascade or set null from, statements run by functions or triggers, schemaless and temporary tables, and databases in memory.COMMITof a transaction holding a big statement, like any merge, moves its changes in memory.
Schemas, search_path, regclass and the constraint catalogs
- Schemas:
CREATE SCHEMA [IF NOT EXISTS],DROP SCHEMA [IF EXISTS] ... [CASCADE | RESTRICT], andschema.namefor tables, views, sequences, types, functions and indexes everywhere (DDL, DML,FROM, joins, foreign keys). Objects outsidepublicare stored under their qualified name and schemas are rows of the world, so they fork, merge, diff (DIFF ... AS SQLtoo), travel in time and replay;publicobjects keep their keys, so existing databases open unchanged. SET search_path/RESET/SHOW, resolving names without a schema as Postgres does (andCREATEinto the first existing schema), withcurrent_schema()andcurrent_schemas()following it; psql's\dn,\dt app.*and\d app.t.'name'::regclass(schema-qualified or not),::regclass::oidand::regclass::text,to_regclass(), with oids matchingpg_class.- Catalogs:
pg_index,pg_constraint,pg_indexes,pg_am,information_schema.schemata,table_constraints,key_column_usage,referential_constraints,constraint_column_usageandcheck_constraints, from the real primary keys, unique constraints, foreign keys, checks and indexes, withpg_get_constraintdefandpg_get_indexdef; checked against Postgres 17 with the reflection queries SQLAlchemy and Prisma send. WITHinside any subquery (inFROM,IN,EXISTS,ANY, a scalar subquery,INSERT ... WITH ... SELECT);unnestandgenerate_subscriptsin the select list;array_length,cardinality,array_lower,array_upper; bitwise&and|.
PL/pgSQL catches errors, runs dynamic SQL; enums change
EXCEPTIONblocks:BEGIN ... EXCEPTION WHEN division_by_zero OR unique_violation THEN ... WHEN SQLSTATE '22023' THEN ... WHEN OTHERS THEN ... END, withSQLSTATE,SQLERRM,GET STACKED DIAGNOSTICS(message, code, detail, hint, context) andRAISE;to raise it again. A caught error takes back what the block wrote (its triggers' writes included), and keeps the handler's, as Postgres's implicit savepoint does; it works inside triggers (a handler can turn aunique_violationinto another write), transactions and savepoints, and a restart replays only what stayed. Statement timeouts and cancels (57014) and failedASSERTs aren't caught byOTHERS, as in Postgres, so a timeout still stops a loop inside a block.EXECUTE(dynamic SQL)[INTO [STRICT] ...] [USING ...],FOR ... IN EXECUTEandRETURN QUERY EXECUTE, withformat()(%s,%I,%L),quote_ident,quote_literalandquote_nullable.ALTER TYPE ... ADD VALUE [IF NOT EXISTS] ... [BEFORE | AFTER ...],RENAME VALUEandRENAME TO,DROP TYPE ... CASCADE(the type's columns and functions go too), andenum_first,enum_last,enum_range. Enums can be functions' arguments and results and PL/pgSQL variables, and compare by their labels' order there. Changes fork, merge, travel in time and replay with the rows.- Comparing an enum with a string that isn't one of its labels is 22P02, as in Postgres (it found no rows).
Fixes from the capability audit
select count(*) || 'x'(an aggregate inside an operator) andORDER BY v <-> NULLon an HNSW-indexed column no longer crash the connection.SET statement_timeoutworks (it was accepted and ignored), Postgres cancel requests stop the running statement, and a statement whose client disconnected stops.column = valueon a big table without a B-tree index no longer builds the table's search index in memory (a 1 GB table grew the server to 10 GB): queries build it only for tables underCHRONOS_WORK_MEM / 8, and scan otherwise.- A range on an integer primary key (
BETWEEN,<,>) reads by key instead of scanning, up to 100,000 keys wide. - Databases bigger than memory (
examples/big.rs: 811 MB with a 32 MB cache stays under 200 MB of footprint):ON CONFLICTon aUNIQUEcolumn no longer builds the table's search index to find the clashing row (an 811 MB database passed 2 GB on one upsert). It reads the constraint's own entry: 2 ms.- A range on a text primary key reads only its rows, at any width (a 1/256 range went from 223–537 ms to 28 ms cold, 2–3 ms warm).
- The page cache counts the memory pages take once decoded, so
CHRONOS_CACHE_MBis a real cap. It had counted their size on disk, which undercounts small rows by up to 2.5 times.SHOW METRICSaddscache_bytes,cache_hitsandcache_misses. CREATE INDEXon a table bigger than memory (andCREATE UNIQUE INDEX,ADD CONSTRAINT ... UNIQUE) held every entry several times over in one write: on a 9.3 GB table the server passed 7.8 GB and was killed. It now sorts the entries withinCHRONOS_WORK_MEM, spilling to disk, finds any duplicate before writing, and writes the entries in parts with checkpoints between, the index appearing only with the last part: a crash mid-build leaves no index. Writes and merges to the world wait for it; reads and forks don't. On 7 million rows of 540 bytes under a 2 GB memory cap,CREATE INDEXtook 65 s at 548 MB andCREATE UNIQUE INDEXon two columns 206 s at 767 MB; before, both were killed at the cap.- B-tree index reads hold the row ids they find (up to
CHRONOS_WORK_MEMof them) and read each row as the query reaches it, instead of holding up to a fifth of the table's rows. - Big writes copy their rows fewer times: a log record of a MB or more is written straight to the file (the log's buffer had grown to the biggest record and kept that memory), the record is encoded into a buffer sized once, and
INSERT ... SELECTlets go of the selected rows as it goes and keeps the rows it made only forRETURNINGorAFTERtriggers. A 250,000-rowINSERT ... SELECTinto that indexed table peaked at 1,022 MB instead of 1,608 MB, and a 1,000,000-row one at 3.5 GB instead of 6.2 GB. (A single statement no longer holds its rows at all pastCHRONOS_WORK_MEM: see Statements bigger than memory.) - Text search corrects typos only in words of up to 32 letters: every longer word (a hash, an id) added its length squared in bytes to the index, and a 19 MB table's index passed 2.5 GB.
- Without a server token, an HTTP bearer token that matches no agent (a disabled agent's included) gets 401 instead of acting as the database's owner.
SHOW HISTORYlists merges settled by columns or row picks.chronos --versionprints the version (it created a database folder named--version), and unknown--optionsare refused.- Worlds no longer write main to number rows.
serial, identity andCREATE SEQUENCEcounters were rows of main, so a fork's inserts (ornextval,setval,RESTARTin a fork) changed main's rows and version. They now belong to the database: logged as their own record and kept in each checkpoint's manifest (format 6), so numbers stay unique across merges, restarts and crashes while no world's data moves. Main may see gaps where forks took numbers, as Postgres does after a rolled-back insert. Time travel and restore don't rewind counters. Databases from before open and carry on numbering (their counters are taken over from main's rows once).
Postgres compatibility: views, sequences, NOTIFY, the catalog
- Views:
CREATE [OR REPLACE] VIEW name [(columns)] AS queryandDROP VIEW [IF EXISTS] ... [CASCADE]. Readable anywhere a table is; kept as rows of the world, so forks,DIFF(andDIFF ... AS SQL), merges, restarts and AS OF (the definition and its tables as they were then) see them. Dropping a table or view a view reads fails with 2BP01 unlessCASCADE. Simple views (one table's columns, no grouping) takeINSERT,UPDATEandDELETE, as Postgres's automatically updatable views; others get Postgres's error. psql's\dvand\d[+] viewwork. - Materialized views:
CREATE MATERIALIZED VIEW ... [WITH [NO] DATA],REFRESH MATERIALIZED VIEW,DROP MATERIALIZED VIEW: a table holding its query's rows, per world; a REFRESH that finds the same rows changes nothing.\dmworks. - Sequences:
CREATE SEQUENCE [IF NOT EXISTS](AS,INCREMENT,MINVALUE,MAXVALUE,START,CYCLE),ALTER SEQUENCE ... RESTART,DROP SEQUENCE,nextval,currval,setval,lastval, andDEFAULT nextval('name')columns. Definitions live in the world; counters are shared by every world holding the sequence, asserialcounters are, so ids made in forks don't collide on merge.\dsand\d sequencework. - LISTEN / NOTIFY / UNLISTEN and
pg_notify(): delivered at COMMIT (or at once outside a transaction), once per channel and payload per transaction, to connections listening in the same world, as NotificationResponse messages; idle connections get them at once. TRUNCATE(several tables,RESTART IDENTITY,CASCADE),UPDATE t AS x/DELETE FROM t x(target aliases), andDROP TABLE a, b [CASCADE].- The catalog:
information_schema.columns,.viewsand.sequencesbeside.tables, andpg_catalog'spg_class,pg_attribute,pg_namespace,pg_type,pg_attrdef,pg_sequence,pg_tables,pg_views,pg_matviewsandpg_sequences, withformat_type,pg_table_is_visible,current_schemaandcurrent_database; catalog queries psql doesn't send go to the SQL engine instead of failing.
Simulations
SIMULATE n WORLDS FROM base AS prefix RUN $$...$$ SCORE $$...$$ [ASC] [KEEP k | ALL] [SEED s] [THREADS t](andDb::simulate,simulateover HTTP and MCP,simulate()in both clients): forks n worlds from one moment of the base, runs the script in each on every core ($1the world's index,$2its seed), scores each, keeps the best k and discards the rest. A world that fails is reported with its error; the rest carry on. Agents' worlds are their own and fit theirmax_worlds(run in batches, or refused up front). 10,000 one-row worlds take 0.4 s.- The same inputs give the same worlds: in a simulation,
random(),gen_random_uuid()and keys of keyless tables are seeded per world, andnow()is the base moment, on any number of threads. Before, two forks running the sameUPDATE ... random()differed in 999 of 1,000 rows. REPLAY WORLD w [AS name](andDb::replay,replayover HTTP and MCP,replay()in the clients): kept worlds record their inputs (metadatasim); a replay forks the base as it was then from history, reruns the script and says whether the rows and score are identical.setseed(x)seeds a session'srandom(), as in Postgres.- Dollar-quoted strings (
$$...$$,$tag$...$tag$), as in Postgres. SHOW METRICStimessimulate.
Limits and cleanup
- Per-agent limits:
max_query_ms(its statements' timeout, which it can lower but not raise),max_concurrent(statements at once; SQLSTATE 53300, HTTP 429) andmax_memory_mb(a statement holding more rows stops with 53200, HTTP 413, instead of growing the server; its spilling starts at a quarter of it).CREATE/ALTER AGENT, HTTPcreate_agent/alter_agent,SHOW AGENTS. - Database-wide:
ALTER SYSTEM SET statement_timeout(every session's default),max_worlds(forks past it are refused with 53400) andworld_idle_ttl(worlds unused that long are discarded by the background worker, unless pinned withALTER WORLD w SET PINNEDor forked from). Kept in the folder; a world's last activity (activeinSHOW WORLDS) survives restarts. Expired and idle worlds' discards are logged and audited assystem. - Space comes back on its own: after 1,000 worlds, or worlds that changed 100,000 rows, are discarded, the background worker checkpoints and cleans up (before, only a doubling of the page files did).
SHOW DISK(andDb::disk_usage) splits page bytes into in use, kept only for history, and reclaimable. DROP WORLD w CASCADEdrops a world and every world forked from it, deepest first, each in the log and audit trail; HTTP and MCPdiscardtakecascade.- Discarding no longer scans every world. Each world keeps its live fork count, worlds are found by ID directly, and
Db::discard_manydrops many at once under one lock (deepest first);DROP WORLD ... CASCADE,SIMULATE's losers and the expiry and idle sweeps use it. Dropping 99,000 of 100,000 worlds went from 54.8 s to 0.93 s one by one (0.51 s in one call), the expiry sweep from 675 µs to 12.6 µs per world, and finding a world by ID from 8.7 ms to under a microsecond (in the 10,000-world simulation, whose worlds hold many rows, the discard phase didn't get faster: 0.7–1.1 s per round). Retired worlds leave the search caches, and checkpoints hand freed memory back to the system on glibc. See BENCHMARKS.md section 9. chronos serve --safe: HTTP and Postgres clients without an agent's token act as the agentguest(MCP's safe mode: fork and change only their own worlds);--admin-tokenis the owner. The namesguestandsystemare reserved for agents.SET statement_timeoutandSHOW statement_timeoutno longer need admin rights for agents (or break MCP's safe mode): they change only the session.
Branches
- Worlds: branches with IDs that are never reused, creation times, depth and JSON metadata (owner, task, tags...).
CREATE/FORK/SWITCH/ALTER/DROP WORLDandSHOW WORLDSin SQL;forkwithmeta,worldandset_metaover HTTP, MCP and both clients; kept in the log and the checkpoint. 100,000 worlds on disk: 0.46 µs per fork, 2 KB of memory each, and queries 1,000 forks deep run as fast as on main. Worlds with few changes keep them as a list in the checkpoint instead of pages of their own: at 100,000 one-row worlds, checkpoints went from 19 s to 1.2 s, reopening from 8.8 s to 1.0 s, and the folder from 429 MB to 24 MB. - O(1) copy-on-write forks.
- One writer per branch, and readers never wait.
- Merges: three-way, row by row. Conflicts come back as data and are resolved with
oursortheirs, and any row both sides wrote is a conflict (first merge wins). - Versions: every write returns the branch version, and
merge_atrefuses a branch that isn't at the expected version. - Storage per world:
SHOW STORAGE [FOR WORLD w](andDb::usage) gives each world's rows changed since its fork and the pages and bytes only it holds; a fresh fork costs 0 bytes. - Any world can expire:
ALTER WORLD w SET TTL '2 hours'/RESET TTL(andDb::expire); the background worker discards it when its time is up.
Agents
- Undo any agent, for 30 days:
UNDO AGENT name SINCE <time> [SKIP CHANGED](andDb::undo_agent) puts back every row the agent changed in a world since then, whether it wrote directly, committed transactions or merged its own worlds, and leaves everyone else's changes. Rows changed by someone after the agent are named, or skipped. Index entries are rebuilt for the rows put back, and unique and foreign keys are checked as in a merge. History is now kept 30 days by default (was a day). - MCP tools for reviewing and undoing:
merge_preview(the dry run: per row, clean or conflicting and why),mergewithby_columnsand per-row decisions (rows),undo_merge,diffwithas_sql, andcheckpoint/rollback. Safe mode stays as strict: previews and checkpoints on the agent's own worlds only, no approvals. A merge conflict now tells agents which tool calls resolve it. - Agents with names, IDs and secret tokens (only hashes kept), made with
CREATE AGENTor over HTTP; an agent's token acts as it over HTTP. - What each may do: read, fork, change its own worlds, change any world or main, merge its own or any world, restore, admin. New agents work on their own worlds; a person merges.
- Owned worlds (the owner can't be changed), world TTLs (expired worlds are discarded in the background), and quotas: live worlds, rows changed per world, writes per minute.
- Audit trail: everything an agent writes is logged as its;
SHOW AUDITlists it. - Checkpoints:
CHECKPOINT WORLD w AS 'name'andRESTORE WORLD w TO CHECKPOINT 'name'. - Agents everywhere: an agent logs in over the Postgres protocol with its name and token, and
chronos mcp --agent NAMEmakes an MCP session act as it; SQL is checked statement by statement, and CREATE WORLD makes a world the agent owns.
Merges
- Dry run: what a merge would do to every row, and why rows conflict, changing nothing (
MERGE ... DRY RUN,dry_run,preview()). - Settling conflicts: row by row (ours, theirs, a given row, or delete) and by columns (rows where the two sides changed different columns are combined); settled rows are logged as what they became, so replay is exact.
- Conflicts explained: each says what each side did and which columns it changed.
- Merge part of a world:
MERGE WORLD x ONLY TABLES (t, ...)orONLY KEYS ('t/1', ...)(only_tables,only_keysover HTTP, MCP and both clients) merges just those rows; x stays open with the rest. Rows merged this way aren't merged or flagged again later, but a later change to them by either side still conflicts. A table goes whole (schema, rows, unique, reference and index rows); a row takes its own unique and reference rows, and is refused if it shares one with a row left out or its table's schema changed. - Merge into any world:
MERGE WORLD x INTO y(into) applies x's changes to y, three-way against x's fork point; x stays open. Agents need the right to merge x and to change y. - Undo a merge:
UNDO MERGE OF WORLD x [SKIP CHANGED](andundo_mergeover HTTP and in the clients) puts back what x's latest merge changed, rebuilt from history; rows changed again since are named, or left alone. - Schema conflicts: both sides changing one table differently is refused even with
USING OURS/THEIRS, which would have misread one side's rows.
Diffs
- Column by column: every changed row lists the columns that changed, and changes to tables themselves come as
create table,alter table(columns added, dropped, renamed or retyped, keys, indexes, constraints) ordrop table. DIFF ... AS SQL(and"sql": trueover HTTP): the statements that make the change, fromCREATE TABLEandALTER TABLEto rowINSERT/UPDATE/DELETEby key. Run on the world the diff starts from, they give the one it ends at.
Time travel
- Any world as it was:
name@whenwherever a world is read (SQL, psql's database name, HTTP, MCP, clients), andAS OF/FOR SYSTEM_TIME AS OFon any table in SQL, joins of now and then included, with each table's schema as it was. Kept for a retention window (30 days by default;alter system set history_retention). RESTORE WORLD, forking a world as it was,DIFF WORLD a TO bbetween any two worlds or moments, andSHOW HISTORY(forks, writes, commits, merges);historyandrestoreover HTTP, MCP and both clients.- How: checkpoints are kept with their log for the window, the log records the time, and cleanup keeps pages only history uses. A moment is rebuilt once (6–60 ms here) and cached; a query
AS OFa fixed time runs as fast as now. INSERT ... SELECT.
Storage
- Write-ahead log: checksummed, with group commit.
- Page trees: content-addressed prolly trees of LZ4 pages, with incremental checkpoints and background checkpointing.
- Cleanup of pages nothing uses.
- Durability: safe on merge. Writes to
mainand merges are fsynced. After a crash, branches that were open are flagged until a merge confirms them. - Cloud storage (feature
s3): any S3-compatible bucket, a single-writer lease with takeover and fencing, and scale to zero.
Search
- One query plan: equality filters (bitmaps), BM25 text with one-typo correction, and vector search (1-bit scan with exact rescoring), fused with reciprocal rank fusion.
- Paging.
- Search inside branches through shared indexes plus each branch's own changes, and an answer cache.
SQL
- Functions and triggers:
CREATE [OR REPLACE] FUNCTIONinLANGUAGE sql(AS $$...$$,RETURN expr,BEGIN ATOMIC) andLANGUAGE plpgsql(a documented subset; anything outside it is refused with 0A000 when the function is made), scalar or set-returning (SETOF,RETURNS TABLE) inFROM,$1or named parameters,STRICT, overloading,DROP FUNCTION;CREATE [OR REPLACE] TRIGGERBEFORE/AFTERINSERT/UPDATE [OF]/DELETE,FOR EACH ROW/STATEMENT,WHEN,DROP TRIGGER,ALTER TABLE ... ENABLE/DISABLE TRIGGER. Both are rows of the world, so they fork, merge, diff (DIFF ... AS SQLincluded) and travel in time with the data. What functions and triggers write commits with the statement that ran them, and a restart replays those rows without firing anything again.RAISE NOTICEreaches psql;\dlists triggers. Checked against Postgres intests/pgdiff.rs. COPY:COPY ... FROM STDINandTO STDOUTover the Postgres copy protocol (psql's\copy, pg_dump-style scripts, drivers' copy APIs), text and CSV withHEADER,DELIMITER,NULL,QUOTE,ESCAPEand theFORCE_*options, checked byte for byte against Postgres. A COPY is all or nothing (bad rows, CopyFail and dropped connections leave nothing), names the line and column of a bad value, and loads without parsing SQL: about 1.7x the rows per second of 1,000-rowINSERTs. Files and programs on the server are refused.PREPARE/EXECUTE/DEALLOCATEin SQL, sharing names with the protocol's prepared statements as in Postgres.- Enum types:
CREATE TYPE ... AS ENUMandDROP TYPE, columns of them checked on every write and compared, sorted,min/max'd in label order; types are rows, so they fork and merge. Checked against Postgres. - Temporary tables:
CREATE TEMP TABLEin a database of the session's own, in memory: never logged, diffed or merged, gone with the connection, readable and fillable alongside the world's tables, and rolled back withROLLBACK. public.names wherever a table goes, andCREATE SCHEMA IF NOT EXISTS public; other schemas are refused with Postgres's errors.- Primary keys of several columns:
PRIMARY KEY (a, b)(andALTER TABLE ... ADD PRIMARY KEY (a, b)), stored in an order-keeping key form, so rows sort by key, full-key equality reads one row and a leading-column equality reads a key range.ON CONFLICTon them, and foreign keys of several columns referencing them (cascade, set null, restrict). Shown and typed as(1,abc)in DIFF, merges, the shell and the HTTP API. One-column keys keep their stored form: a folder written before reads the same (checked on a fixture written by the previous binary).chronos importtakes tables whose key has several columns. - Spilling to disk: one-table
GROUP BYandORDER BYstay withinCHRONOS_WORK_MEM(256 MB by default), spilling toCHRONOS_SPILL_DIR: groups are hashed into files grouped one at a time, sorts write runs and merge them, andORDER BY ... LIMIT kholds only k rows. Hash joins spill too (grace hash join: both sides to files by the joined value's hash, each pair joined alone), at any step of a join. Same answers and order as in memory;spilled_bytesinSHOW METRICS. ON CONFLICTon several columns andON CONFLICT ON CONSTRAINT name.ANY/SOME/ALLwith any comparison, over subqueries,ARRAY[...], array literals and JSON arrays, with SQL's NULL logic.RIGHTandFULLjoins, andUSING(the column shown once, as Postgres does), andNULLS FIRST/LAST.- Regular expressions:
~,~*,!~,!~*,regexp_replace,regexp_match(es),regexp_like,regexp_count,regexp_substr,regexp_split_to_array/table, with a built-in matcher that stops runaway patterns. - Full-text search as in Postgres:
tsvector/tsquery,to_tsvector,to_tsquery,plainto_/phraseto_/websearch_to_tsquery,@@,ts_rank,setweight,strip,||, with Postgres'senglishstemmer and stop words (identical on 20,000 dictionary words) andsimple. Floats now print as Postgres prints them (1e-20,1.5e+20). Array columns (text[], ...), subscripts on arrays and jsonb (tags[1],doc['a'][0]) andstring_to_array.search('table', 'words' [, k])in FROM ranks rows with the built-in BM25 index. GIN indexes on a tsvector (USING gin (to_tsvector('english', body))) serve@@: 12x faster than a scan at 100,000 rows. - PostGIS points:
geometry/geography,ST_MakePoint,ST_Distance(the exact WGS84 geodesic, matching PostGIS to the printed digit),ST_DWithin,ST_AsText/ST_AsGeoJSONand the other point functions, printed as PostGIS prints them.USING giston a point column makesST_DWithinread only the latitude band it can reach: about 100x faster than a scan at 200,000 points.<->and nearest-firstORDER BY ... LIMITfrom that index (the nearest 10 of 200,000 in about 1 ms), andCREATE EXTENSION postgis. An index's second column's range is now checked on each entry before the row is read. - Graph traversals use indexes: a join starts from a subquery's, CTE's or
WITH RECURSIVEround's rows when they're fewest, so each round looks edges up by index instead of scanning: a 4-round traversal of 200,000 edges went from 30 ms to 1 ms. The shell now runsWITH,EXPLAIN,VALUES,SET,SAVEPOINTandRELEASEas SQL. SIMILAR TO(withESCAPE),to_charfor numbers (9 0 . , D G S MI PL SG PR FM), unique indexes on expressions or withWHERE,RANGEwindow frames with offsets (numbers and intervals) andFILTERon window aggregates, all checked against Postgres.- Set-returning functions in FROM:
generate_series,unnest,jsonb_array_elements(_text),jsonb_each(_text),jsonb_object_keys;ARRAY[...]andtype[]casts. LATERALsubqueries (, lateral (...),CROSS JOIN LATERAL,LEFT JOIN LATERAL ... ON).WITHbeforeINSERT/UPDATE/DELETE,UPDATE ... FROMandDELETE ... USING.FILTER (WHERE ...)on aggregates,age(),to_char()for dates and times,md5().ALTER TABLE ... ADD PRIMARY KEY: rows move to their keys in one step, with constraints and indexes rebuilt.- Array parameters for
= ANY($1)/<> ALL($1), typed as drivers expect (int8[],text[], ...), in text or binary. - pgvector's SQL: the
vectortype,<=>,<->and<#>, andCREATE EXTENSION vectorandUSING hnswindexes accepted, plusinformation_schema.tables,public.names andDEALLOCATE, so Mem0's pgvector store runs unchanged (psycopg 2 and 3). With aUSING hnswindex,ORDER BY <distance> LIMIT kgets its candidates from the search index (filters on columns andjsonb ->> 'key'included) and re-ranks them exactly. - Vectors stored as 4-byte floats (
Value::Vector): rows with 1,536-dimension vectors shrink from about 17 KB to 8 KB and read back without parsing numbers; vectors print as pgvector prints them. - Vector search puts recall first: exact scans (1-bit codes with rescoring, more of it below 512 dimensions, or whole vectors for small candidate sets and up to 32 dimensions) on the free cores; HNSW graphs test themselves against the scan when built and are used early only if they find at least 99% of its top 10, otherwise past 8.4M code words (
CHRONOS_SCAN_WORDS). From 512 dimensions graphs are walked on 1-bit codes (about 6× faster). Vector-only searches no longer fetch 4× the results they return. On real OpenAI embeddings: 99.98% recall@10; on synthetic 500k × 384: 99.8–100% in 0.12–0.83 ms. - HNSW graphs link every node: a pass after building links nodes nothing pointed to (about 0.7% of 76,000 real embeddings were unreachable).
SET hnsw.ef_searchin SQL andefinfindset the graph's beam.CHRONOS_CACHE_MBsets the page cache (default 256 MB).- Faster Linux and Intel Mac binaries: the static musl builds use mimalloc instead of musl's malloc, which took most of a vector search's time on Linux, and x86-64 builds assume x86-64-v2, so 1-bit scans count bits with POPCNT rather than a software routine. The vector loops (dot products, 1-bit distances) also come in an AVX2 build, picked at run time on CPUs that have it.
- Search filters on
field->>key: a key of an object field, as SQL's->>reads it. chronos_search_graphs_buildingmetric: 1 while an HNSW graph builds in the background.- Exact
numeric: 38 digits,numeric(p, s), decimal literals typed numeric as in Postgres, Postgres's division scale, exactsum/avg, order-keeping index keys and the binary wire format. The Postgres importer keeps numeric columns exact. - Indexes on expressions (
lower(email),(doc ->> 'k')) and partial indexes (WHERE), kept up by every write, merge, rename and drop. - Aggregates:
string_agg,jsonb_agg,jsonb_object_agg,array_agg(with their ownORDER BY),bool_and,bool_or,every. - jsonb operators and functions:
->,->>,#>,#>>,@>,<@,?,?|,?&,||,jsonb_build_object,jsonb_set, ...; jsonb prints in Postgres's form. - 40 more functions: text (
concat,substring ... from ... for,position ... in,trim(leading ... from ...),split_part,lpad,initcap, ...), math (ceil,floor,trunc,power,sqrt,ln,log,mod,greatest,least,random, ...),nullif,make_date,to_timestamp. All checked against Postgres. - Named windows:
WINDOW w AS (...)andOVER w/OVER (w ORDER BY ...). time, with its arithmetic, casts,extract, indexes and the Postgres binary format.- Checked against Postgres: tests/pgdiff.rs runs each new feature's statements on Chronos and on a real Postgres and compares every result.
Interfaces
- HNSW graph for vector search: fields with 50,000+ vectors get a graph index, built in the background. Unfiltered and broad searches walk it (about 18× faster than the scan at 500k × 384: 0.25 ms vs 4.6 ms, 98.6% recall@10), and narrow filters keep the exact scan. It works inside branches.
- B-tree indexes:
CREATE [UNIQUE] INDEXandDROP INDEX, one or more columns. They answer=, ranges,BETWEEN,IN,IS NULLandLIKE 'prefix%', andORDER BY ... LIMITreads them in order, forwards or backwards (the latest 20 of 100,000 rows: 230 ms to 0.04 ms). Entries are rows, so forks share them and crashes recover them; merges rebuild them for the rows they change. EXPLAIN [ANALYZE]in Postgres's layout: which index or key each table is read through, with row counts and time underANALYZE.CHECKconstraints, kept on every write and merge.- Savepoints:
SAVEPOINT,RELEASEandROLLBACK TO, as forks of the transaction. - Postgres comparison suite: 1,000 random app-style queries return the same rows as Postgres, and none reads a whole table where Postgres uses an index.
- SQL, with our own engine:
CREATE TABLE,INSERT,SELECTon one table,UPDATE,DELETE,RETURNINGand$nparameters. - Big reports use every core: scans, hash joins and
GROUP BYover more than about 8,000 rows split across all the cores the machine (or container) allows, with the same answers on any machine;CHRONOS_THREADSoverrides. Scans also no longer copy each key, andLIKEno longer allocates. Hash-join tables are built on every core too, and scans drop columns only their own filters read. A 200,000-row join withGROUP BYwent from 214 ms to 34 ms on 8 cores (Postgres: 45 ms), and all five report benchmarks beat Postgres. Two-table joins then join each part of the first table as it is read, and group it there for exact aggregates, so neither the table nor the joined rows are held in full (join +GROUP BY33.9 → 29.4 ms). Integersum/avgadd in 128 bits: only the total has to fit. - JSON writes follow SQL's rules:
put/delete/batchinto SQL tables check columns, types,NOT NULL, keys,UNIQUEand foreign keys, fill in defaults, and store rows packed;_sonos_keys are reserved. - Window functions and WITH RECURSIVE: ranking,
lag/lead,first/last/nth_valueand aggregates over partitions and frames; recursive queries for trees and graphs, with a guard against runaway loops. - Subqueries and set operations: scalar,
INandEXISTSsubqueries (correlated too),UNION/INTERSECT/EXCEPT[ALL], subqueries inFROM, andWITH. - UNIQUE constraints and foreign keys:
ON DELETE CASCADE/SET NULL/RESTRICT, kept with index rows. Checked on every statement and every merge, so branches can't combine into a violation. Upserts can target unique columns, and psql's\dshows them. - Upserts and CASE:
INSERT ... ON CONFLICT DO NOTHING / DO UPDATEwithEXCLUDED, andCASEexpressions. - App features in SQL:
ALTER TABLE(add, drop, rename, retype columns, rename tables, defaults,NOT NULL), with versioned row layouts, so no rows are rewritten and branches merge across schema changes.serialand identity columns on a sequence shared by all branches, andgen_random_uuid().date,timestamp,timestamptzandinterval, with calendar arithmetic,now(),date_truncandextract.- psql's
\d,\dtand\l.
- Batch execution: rows flow through queries in flat batches, decoded into reused buffers, with fast hash tables for joins and grouping.
- Join ordering: inner joins start from the most selective table, and small driving sides look matches up by key or index.
- Packed rows: SQL tables store rows packed instead of as JSON (about 2× faster column reads, half the size). They're shown as JSON everywhere outside SQL.
- Joins and grouping in SQL: inner, left and cross joins (primary-key lookup and hash joins),
GROUP BY,HAVING,DISTINCT,count/sum/avg/min/max, andcoalesce,lower,upper,length,abs,round. - Branches and transactions in SQL:
CREATE BRANCH,USE BRANCH,DIFF,MERGE BRANCH,DROP BRANCHandSHOW BRANCHES.BEGIN/COMMIT/ROLLBACKrun on hidden branches: snapshot reads, and a conflicting commit fails with 40001. - The Postgres protocol:
psqland Postgres drivers connect tochronos serveon port 5433; the database name is the branch. - The
chronosshell. chronos mcpfor Claude Code and other MCP agents.chronos serve, an HTTP JSON API with a token for non-local listening.- One-file TypeScript and Python clients.
Operations
- Encryption at rest:
chronos keygen, thenCHRONOS_KEY(orCHRONOS_KEY_FILE, orDb::open_encrypted) encrypts a new database: log records, manifests, history and pages (with keyed page names), so backups and cloud copies are encrypted too. ChaCha20-Poly1305; tampering is refused; a wrong or missing key fails on open. - TLS on both ports of
chronos serve(--tls-cert,--tls-key): HTTPS, and TLS required on the Postgres protocol. The HTTP server is now Chronos's own (keep-alive,Expect: 100-continue), replacing tiny_http. - Migrations:
chronos mydb migrate migrations/applies new.sqlfiles in name order, each in one transaction with a record and checksum. Applied files are skipped, and edited applied files are refused.-b worldtries them on a world first. - Backups while running:
BACKUP DATABASE TO, thebackupHTTP op orbackupin the shell. A backup is a consistent copy with worlds and history, written into a new folder and renamed into place when done.chronos restorestarts a database from one and verifies it.VERIFY DATABASEreads every page and log record and checks each one. - Metrics: count and p50/p95/p99/max latency of every kind of statement, commit, log sync, fork, diff, merge, search, checkpoint and cleanup, plus rows written, errors, conflicts, worlds and bytes on disk.
SHOW METRICSin SQL,GET /metricsfor Prometheus.
Tests
- Crash suite: 10,000 runs.
- Crashes during checkpoints and cleanup: a child process is killed at each of 232 steps. Every time, the folder reopens with the same rows and verifies.
- Crashes during schema changes: the log is cut at every record and at random bytes, and the database comes back as it was after a whole number of statements.
- Deterministic simulation with shuttle.
- Jepsen-style
kill -9test. - Brute-force checks for search.
- End-to-end tests of both clients and the agent examples.
- A search latency budget in CI.
Fixed before release, both found by the Jepsen-style test
- Lost updates: a merge that skipped rows the parent had changed to the same value lost updates (two debits became one).
- Silent crash loss: merging a branch whose writes died in a crash reported success.
Fixed before release, found writing the crash tests
- History files after a power cut: a history manifest was renamed into place before it reached the disk, so a power cut could leave an empty one, and cleanup would then fail every time. Each one is now written and synced before its rename.
Fixed before release, found while adding UPDATE ... FROM
- A subquery could get another's answers: subquery results were cached by where the subquery sat in memory, and a later one can reuse that spot in the same statement (UPDATE drops its WHERE's before binding its SET's). They're now cached by what the query is.
Fixed before release, found by an independent review
- Agents could make themselves admin: SQL could update
_sonos_agentsin an agent's own world, and merging that world granted the rights. It also hid fromDIFF. SQL now can't reach any_sonostable. - Row quotas undercounted: SQL statements, transactions, restores and merges into a world were checked as zero rows.
max_changesis now enforced where rows are written, so every row counts. A restart used to reset the count; a world's earlier changes now count after one. - The database's own tables over HTTP, MCP and the shell:
get,putandfindon_sonostables are refused, as in SQL. - ADD COLUMN dropped its constraints:
REFERENCES,UNIQUEandCHECKon an added column were silently ignored; they're now added asADD CONSTRAINTadds them. - Diffs showed agents' token hashes: a diff of main (over HTTP, MCP or the shell) listed
_sonos_agentsrows. Diffs now leave out the database's own rows, except table schemas. - Stopping
chronos servelost writes: SIGTERM or Ctrl-C killed it like a crash, losing a world's acknowledged, unsynced writes and marking it crashed. It now closes the database cleanly first. - A merge dry run hid the crash mark: it now says the merge will need CONFIRM.
- Agents over MCP and HTTP were checked more strictly than over Postgres (UNDO MERGE needed admin). Their SQL now runs in a session acting as the agent, checked the same way everywhere.
- Agents rebuilt the past wrong: time travel and UNDO MERGE run by an agent re-checked the replayed writes against its quota and skipped them. Replay now runs as nobody.