The OxiDB SQL engine: a real relational engine on its own files, with the PostgreSQL wire and EF Core
I added a second engine to OxiDB next to the document engine, with its own WAL and files: enforced constraints, CTEs, window functions, stored procedures in Cobra bytecode, unmodified psql/psycopg and EF Core.
OxiDB started life as a document database, and for a while I was stubborn about that: the first SQL layer I tried, a thin dialect sitting on top of the document engine, I ripped out entirely. But real projects kept asking the same question: "I want relational tables for invoices, orders and stock. Do I really have to run a separate PostgreSQL?" This post is about my answer, the SQL engine I added to OxiDB. It is not a dialect. It is a second engine with its own catalog, its own WAL and its own files. It runs in the same process next to the document engine, it speaks the PostgreSQL wire protocol so clients like psql and psycopg connect unmodified, and on the .NET side it has a full EF Core provider. Every output in this post comes from sessions I ran myself against an OxiDB 0.44.2 server on a local Apple M-series Mac.
This is the fourth post in the OxiDB series. The document side is covered in the document engine post and the time series engine in the time series post.
Why a separate engine: ADR-0010
The heart of the decision fits in one sentence in ADR-0010: two engines, one instance, different files. The SQL engine never touches the document engine's files, and the document engine never enters the SQL directory. All they share is the process, the port, authentication and the lifecycle. No shared files, no shared lock map, no shared storage format.
That was deliberate. My earlier attempt ran SQL queries over document collections. It was cheap on paper, but it dragged relational semantics (fixed schemas, typed columns, primary keys, foreign keys) into a store that is not relational, and you got the worst of both worlds. ADR-0010 weighed three options and picked the second: a separate oxidb-sql crate that owns its storage, WAL, recovery, catalog, planner and executor. Today that crate is about 30 thousand lines of Rust.
In practice the separation has three consequences:
- Namespaces never collide. A SQL table called
customersand a document collection calledcustomerscan exist at the same time, because they never share a directory. I show this live further down. - Off by default. Without
OXIDB_SQLthe engine is never constructed and costs nothing. - Wire compatibility is untouched. On the native OxiWire protocol, routing is done by an optional
enginefield in the request. Any request without it goes to the document engine down exactly the same code path as before.
The cost is just as explicit: there is no atomic transaction spanning both engines. A document write and a SQL write are each durable on their own, but never one atomic unit. I wrote a design for that in ADR-0011, built on a shared global sequence number, and then deliberately deferred it: every real workload picked one engine and stayed there, while the price would have been permanent complexity in the crash-recovery path, the one place complexity is least affordable.
Running it
The SQL engine and the PostgreSQL listener are switched on with two environment variables:
OXIDB_ADDR=127.0.0.1:15400 \
OXIDB_DATA=./data \
OXIDB_SQL=1 \
OXIDB_PG_PORT=15401 \
oxidb-serverI used my own ports for this post. In production, OXIDB_PG_PORT=5432 means existing connection strings work unchanged. For a server that only serves SQL (and optionally time series) you can also set OXIDB_DOC=0. According to the docs, such a server sits idle at 9.8 MB RSS and 8 threads, against 14.2 MB and 23 threads with the document engine on.
The other settings that matter:
| Variable | Default | Meaning |
|---|---|---|
OXIDB_SQL_DATA | ${OXIDB_DATA}/sql | SQL engine data directory |
OXIDB_SQL_SYNC | full | full: a true storage flush per commit; data: OS-level sync, several times faster |
OXIDB_SQL_DISK_FIRST | on | Table data served from the mmap'd snapshot, only post-checkpoint changes in RAM |
OXIDB_SQL_CHECKPOINT_BYTES | 64 MiB | Auto-checkpoint once the live WAL passes this size |
OXIDB_SQL_LOCK_TIMEOUT_MS | 5000 | Row-lock wait; turns a deadlock into an error |
Once the server is up I connect with plain psql 18 from Homebrew. No extension, no special driver:
psql -h 127.0.0.1 -p 15401 -U admin -d oxidb -c "SELECT version();" version
---------------------
16.0 (OxiDB 0.44.2)
(1 row)Data model and DDL
For the examples I built a small shop: customers, products, orders and order items. The DDL should look familiar from PostgreSQL or MySQL:
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
email TEXT UNIQUE,
city TEXT
);
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock SMALLINT DEFAULT 0
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INT NOT NULL REFERENCES products(id),
qty INT NOT NULL,
CONSTRAINT pk_items PRIMARY KEY (order_id, product_id)
);Keys generated by AUTO_INCREMENT (also spelled AUTOINCREMENT, or PostgreSQL's GENERATED ... AS IDENTITY) can be read back with RETURNING, which is exactly how EF Core and ADO.NET read generated keys:
INSERT INTO customers (name, email, city) VALUES
('Ayşe', 'ayse@example.com', 'İstanbul'),
('Mehmet', 'mehmet@example.com', 'Ankara'),
('Zeynep', 'zeynep@example.com', 'İzmir'),
('Can', 'can@example.com', 'İstanbul')
RETURNING id, name; id | name
----+--------
1 | Ayşe
2 | Mehmet
3 | Zeynep
4 | Can
(4 rows)
INSERT 0 4A multi-row INSERT is applied as a single WAL batch: every row is validated first, then all of them land with one fsync. I added products, six orders and eleven order items the same way. For introspection there are SHOW TABLES, DESCRIBE, SHOW INDEXES, SHOW VIEWS and SHOW PROCEDURES:
DESCRIBE order_items;
column | type | nullable | primary_key | auto_increment
------------+------+----------+-------------+----------------
order_id | INT | f | t | f
product_id | INT | f | t | f
qty | INT | f | f | f
(3 rows)Both columns of the composite primary key are flagged primary_key, and both are implicitly NOT NULL.
The type system is deliberately small: BIGINT, INT, SMALLINT, TINYINT, DOUBLE, TEXT (including VARCHAR(n)), BOOL, TIMESTAMP, DECIMAL and BLOB. TIMESTAMP is stored as epoch milliseconds and treated as UTC. There is one integer detail worth its own paragraph, which I get to in the constraints section.
Constraints that are actually enforced
A constraint a database accepts and then silently ignores is worse than one it rejects. So the rule is simple: either it is enforced, or the docs say in so many words that it is not. The whole session below is real, exactly as psql printed it:
In order:
- PRIMARY KEY: a duplicate key is rejected, on plain INSERTs, on multi-row INSERTs (checked across the whole batch before any row is applied), on UPDATEs and inside transactions. A composite key collides only when every column matches;
(1, 1)was refused fororder_items. - UNIQUE: single-column
UNIQUEis enforced, with NULLs exempt as the SQL standard says. - NOT NULL: a NULL
nameis refused withschema mismatch: column "name" is NOT NULL but got NULL. - VARCHAR(n): length is counted in characters, not bytes. A 47-character Turkish name breaks the 40-character limit.
- Integer width: this one is subtle. The engine stores every integer as 64 bits, so
SMALLINTsaves no space. But a value outside the declared range is refused rather than silently widened, so a column's declared type stays true of its contents.ALTER TABLE ... ALTER COLUMN ... TYPE SMALLINTdry-runs every stored row first, so narrowing either succeeds completely or leaves the column untouched. - FOREIGN KEY: a child INSERT/UPDATE must find its parent, and a parent DELETE honours
ON DELETE NO ACTION,RESTRICT,CASCADEandSET NULL. Deleting a product was refused because three order items still referenced it. Deleting a customer cascaded two levels deep: Mehmet's two orders and their five items went with him. Before the delete there were 6 orders and 11 items; afterwards, 4 and 6.
The PostgreSQL wire layer maps these errors to the proper SQLSTATE codes (23505, 23503, 23502, 22001, 22003), so drivers raise the right exception class. I show that with psycopg below.
What is not enforced needs saying just as plainly: CREATE UNIQUE INDEX creates an ordinary index and its UNIQUE is not enforced. A table-level UNIQUE over several columns is accepted and not enforced. Multi-column FKs and ON UPDATE actions are not enforced either. CHECK constraints are rejected at parse time. While working on this post I found one more difference: in a self-referencing table (an employee and their manager) you cannot insert the parent and the child in the same multi-row INSERT, because the FK check sees the committed state rather than the batch itself. PostgreSQL checks at the end of the statement and accepts it. The fix is simple: insert the parent first, in its own statement.
Queries: joins, GROUP BY, CTEs, window functions
I added a couple more customers, orders and items (including Ece from Bursa, who has no orders yet) and moved on to queries. A five-table join with GROUP BY and HAVING:
SELECT c.name, COUNT(DISTINCT o.id) AS orders, SUM(oi.qty * p.price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.name
HAVING SUM(oi.qty * p.price) > 5000
ORDER BY revenue DESC; name | orders | revenue
--------+--------+----------
Zeynep | 2 | 20752.00
Ayşe | 3 | 15401.00
Can | 1 | 6300.00
(3 rows)The two decimal places are not an accident: DECIMAL is an exact type now, and it travels over the PostgreSQL wire as numeric in text form. INNER, LEFT, RIGHT, FULL and CROSS joins chain to any depth. Equi-joins run as hash joins, or as direct-address array joins for dense integer keys; non-equi ON conditions fall back to nested loops. Finding customers without orders is a classic anti-join:
SELECT c.name, c.city
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; name | city
------+-------
Ece | Bursa
(1 row)CTEs via WITH and window functions together. One query ranks each customer's orders by total, keeps a per-customer running total and an overall rank:
WITH order_totals AS (
SELECT o.id, o.customer_id, o.created_at, SUM(oi.qty * p.price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id, o.customer_id, o.created_at
)
SELECT customer_id, id AS order_id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running,
RANK() OVER (ORDER BY total DESC) AS overall_rank
FROM order_totals
ORDER BY customer_id, created_at; customer_id | order_id | total | rn | running | overall_rank
-------------+----------+----------+----+----------+--------------
1 | 1 | 2151.00 | 3 | 2151.00 | 6
1 | 2 | 7800.00 | 1 | 9951.00 | 2
1 | 11 | 5450.00 | 2 | 15401.00 | 4
3 | 4 | 16850.00 | 1 | 16850.00 | 1
3 | 10 | 3902.00 | 2 | 20752.00 | 5
4 | 5 | 6300.00 | 1 | 6300.00 | 3
(6 rows)ROW_NUMBER, RANK, DENSE_RANK and COUNT/SUM/AVG/MIN/MAX work over a window. Without an ORDER BY in the window an aggregate covers the whole partition; with one it is cumulative and peer rows (equal sort keys) share the value, which is the standard default frame. Explicit frames (ROWS BETWEEN ...) are not there yet.
Non-recursive CTEs are desugared into derived tables at parse time. A self-referencing CTE must have the shape anchor UNION [ALL] step and is materialized by fixpoint iteration in the executor: each step re-runs with the CTE bound to the previous iteration's rows. UNION dedups against everything seen so far, so cycles terminate on their own, and there are guards at one million iterations and ten million rows against runaway queries. An org chart:
WITH RECURSIVE chain(id, depth, path) AS (
SELECT id, 0, name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, c.depth + 1, c.path || ' > ' || e.name
FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT id, depth, path FROM chain ORDER BY path; id | depth | path
----+-------+-----------------------------
1 | 0 | Barış
2 | 1 | Barış > Elif
4 | 2 | Barış > Elif > Deniz
6 | 3 | Barış > Elif > Deniz > Mert
5 | 2 | Barış > Elif > Selin
3 | 1 | Barış > Kerem
(6 rows)The same mechanism generates a date series, with INTERVAL arithmetic working inside the recursion:
WITH RECURSIVE days(d) AS (
SELECT TIMESTAMP '2026-10-01'
UNION ALL
SELECT d + INTERVAL '1 day' FROM days WHERE d < TIMESTAMP '2026-10-04'
)
SELECT d FROM days; d
---------------------
2026-10-01 00:00:00
2026-10-02 00:00:00
2026-10-03 00:00:00
2026-10-04 00:00:00
(4 rows)LATERAL, DISTINCT ON and ordered-set aggregates
The three PostgreSQL idioms I reach for most in analytics are there too. "Each customer's latest order" has two answers. One is DISTINCT ON, the first row per key according to the ORDER BY:
SELECT DISTINCT ON (customer_id) customer_id, id, created_at
FROM orders
ORDER BY customer_id, created_at DESC; customer_id | id | created_at
-------------+----+---------------------
1 | 11 | 2026-10-09 08:00:00
3 | 10 | 2026-10-08 11:20:00
4 | 5 | 2026-10-02 12:00:00
(3 rows)The other is LATERAL: a derived table that may reference the rows to its left and is re-executed per left row. With LEFT JOIN LATERAL, Ece stays in the result even without orders:
SELECT c.name, last_o.id AS last_order, last_o.created_at
FROM customers c
LEFT JOIN LATERAL (
SELECT id, created_at FROM orders o
WHERE o.customer_id = c.id
ORDER BY created_at DESC LIMIT 1
) last_o ON TRUE
ORDER BY c.name; name | last_order | created_at
--------+------------+---------------------
Ayşe | 11 | 2026-10-09 08:00:00
Can | 5 | 2026-10-02 12:00:00
Ece | |
Zeynep | 10 | 2026-10-08 11:20:00
(4 rows)Wherever EF Core produces CROSS APPLY / OUTER APPLY, the provider renders exactly this [LEFT] JOIN LATERAL form. RIGHT and FULL LATERAL are rejected. For ordered-set aggregates there is mode() WITHIN GROUP:
SELECT mode() WITHIN GROUP (ORDER BY product_id) AS top_product,
COUNT(DISTINCT order_id) AS orders
FROM order_items; top_product | orders
-------------+--------
1 | 6
(1 row)On top of these: UNION/EXCEPT/INTERSECT (each with an ALL form and standard precedence), correlated subqueries at any depth, EXISTS, the VALUES table constructor, views (CREATE VIEW), CASE, COALESCE, NULLIF, LIKE, CAST, and string and math functions.
Date arithmetic
Because TIMESTAMP is epoch milliseconds, date arithmetic is plain and predictable. INTERVAL literals fold to millisecond integers, and ts ± interval and ts - ts both work. EXTRACT/date_part and date_trunc use UTC calendar math, ISO weeks (weeks start on Monday) and PostgreSQL's day-of-week numbering:
SELECT id, created_at,
created_at + INTERVAL '7 days' AS due,
date_trunc('week', created_at) AS week,
EXTRACT(DOW FROM created_at) AS dow,
(TIMESTAMP '2026-10-12' - created_at) / 86400000 AS days_ago
FROM orders ORDER BY id LIMIT 4; id | created_at | due | week | dow | days_ago
----+---------------------+---------------------+---------------------+-----+----------
1 | 2026-09-01 10:15:00 | 2026-09-08 10:15:00 | 2026-08-31 00:00:00 | 2 | 40
2 | 2026-09-14 18:40:00 | 2026-09-21 18:40:00 | 2026-09-14 00:00:00 | 1 | 27
4 | 2026-09-20 21:30:00 | 2026-09-27 21:30:00 | 2026-09-14 00:00:00 | 0 | 21
5 | 2026-10-02 12:00:00 | 2026-10-09 12:00:00 | 2026-09-28 00:00:00 | 5 | 9
(4 rows)The difference of two timestamps is in milliseconds, hence the division by 86,400,000 for days. One limit is on purpose: INTERVAL only accepts fixed units, and months and years are rejected, because "one month" is not a fixed number of milliseconds. For calendar-correct months there is add_months(ts, n), which clamps overflowing days and is what EF's AddMonths/AddYears translate to.
Interactive transactions
Transactions are interactive: BEGIN opens a transaction that lives across requests on the same connection. Its reads see its own buffered writes, and other connections see nothing until commit. Savepoints give partial rollback:
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 1000000 WHERE id = 2;
ROLLBACK TO SAVEPOINT bonus;
SELECT id, owner, balance FROM accounts ORDER BY id;
COMMIT; id | owner | balance
----+-------+---------
1 | Ayşe | 700.00
2 | Can | 550.00
(2 rows)The million-lira "bonus" is gone, the transfer stays. The commit is one atomic WAL batch and one fsync. When a statement fails, the transaction is poisoned PostgreSQL-style: later statements are refused and COMMIT answers ROLLBACK:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE 1
INSERT INTO accounts VALUES (2, 'Kopya', 0);
ERROR: duplicate key: PRIMARY KEY value Int(2) already exists in "accounts"
SELECT 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;
ROLLBACKThe engine itself is simpler than that: it rolls the transaction back at the error. The PostgreSQL session layer emulates the poisoned state because psycopg's with conn.transaction(): recovery depends on it.
To show how two connections see each other, and what row locks do, I wrote a short script with psycopg 3.3. This is stock psycopg; nothing in it is OxiDB-specific except the connection string:
import psycopg, time
DSN = "host=127.0.0.1 port=15401 user=admin dbname=oxidb"
a = psycopg.connect(DSN)
b = psycopg.connect(DSN, autocommit=True)
a.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
print("A sees:", a.execute("SELECT balance FROM accounts WHERE id = 2").fetchone()[0])
print("B sees:", b.execute("SELECT balance FROM accounts WHERE id = 2").fetchone()[0])
a.commit()
print("B after A commits:", b.execute("SELECT balance FROM accounts WHERE id = 2").fetchone()[0])
a.execute("SELECT * FROM accounts WHERE id = 1 FOR UPDATE")
t = time.time()
try:
b.execute("UPDATE accounts SET balance = 0 WHERE id = 1")
except psycopg.Error as e:
print(f"B blocked {time.time()-t:.1f}s, then: {type(e).__name__} [{e.sqlstate}] {e}")
a.rollback()
for sql in ["INSERT INTO accounts VALUES (1, 'x', 0)",
"INSERT INTO accounts VALUES (3, NULL, 0)",
"SELECT * FROM nope"]:
try:
b.execute(sql)
except psycopg.Error as e:
print(f"{type(e).__name__:22} {e.sqlstate}")
cur = b.execute("SELECT owner, balance FROM accounts WHERE balance > %s ORDER BY id", (600,))
print(cur.fetchall(), [d.name + ":" + str(d.type_code) for d in cur.description])A sees: 650.00
B sees: 550.00
B after A commits: 650.00
B blocked 5.0s, then: LockNotAvailable [55P03] lock timeout: row 1 of 'accounts' is locked by another transaction
UniqueViolation 23505
NotNullViolation 23502
UndefinedTable 42P01
[('Ayşe', Decimal('700.00')), ('Can', Decimal('650.00'))] ['owner:25', 'balance:1700']Every line tells you something. A sees its own write; B does not until the commit. SELECT ... FOR UPDATE takes a real row lock, and B's write waits out the 5-second lock timeout and fails with 55P03, so a deadlock turns into an error instead of hanging forever. SQLSTATEs map to psycopg's proper exception classes. The %s parameters are bound server-side through the extended protocol. And the DECIMAL column is described as OID 1700 (numeric) and arrives in Python as a Decimal.
To be honest about the concurrency model: this is not full MVCC. The row ids and AUTO_INCREMENT values an open transaction writes are reserved from the engine as it buffers each write, and uniqueness is re-checked against the committed state at COMMIT. If another writer took one of your keys while your transaction was open, the commit is refused with a duplicate-key error and nothing of it lands; retry the transaction. One side effect: failed statements consume AUTO_INCREMENT values too, just like PostgreSQL sequences. In the session above, three failed customer INSERTs burned ids 5, 6 and 7. That is why Ece got 8, and the customer I add over OxiWire in a moment gets 9.
Stored procedures: SQL and Cobra
There are two kinds of stored procedures. The first is the zero-toolchain path: SQL text. A named, parameterized batch of DML/SELECT, stored in the catalog and executed atomically:
CREATE PROCEDURE transfer(src INT, dst INT, amount DECIMAL) AS BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = src;
UPDATE accounts SET balance = balance + amount WHERE id = dst;
SELECT id, owner, balance FROM accounts WHERE id IN (src, dst) ORDER BY id;
END;
CALL transfer(1, 2, 125.50); id | owner | balance
----+-------+---------
1 | Ayşe | 574.50
2 | Can | 775.50
(2 rows)Parameters are rewritten into $N placeholders at creation, and SHOW PROCEDURES shows the stored form: UPDATE accounts SET balance = balance - $3 WHERE id = $1; .... A top-level CALL runs in an implicit transaction, so any failing statement rolls the whole call back. But the body has no control flow. Look at this:
CALL transfer(1, 99, 10);
id | owner | balance
----+-------+---------
1 | Ayşe | 564.50
(1 row)Account 99 does not exist. The second UPDATE touched zero rows without failing, and Ayşe's 10 lira evaporated. A SQL-text procedure cannot ask "does the account exist, is the balance enough?". That is exactly why ADR-0014 exists.
Cobra is a scripting language I wrote, with a bytecode compiler and a stack VM. In ADR-0014 I chose .cobrac bytecode as the stored-procedure interchange format. The compiler stays the Go cobra tool; inside the SQL engine there is a compact Cobra VM written in Rust (the oxidb-cobra crate) that only executes. The rejected alternatives are instructive: linking the Go runtime into the Rust process with cgo (GC, signal handling, double FFI on every call), a sidecar process (a CALL has to run inside the caller's transaction, and shuttling every statement over IPC is slow and fragile), and growing SQL procedures into a PL/pgSQL-style dialect (designing a second language when I already had a tested language and compiler in house).
The safe version of the same transfer:
# CREATE PROCEDURE safe_transfer(src INT, dst INT, amount DECIMAL) LANGUAGE COBRA
def run(db, src, dst, amount)
let src_rows = db.query("SELECT balance FROM accounts WHERE id = ?", [src])
let dst_rows = db.query("SELECT balance FROM accounts WHERE id = ?", [dst])
if len(src_rows) == 0 or len(dst_rows) == 0
throw "unknown account"
end
if src_rows[0]["balance"] < amount
throw "insufficient funds"
end
db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, src])
db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, dst])
print("moved", amount, "from", src, "to", dst)
return db.query("SELECT id, owner, balance FROM accounts ORDER BY id")
endI compile it to portable bytecode and send it as base64; 668 bytes of bytecode, 892 characters of base64:
cobra build --portable safe_transfer.cobra
B64=$(base64 -i safe_transfer.cobrac | tr -d '\n')
psql ... -c "CREATE PROCEDURE safe_transfer(src INT, dst INT, amount DECIMAL) LANGUAGE COBRA AS '$B64';"CALL safe_transfer(2, 1, 100);
NOTICE: moved 100.0 from 2 to 1
id | owner | balance
----+-------+---------
1 | Ayşe | 664.5
2 | Can | 675.5
(2 rows)
CALL safe_transfer(1, 99, 10);
ERROR: evaluation error: procedure "safe_transfer": line 6: uncaught throw: "unknown account"
CALL safe_transfer(2, 1, 5000);
ERROR: evaluation error: procedure "safe_transfer": line 9: uncaught throw: "insufficient funds"A few details. print output comes back as notices, and psql shows it as a PostgreSQL NOTICE. An uncaught throw rolls back the whole CALL, and the error carries the Cobra line number. db.query and db.execute run through the caller's store, so the procedure joins the CALL's transaction, and database errors are catchable in Cobra with try/catch. The return value is shaped into a result set: a dict becomes one row, a list of dicts a table, and a scalar a single value column.
Safety and clustering shaped the design too. The VM reaches the outside world only through the functions I register: no filesystem, network or process access. In cluster mode Raft replicates CALL as a write statement and re-executes it on every node, so the bytecode must be deterministic. The builtin allowlist therefore excludes time, randomness, I/O and parallelism, and unknown opcodes and builtins are rejected at CREATE, not mid-call. A non-catchable fuel limit of 100 million instructions bounds runaway loops.
An honest note: this bridge does not use an exact decimal type for Cobra values. SHOW PROCEDURES shows that the amount DECIMAL parameter is stored as amount DOUBLE, which is why the balances above come back as 664.5 without DECIMAL formatting. Worth knowing before writing money logic in a Cobra procedure.
Disk-first storage, the WAL and group commit
Now inside the engine. This is the on-disk layout, the real state of my data directory after the crash test at the end of this section (abridged):
data/sql/
MANIFEST {"version":1,"generation":11,"wal_seq":131}
wal/live.wal
gen.11/
catalog.json
customers.rdat customers.$pk.sidx customers.$uq2.sidx
events.rdat events.$pk.sidx
...- The WAL (
wal/live.wal) is the SQL engine's own log. The header starts with theOXSWmagic, and each record is[crc32][len][seq][payload]. Recovery reads records until it hits a torn tail (a write cut short by a crash) or a CRC mismatch; the valid prefix is everything read up to that point. - Snapshots: at checkpoint, each table's live rows are written to
gen.N/<table>.rdat, headerOXSR, records[row_id][len][crc32][payload]. A snapshot is always written whole (temp file, fsync, rename), so it is always a consistent point-in-time image. - Indexes (
.sidx) are a mmap'd, sorted, fixed-stride entry table. Not a B-tree, on purpose: every writer rewrites the file whole at checkpoint time, so there are no pages to split, no free-space map and no page-level concurrency to get wrong. A lookup is a binary search over a fixed-stride table, and a range scan is a lower bound plus a walk. - MANIFEST is the commit point. Committing a checkpoint is a single atomic step: a temp file, an fsync, and a rename over
MANIFEST. A crash before the rename leaves the previous MANIFEST, and so the previous intact generation, in force; the half-built generation is ignored at open and garbage-collected. Because catalog and snapshots switch together, recovery never sees a catalog whose column count disagrees with its snapshots.
Disk-first mode has been the default since 0.41.33. Table data is served from the mmap'd snapshot of the last checkpoint, with only post-checkpoint changes held in RAM. Rows, secondary indexes, primary keys and UNIQUE columns are all mapped files. According to the project's measurements (docs/pg-memory-benchmark.md), a warm 1.2-million-row database runs in about 39 MB, against roughly 370 MB for the same rows in resident mode, where everything sits in RAM. The price is paid on scans: disk-first decodes every cell, so scans run at 0.72 to 0.78 times PostgreSQL's speed, against about 1.0 in resident mode. Point and indexed queries are the same in both. The on-disk format is identical either way, so a database opens in either mode.
The thing that took me the most effort on the memory side was the row offset index: the structure that records where each row lives in the .rdat file used to cost about 33 bytes a row, which at 100 million rows would be 3.1 GB of anonymous memory. I made it sparse: one entry per 32 records, plus a short walk over record headers to reach a specific row. That is 0.69 bytes a row, about 69 MB at 100 million rows. Sequential readers use a cursor and never walk, so scans are unaffected. Two more details: secondary indexes are built on first use rather than at open, and a checkpoint hard-links the files of tables that have not changed since the previous generation instead of writing them again. Without that, every checkpoint rewrote the whole database however little had changed.
Group commit: concurrent writers share their WAL flush. A write appends its record and applies it under the engine lock, releases the lock, and only then fsyncs. One writer is elected to perform the flush, and everyone whose record was already on disk when it started is covered by it. The engine lock is never held across an fsync. The acknowledgement rule did not change: a statement returns to its caller only after a flush that covers its own WAL sequence. What is newly observable is that another connection can read a write in the window between its apply and its flush. A crash there loses it, but its writer never got an acknowledgement; PostgreSQL has the same window. According to the project's measurements (docs/wire-benchmark.md), throughput went from a flat ~266 writes/s at every concurrency level before group commit to ~1.2k/s at 16 connections after. The gain starts at about four concurrent writers.
Metadata-only ALTER TABLE
This one I wanted to measure myself. I loaded a one-million-row events table with psycopg, sending 100 multi-row INSERTs of 10,000 rows each (4.7 seconds on my local Mac), then added and dropped a column with \timing on:
SELECT COUNT(*) FROM events;
count
---------
1000000
(1 row)
Time: 13.230 ms
ALTER TABLE events ADD COLUMN region TEXT DEFAULT 'tr';
ALTER TABLE
Time: 3.275 ms
SELECT id, kind, amount, region FROM events WHERE id = 999999;
id | kind | amount | region
--------+--------+--------+--------
999999 | refund | 1498.5 | tr
(1 row)
Time: 0.125 ms
ALTER TABLE events DROP COLUMN region;
ALTER TABLE
Time: 4.367 ms
SELECT kind, COUNT(*), SUM(amount) FROM events GROUP BY kind ORDER BY kind;
kind | count | sum
--------+--------+-------------
refund | 333333 | 249750499.5
sale | 666667 | 499499500.5
(2 rows)
Time: 98.772 msAdding a column to a million-row table took 3.3 ms, dropping it 4.4 ms. That is because ADD COLUMN and DROP COLUMN are metadata-only, O(1) whatever the table size. The catalog stores the physical schema, while the executor sees a logical view. ADD appends a slot, and old rows read back padded with the new column's default. DROP tombstones the column in place with a dropped flag (like PostgreSQL's attisdropped); the physical cell stays and is projected out on read. Durability rides on a single WAL record. The real O(n) rewrite is folded into the next checkpoint, which rewrites every row anyway, and that is where the space is reclaimed. RENAME COLUMN and ALTER COLUMN ... TYPE still touch the rows; the latter dry-runs every row first.
Crash recovery
Finally, a crash. I inserted a row, killed the server with kill -9 and started it again:
$ psql ... -c "INSERT INTO accounts VALUES (3, 'Kaza Testi', 42.00)"
INSERT 0 1
$ kill -9 $(pgrep oxidb-server)
$ oxidb-server # restart, WAL replay
$ psql ... -c "SELECT * FROM accounts WHERE id = 3"
id | owner | balance
----+------------+---------
3 | Kaza Testi | 42.00
(1 row)The acknowledged write came back from the WAL. After the restart, with the one-million-row table and the document engine on, the process showed about 65 MB RSS on my local Mac.
The native protocol: OxiWire
The PostgreSQL wire is not the only way in. On the native OxiWire port (length-prefixed JSON, MsgPack or the OxiWire encoding), adding "engine": "sql" to the request envelope is all it takes. A raw request with nothing but the Python standard library:
import json, socket, struct
def call(sock, req):
body = json.dumps(req).encode()
sock.sendall(struct.pack("<I", len(body)) + body)
n = struct.unpack("<I", sock.recv(4))[0]
buf = b""
while len(buf) < n:
buf += sock.recv(n - len(buf))
return json.loads(buf){"engine": "sql", "cmd": "sql", "sql": "SELECT c.name, COUNT(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.city = $1 GROUP BY c.name ORDER BY c.name", "params": ["İstanbul"]}
{"ok": true, "data": [{"columns": ["name", "orders"], "types": ["TEXT", "INT"], "rows": [["Ayşe", 3], ["Can", 1]]}]}
{"engine": "sql", "cmd": "sql", "sql": "INSERT INTO customers (name, email, city) VALUES (?, ?, ?)", "params": ["Deniz", "deniz@example.com", "Bursa"]}
{"ok": true, "data": [{"affected": 1, "last_insert_id": 9}]}There is one result per statement. The types array carries the statically known column types (so ADO.NET's GetFieldType does not have to guess), and both ? and $N placeholders work. The separation of the two engines shows on the same socket: a request without an engine field goes to the document engine and creates a customers collection completely independent of the SQL table:
{"cmd": "insert", "collection": "customers", "doc": {"name": "doc-world"}}
{"ok": true, "data": {"id": 1}}
{"cmd": "count", "collection": "customers"}
{"ok": true, "data": {"count": 1}}At that point the SQL table holds 5 customers (ids 1, 3, 4, 8 and 9) and the collection holds 1 document. Same name, different worlds. The Python, Go, .NET and JS clients put this envelope behind a sql() method, and with the HTTP API enabled the same engine is reachable at POST /api/sql.
The PostgreSQL wire protocol: ADR-0023
The question ADR-0023 answers is: can OxiDB serve the PostgreSQL v3 protocol without disturbing its own, and what is the honest boundary of a first version? Three things already in the codebase made it cheap. Listeners were already independent (nine of them, including RESP, MQTT, AMQP and S3). SCRAM-SHA-256 already existed server-side. And the engine already had the shapes the protocol needs: $N parameters, interactive transactions parked between requests, one result per statement and statically known column types. So oxidb-server/src/pg/ gained a codec, auth, session, catalog, types and errors, and nothing in the OxiWire path changed.
Four decisions inside it are worth describing:
- A portal runs once, at Describe or Execute, whichever comes first. PostgreSQL can describe a portal without executing it. This engine reports a result's shape by producing it. Answering
DescribewithNoData(the honest "I don't know yet") broke psycopg, which reads its column metadata from exactly there. So a portal executes on first touch and buffers its output, andExecutestreams that buffer while honouring the row limit. The only visible difference is that an error surfaces one message earlier. - Session and catalog statements never reach the engine.
SET extra_float_digits = 3, which every client sends before it will talk at all,version(),current_database()and friends are answered bypg/catalog.rs.\dtand\lcome from the engine's own catalog. - SQLSTATEs first, messages second. Clients recover on the code, not the text, so every
SqlErrorvariant maps to the code PostgreSQL would use. - pg_catalog is answered, not executed. Recognised query shapes (the type catalog, JDBC's
DatabaseMetaDatacalls,\dt,\l, and whole-row reads of the catalog tables) are served from OxiDB's own schema. Everything else is refused with0A000. Returning an empty result would be tempting and wrong, because an empty result is believed. That was not theoretical: answering psql's\d <table>probe with the table list made psql fail on a column it expected, and that is how the refusal rule got written.
List of tables
Schema | Name | Type | Owner
--------+-------------+-------+-------
public | accounts | table | admin
public | customers | table | admin
public | employees | table | admin
public | events | table | admin
public | order_items | table | admin
public | orders | table | admin
public | products | table | admin
(7 rows)The docs list the clients verified against the real drivers: psql 18, psycopg 3.3, Npgsql 8.0, pgjdbc 42.7 (including DatabaseMetaData calls such as getTables, getColumns, getPrimaryKeys and getImportedKeys) and DBeaver 25.3 with its native PostgreSQL driver. My favourite lesson in the ADR: every prediction I made about which drivers would work was wrong until the drivers were actually run. Npgsql's observed failure (unsupported sql: function version()) pointed at a statement that was already handled; the real cause was a multi-statement batch mixing intercepted and engine statements. A logging proxy that dumped the exact bytes found it in minutes. Reasoning about it had not.
What is deliberately missing: COPY, LISTEN/NOTIFY, named server-side cursors (DECLARE ... CURSOR), query cancellation, real schemas (public.users and users are the same table), information_schema and pg_dump. Also, CREATE DATABASE runs on the native port, not on this one.
EF Core: server and embedded
On the .NET side, ADR-0013 built a full EF Core story in five phases: first the expression surface (CASE, LIKE, EXISTS, CAST, string functions), then interactive transactions, then an ADO.NET provider (OxiDb.Data, with Dapper running end to end), then DDL and types for migrations, and finally OxiDb.EntityFrameworkCore. The provider supports migrations (Database.Migrate() with a real __EFMigrationsHistory table), reverse engineering with dotnet ef dbcontext scaffold, and ExecuteUpdate/ExecuteDelete. As documented in the README, it passes the official EF Core relational specification suite, 3832 of 3832 across all twelve Northwind suites; the few known limits are explicit overrides in the test classes.
I tried it myself: a small .NET 10 console app built against the 0.44.2 NuGet packages in the repo:
using Microsoft.EntityFrameworkCore;
using OxiDb.EntityFrameworkCore;
var cs = args.Length > 0 ? $"Path={args[0]}" : "Host=127.0.0.1;Port=15400;Database=efshop";
using (var db = new Shop(cs))
{
db.Database.EnsureCreated();
var ada = new Author { Name = "Ada" };
var linus = new Author { Name = "Linus" };
db.Authors.AddRange(ada, linus);
db.Posts.AddRange(
new Post { Title = "SQL engine", Words = 4200, Author = ada },
new Post { Title = "Time series", Words = 3100, Author = ada },
new Post { Title = "Ledger", Words = 2800, Author = linus });
db.SaveChanges();
Console.WriteLine($"generated keys: ada={ada.Id}, linus={linus.Id}");
}
using (var db = new Shop(cs))
{
var q = db.Authors
.Select(a => new { a.Name, Posts = a.Posts.Count, Words = a.Posts.Sum(p => p.Words) })
.Where(x => x.Words > 1000)
.OrderByDescending(x => x.Words);
Console.WriteLine(q.ToQueryString());
foreach (var row in q) Console.WriteLine($"{row.Name,-6} posts={row.Posts} words={row.Words}");
var n = db.Posts.Where(p => p.Words < 3000)
.ExecuteUpdate(s => s.SetProperty(p => p.Words, p => p.Words + 500));
Console.WriteLine($"ExecuteUpdate touched {n} row(s)");
}
public class Author { public int Id { get; set; } public string Name { get; set; } = ""; public List<Post> Posts { get; set; } = new(); }
public class Post { public int Id { get; set; } public string Title { get; set; } = ""; public int Words { get; set; } public int AuthorId { get; set; } public Author? Author { get; set; } }
public class Shop(string cs) : DbContext
{
public DbSet<Author> Authors => Set<Author>();
public DbSet<Post> Posts => Set<Post>();
protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseOxiDb(cs);
}In server mode (after creating the database with CREATE DATABASE efshop on the native port):
connection: Host=127.0.0.1;Port=15400;Database=efshop
generated keys: ada=1, linus=2
SELECT "a"."Name", (
SELECT COUNT(*)
FROM "Posts" AS "p1"
WHERE "a"."Id" = "p1"."AuthorId") AS "Posts", (
SELECT COALESCE(SUM("p2"."Words"), 0)
FROM "Posts" AS "p2"
WHERE "a"."Id" = "p2"."AuthorId") AS "Words"
FROM "Authors" AS "a"
WHERE (
SELECT COALESCE(SUM("p"."Words"), 0)
FROM "Posts" AS "p"
WHERE "a"."Id" = "p"."AuthorId") > 1000
ORDER BY (
SELECT COALESCE(SUM("p0"."Words"), 0)
FROM "Posts" AS "p0"
WHERE "a"."Id" = "p0"."AuthorId") DESC
Ada posts=2 words=7300
Linus posts=1 words=2800
ExecuteUpdate touched 1 row(s)The correlated scalar subqueries EF generates run as they are, and generated keys come back through RETURNING. The tables EF created are ordinary SQL tables, so I can look at the same data from psql:
Title | Words
-------------+-------
SQL engine | 4200
Time series | 3100
Ledger | 3300
(3 rows)In embedded mode the only change is the connection string, UseOxiDb("Path=./embedded-data"), and the whole EF Core stack runs in-process with no server, SQLite-style:
connection: Path=./embedded-data
generated keys: ada=1, linus=2
Ada posts=2 words=7300
Linus posts=1 words=2800
ExecuteUpdate touched 0 row(s)I have to call out that last line: while working on this post I noticed that in embedded mode ExecuteUpdate reports 0 affected rows. The update itself is applied (when I opened the same data directory a second time, Linus's post had 3300 words), so this is a bug in how the embedded path reports the affected-row count. Server mode reports it correctly. I will fix it in the next release. As a third path, EF Core also works through the unmodified Npgsql provider over the PostgreSQL port (tests/efcore-npgsql-test in the repo).
Performance: the project's measurements
I did not measure the numbers below; they come from the project's own benchmark documents. docs/query-benchmark.md compares OxiDB and PostgreSQL on the same dataset (1.2 million rows over five related tables with foreign keys, composite primary keys and eight indexes). Because both speak the PostgreSQL wire, one hand-rolled client drives both servers and decodes every cell, so there is no driver difference and no language difference. PostgreSQL runs stock after ANALYZE, and answers were checked to be identical before anything was timed.
This table was measured in resident mode. In disk-first mode, point and indexed queries stay the same while scans drop to 0.72-0.78x, as described above. The most valuable part of that document, to me, is the "what still loses, and why" section. Joins went from 0.56x to 0.87x, but on the way there were two measured failures: raising the index-nested-loop threshold made it worse (0.29x), and the obvious BTreeSet membership test was worse still (0.22x). What finally worked was a direct-addressed bitmap over the integer key range, cheap enough to beat building a row. The first streamed-aggregate attempt also bypassed the index, turning count(*) WHERE customer_id = ? from a 25 µs lookup into a 4 ms full scan. The benchmark caught it immediately.
docs/sql-statement-benchmark.md measures the engine at the granularity most clients use it, one statement per request, and describes the six bottlenecks it found: a transaction's cost growing with its size, lookups inside a transaction returning the whole overlay, a join's driving table ignoring its index, and so on.
Limits and honest trade-offs
Collected in one place:
- No transactions spanning both engines. Document and SQL writes are each atomic on their own (ADR-0011 is deferred).
- Some constraints are not enforced: the uniqueness of
CREATE UNIQUE INDEX, multi-column table-levelUNIQUE, multi-column FKs andON UPDATEactions.CHECKis rejected, and there is noALTER TABLE ... ADD/DROP CONSTRAINT. - Query gaps: no explicit window frames, no
GROUPING SETS/ROLLUP/CUBE, noMERGE, noTRUNCATE.INSERT ... ON CONFLICTparses but its conflict clause is ignored. While trying things for this post I also found thatINSERT ... SELECTis not supported (unsupported sql: INSERT ... SELECT), which is why I loaded the million rows with multi-row INSERTs. - Self-referencing FKs do not accept parent and child in the same multi-row INSERT.
- Transaction model: not full MVCC. Uniqueness is re-checked at commit and a conflicting commit is refused. In a cluster that re-check is skipped, because a replicated commit has already been agreed by the cluster.
ALTER TABLE, view and procedure DDL cannot run inside a transaction. - PostgreSQL wire: no arbitrary
pg_catalogqueries, no\d <table>, noCOPY, noLISTEN/NOTIFY, no named cursors, nopg_dump. The listener is not started in cluster mode. - Cobra procedures see
DECIMALasDOUBLE. - Disk-first is about 30% slower than resident mode on scans. For scan-heavy analytics on data that comfortably fits in RAM,
OXIDB_SQL_DISK_FIRST=0is the right choice.
In cluster mode SQL writes replicate through Raft: the SQL text and parameters are appended to the Raft log and re-executed on every node. A lone COMMIT of an interactive transaction replicates its buffered writes as one atomic entry.
How it fits with the rest of OxiDB
In OxiDB the SQL engine is not an either-or choice. While documents, time series, the OxiMem key-value store, S3 and the messaging surfaces run in the same server, the data that should be relational sits in its own files under its own rules. The same authentication (SCRAM, roles, per-database grants) and the same multi-database model (ADR-0012) apply to both engines: each database has its own SQL engine, and both ports see the same data. And if SQL is all you need, OXIDB_DOC=0 turns the document engine off entirely and leaves you with a small PostgreSQL-compatible server.
Try it
You can download OxiDB as a ready-made binary from the GitHub releases, run the Docker image, or build it from the Rust workspace. The shortest path:
OXIDB_SQL=1 OXIDB_PG_PORT=5432 oxidb-server
psql -h 127.0.0.1 -p 5432 -U admin -d oxidbThen point any PostgreSQL client you have at it. If something does not work, the [pg] refused catalog query: ... line in the server log is exactly what to send me. The next post in the series moves on to the time series engine.