# skaidb — complete reference for LLM agents This single file contains everything an LLM needs to manage a skaidb deployment and to write software that fully uses it. It is self-contained; the per-topic docs (SEARCH.md, TIMESERIES.md, VECTOR.md, CLUSTERING.md, GRAFANA.md, UI.md, QUERY_SYNTAX.md) go deeper but are not required. **What skaidb is:** a distributed, schema-less, SQL-speaking database in a single static binary. One engine serves relational rows (LSM storage), full-text search (embedded Tantivy, BM25), time-series (Gorilla-compressed samples with PromQL), and vector search (HNSW). Leaderless replication on a consistent-hash ring; quorum reads/writes, hinted handoff (in-memory → per-replica on-disk spill; self-expiring: `cluster.hint_max_age_secs` default 86400, per-replica `cluster.hint_max_disk_mb` cap default 1024, departed-replica logs deleted, drops counted on `skaidb_cluster_hints_expired_total` — anti-entropy covers what expired hints carried), read repair, anti-entropy, online resharding. Two binaries ship: `skaidb` (server) and `skaidbsh` (network SQL shell + admin client). --- ## 1. Connecting | Surface | Default port | Auth | Use for | |---|---|---|---| | Binary protocol (drivers, `skaidbsh`) | 7000 | SCRAM | sessions, prepared statements, batched executemany, pipelining, streaming | | REST `POST /query` | 7080 | HTTP Basic | one-shot SQL from anything that speaks HTTP | | ES-compatible REST subset | 7080 | HTTP Basic | existing ES clients / log shippers | | Prometheus `remote_write` + `/api/v1/*` | 7080 | HTTP Basic | metrics ingest + Grafana | | Web UI `/ui` | 7080 | HTTP Basic (login form) | humans: overview, SQL console, data inventory, admin | | `GET /metrics`, `/health`, `/ready`, `/status` | 7080 | none (`/status` optionally `MONITOR`) | probes/scrapers (read-only, secret-free) | Readiness contract: `/health` = process liveness, always 200 while the listener serves. `/ready` = "will serve reads and accept writes" — 503 (body names every reason) while the engine is unavailable, the node is shedding writes (memory overload, IWM state 2) or disk-blocked (state 3). `/ready?strict=1` additionally reports transitional states a rolling operation should wait out: resharding migration, resync backfill, and heavy maintenance rebuilds (search catch-up, index or vector backfill). WAL replay happens inside engine open, before the listener binds, so a replaying node fails the connect itself. Gate rolling upgrades on `?strict=1` plus drained hints (`/status` peers[] `hints_pending`). `skaidbsh wait-ready [--strict] [--timeout ]` polls it for scripts (exit 0 = ready). Version discovery (all three report the same string): `skaidb --version` prints one line `skaidb ()`; `GET /status` carries a top-level `"version"`; `/metrics` labels `skaidb_build_info` with `version`/`git_sha`. | MQTT broker (3.1.1 + 5.0, TCP/TLS/WebSocket) | 1883 / 8883 | username/password | IoT clients (Home Assistant, zigbee2mqtt, Tasmota, …); off by default (`[mqtt] enabled`) | | Internode | 7100 | configurable | cluster traffic (not for clients) | HTTP Basic verification is **memoized**: the SCRAM stored-key derivation is deliberately expensive (15k PBKDF2 iterations), and Basic auth is stateless, so without the cache every request would pay it (~46 ms of hashing for a `SELECT 1` that otherwise takes ~1 ms). The cache holds a pure (password, salt, iterations) → key mapping and the check still compares against the account's current verifier, so a password change takes effect immediately. High-rate REST users (Grafana scraping `/api/v1/query`, log shippers on the ES subset) pay no hashing tax per call. A **failed login never reveals whether the account exists**: an unknown username is answered with the same message as a wrong password (`authentication failed`) and runs the same key derivation against a decoy credential, so neither the wording nor the wall clock distinguishes the two. SCRAM server nonces come from the OS CSPRNG, and each credential carries a random salt drawn when the password is set (stored inside the verifier, so it replicates with it and survives restarts) — existing credentials keep authenticating unchanged and pick up a random salt at the next `ALTER USER … PASSWORD`. ```bash # SQL over REST — body is plain SQL, or JSON with an optional session db: curl -u user:pass -X POST http://node:7080/query -d "SELECT * FROM t LIMIT 5" curl -u user:pass -X POST http://node:7080/query \ -d '{"sql":"SELECT * FROM t","db":"mydb"}' # Response: {"columns":[...],"rows":[[...],...]} | {"affected":n} | {"ok":true} # | {"error":"..."} (HTTP 400) # Optional "consistency": "one" | "quorum" | "all" overrides the defaults # for this request. Reads at "one" answer from the coordinator's local # replica — bounded and fast (an indexed ORDER BY ... LIMIT n reads n rows), # may lag an in-flight write by a beat. # Bulk JSON document upsert (overwrites on primary key). Optional # "consistency": "one" | "quorum" | "all" overrides the write default for # this request — bulk loaders use "one" so the ack never waits on the # slowest replica (replication still reaches every replica via the async # tail; hints + anti-entropy backstop). curl -u user:pass -X POST http://node:7080/insert \ -d '{"db":"mydb","table":"t","rows":[{"id":"a","x":1}],"consistency":"one"}' # Response: {"inserted":n} | {"error":"..."} # Shell (nearest-node selection, failover, discovers peers via /status): skaidbsh --host node --port 7000 --rest-port 7080 [--user u --password p] # One-shot with machine output: --format csv (RFC-4180, header row, NULL = # empty cell) or --format json (NDJSON, NULL fields omitted). Row data ONLY # on stdout — DDL/mutation acks suppressed, errors on stderr — so # skaidbsh … -e "SELECT …" --format csv > out.csv composes cleanly. skaidbsh … -e "SELECT id, v FROM app.t WHERE v > 5" --format json ``` Key facts an agent must know: - **One statement per call.** No multi-statement bodies. `;` is allowed as a terminator but does not chain statements. - **The REST gateway is stateless**: `USE db` does not persist between calls. Pass `{"sql": ..., "db": "mydb"}` per request, or qualify names (`mydb.orders`). Binary-protocol sessions do keep `USE` state. - **REST row results stream** (chunked JSON, ~64 KiB at a time): no response-size cap and no response-sized buffer. Request bodies over 64 MiB → 413; sockets carry 30 s read / 60 s write timeouts, so a stalled client can't pin a handler. Bulk WRITES belong on the binary protocol. - **String literals use single quotes** (`'ada'`, escape by doubling: `'O''Brien'`). **Double quotes are identifiers** (`"weird name"`). Sending `"text"` where a string is expected is a common LLM error. - Rows are schema-less documents: any field not present reads as `NULL`; there is no column DDL to manage. **Python driver** (`drivers/python`, DB-API 2.0 / PEP 249, pure standard library, no dependencies). `apilevel = "2.0"`, `threadsafety = 1` (threads may share the module, **not** a connection), `paramstyle = "qmark"` (`?` placeholders). `__all__` exports exactly: `connect`, `Connection`, `ConnectionPool`, `pool`, `Cursor`, `Error`, `DatabaseError`, `OperationalError`, `ProgrammingError`, `Consistency`, `apilevel`, `threadsafety`, `paramstyle`. **There is no `Client` class in Python** — `Client::connect_*` elsewhere in this file is the **Rust** driver (`crates/skaidb-driver`). `InterfaceError` exists but is not exported. `skaidb.connect(...)` keywords (the auth keyword is **`user`**, not `username`): | Keyword | Default | Meaning | |---|---|---| | `host` / `port` | `"localhost"` / `7000` | single endpoint | | `user` / `password` | `"anonymous"` / `""` | SCRAM credentials | | `consistency` | `Consistency.QUORUM` | `ONE`=0, `QUORUM`=1, `ALL`=2; also accepts the name as a string | | `timeout` | `10.0` | default for both dial and reads | | `connect_timeout` / `read_timeout` | `None` | override each half of `timeout` independently — a read timeout can sit above the server's statement timeout without also slowing dial failures | | `database` | `None` | issues `USE ` as part of connecting | | `seeds` | `None` | `["h1:7000", "h2", ...]`; entries may carry their own port, else `port` | | `tls` | `False` | TLS for the binary port; **also implied** by passing `tls_ca` or `tls_insecure` | | `tls_ca` | `None` | CA file verifying the server cert (the cluster `ca.crt`); without it the system trust store is used | | `tls_insecure` | `False` | skip verification entirely — **dev only** | | `tls_server_name` | `"skaidb"` | SNI / SAN to verify | ```python import skaidb conn = skaidb.connect(seeds=["n1:7000", "n2:7000", "n3:7000"], user="app", password="secret", database="mydb", tls=True, tls_ca="ca.crt") cur = conn.cursor() cur.execute("INSERT INTO people (id, name, tags, meta) VALUES (?, ?, ?, ?)", (1, "ada", ["math", "eng"], {"team": {"name": "core"}})) cur.execute("SELECT id, name FROM people WHERE id IN (?)", ([1, 3],)) for row in cur: # or cur.fetchall() / fetchone() / fetchmany(n) print(row) ``` - **Seeds are tried in shuffled order** — skaidb is leaderless, any seed serves any request, and shuffling stops a client fleet from all opening on the first entry. Failover applies at connect time. - **Typed binding, no SQL literal form needed**: a `list`/`tuple` binds as an Array, a `dict` as a nested Document (keys must be strings, else `ProgrammingError`). Binding an array to `IN (?)` is the set-membership idiom. `datetime` → Timestamp, `uuid.UUID` → Uuid. - **Cursor**: `execute` (returns the cursor, so `.execute(...).fetchall()` chains), `executemany`, `fetchone`/`fetchmany`/`fetchall`, iteration, `set_consistency`, `close`, context manager. `Connection.execute` is a shortcut that makes a cursor for you. `executemany` prepares once and ships every parameter row in **one round-trip** (`ExecuteBatch`), falling back to a per-row loop for unpreparable statements or a server that predates the opcode; a row with the wrong parameter count raises `ProgrammingError` naming the row index. Server-side, plain-INSERT batches (no `ON CONFLICT`) are SPLICED into multi-row statements in 500-row chunks — one write-path traversal per chunk instead of per row — so `executemany` is the genuinely fast bulk-insert path, not just a round-trip saver; other statements keep per-row execution. - **`Connection.stream(sql, consistency=None)`** yields rows one at a time over `OP_QUERY_STREAM`, holding one chunk instead of the whole result — for exports and large scans. It takes **no parameters** (the opcode carries SQL text), and the connection is busy until the generator is exhausted or closed, so run nothing else on it meanwhile. Against a server too old to know the opcode it raises `ProgrammingError`. - **Transactions are SQL, not methods**: `conn.commit()` is an accepted no-op (each statement autocommits) and `conn.rollback()` **raises** `OperationalError`. Use `BEGIN` / `COMMIT` / `ROLLBACK` as statements on one connection — see §3. - **Pooling**: `skaidb.pool(maxsize=10, **connect_kwargs)` → `ConnectionPool`; every `connect` keyword passes through, so pooled connections inherit seed failover. Use the `.connection()` context manager (`getconn`/`putconn` underneath); connections are validated on checkout/checkin and discarded if a transport error left them broken. The pool is thread-safe even though a single connection is not. - **TLS**: node certificates carry a single SAN, `DNS:skaidb`, which is never the address you dial — hence `tls_server_name` defaulting to `"skaidb"`. The driver verifies the cert against that name, so dialing by IP is fine; HTTP clients that verify hostnames themselves need `--resolve skaidb:7443:` to reach the REST port. - **Install**: **not published on PyPI** — `pip install skaidb` does not work. From a checkout: `pip install ./drivers/python`, a path dependency (uv `[tool.uv.sources]`), or vendor the `skaidb/` package next to your code — it is one pure-stdlib module with zero dependencies. Every other driver (`go`, `java`, `nodejs`, `php`, `ruby`, `dotnet`) speaks the same binary protocol and the same SQL; see `drivers//README.md` and `docs/HOWDOI.md` for per-language equivalents of the above. --- ## 2. Data model, types, expressions - **Table** = documents keyed by a declared primary key (single or composite). `CREATE TABLE t (PRIMARY KEY (id))` is the whole schema. - **Types** (dynamic): `null`, `bool`, `int64`, `float64`, `decimal`, `string`, `bytes`, `uuid`, `timestamp` (Unix ms), `array`, `document` (nested). SQL literals exist for int, float, string, bool, null, constant arrays (`[0.1, -0.2]`), and constant objects (`{name: 'ada', addr: {city: 'x'}}` — quote reserved-word keys: `{'from': 1}`); the other types arrive via stored data or bound params. `SET meta.addr = {…}` replaces that whole sub-document; dotted-path `SET` updates one scalar leaf. - **Paths**: dotted paths reach nested fields everywhere — `address.city`, in projections, WHERE, GROUP BY, ORDER BY, UPDATE SET, and index declarations. - **Duration literals**: `250ms 15s 5m 2h 30d 1w` — integers in ms, usable wherever an integer is (`WHERE ts >= now() - 1h`). - **Operators** (rising precedence): `OR`; `AND`; `NOT`; comparisons `= != <> < <= > >=`; `IS [NOT] NULL`; postfix `[NOT] IN (v, ...)` / `[NOT] BETWEEN lo AND hi` / `[NOT] LIKE|ILIKE pat`; `+ -`; `* /`; unary `-`; parens. Three-valued logic: `NULL` comparisons are unknown. `in/between/like/ilike` are contextual (still valid column names). - **`BETWEEN`**: inclusive range, sugar for `>= lo AND <= hi`; literal bounds join index/PK range pushdown like the two comparisons would. - **`LIKE` / `ILIKE`**: exact substring/prefix match (`%` any run, `_` one char, no escape); `ILIKE` folds case. Non-string operands → unknown, not an error. Residual filter (no index acceleration) — complements analyzed `MATCH()` word search; same scan-budget caveat as `IN` on large scans. - **`IN` / `NOT IN`**: `x IN (a, b, c)` set membership (≥1 element; `IN ()` errors). An array-valued element is flattened, so `WHERE id IN (?)` bound to `[1,2,3]` tests membership in that set — the "fetch these N ids" pattern, and the native replacement for the old `$in`→OR-chain. Array columns match by containment (like `=`). **PK-pinned `IN` is a point-read set**: every PK column pinned by `=`/literal-`IN` → one point read per candidate key (≤1000; composite keys cross-multiply), replica-routed on a cluster (EXPLAIN: `point-read set`). Non-PK / `NOT IN` shapes stay a residual filter and can hit the scan budget on large unindexed scans. - **Scalar functions**: `now()` (statement start, timestamp), `if(cond, then [, else])` + `nullif(a, b)` — CONDITIONAL AGGREGATES in one pass, since COUNT ignores NULLs: `count(if(status >= 400, 1, NULL))` beside `count(*)` gives an error count and a total in ONE scan (else defaults to NULL; a NULL/non-boolean condition takes the else branch rather than erroring), strings `lower(s)` `upper(s)` `trim(s)` `length(s)` (chars; array → elements) `substr(s, start [, len])` (1-based) `concat(a, b, ...)` (NULLs skipped) `starts_with(s, p)` `ends_with(s, p)`, numbers `abs(n)` `floor(x)` `ceil(x)` `round(x)` (float → int); all deterministic (CHECK / generated / expression-index safe); wrong-typed argument → NULL, `time_bucket(step, ts)` (floor to bucket: `time_bucket(5m, ts)`), `to_timestamp(v)` (epoch-ms number or ISO-8601 string → timestamp; unparseable/mistyped → NULL — range-filter string timestamps in-query), and `CAST(x AS INT|FLOAT|STRING|BOOL|TIMESTAMP)` (desugars to `to_int`/`to_float`/`to_string`/`to_bool`/`to_timestamp`, same NULL-on-unconvertible policy; timestamps stringify as ISO-8601). - **Geospatial**: `geo_distance(point, lat, lon)` → haversine metres; `geo_bbox(point, min_lat, min_lon, max_lat, max_lon)` → bool (min_lon>max_lon crosses the antimeridian). `point` is a `{lat,lon}` object or `[lat,lon]` array; non-point/NULL → NULL. Use in `WHERE geo_distance(loc,..) <= ` and `ORDER BY geo_distance(loc,..) LIMIT k` (nearest-first). `CREATE GEO INDEX ON ()` makes both predicates prune via a Morton/Z-order index (transparent — no query change; `geo_bbox` boxes and `geo_distance <= r` radii route to code-range scans, exact-filtered on re-read — an antimeridian-crossing box/radius splits into two covered halves); without one they scan. Broadcast DDL, self-maintaining, on-disk (no rebuild on restart), cluster-wide via the secondary-index scatter. `distance('5km')` → metres (unit literal: m/km/mi/yd/ft/NM/…; constant, index-prunable radius). See [GEO.md](GEO.md). - **`SELECT ` without FROM**: constant projection, one row (`SELECT 1` = liveness probe; needs no privilege). `*` and other clauses still require a table; a FROM-less leg works inside UNION. - **PK point reads & prefix slices**: a full composite-PK equality (`channel = ? AND ts = ?` on PK `(channel, ts)`) is a single bloom-gated point read — even for an absent key (no full-table scan). A leftmost equality *prefix* (plus one trailing range on the next PK column) scans only that key slice — `WHERE channel = ?` reads one channel, `AND ts >= ?` narrows it. A **range alone on the LEADING pk column** slices too: `WHERE id >= ? AND id < ?` on `PRIMARY KEY (id)` seeks instead of scanning, so a time-sortable PK (ULID/snowflake) serves time ranges — including range AGGREGATIONS (`GROUP BY`, `count`). Bounds are conservative (`>` widened to `>=`) and the full filter is re-applied per row, so results are exact. This holds on CLUSTERED reads too (SELECT and COUNT at any consistency): the coordinator and every member narrow their shard walks to the pinned slice, instead of each full-scanning its shard to push the filter down. EXPLAIN reports it as `primary-key prefix range`. No secondary index needed for shapes the primary key orders: `ORDER BY LIMIT k` (optionally with a pk range — keyset pagination: `WHERE id > ? ORDER BY id LIMIT ?`) walks the table in key order with early stop, at ONE and QUORUM (k ≤ 10000) alike, with bounded memory. **`DESC` costs the same as `ASC`**: the walk seeks the range's upper bound and pages BACKWARD, so "latest N" — `ORDER BY id DESC LIMIT n`, a trace/log UI's default view — examines ~n rows. An exact single-key `ORDER BY LIMIT k` keeps a bounded top-k instead of gathering every row — O(k) memory, though still a full-table scan's worth of work. - **ORDER BY**: a multi-key `ORDER BY` whose leading key is indexed walks the index bounded by LIMIT plus the leading-key tie group, then re-sorts by the full clause — exact, without gathering every matching row. When a strictly more selective equality index also covers the filter, the planner probes its range first (capped peek): if it holds ≤256 candidates it gathers and sorts those instead — a filter matching (almost) nothing answers through the index instead of walking the whole sorted range finding nothing. - **DISTINCT**: `SELECT DISTINCT ` streams the value set (no row materialization; array columns dedupe as whole arrays). At consistency "one" on a full-copy cluster it is a single local pass. - **Consistency ONE on a full-copy cluster is a single LOCAL pass** for every read shape: point reads, `COUNT(*)`, filtered counts, DISTINCT, ordered reads, and plain and grouped row gathers (`GROUP BY`, `time_bucket`). Analytics/dashboard reads over a full-copy cluster are the intended users; the trade is this replica's view, which may lag an in-flight write by a beat. - **Memory tables**: `CREATE TABLE t (...) WITH (memory = true)` — RAM-only (no WAL fsync, never flushed, empty on restart, excluded from repair); pair with `ttl`. `SHOW STATUS` table counts are approximate version counts (exact after compaction). - **Scan budget**: one statement may examine at most `storage.scan_row_budget` rows (default 250k; 0 disables), materialize at most `storage.scan_byte_budget` bytes into a result set (default 256 MB; 0 disables), and run at most `storage.statement_timeout_secs` (default 120s; 0 disables) — past any of them the statement errors. The deadline binds waits too: a statement parked on the engine lock past its deadline fails the moment it acquires, rather than running to completion however long the wait took (150 s statements against a 120 s timeout, once). it errors with `resource limit: ...`. **Setting `storage.memory_target` (`"auto"` / `"512MB"` / `"2GB"`) derives BOTH scan budgets from the node's own cgroup-aware memory limit** instead of fixed constants, so a 320 MB container and an 8 GB server do not share one ceiling; an 8 GB node on `"auto"` lands on exactly the default 256 MB / ~250k. **Streamed `COUNT`/`DISTINCT` are exempt from the ROW budget** — they retain nothing per row, so they are bounded by the byte budget and the deadline instead. On a clustered gather the row budget counts each DISTINCT row key once, however many replicas deliver a copy — an RF=3 full-copy gather of a 183k-row table costs 183k, not 3×183k. `LIMIT` bounds output, not scan work: a filter matching nothing under `ORDER BY .. LIMIT` walks the whole range. The row budget bounds work; the byte budget bounds MEMORY — a scan of many multi-KB rows can stay under 250k rows yet gather gigabytes on the coordinator — enough to OOM a small node. The byte budget caps BOTH the finalized result AND the coordinator's in-flight gather buffer. The clustered gather PACES its sources against that buffer: once the un-finalized merge buffer crosses half the byte budget, only the source at the slowest cursor is pulled until finalization drains it, so a full-copy scan where one replica lags (or where the local shard outruns peer pages) stays bounded and COMPLETES — a retain-nothing shape (`COUNT`, `DISTINCT`, streamed `GROUP BY`) over any table size runs at any consistency in O(groups) coordinator memory. Only a genuinely un-drainable buffer (a single page larger than the budget) still errors (`scan gather buffer exceeded N resident bytes`) instead of OOMing. Streaming `COUNT`/`DISTINCT` retain nothing and are never charged bytes; for retaining scans add a `LIMIT`, narrow the projection/filter, or raise `storage.scan_byte_budget`. A gather source that answers `busy: engine write-locked` (live-ingest write lock held at that instant) is retried in place briefly rather than dropped; a source that stays busy or unreachable is dropped, and if too few sources remain the statement fails `read quorum not met: R/N members responded (: ; …)` — the per-source causes name why. Before that error, every unanswered peer gets one **breaker-bypassing probe** (bounded 2 s): a peer suspended by its circuit breaker — the state a just-restarted or briefly-`busy` member is in on every coordinator until the cooldown passes — is otherwise indistinguishable from a dead one, which failed QUORUM reads on clusters with every member up. The probe runs only when the read would otherwise fail, and a success closes that peer's circuit for all callers. Point reads and time-series gathers take the same second chance. - **Aggregates**: **grouped aggregations STREAM when every aggregate is foldable** (`COUNT`/`SUM`/`AVG`/`MIN`/`MAX` with plain args): rows fold into O(groups) per-group state as the gather produces them instead of materializing O(rows), so a `GROUP BY` over a window larger than `scan_row_budget` answers — embedded, and on a cluster **at any consistency** (QUORUM streams too, via the same access-path ladder `SELECT` uses with a fold sink on the branches that can be unbounded, so a shape that prunes for a row gather prunes identically here — not a second, independently-reasoned plan). **LWW ties are deterministic**: an exact cross-node HLC stamp collision resolves by one rule everywhere (Put beats Delete, then larger value bytes — `skaidb_storage::lww_wins`), applied at the memtable, the LSM merge, the coordinator gather and point reads. **UNIQUE indexes**: `CREATE UNIQUE INDEX i ON t (paths)` — cluster-wide (reservation keyed by value, claimed via the per-key consensus round → exactly one winner under races); NULL/absent unconstrained; distinct `unique violation` error; freed by update-away/delete; DDL refuses over existing duplicates; not combinable with global. Every write path pays the claim — multi-row INSERT goes row by row (a colliding row fails the statement; rows before it stay, as on standalone). **Array functions**: array_append / array_append_distinct / array_remove / array_set(i errors OOB) mutate list fields inside UPDATE/upserts (linearizable per row; NULL base = []); array_length / array_contains usable in WHERE. **INSERT ON CONFLICT DO NOTHING / DO UPDATE SET** upserts atomically through the same per-row consensus round (merge sees old columns bare, as `.`, and the incoming row as `excluded.`; a real column named after its own table wins over the qualified form; plain INSERT stays replace; toast/gidx/unique-indexed tables best-effort read-merge-put — the unique VALUE claim stays consensus-serialized). **Clustered UPDATE is linearizable per row**: each touched row goes through a per-key single-decree Paxos round among its replicas (ballot = (hlc, node) — HLC alone is NOT unique across nodes; commit = plain LWW put at the value's origin ballot carrying a reserved-field commit history as exactly-once evidence; durable promise/accept state in a node-local `/paxos` engine). Concurrent `SET n = n + 1` counts exactly (100/100 + prod-shape 20x25 harness runs). PK-changing assignments and INSERT/DELETE stay LWW (documented caveat); toast/gidx/unique-indexed tables fall back to the plain path (for unique tables the fallback IS the enforcement path: put runs the claim/release legs; UPDATE into a taken value rejects, moving off a value frees it, multi-row self-collisions reject whole pre-write). The fold streams **at the storage layer too**: the PK-range branch scans via the streaming k-way merge iterator, never collecting the whole key slice into RAM first. Two per-row costs on that path are also avoided: the projected row decode allocates field-name strings only for KEPT fields (skipped fields are compared as borrowed bytes), and when the filter is provably identical to the key slice — a conjunction of inclusive (`>=`/`<=`) string comparisons on the leading PK column, the ULID-dashboard shape — the per-row residual filter re-evaluation is skipped entirely (`pk_prefix_scan_range_exact`; any shape it cannot prove exact keeps the conservative bounds + per-row re-check). Large streamed folds also **parallelize**: when the plan is a pure range walk (unindexed full scan or PK-prefix slice; no LIMIT early-stop, no open transaction, no index/geo/point plan) and the range overlaps ≥16 MB of stored data, the scan splits at SSTable block boundaries (weighted by block bytes, un-flushed memtable versions included so the newest key region doesn't pile onto the last chunk) into up to `[storage] parallel_scan_threads` contiguous chunks folded on scoped worker threads — per-partition group states merge in key order, so first-seen group order, representative rows, and min/max ties answer exactly as the serial fold (float sums may re-associate, same as the cluster's cross-member aggregate merge); scan budgets, the statement deadline, and `KILL QUERY` bind across workers through shared meter counters. Small ranges, LIMIT shapes, and index-served filters stay serial. At the storage layer, every scan — range and point alike — goes through a per-table **decompressed-block cache** (byte-budgeted; default 16 MB, sized to `memory_target/8` clamped [4 MB, 1 GB] when a memory target is set; ONE cache per NODE shared by every table and all their SSTable files, so the budget bounds the node's resident decompressed bytes in aggregate — per-table budgets were individually legal but summed to 1.7-2.6 GB on prod nodes — compaction inputs bypass it, retired files' entries purge on drop), so a re-queried window serves its decrypt+decompress work from RAM; `skaidb_block_cache_bytes` (+hits/misses/evictions, also in `SHOW STATUS`) reports residency; and sequential scans read the file through a 256 KB **readahead window** — one pread covers many adjacent blocks instead of one syscall each. The SSTable block size defaults to 16 KB (`[storage] block_target_kb`, engine option `block_target_bytes`) — the measured knee of the scan-vs-point-read tradeoff (vs 4 KB: +18% scan throughput, 15% smaller files, −32% CPU-bound cold point reads, and the next doubling bought only ~3% more scan for another halving of point reads) — applied as data is flushed/compacted, so a change rolls through existing files gradually with no migration. **Value-TOAST**: `ALTER TABLE t SET (toast_threshold = )` stores big top-level fields out-of-line in a hidden `__toast__` companion table (replicated, resynced and witness-mirrored like any table); the row keeps a reserved-name manifest, scans that skip the field skip a ~20-byte marker instead of the payload, and reads that want it resolve transparently at any consistency. New writes only; indexed/PK fields never toast; indexing a possibly-toasted field is refused. See docs/QUERY_SYNTAX.md. **Range partitioning**: `CREATE TABLE t (...) WITH (partition_by = 'range(, )')` (intervals `15min`/`1h`/`1d`/`1w`) buckets rows into hidden per-interval child tables — WHERE bounds on the column give PARTITION ELIMINATION (the scan budget counts post-pruning rows), `ttl` drops whole expired partitions O(1) (retention is partition-granular: rows live up to one interval past a pure row-ttl; fully-expired partitions never serve reads), late rows land in their value's partition (no OOO horizon), and duplicates on the column are first-class (identity stays the PK — keep the partition column immutable per key, it is placement). CLUSTERS: parent DDL replicates; every member lazily creates the same deterministic children as writes apply (no child-DDL broadcast); retention = replicated DROP TABLE from the flusher (~5 min cadence), reads hide expired partitions regardless. CLUSTERING: `WITH (cluster_by = (c1[, c2…]))` — storage key AND identity = (cluster…, pk), Cassandra-style. Clustering-prefix ranges SEEK (budget counts only matched rows); ORDER BY a prefix walks sorted; same-tuple re-ingest overwrites (idempotent); same pk at a different clustering value = NEW row; UPDATE moving a clustering value re-keys (put-new-delete-old); bare-pk lookup = scan (index the key column for needles); clustering cols non-null, unrenameable, disjoint from pk; no distribute_by/toast combos; composes with partition_by (partition col at the clustering head). CREATE-only. EVENT ROLLUPS: `CREATE ROLLUP r ON t BUCKET 1h [BY (dims)] AGGREGATE (count(*) AS n, sum(c) AS total, …) [REFRESH dur] [RETENTION dur]` — a real bucket table (`PRIMARY KEY (bucket, dims…)`) pre-aggregating a partitioned/clustered source; query `SELECT bucket, n FROM r WHERE bucket >= …` instead of scanning raw events. Bucket column inferred (partition col, else clustering head; neither = refused). Background pass (~1 min; clusters: one owner per rollup) RECOMPUTES the trailing REFRESH window (default 2 buckets) from raw and upserts — replaces, never increments, so retries/handovers/replicas converge; first pass populates full history; rows later than the window don't update their bucket. RETENTION ages out rollup rows independently of the source ttl. NULL bucket/dim groups are skipped; source drop cascades; rollup-of-rollup refused; aggs: count/sum/min/max only (avg = derive sum/count). ADOPTION: `ALTER TABLE t SET (partition_by = 'range(col, 1d)')` on an EXISTING table — directory-rename into a hidden legacy partition (no copy), background drain relocates rows (original HLCs, idempotent), reads union children+legacy throughout, writes retire the pre-adoption copy, legacy drops when empty (replicated drop on clusters after a merged emptiness check). Clusters drain ONLINE per-node; embedded/standalone complete within the ALTER. Rows missing the column stay in legacy (served, SHOW PARTITIONS NULL bounds). Requires shedding indexes/streams first (recreate after). SECONDARY INDEXES on partitioned parents fan per-partition physical indexes (template on the parent; new partitions inherit; DROP fans; needle lookups prune through them). `SHOW PARTITIONS ` lists range/rows/disk/serving per partition; EXPLAIN adds partitions_total/scanned/pruned. UNIQUE+global indexes, search/ vector/geo, streams/triggers/memory/toast/distribute_by are refused loudly, as is BEGIN ATOMIC PARTITION; AFTER keyset paging says to page with a WHERE range instead. Per-group state is charged to the byte budget as groups are created, so `GROUP BY ` still meets the memory guard. Non-foldable aggregates (`PERCENTILE`, `COUNT(DISTINCT …)`, ts-only funcs) keep the materializing path at every consistency — the guard is scoped, not disabled. `COUNT(*)`, `COUNT(expr)`, `COUNT(DISTINCT expr)` (exact), filtered `COUNT(*)` is answered index-only when a secondary index fully covers a conjunctive equality/range filter (no row reads — safe on tables of any size). Row-producing shapes (`GROUP BY`, `time_bucket`, plain `SELECT`) resolve a candidate key set first, and the merge is bounded by that set's key span. One NULL-safe negated equality (`col != v OR col IS NULL`) beside a covering conjunction counts by complement (two index-range cardinalities); a filter an index BOUNDS but does not cover counts its candidate slice rather than the table; other filtered counts stream with bounded memory (at consistency "one" on a full-copy cluster: a single local pass, like DISTINCT), `APPROX_COUNT_DISTINCT(expr)` (opt-in HLL on the search pushdown, exact everywhere else), `SUM`, `AVG`, `MIN`, `MAX`, `PERCENTILE(expr, p)` (exact percentile_cont, p a literal fraction in (0,1], row-gather path only — no pushdown/partials); time-series only: `RATE`, `INCREASE`, `DELTA`, `FIRST`, `LAST`. - **Window functions**: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `LAG(expr[, offset[, default]])`, `LEAD(…)` with `OVER ([PARTITION BY …] [ORDER BY …])` — SELECT-list only (alias + `ORDER BY alias` to sort by one), no mixing with `GROUP BY`/aggregates. Computed at the coordinator over the FULL filtered set before outer `ORDER BY`/`LIMIT` (a windowed query therefore gathers every matching row — no top-k truncation); ties and missing window `ORDER BY` break by input position. - **`GROUP BY` memory**: a plain `GROUP BY`/aggregate query (no `TOP k BY`, `*`, join, or set op) decodes only the columns the filter, grouping, aggregates, `HAVING`, and `ORDER BY` actually reference — not every column of every matching row — so grouping on one or two fields of a wide/large-document table costs roughly what those fields alone would, regardless of how large the other columns are. `GROUP BY ... TOP k BY` returns whole rows per group and does not get this — it still materializes every selected column. - **Bind parameters**: `?` in prepared `SELECT/INSERT/UPDATE/DELETE` (binary protocol / drivers), including `LIMIT ? OFFSET ?` (non-negative integer), `NEAREST`'s query/k, and `EXPLAIN ` (explain the exact bound query). Values bind as **typed** values, so `?` can carry an array or nested document (e.g. Python `list`/`dict`) that has no SQL literal form — including `WHERE id IN (?)` bound to an array. Not on the one-shot REST path. **Not in the language**: subqueries, CTEs, window functions, `FULL OUTER JOIN`, `INTERSECT`/`EXCEPT`, `ADD/DROP COLUMN` / typed columns (schema-less; NOT NULL / DEFAULT / CHECK / SERIAL / IDENTITY / GENERATED … STORED column items exist), `VIRTUAL` generated columns, `currval()`/`lastval()` (use `INSERT … RETURNING`), `UPDATE`/`DELETE … RETURNING`, `ORDER BY embedding <-> [..]` (use `NEAREST`). --- ## 3. Statement reference ```sql -- DDL CREATE TABLE [IF NOT EXISTS] t (PRIMARY KEY (col [, col ...])) [WITH (ttl = dur, witness = bool, replication = n, nodes = ['id', ...], distribute_by = (col [, ...]))] -- replication = n: per-table RF override (n >= members = full copy). -- nodes = [...]: pin the whole table to those members; entries accept -- aliases, resolved to stable ids at DDL time; non-members refused (mutually exclusive -- with replication; every pin holds every row; non-pin coordinators route -- to the pins; REMOVE NODE refuses while a table pins the node). -- distribute_by = (col, ...): ring placement by the encoded prefix of these -- LEADING primary-key columns — all rows sharing the prefix values land on -- one replica set (partition co-location at RF < members). Must be a -- leading PK prefix; fixed at CREATE (ALTER refuses). Write partition -- columns with one consistent type (1 vs 1.0 are different prefixes). BEGIN ATOMIC PARTITION (col = lit [, ...]) ... COMMIT | ROLLBACK -- Cluster single-shard transaction (driver connections): pins one partition -- of distribute_by tables; statements may touch only rows whose -- distribution columns equal the pinned literals (any number of tables), -- reads see the session's buffered overlay, COMMIT applies all-or-nothing -- via a partition-scoped consensus round (crash-safe: replicas complete an -- interrupted commit). Isolation = atomicity only (LWW between -- transactions; no read-set validation). Refused inside: DDL, USE, TS -- writes, tables with UNIQUE/GLOBAL indexes or value-TOAST; buffer cap -- 8 MB. Plain BEGIN stays embedded/standalone-only. ALTER TABLE t SET (replication = n | nodes = ['ref', ...]) -- Placement + witness flag also shown in the UI data tab (pins as aliases). -- ONLINE placement transition: reads/writes address the UNION of old+new -- placement until a background driver repairs to convergence and -- auto-finalizes (SHOW TABLES: transition = true while open). One per -- table at a time. Escape hatch if the driver died: REPAIR CLUSTER then -- ALTER TABLE t SET (placement_finalized = true). RECLAIM trims old -- copies after finalize. Pin-set SHRINK applies immediately (no window). -- witness = false: exclude the table from witness-node mirroring (and from -- the witness tombstone-GC floor). Toggle later: ALTER TABLE t SET (witness = true) -- System tables refuse the option. Default true. -- ttl: rows expire after their last write — immediately invisible to -- every read; space reclaimed by compaction. Converges at any RF. -- Live-tunable: ALTER TABLE t SET (ttl = 30d); 0 clears. Shortening can -- expire existing rows at once; widening/clearing un-expires rows -- compaction hasn't reclaimed yet (expiry is lazy). -- VISIBILITY is immediate; SPACE comes back on a background sweep. Flush is -- size-triggered, so a low-write TTL table would otherwise hold every -- expired row in RAM for years; the sweep freezes+flushes it and runs a -- reclaim compaction every storage.ttl_reclaim_interval_secs (900; 0 = off). -- Only tables the ordinary LSM triggers never reach are swept (whole -- on-disk size under the flush threshold), and an attempt that reclaims -- nothing backs off, so a quiet table never becomes a compaction treadmill. -- Plan for retention + ~one sweep interval of rows, not retention exactly. -- A TTL table is also digest-INELIGIBLE: anti-entropy stamp-scans it. DROP TABLE [IF EXISTS] t -- cascades to the table's -- secondary/search/vector indexes ALTER TABLE t RENAME TO t2 ALTER TABLE t RENAME COLUMN a TO b -- rewrites rows, rebuilds indexes CREATE TABLE child (PRIMARY KEY (id), [CONSTRAINT n] FOREIGN KEY (c [, ...]) REFERENCES parent (p [, ...]) [ON DELETE RESTRICT|NO ACTION|CASCADE|SET NULL] [ON UPDATE ] [, ...]) ALTER TABLE child ADD [CONSTRAINT n] FOREIGN KEY (...) REFERENCES p (...) [ON ...] [NOT VALID] ALTER TABLE child VALIDATE CONSTRAINT n -- check existing rows, mark valid ALTER TABLE child DROP CONSTRAINT [IF EXISTS] n -- takes its auto index along; CHECK or FK CREATE TABLE t (PRIMARY KEY (id), c [NOT NULL] [DEFAULT expr] [CHECK (expr)] [, ...], -- column item: NO type [CONSTRAINT n] CHECK (expr) [, ...]) -- table-level ALTER TABLE t ADD [CONSTRAINT n] CHECK (expr) [NOT VALID] ALTER TABLE t ALTER [COLUMN] c SET NOT NULL -- scans existing rows (cluster-wide) first ALTER TABLE t ALTER [COLUMN] c DROP NOT NULL | SET DEFAULT expr | DROP DEFAULT -- Column constraints (schema-less stays: `c TEXT NOT NULL` is a parse -- error; no ADD/DROP COLUMN). c = top-level field or dotted path. -- NOT NULL: column present and non-NULL in the row that lands (absent -- field = NULL). DEFAULT: INSERT only, for columns the column list does -- not name (explicit NULL is named); applied before the key is computed; -- evaluated per row against an empty row, so no column refs / aggregates -- / windows / params / unknown functions (DDL-time errors). CHECK: on -- the final row; false fails, true or NULL passes, non-boolean = type -- error; NOT VALID = new writes only until VALIDATE. UPDATE gated per -- row; ON CONFLICT DO UPDATE checks the PROPOSED row before conflict -- resolution, then the merged row (Postgres). Violating statement writes -- nothing. Errors `not null violation: column "c" of table "t"` / -- `check violation: constraint "n" of table "t": c = v, …` (Constraint -- class; procedures NOT_NULL_VIOLATION / CHECK_VIOLATION). Names: -- chk__ (column CHECK), chk_[_] (table-level); one namespace -- with FKs per table. RENAME COLUMN rewrites items, refuses when a CHECK -- expr references the column. CLUSTER: ADD CHECK / VALIDATE / SET NOT -- NULL scan every member at QUORUM before broadcast; travel with schema -- sync. Partition children use the parent's constraints. -- FOREIGN KEY: referenced cols = parent's full key (cluster_by first) or -- exactly one UNIQUE index's paths. Child tuple with any NULL/absent col is -- unconstrained (MATCH SIMPLE). Violation = `foreign key violation` -- (Constraint class; procedures: FOREIGN_KEY_VIOLATION); whole statement -- writes nothing. Default RESTRICT on both sides; CASCADE/SET NULL chain -- through levels (max depth 16), any RESTRICT down the chain fails the -- statement. Self-refs OK; one multi-row INSERT satisfies itself in any -- order. Same database only; parent not TS/memory/ttl/partitioned, child -- not memory/partitioned; top-level columns only. Default name -- fk__. Supporting index: reuses a local index -- whose leading paths are the FK columns, else creates _idx (dropped -- with the constraint). DROP INDEX refuses on an index a constraint uses -- (either side); DROP TABLE refuses on a referenced parent. NOT VALID = -- enforce new writes only until VALIDATE. Txn overlay is visible to the -- checks. CLUSTER: enforced cluster-wide by the coordinator (QUORUM read -- of the other side, then write); concurrent opposite-side statements are -- serialized per referenced tuple through the hidden guard table -- __fkref____ (CAS rows {n: claims in flight, at, -- fence: token, fence_at}): child write = claim → probe → put → release; -- parent delete/key change = fence → drain claims → index-maintenance -- barrier on every member → RESTRICT probe / cascade (fence refreshed per -- page) → unfence. State older than 10 s (FKREF_GRACE_MS) without a -- heartbeat is a dead statement's and is reset by the other side -- (counters skaidb_cluster_fk_guard_waits_total / -- _stale_resets_total). Not covered: a child put stalled > 10 s between -- claim and release; ADD/VALIDATE scans (unfenced); repair/resync/PITR -- replay. Standalone never writes the guard (DB lock serializes). -- ADD/VALIDATE scan every member before broadcasting; the constraints -- travel with schema sync; a violating statement writes nothing, a -- passing one lands row by row. FK tables are refused inside BEGIN -- ATOMIC PARTITION. -- Tables with no constraint keep the batched put / CAS paths untouched. CREATE SEQUENCE [IF NOT EXISTS] s [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE] [START [WITH] n] [CACHE n] [CYCLE | NO CYCLE] [OWNED BY t.c | OWNED BY NONE] ALTER SEQUENCE [IF EXISTS] s [same options] [RESTART [WITH n]] -- RESTART = setval(n, false) DROP SEQUENCE [IF EXISTS] s [, ...] -- refused while a column DEFAULT calls nextval(s) SELECT nextval('s') / SELECT setval('s', n [, is_called]) CREATE TABLE t (PRIMARY KEY (id), id SERIAL | BIGSERIAL -- = GENERATED BY DEFAULT AS IDENTITY id GENERATED ALWAYS | BY DEFAULT AS IDENTITY [(seq options)], ...) ALTER TABLE t ALTER [COLUMN] c ADD GENERATED ALWAYS | BY DEFAULT AS IDENTITY [(...)] ALTER TABLE t ALTER [COLUMN] c DROP IDENTITY [IF EXISTS] CREATE TABLE t (PRIMARY KEY (id), c GENERATED ALWAYS AS (expr) STORED [NOT NULL] [CHECK (e)], ...) ALTER TABLE t ALTER [COLUMN] c ADD GENERATED ALWAYS AS (expr) STORED -- rewrites every row ALTER TABLE t ALTER [COLUMN] c SET EXPRESSION AS (expr) -- rewrites every row ALTER TABLE t ALTER [COLUMN] c DROP EXPRESSION [IF EXISTS] -- values stay as data INSERT INTO t (c, ...) [OVERRIDING SYSTEM VALUE] VALUES (expr | DEFAULT, ...) [ON CONFLICT ...] [RETURNING * | expr [AS a], ...] -- Sequences: database-scoped counters, defaults as Postgres (increment -- 1, min 1 / max i64::MAX ascending — mirrored descending, start = min, -- CACHE 1, NO CYCLE); values i64. nextval('s') / nextval('db.s') allowed -- ONLY in a column DEFAULT, an INSERT's VALUES and a FROM-less SELECT; -- setval only in a FROM-less SELECT; elsewhere `nextval() is allowed in a -- DEFAULT, an INSERT's VALUES and a FROM-less SELECT`. Bound reached on -- NO CYCLE: `nextval: reached maximum value of sequence "s" (n)`; CYCLE -- wraps. A failed statement still consumes its values (never rolled -- back). CACHE n leases n values per counter write, per node: unique -- cluster-wide, dense per coordinator, interleaved across coordinators; -- setval/RESTART only invalidate the running node's block. CACHE 1 -- (default): one counter round per value (local write / cluster CAS), -- dense across the cluster. Counter = one row of the hidden per-db -- table __seq__ (RF of the database); definitions travel with schema -- sync (seq: keys, tombstoned on DROP). -- SERIAL / IDENTITY: creates
__seq OWNED BY t.c (dropped -- with the table / DROP IDENTITY, keeps its name across RENAME) and -- makes c NOT NULL with a nextval DEFAULT; a DEFAULT on a PK column is -- applied before the key is computed, so INSERT INTO t (name) VALUES -- ('x') keys the row. BY DEFAULT (and SERIAL): a supplied value is -- taken, counter unchanged. ALWAYS: a supplied value is refused -- (`column "c" of "t" can only be updated to DEFAULT: it is an identity -- column defined as GENERATED ALWAYS`) unless OVERRIDING SYSTEM VALUE; -- UPDATE SET c / ON CONFLICT DO UPDATE SET c always refused. No DEFAULT -- together with an identity item. DEFAULT as a VALUES element = column -- not named for that row. ADD GENERATED: every row must already have -- the column, refused on a column with a DEFAULT or already identity. -- RETURNING: the rows as they landed (defaults/ids applied; merged row -- for DO UPDATE; DO NOTHING-skipped rows omitted) as a result set -- instead of an affected count; `*` = every field in name order. CREATE TYPE [IF NOT EXISTS] t AS ENUM ('a', ...) / ALTER TYPE t ADD VALUE [IF NOT EXISTS] 'v' CREATE DOMAIN [IF NOT EXISTS] d [AS] [NOT NULL] [DEFAULT e] [CHECK (e over VALUE)] DROP TYPE | DROP DOMAIN [IF EXISTS] n [, ...] / SHOW TYPES CREATE TABLE p (PRIMARY KEY (id), m t, q d NOT NULL, ...) -- : the bundle expands -- No column types: an enum/domain is a NAMED CONSTRAINT BUNDLE. Enum → -- column CHECK `_` = `col IN (...)`; domain → its NOT NULL / -- DEFAULT / CHECK (VALUE → column; a column DEFAULT wins). ALTER TYPE ADD -- VALUE rewrites every derived CHECK (by name + exact expr); DROP TYPE -- refused while one exists; DROP DOMAIN leaves its constraints. Enum -- values compare as strings. Db-scoped, schema-synced (typ: keys). CREATE [OR REPLACE] VIEW [IF NOT EXISTS] v [(c1, ...)] AS [WITH [NO] DATA] REFRESH MATERIALIZED VIEW m -- affected = new row count DROP MATERIALIZED VIEW [IF EXISTS] m [, ...] -- Views: stored SELECT text (any SELECT; no ? params, no nextval), in -- the table namespace of its db, sees tables as they are NOW (schema- -- less: new columns show through `*`), exposes exactly its projection -- (`column "x" does not exist in view "v"`; a computed item has no -- sub-fields). Simple body (no DISTINCT/GROUP BY/HAVING/set ops/LIMIT/ -- OFFSET/aggregates/windows/NEAREST) INLINES onto the base table (point -- lookups, index scans, ORDER BY/LIMIT pushdown, FTS, NEAREST work -- through it); otherwise (or as a JOIN right side) the body runs first -- and the outer reads its rows, grouping-column WHERE conjuncts pushed -- in. Views over views ok (cycle refused at CREATE, depth 16). Writes -- through a view refused (`"v" is a view`); DROP TABLE / RENAME of a -- referenced table refused (`view "v" depends on "t"`); DROP DATABASE -- drops its views. RBAC: SELECT on the VIEW name; body = definer rights. -- Materialized: result stored in hidden `__mv__m` (PK `__row` = content -- hash + ordinal — unchanged rows keep keys, duplicates survive), placed -- like the body's FROM table; reads are plain table reads of the -- snapshot (empty before the first refresh / WITH NO DATA). REFRESH = -- run body at session consistency, upsert rows, delete stale keys — -- readers see old ∪ new meanwhile, never empty (no CONCURRENTLY -- needed). refresh = '' (ms/s/m/h/d/w): maintenance tick -- (60 s), cluster owner = ring owner of the name. Sync: CREATE OR -- REPLACE VIEW / CREATE MATERIALIZED VIEW … WITH NO DATA in dependency -- order (vw:/mv: keys, tombstoned). No updatable views, CHECK OPTION, -- ALTER VIEW, indexes on a matview, CREATE TABLE AS. WITH a [(cols)] AS (SELECT …) [, b AS (SELECT …)] SELECT … -- CTEs (SELECT only) -- Each CTE = a statement-scoped view (same inline/overlay expansion, -- same projection rule), visible to the statement's subqueries and a -- view body may use WITH; later CTEs may name earlier ones; a CTE -- shadows a same-named table/view. No WITH RECURSIVE; a CTE named -- twice runs twice (not a materialization fence). [NOT] IN (SELECT …) / [NOT] EXISTS (SELECT …) / (SELECT …) -- subqueries -- Anywhere an expression goes (SELECT items/WHERE/HAVING/ORDER BY/GROUP -- BY/ON, UPDATE SET/WHERE, DELETE WHERE, INSERT VALUES); nest freely; -- may name a view. UNCORRELATED ONLY: an inner column qualified by an -- OUTER alias is refused (`correlated subqueries are not supported: -- "c.id" refers to the outer query`); no ANY/ALL. Each runs ONCE per -- statement (session consistency, scan byte budget) and becomes a -- literal: IN → IN (list) with NULLs dropped (NOT IN = plain list, no -- three-valued trap), EXISTS → bool (inner LIMIT 1), scalar → value -- (NULL when no row; >1 row / >1 column = error). The statement then -- runs as an ordinary one (pushdown, index scans, keyset paging intact). -- Not allowed in CHECK/DEFAULT/generated. RBAC: SELECT on every table -- read (FROM, joins, set-op legs, subqueries) on the user's own grants. -- GENERATED ALWAYS AS (expr) STORED: computed from the landing row -- (after DEFAULTs / UPDATE assignments / the DO UPDATE merge) on EVERY -- write path, overwriting the column; NULL result stored as NULL (NOT -- NULL then refuses), evaluation error fails the write; RETURNING, -- indexes and filters see a plain stored field. Explicit writes -- refused: INSERT naming it (`cannot insert a non-DEFAULT value into -- column "c" of "t": it is a generated column`; DEFAULT element ok, -- OVERRIDING SYSTEM VALUE does not apply), UPDATE SET c / ON CONFLICT -- DO UPDATE SET c (`column "c" of "t" can only be updated to DEFAULT: -- it is a generated column`). expr reads other columns only: no -- aggregates / windows / params / nextval / now() / excluded. / itself -- / other generated columns (DDL-time). Not a PK / distribute_by / -- cluster_by / partition column; no DEFAULT or identity with it; NOT -- NULL + CHECK combine. ADD GENERATED / SET EXPRESSION rewrite every -- row (ordinary UPDATE path, cluster-wide from the DDL coordinator; a -- constraint failure fails the ALTER after the schema change); DROP -- EXPRESSION keeps the values. RENAME COLUMN refused while a generated -- expr references the column. Sync key gen:
/. No VIRTUAL. CREATE INDEX [IF NOT EXISTS] i ON t (path[[]] | (expr) [, ...]) [WHERE pred] -- composite = leftmost-prefix -- (expr): expression index (CHECK rules; planner matches the same expr text -- in WHERE/ORDER BY); WHERE pred: partial — entries only for matching rows, -- used only when the query's WHERE contains every conjunct of pred; a -- partial UNIQUE index constrains only the rows it covers. -- a `path[]` component makes the index MULTIKEY: one entry per array -- element, so `col = 'x'` containment is an index probe (exact counts); -- planner requires equality through the [] column; max one [] per index -- append WITH (global = true) for a value-sharded GLOBAL index: a -- full-tuple equality probe routes to the value's replica set (one -- round-trip, no cluster scatter — the RF) [WITH (start = 'now', retention = '24h')] -- Standing filter over t's writes. Each matching INSERT/UPDATE appends to -- `_stream_`, an ordinary table in the same db: (id, op, k, ts, doc) -- where id is the position, k the row's PRIMARY KEY value and doc the row. -- Replay = ordered read: SELECT * FROM _stream_s WHERE id > '' -- ORDER BY id LIMIT n. retention is that table's TTL; DROP STREAM drops it. -- The log is READ-ONLY: INSERT/UPDATE/DELETE and DROP TABLE on it are -- refused (a forged row would be indistinguishable from a captured event); -- use DROP STREAM. CREATE STREAM refuses if a table of the log's name -- already exists rather than adopting it. A log that cannot be written -- never fails the source write — the event is counted lost by -- skaidb_stream_events_lost_total. -- With [mqtt] enabled each event is ALSO published live as JSON to -- $stream// (QoS 0, best-effort: the log is authoritative — -- resume by replaying from the last id). Emission covers the cluster -- paths; every replica emits locally and all mint the same event id, so -- the log converges instead of duplicating. -- WITH (pre_image = true) adds an `old` column carrying the row as it was -- BEFORE the change (NULL for an insert). OPT-IN and default false: a -- stream already doubles the write volume of what it matches, and carrying -- the pre-image makes each event two documents instead of one — charging -- that to existing streams would be a silent regression. A trigger's own -- stream sets it automatically. -- op is "put" (matches now), "exit" (matched before, no longer does) or -- "delete" (emitted when the PRE-IMAGE matched). start = 'now' | 'earliest' (earliest backfills existing rows -- inline). SHOW STREAMS reports live consumers/lag. Guarantees: -- at-least-once, per-key order only; a write that lost LWW never appears; -- repair cannot replay pre-creation history. The tail position survives a -- restart (skips ahead if >10k behind). Costs a second replicated row per -- match — filter narrowly. Full detail: docs/STREAMS.md -- WHEN is a normal boolean expression (evaluated like WHERE) and REQUIRED. -- retention takes a duration ('24h', '90m', 30d); start takes only 'now' -- (backfill unimplemented — any other value is refused, not ignored). DROP STREAM [IF EXISTS] s CREATE PROCEDURE [IF NOT EXISTS] p(a TEXT, n INT) BEGIN ; ; END -- Server-side statement list stored in the catalog and run by CALL, so N -- round trips become one. Types: TEXT|INT|FLOAT|BOOL|TIMESTAMP|JSON|ARRAY -- |ANY (a CALL-time contract only — the store stays schema-less; NULL fits -- any type, INT widens to FLOAT, ANY opts out). Body takes -- SELECT/INSERT/UPDATE/DELETE and nested CALL: no DDL, no BEGIN/COMMIT, no -- dynamic SQL (a string has no knowable footprint). Every statement ends -- with ';', including the last. Nesting is capped at 16 deep — a cycle -- cannot be refused at creation (either half can be recreated), so the cap -- aborts it with an UNCATCHABLE resource-limit error. -- Unqualified names in the body bind to the CREATING session's database, -- not the caller's, and are stored qualified. -- A parameter SHADOWS a same-named unqualified column, so `WHERE id = id` -- would substitute both sides; that collision is REFUSED at CREATE when -- the column is declared (primary key, series key, indexed path). Rename -- the parameter or qualify the column (`orders.id` never substitutes). -- Control flow (all inside a body only): -- DECLARE v [DEFAULT ] -- block-scoped; NULL by default -- DECLARE c CURSOR FOR ) DO ... END FOR -- v is a row document: v.col -- OPEN c; FETCH c INTO v1[, v2]; CLOSE c -- FETCH sets FOUND -- RETURN [ | {k: , ...}] -- BEGIN ... [EXCEPTION WHEN [, ] THEN ...] END -- Only TRUE takes a branch (NULL behaves as false, like WHERE). Variables -- substitute exactly as parameters do, so the same shadowing rule applies. -- RETURN {..} builds fields from EXPRESSIONS (a plain {..} literal is -- constant-only); it yields ONE row, one column per field. RETURN -- yields one column named `result`. No RETURN = the last statement's rows. -- No iteration cap: WHILE is bounded by the CALLER's scan budget/deadline, -- which is the only termination guarantee. -- EXCEPTION conditions: NOT_FOUND | CONSTRAINT | UNIQUE_VIOLATION | -- FOREIGN_KEY_VIOLATION | NOT_NULL_VIOLATION | CHECK_VIOLATION | -- TYPE_ERROR | UNSUPPORTED | OTHERS; `error_kind`/`error_message` are bound -- inside a handler. NEVER catchable: resource limits (scan budget, -- statement deadline, KILL QUERY, call depth) — a body that could catch its -- own timeout could loop forever — and storage/IO/CLUSTER errors, because -- swallowing "peer unreachable" turns it into "there was nothing there". -- CURSOR = resumable keyset scan over the PK, NOT a snapshot: it sees -- concurrent writes. Query must be a plain SELECT of columns from one table -- with an optional WHERE and a single-column PK (anything else refused). -- Resumes at ANY consistency/placement: a full local copy at ONE walks -- its own shard; every other cluster route pages the LWW-merged -- distributed read seeded strictly after the cursor. FOR..IN -- materializes instead (byte-budget bounded); use a cursor for sweeps -- larger than that. DROP PROCEDURE [IF EXISTS] p CALL p('x', 3) -- returns the LAST statement's -- result set (intermediate ones are discarded); `CALL p(?, ?)` prepares, -- so drivers bind arguments. Arguments must be constant (a call site has -- no row): a column reference is refused, not read as NULL. Errors name -- the failing site — "procedure p, statement 2: ...". -- Runs at ONE coordinator and replicates its EFFECTS through the ordinary -- write path; it is NOT a transaction (may run inside BEGIN ATOMIC -- PARTITION, where its writes buffer and commit with the rest — checked -- against every table the body COULD write, transitively, before any of it -- runs, so a refused CALL buffers nothing). -- The body inherits the CALLER's scan budget and deadline — one ceiling -- for the whole call, not one per statement. SHOW PROCEDURES -- name, params, definer, created -- (never the body: it names tables the lister may not read) CREATE JOB [IF NOT EXISTS] j ON SCHEDULE {EVERY '' | CRON ''} CALL p() [WITH (opts)] -- EVERY = duration since the last tick (drifts vs the calendar); CRON = -- calendar instants (the only way to say "3am daily"). Both reduce to one -- stored next-run instant. Cron = 5 fields (min hour dom mon dow), with -- * a a-b a,b */n a-b/n and jan../mon.. names; Sunday = 0 or 7; NO seconds -- field (use EVERY). When BOTH dom and dow are restricted a day matching -- EITHER fires (classic cron rule). A schedule that can never fire -- (0 0 30 2 *) is REFUSED at CREATE, not silently never run. -- opts: timezone (UTC default; ONLY 'UTC' or fixed '+HH:MM'/'-HH:MM' — -- named zones are REFUSED, no tz database is embedded, so DST ambiguity -- cannot arise; CRON only), catchup (false), timeout ('5m'), budget -- (1000000 rows), ttl ('7d' history), result_cap (4096 bytes). -- timeout+budget are NOT tuning: a CALL inherits the caller's budget and -- deadline, a scheduled run has no caller, and an UNSET budget means -- UNBOUNDED — so a job always runs with both installed. -- A job runs as its recorded DEFINER (a schedule has no invoker). The -- SERVER stamps it from the authenticated role, so a role can only create -- a job that runs as ITSELF — the escalation surface of the feature. -- CREATE JOB needs everything a CALL of the procedure would, and the -- definer's rights are RE-CHECKED before every run (a later REVOKE stops -- the job; the refused run is recorded as a failure with the reason). -- OWNERSHIP: preferred owner = ring primary for the job name, made correct -- by a linearizable CAS lease on _job_state (expires, so a dead owner is -- replaced with no operator action). At-least-once: an owner can run and -- die before recording. Scheduler ticks every 5s, so a finer schedule -- fires at the TICK rate and records the skipped ticks. Under memory/disk -- pressure a tick is DEFERRED: it did not run and is retried, not recorded -- complete (skaidb_jobs_deferred_total — invisible in the job's own state). -- Missed windows SKIP to the present by default (recorded), catchup = true -- runs the oldest instead. Failures are recorded + counted + backed off -- exponentially (capped 1h). History is best-effort; the LEASE recovers a -- job, not the history. -- In a job body (and in the job's CALL args), four zero-arg calls: -- SCHEDULED_AT() -- the LOGICAL tick, identical across attempts; key -- -- idempotent writes on it (INSERT .. ON CONFLICT) -- CURRENT_RUN() -- this attempt's id (NOT an idempotency key) -- LAST_SUCCESS() -- finished_at of the last ok run, else NULL -- PREVIOUS_RESULT() -- that run's RETURN document -- RETURN doc -> _job_runs.result, capped by result_cap and FLAGGED when it -- does not fit ({truncated: true}) rather than silently cut; it is also the -- next run's PREVIOUS_RESULT(), which is how an incremental job carries a -- watermark without its own table. -- Metrics: skaidb_jobs_runs_total / _failures_total / _deferred_total. -- _job_runs also records what the run DID: effects[] = one entry per -- statement SITE (position in the code), NOT per execution — a WHILE loop -- running 10k statements is ONE entry {stmt, executions, rows, ms}, so a -- record is bounded by the LENGTH OF THE CODE, not the data volume. Capped -- at the heaviest 20 sites + one "+rest" rollup carrying the totals (it -- says so; a silent truncation would read as "the body only did this"). -- tables_written = what it ACTUALLY changed (vs the static footprint = -- what it could). A trigger's whole batch shares one record and the -- effects accumulate across it. -- Jobs and triggers run on standalone servers too (one node always owns -- the lease; no ring to pick from). CREATE TRIGGER [IF NOT EXISTS] t ON
WHEN () CALL p() [WITH (opts)] -- A JOB whose source is a change stream instead of a schedule — ONE -- concept, so ownership/lease/history/failure handling are identical. -- Creates and OWNS a hidden stream `__trg_` (pre_image = true) plus -- the job; DROP TRIGGER removes both (leaving the stream would keep -- doubling the source table's writes for nobody). -- Body sees NEW (row now), OLD (row before; NULL on insert — test -- OLD IS NULL), EVENT() (the id: the identity to key idempotent writes -- on) and OP (put|exit|delete). Both NEW and OLD come from the LOG, not a -- re-read. SCHEDULED_AT() is NULL in a trigger (no tick). -- Fires from the LOG, never inline on the write path: user code in the -- applier is a deadlock class, capture must never fail the write, and only -- background work can be shed. Inherits the stream's guarantees (predicate -- re-checked per committed write, LWW losers never fire, repair cannot -- replay ancient history). -- ONE run record per BATCH, never per event (a record per fired trigger -- re-doubles write volume on a hot table). A poison event is retried -- (3x) then SKIPPED, counted and recorded — a cursor that never moves is a -- trigger that stopped silently. Cost: stream doubles matched write volume, -- pre-images double it again; SHOW TRIGGERS names the stream so you can -- measure it. -- CASCADE CYCLES ARE REFUSED at CREATE (static walk of each trigger's -- procedure write-footprint across the trigger graph). A cascade without a -- cycle is a DAG and terminates, so this bounds what a runtime depth cap -- would — deterministically, since a per-event depth counter would be -- whichever replica's copy won LWW. -- opts: retention (stream's), timeout, budget, ttl, result_cap. -- timezone/catchup are REFUSED (a trigger has no calendar, and its cursor -- is already where it left off). DROP TRIGGER [IF EXISTS] t -- removes the job AND its stream SHOW TRIGGERS -- name, table, predicate, procedure, definer, stream, cursor, -- last_status, last_error, failures DROP JOB [IF EXISTS] j SHOW JOBS -- name, source, procedure, definer, owner, next_run, last_run, -- last_status, last_error, failures -- Tables (ordinary, replicated, created with the first job in a db): -- _job_state (PK job) = owner/lease/next+last run/status/error/failures; -- _job_runs (PK id, TTL'd per job) = one row per RUN. Both FEATURE-OWNED: -- readable by clients, and INSERT/UPDATE/DELETE/DROP TABLE refused (a -- forged row is indistinguishable from a real one; dropping _job_state -- takes the scheduler down). CREATE STREAM on either is refused — it -- would capture its own writes. DROP JOB keeps the history. GRANT EXECUTE ON PROCEDURE p TO r -- also ON DATABASE d / ON * -- CALL runs with INVOKER rights: EXECUTE is only the gate. The caller -- must ALSO hold every privilege the body needs — the UNION over the whole -- call graph, walked at call time (not snapshotted), so redefining a -- callee changes its callers' requirements immediately. Jobs/triggers (phases 3-4) instead -- run as the recorded definer, which the SERVER stamps from the -- authenticated role — a client's DEFINER clause is discarded. -- Full detail: docs/PROCEDURES.md CREATE SEARCH INDEX [IF NOT EXISTS] s ON t (path [, ...]) [WITH (opts)] DROP SEARCH INDEX [IF EXISTS] s REBUILD SEARCH INDEX s ALTER SEARCH INDEX s SET (opts) -- query-time opts only, live CREATE TIMESERIES TABLE [IF NOT EXISTS] t (SERIES KEY (label [, ...]) [, RETENTION dur] [, OOO dur]) ALTER TABLE ts_table SET (retention = dur | ooo = dur) -- live-tunable; 0 clears retention CREATE ROLLUP [IF NOT EXISTS] r ON ts_table BUCKET dur [RETENTION dur] CREATE ROLLUP [IF NOT EXISTS] r ON event_table BUCKET dur [BY (dim, ...)] AGGREGATE (count(*)|count(c)|sum(c)|min(c)|max(c) AS alias [, ...]) [REFRESH dur] [RETENTION dur] -- event-table form, see below -- DML (UPDATE/DELETE rejected on time-series tables — append-only) INSERT INTO t (col, ...) VALUES (expr, ...) [, (...), ...] UPDATE t SET path = expr [, ...] [WHERE expr] DELETE FROM t [WHERE expr] -- Query SELECT [DISTINCT] item [, ...] FROM t [[AS] a] [JOIN ...] [NEAREST (path, [vector], k)] [WHERE expr] -- NEAREST: kNN over a vector index. MANAGED index (CREATE VECTOR INDEX … -- ON t(text_col) EMBED DIM n) embeds the text column via [inference] at -- ingest (out of band, never blocks a write) and auto-embeds a STRING query: -- NEAREST(text_col, 'natural language', k). [inference] keys can also be -- set via SKAIDB_INFERENCE_ env vars (override the config file at -- startup, type-checked). See docs/VECTOR.md. [RANK BY RRF [(c)]] -- RANK BY RRF: HYBRID search — fuse the NEAREST (vector) leg and the WHERE -- search-predicate (text) leg by Reciprocal Rank Fusion (ES `rrf` retriever). -- rrf_score() = sum 1/(c+rank) over both legs (c default 60); residual WHERE -- filters both legs; ordered rrf_score() desc. Needs a NEAREST + a search -- predicate; cluster-wide. [RERANK [ON col] [WITH 'model'] [QUERY 'text'] [TOP n]] -- RERANK: second-stage CROSS-ENCODER reranking (ES text_similarity_reranker). -- Top n candidates (default 100, cap 1000) of the search/hybrid retrieval -- are re-scored by the external [inference] rerank_url endpoint -- (Cohere/Jina/TEI wire) and served in the reranker's order; score() reads -- the rerank score (rrf_score() keeps the fusion score on hybrid). Defaults: -- ON = the searched columns, WITH = inference.rerank_model, QUERY = the -- search text. Needs a WHERE search predicate; no ORDER BY/GROUP BY. -- Coordinator-side, opt-in per query — endpoint down fails only RERANK -- queries. See docs/SEARCH.md, docs/VECTOR.md. [GROUP BY expr [, ...] [TOP k BY expr [ASC|DESC]]] [HAVING expr] -- GROUP BY ... TOP k BY e: per-group top-k ROWS (not aggregates); with -- MATCH + TOP k BY score() it is ES top_hits in SQL [{UNION | INTERSECT | EXCEPT} [ALL] SELECT ...] -- left to right; ALL = multiset -- SELECT DISTINCT ON (e, ...) items ... ORDER BY e, ..., more: first row per -- group in that order (ORDER BY must start with the ON exprs; LIMIT/OFFSET -- after the pick; no GROUP BY/aggregates — use GROUP BY … TOP 1 BY …). [ORDER BY expr [ASC|DESC] [, ...]] [LIMIT n] [OFFSET n] -- Output aliases resolve in ORDER BY/GROUP BY (bare names) and HAVING -- (anywhere in the predicate): count(*) AS c ... HAVING c > 1 ORDER BY c. -- Alias shadowing a source column: ORDER BY prefers the output column, -- GROUP BY/HAVING the source column. [AFTER (last_sort_value, last_pk_value)] -- AFTER: DEEP PAGINATION keyset cursor (ES search_after). Search queries, -- ordered by score() DESC or one column + LIMIT; pk = implicit ASC -- tie-break (single-column pk required; every sorted search page is -- (sort value, pk)-deterministic). STABLE under concurrent writes (no -- shifted/duplicated pages); per-page cost ≈ the OFFSET equivalent -- (doubling ranked fetch, cap 65,536). No OFFSET/GROUP BY/RRF/RERANK. -- PLAIN (non-search) selects: `AFTER ()` with bare-column -- items + ORDER BY the single-column pk ASC + LIMIT resumes the pk walk -- BY STORED KEY POSITION (not an SQL compare — mixed-type keys page -- exactly once where `WHERE pk > last` silently drops cross-type rows); -- cluster: ANY consistency/placement — a full local copy at ONE walks -- its own shard, every other route (QUORUM default, sharded, pinned) -- serves each page as a bounded LWW-merged distributed read seeded -- strictly after the cursor, page-level read quorum enforced. Each -- page is its own read (between-pages writes land or miss, ordinary -- keyset semantics). -- This page is the unit the binary protocol's query_stream lane AND -- REST /query use: an eligible unbounded SELECT streams server-side -- page by page (1024 rows/page; /query emits them as chunked JSON), so -- the node holds ONE PAGE, not the result — dump-sized reads no longer -- accumulate a result-sized Vec (and no engine lock is held across -- socket writes). A lone SELECT * is eligible: its columns resolve UP -- FRONT via one retain-free discovery scan over the matching rows (the -- sorted field union — the same columns the one-shot reports), absent -- fields pad NULL; a field first appearing mid-stream is not among that -- stream's columns; the WILDCARD form is the one shape still needing -- consistency ONE on a full-copy node (its column discovery reads the -- local shard). Ineligible shapes (ORDER BY on non-pk, aggregates, -- expressions, functions/params in the filter, composite pk, TS tables) -- keep the classic materialize-then-chunk path under scan_byte_budget. -- Filter-only queries on other columns: use WHERE col > last -- instead. No PIT. -- joins: [INNER|LEFT [OUTER]|RIGHT [OUTER]|FULL [OUTER]|CROSS] JOIN t [AS a] [ON expr] -- FULL = every left row (null right when unmatched) + unmatched right -- rows (left sides nulled). -- equi-joins hash-join; qualify columns by alias (u.id). No cluster -- join pushdown: joins pull both tables to the coordinator — fine for -- lookups, wrong for large fact-to-fact joins. -- Search-specific statements SUGGEST 'text' ON index [COLUMN col] [LIMIT n] -- "did you mean" terms EXPLAIN SCORE ") in dependency order; -- rows = backing-table count (cluster-wide on a cluster). -- SHOW TABLES lists views with kind view|matview, no key; -- SHOW CREATE TABLE prints the CREATE statement. SHOW FOREIGN KEYS [ON t] -- (constraint, table, columns, references, -- referenced_columns, on_delete, on_update, index, valid, -- definition); ON t = declared on OR referencing t SHOW STREAMS -- (name, table, predicate, consumers, lag). consumers/lag -- are 0: streams are DECLARATION-ONLY today, nothing emits DESCRIBE t -- (column, key, indexes): one row per PK/indexed column of DESC t -- table t (DESC is an alias). Catalog-only, no privilege. DESCRIBE t FULL [SAMPLE n | EXACT] -- also reads rows to surface EVERY field with its type: -- (column, type, key, indexes). SAMPLE n = first n rows in -- PK order (default 1000). EXACT = scan all rows, cached in -- a RAM field registry keyed by the table's write stamp: -- repeats are O(fields) until the table changes; always -- exact (TTL tables: never cached). Reads data -> needs -- SELECT; local shard on a cluster (complete when RF >= -- members). SHOW STATUS -- (metric, value): disk/memtable(+max)/wal/cache/flushes/compactions, -- per-table table..
.* (row: live_keys/tombstones/ -- disk_bytes; TS: kind/series/samples_appended/disk_bytes), -- per-index search..* SHOW DATABASES -- Admin statements (SQL spellings of the HTTP admin surface; reads need -- MONITOR on *, mutations need ADMIN on * (ADMIN implies MONITOR); share -- its RBAC + audit path — a SQL-only client is fully self-sufficient) SHOW CLUSTER -- ring/peers/liveness as (key, value) rows SHOW CONFIG [LIKE 'pat%'] -- full config flattened to dotted keys, secrets masked SET CONFIG section.key = literal -- live-mutable keys apply instantly SHOW SLOW QUERIES [LIMIT n] -- slow-query log (masked SQL) SHOW QUERIES -- statements running NOW on this node: id, user, -- db, via, elapsed_ms, rows_examined (live, from -- the scan meter), sql (truncated). MONITOR. SHOW MAINTENANCE -- background jobs running NOW on this node: id, -- kind, target, elapsed_ms, progress. Covers -- anti-entropy passes, content-digest builds, -- index/vector/geo backfills, search catch-ups, -- hint drains, flush builds, compactions, -- embed drains. MONITOR. KILL QUERY -- cooperative terminate by SHOW QUERIES id (ADMIN): -- the statement errors "terminated by KILL QUERY" -- at its next scan tick; a statement that never -- ticks (pure point write) finishes normally. -- Registration is per-node — run on the node the -- client is connected to. -- DISCONNECT CANCELLATION (OPT-IN, default OFF — -- SET CONFIG iwm.disconnect_cancellation = 'true', -- live-mutable): the same cooperative kill fires -- automatically ("terminated by client -- disconnect") when a DRIVER client closes its -- connection while its statement runs — within -- ~2-3 s, instead of the statement running on to -- scan_row_budget/statement_timeout_secs with -- nobody listening. The `internal` lane is -- exempt (a connection's own teardown work runs -- there, on that connection's thread, after its -- client has legitimately gone). Counted by -- skaidb_queries_disconnect_kills_total. REST is -- deliberately excluded: an HTTP client may -- legitimately half-close after sending its -- request, so there end-of-stream does not mean -- "gone" and the timeout ceiling still governs. RESET OOM COUNTER [ON ''] -- zero the oom_kills counter (node_stats/host -- stats) on every member AND registered witness, -- or one node by member id / witness id (ADMIN). -- The kernel cgroup count is read-only, so this -- persists a per-node baseline that is subtracted -- from it; the count survives service restarts -- and self-heals across container reboots. -- Returns one row per targeted node: -- (node, absorbed, status). Witnesses are outside -- the ring (pull-based), so their reset is -- STAMPED on the witnesses registry row and -- applied on the next pull cycle, exactly once — -- their row reports absorbed = NULL, status -- "stamped". REPAIR CLUSTER -- anti-entropy pass, full sweep: also -- re-verifies hot tables the timer's -- passes defer (see hot-table backoff) RECLAIM -- drop keys/series this node no longer owns ALTER CLUSTER ADD NODE 'host:7100' -- online resharding ALTER CLUSTER REMOVE NODE 'id' -- Backup / restore (paths are server-side, on the answering node) BACKUP TO 'path' -- crash-consistent copy of this node's data dir BACKUP CLUSTER TO 'path' -- all members + one cut instant (CLUSTER_CUT) -- (per-shard on a cluster); refuses to overwrite RESTORE FROM 'path' -- embedded/standalone only — on a cluster stop the -- node, restore its dir offline, let repair converge RESTORE FROM 'path' TO TIMESTAMP '2026-08-19T14:06:00Z' -- point-in-time recovery: restore the backup, then -- replay archived WAL up to that instant (undo a bad -- DELETE without losing work done since the backup). -- ISO-8601 or epoch ms; needs storage.wal_archive_dir -- Session consistency (binary protocol only; overrides the per-request -- value until changed. REST is stateless and rejects it with guidance.) SET CONSISTENCY ONE | QUORUM | ALL SET SCAN BUDGET ROWS n [BYTES n] | DEFAULT -- session-scoped, TIGHTENING-only (min with node config); -- any role may issue; reverts on silent driver reconnect ``` **RBAC**: privileges are `SELECT INSERT UPDATE DELETE CREATE DROP GRANT MONITOR ADMIN`. `ADMIN ON *` = superuser; a database grant covers its tables; a user acts as its own-named role and inherits granted roles. A user created `… GSSAPI` is external (Kerberos): no local password, the KDC vouches for the principal and skaidb maps it to the same own-named role; external users can't authenticate by SCRAM and password users can't be reached through the external path. Table grants are matched by the table's **canonical `db.table` identity**, not the raw name: `GRANT ON t` in session db `d` means `d.t` (never a cross-database wildcard), and `GRANT ON d.t` authorizes the natural `USE d; ... t` query — both spellings resolve to the same table. Management statements need `GRANT`; `SHOW GRANTS FOR ` is always allowed. `MONITOR ON *` = read-only control plane (SHOW CLUSTER/CONFIG/ SLOW QUERIES + read-only admin HTTP), never mutations. **Index DDL is table-scoped**: CREATE/DROP/REBUILD/ALTER of an index need `CREATE` on the owning table — a role that creates its indexes can drop them. `remote_write` needs `INSERT` and `/api/v1/query*` need `SELECT` on the SCOPED table (default: `metrics` in the default db; a `/db/[/table/]` path prefix moves both the target and the check — a db-scoped account can serve Grafana from its own data). Mutating admin HTTP endpoints need `ADMIN` on `*`. **Indexing**: predicates on indexed columns (`=`, ranges, AND-combined) and matching `ORDER BY` accelerate; everything else scans with identical results. The primary key routes in a cluster: `WHERE pk = v` is a point read to the key's replica set. QUORUM `ORDER BY LIMIT k` (k ≤ 10000) is a **distributed sorted top-k**: each member contributes its local index-ordered candidates, the bounded union is quorum re-read and re-sorted — reads ~members × 4k rows, not the match set. Multi-key orders ride the leading-column windows under a completeness proof (≥ k rows must sort strictly before every truncated window's weakest leading value); proof failure — e.g. a low-cardinality leading column — falls back to the exact gather. --- ## 4. Full-text search ```sql CREATE SEARCH INDEX articles_fts ON articles (title, body, year, published) WITH (analyzer = 'english', refresh_ms = 1000, title.boost = 2.0, title.keyword = true, title.copy_to = 'everything', body.copy_to = 'everything', year.type = 'long', published.type = 'bool'); SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet FROM articles WHERE MATCH(body, 'quick brown fox') AND published = true ORDER BY score() DESC LIMIT 10; ``` **Index options** — global: `analyzer` (default `'standard'` = UAX§29 + lowercase; `'folding'`, `'whitespace'`, `'keyword'`, `'ngram(min,max)'`, `'edge_ngram(min,max)'`, languages `'english'`, `'german'`, … with stopwords+stemming; or a CUSTOM PIPELINE `' | | …'` — tokenizers unicode/whitespace/keyword/ngram/edge_ngram/regex(pat), filters lowercase/ascii_folding/alphanum_only/remove_long(n)/stop(lang)/ stopwords(w1,w2)/stem(lang); NO char filters — they'd skew highlight offsets), `refresh_ms` (NRT visibility, default 1000; `0` = commit every write), `synonyms` (`'quick,fast; new york,nyc'` — multi-word entries match as phrases, both directions, hot-reloadable via `ALTER`). Per-column: `.type` (`text` default, `keyword`, `long`, `double`, `bool`, `date` — typed columns become fast fields queryable in `SEARCH()` ranges and usable for sorting/facets), `.analyzer`, `.search_analyzer`, `.boost`, `.keyword = true` (adds an exact-match `.keyword` twin), `.copy_to` ("search everything" field). **Predicates** (compose with AND/OR/NOT among themselves; ordinary conditions join at top level with AND and filter afterward; mixing under OR/NOT is rejected): - Analyzed: `MATCH(col,'text')`, `MATCH_PHRASE(col,'text'[,slop])`, `FUZZY(col,'text'[,dist])` (≤2), `SEARCH('query-string')` (mini-language: `term "phrase" col:term +must -must_not AND OR year:[2020 TO 2024]`), `MATCH_CROSS(col, col, ..., 'text')` (term-centric multi-field, ES cross_fields), `MATCH_BEST(col, col, ..., 'text')` (field-centric dis-max over an explicit column subset, ES best_fields — a row scores as its best single field). - Pattern (NOT analyzed — lowercase them under lowercasing analyzers): `MATCH_PREFIX(col,'pre')`, `WILDCARD(col,'qu*ck')`, `REGEXP(col,'...')`. - `MORE_LIKE_THIS(col, 'like text')` — similar rows. - `BOOSTED(required, optional, ...)` — `required` decides matches, each `optional` only raises scores (ES must+should). - `score()` projects BM25 (also injected as `_score`); `ORDER BY score() DESC LIMIT k` is the pushed top-k (LIMIT required, DESC only). `ORDER BY LIMIT k` also pushes down. - `HIGHLIGHT(col [, max_chars [, pre_tag, post_tag [, no_match_size [, fragments]]]])` — snippet with matches marked (`…` default; a pre/post string pair = ES pre_tags/post_tags; no_match_size = leading chars when unmatched; fragments 2-10 = ES number_of_fragments → the value becomes an ARRAY of fragments in text order; default 1 = single string). - Per-group top documents: `SELECT region, title, score() FROM t WHERE MATCH(title, 'q') GROUP BY region TOP 3 BY score()` — each group's 3 best-scoring rows (ES `top_hits` equivalent). - Aggregations work over search (`GROUP BY region`, `COUNT/SUM/AVG/MIN/ MAX`, `time_bucket` date histograms, `COUNT(DISTINCT)` exact, `APPROX_COUNT_DISTINCT` sketch) — exact fast-field pushdown or exact row fallback, never approximated silently. On SHARDED corpora (RF < members) ungrouped distinct counts scatter as mergeable term-set partials (exact across shards; the coordinator merges — per-shard finals would double-count shared values); grouped distincts row-fall back. - Diagnostics: `EXPLAIN ` (plan rows: access path, pushdown vs. fallback, cluster fan-out — advisory, never executes). **On a cluster the `access` row is the LOCAL plan only**; `cluster.access` states what the coordinator actually executes and whether the local plan's index bound is enforced distributed, and `cluster.caveat` flags any shape that drops it. The two rows disagreeing is the signal that a bound was planned and discarded; `EXPLAIN SCORE SELECT ... WHERE MATCH(...) FOR ` (BM25 breakdown JSON; works at any RF — routed to a replica of the key); `SUGGEST 'levensthein' ON idx` (typo suggestions). **Semantics to remember**: the table is the source of truth — indexes are derived and rebuild automatically if lost/torn/mismatched. Writes become searchable within `refresh_ms` (+ a 200 ms server tick); the writing session sees its own writes immediately. Multi-field scoring is dis-max. Distributed: relevance top-k scatters and merges at any RF; aggregations and fast-field sorted top-k use sharded partials on RF < members clusters (each node aggregates only key-space it primarily owns), falling back to an exact row gather when anything wobbles. --- ## 5. Time-series ```sql CREATE TIMESERIES TABLE cpu (SERIES KEY (host, core), RETENTION 30d, OOO 10m); INSERT INTO cpu (host, core, ts, value) VALUES ('web1', '0', 1712000000000, 0.63); SELECT time_bucket(1m, ts) AS t, host, avg(value), max(value) FROM cpu WHERE ts >= now() - 1h AND host = 'web1' GROUP BY t, host ORDER BY t; SELECT time_bucket(5m, ts) AS t, rate(value) FROM cpu WHERE ts >= now() - 6h GROUP BY t; ``` - `SERIES KEY` columns are string labels (all required per insert); `ts` is required (timestamp or int ms, increasing per series unless within the `OOO` window; equal ts = last-write-wins); other columns are numeric fields. Append-only: UPDATE/DELETE rejected; `RETENTION` expires blocks. - **`ALTER TABLE SET (retention = 60d | ooo = 1h)`** — both live-tunable (retention applies at next flush, `0` clears it; ooo applies to subsequent inserts — widen it temporarily to backfill a live table). - **`SELECT DISTINCT