technology

One File: The SQLite Layout Both Runtimes Write

A Precomputing file is an ordinary SQLite file. Any SQLite tool opens it, the same SQL works on it whichever runtime wrote it, and it carries its own policy, so a reader never has to guess what is inside. This post walks through the layout for one stream.

What Is in the File

For Demo 1’s stream, latency, with the key endpoint and the value ms:

The file for the latency stream. You insert into the view latency and read the answer views requests, avg_ms and p99_ms. Behind them sit latency_raw with whole events, latency_win with one summary per window and key, latency_ms_sk with sketch buckets, latency_sample, latency_anomaly and latency_base, the state tables behind the answers, and three tables that describe the file: _precomputing with the policy, _precomputing_objects and, when the Engine writes, _precomputing_sources.

Name Kind Holds
latency view Where events are inserted. Reading it shows the raw events still kept
latency_raw table Whole events, for as long as raw keep says
latency_win table One summary per rollup length, window and key
latency_ms_sk table Quantile sketch buckets of ms, per rollup length, window and key
latency_sample table A few whole events per window
latency_anomaly table Unusual events kept whole, with their z-score
latency_base table The running baseline anomalies are judged against
requests, avg_ms, p99_ms views The ready answers, one per precompute, named as in the policy
_precomputing table The format, the compiler, the full policy and the distill statements
_precomputing_objects table Every object above with its kind and settings, as JSON
_precomputing_sources table Written by the Engine: for each sender, the last sequence number the file holds

Reading It With Plain SQL

The ready answers are views, so most questions are one line: SELECT * FROM p99_ms. Everything else is plain SQL over plain tables. A window summary holds, for each value, the count n, the sum, the sum of squares, the minimum, the maximum, the first and the last, so the mean of any window is ms_sum / n:

SELECT time(w, 'unixepoch') AS minute, endpoint, n, ms_sum / n AS avg_ms, ms_max
FROM latency_win WHERE res = 60 ORDER BY w DESC LIMIT 10;

A percentile over any stretch of time comes from the sketch buckets: add the buckets up, then find the one where the running count passes 99%. Bucket b holds the values whose logarithm falls in a slice about 2% wide, and it reads back as 2 * γ^b / (γ + 1), within 1% of every value in it. The p99 of /api/checkout for one hour:

WITH b AS (
  SELECT b, sum(n) AS n FROM latency_ms_sk
  WHERE res = 60 AND endpoint = '/api/checkout' AND w >= 1790589600 AND w < 1790593200
  GROUP BY b),
c AS (SELECT b, sum(n) OVER (ORDER BY b) AS cum, sum(n) OVER () AS tot FROM b)
SELECT 2 * pow(1.02020202020202, min(b)) / (1.02020202020202 + 1) AS p99
FROM c WHERE cum >= 0.99 * tot;

Windows That Merge

Summaries merge by adding counts and sums, taking the smaller minimum and the larger maximum, and keeping the first and last by time. Sketches merge by adding bucket counts. That rule is why sixty 1-minute windows give exactly the hour, why a late event can be added to a window long after it opened, and why the Engine and the triggers can both fill the same window. It is also what will one day let files from many machines add up to one answer.

Exact Streams and Logs Add a Few Tables

An exact stream, such as Demo 3’s usage, adds three tables: usage_ids with the ids counted within the repeat window, usage_clock with the newest event time the late and closed rules go by, and usage_refused with every refused event counted by reason and key. A quota adds a table of limits and a view with used, lim, remaining and reached.

A policy that reads logs adds _precomputing_templates, with every template learned: its number, service and level, its text with <*> where lines differ, the tokens it started from, its line count and its first line. Streams from logs keep the whole line in a line column beside their raw events, samples and anomalies.

Either Runtime, One Writer

The Engine writes every table exactly as the compiled triggers would, value for value, and the triggers are in its file too. So 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. One file should have one writer at a time. Readers can come and go as they like, in any tool.

A Format That Only Grows

Old detail leaves the file through the distill statements stored inside it, which delete raw events and windows that have outlived their keep. Anomalies, baselines, ready answers and refusal counts are never deleted. The file records the format it was written in, format 1 for version 0.1, and format 1 changes only by adding: a later release may add tables and columns, and will not change the meaning of the ones above.