Inside the Engine: Same Answers, 12 to 17 Times Faster
The SQL runtime does all its work inside SQLite: every insert runs a trigger that updates every summary, sketch and answer the policy asks for. That is simple and it runs anywhere, but a trigger pays SQLite’s price for each small update. The Engine runs the same policy in Go, keeps the recent work in memory and writes what changed to the same SQLite file in one transaction at every checkpoint. Its answers are the same, to the last bit, and on Demo 2’s trades it is 12 to 17 times faster.
precomputing put --policy trades.precompute --seq trades.db < trades.csv
precomputing get trades.db last_price
Sequence Numbers and Crashes
Each sender numbers its events from 1. The Engine remembers the last number it applied from each sender and skips any event whose number is not above it. The file records that number in _precomputing_sources, in the same transaction as the rows. So the rule for a sender is short: keep every event until a checkpoint acknowledges it, and after a crash, ask the Engine where the file stands and send again from the next number. Nothing is lost, and nothing is counted twice.
A crash in the middle of a checkpoint leaves no trace. SQLite rolls the unfinished transaction back when the file is next opened, and the file holds exactly the last complete checkpoint. The crash lab tests this on the native binary:
The lab feeds Demo 2’s trading day as CSV, kills the Engine with SIGKILL at random moments 40 to 300 milliseconds apart, and restarts it. Each time it checks that the file never holds less than the last acknowledgement, then resends from where the file stands. Of 100 kills, 45 landed in the middle of a checkpoint in one run and 42 in another. No acknowledged trade was lost, and the final file was identical to an uninterrupted run and to the compiled triggers: 156,320 rows and 1,296,775 values.
What It Keeps in Memory
Every event updates state held in memory: the window summaries of each rollup, the sketch buckets, the samples, the anomaly baseline of its key and the rows behind each precompute. A checkpoint writes everything that changed since the last one in a single transaction, together with where each sender stands, and then runs the policy’s distill statements. When a checkpoint returns, every event applied so far is in the file.
The Engine reads its file in three cases only. When it opens, it reads where each sender stands and the newest window of every key. When an event touches a window older than the newest one it knows, such as a late event, it reads that window. And the first time it meets a key’s baseline or a precompute row, it reads that row. Closed windows leave memory after the checkpoint that wrote them, so memory holds the open windows of active keys, one baseline per key and the current period’s precompute rows.
The Same Answers as the Triggers
Every update follows the compiled trigger step by step, with SQLite’s arithmetic in the same order. Integers stay exact integers as SQLite keeps them. Ties in minimum and maximum go the way SQLite’s min and max send them, and the first and last values follow the same rules. The one function whose last bit depends on the platform is the logarithm behind sketches and log anomalies, and the native Engine calls the same C library logarithm as the SQLite built into the same binary.
The tests check this directly. The same events go through the compiled triggers and through the Engine, with late events, zeros, price jumps and day boundaries among them, and the two files are compared table by table and value by value. They match. Small deliberate changes to the Engine’s rules, such as moving the last value of a window on a tie, make the comparison fail, so it is a real check.
The triggers are in the Engine’s file too. The SQL runtime can carry on in a file the Engine wrote, with plain INSERT statements, and the Engine can open a file the triggers filled and carry on from it. The tests hand a file over both ways and compare it with a file made by one runtime alone.
Three Ways to Run It
| Way | What it looks like |
|---|---|
| Command line | put reads events from standard input, as CSV, JSON lines or log lines, and prints ok SEQ after each checkpoint. get, stats and inspect read the file |
| HTTP | serve takes events with POST /v1/events and answers GET /v1/answers/NAME and read-only queries. Its reply comes once the events are in the file |
| Browser | The same Go code built for WebAssembly, 5.0 MB and 1.3 MB compressed. A small bridge gives it the page’s SQLite as its file; a checkpoint travels as one block of bytes |
Measured
On a two-core cloud server (Intel Xeon at 2.8 GHz):
| Run | Result |
|---|---|
| Demo 2’s trading day, 4,048,210 trades, into a file on disk with a full sync every 10,000 trades | 12 to 16 seconds in our runs, 258,000 to 337,000 trades a second, every closed candle identical to a recount |
| The first 200,332 trades through the Engine and through the compiled triggers | 450,000 to 570,000 against 26,700 to 37,000 trades a second, 15 to 17 times faster; the two files identical |
| Demo 3’s month of usage, 373,351 reports, with a full sync at every checkpoint | 2.3 to 3.3 seconds, 113,000 to 162,000 a second; about five times the triggers, because every request is still written whole |
| Demo 4’s two hours of log lines, 281,164 lines | 1.8 to 2.6 seconds, 108,000 to 156,000 lines a second, templates included |
| Demo 2 in Chromium | 170,000 to 220,000 trades a second while writing the file five times a second; back from a pulled plug in about 0.2 seconds |
The prototype includes the commands that repeat each row: one runs the trading day and checks it, another runs the crash lab.
Limits of 0.1
- One writer per file. Reads can run alongside, through a second, read-only connection that sees the last checkpoint.
- The Engine keeps one baseline per key in memory and reads the newest window of every key when it opens, so millions of keys cost memory and start-up time.
- The HTTP server has no authentication. It belongs on localhost or behind a proxy that has it.
- A file keeps the policy it was made with.
The market data case study follows the Engine through a trading day, pulled plug included.