Change streams

A stream is a standing filter over a table's writes. Every committed change that matches its predicate is captured, kept for a retention window you can replay, and published live to subscribers.

CREATE STREAM [IF NOT EXISTS] big_orders ON orders
  WHEN (total > 1000 AND status != 'draft')
  WITH (start = 'now', retention = '24h');

DROP STREAM [IF EXISTS] big_orders;
SHOW STREAMS;          -- name, table, predicate, consumers, lag

WHEN is required and is an ordinary boolean expression, evaluated exactly like a WHERE clause. A stream without one would be the table itself.

Options

Option	Values	Meaning
`start`	`'now'` (default), `'earliest'`	`'now'` captures future writes only. `'earliest'` first backfills every row that already matches, so a consumer can build a derived copy from scratch.
`pre_image`	`false` (default), `true`	Also carry the row as it was before the change, in an `old` column (`NULL` for an insert). Opt-in: it makes each event two documents instead of one, and charging that to a stream that did not ask would be a silent regression. `CREATE TRIGGER` sets it on the stream it owns.
`retention`	a duration — `'24h'`, `'90m'`, `30d`	How long events stay visible and replayable. Omit to keep forever. See What retention actually bounds below — the space is freed by a background sweep, shortly after but not at the instant events expire.

An option that cannot be honoured is refused, not ignored: a start other than the two above fails the statement rather than silently capturing the wrong thing.

The log

Events land in _stream_<name>, an ordinary table in the same database:

Column	Meaning
`id`	Position. Sorts in event order; this is your resume cursor.
`op`	`put` (matches now), `exit` (matched before, no longer does), or `delete`
`k`	The row's primary-key value
`ts`	When the change was written
`doc`	The row. For a delete, the row as it last was.
`old`	The row before the change — only with `WITH (pre_image = true)`, and `NULL` for an insert.

Because it is a table, everything else follows for free: it replicates like your data, retention is its TTL, and reading it is just SQL.

The log is read-only: INSERT, UPDATE and DELETE against it are refused, because a hand-written row would be indistinguishable from a captured event and consumers would act on it. DROP TABLE on it is refused too — DROP STREAM is what removes a stream and its log together. For the same reason, CREATE STREAM refuses to start if a table of the log's name already exists, rather than adopting it.

Reading events

Replay (any client, no extra dependencies)

SELECT id, op, k, ts, doc FROM _stream_big_orders ORDER BY id LIMIT 500;
-- then, from the last id you saw:
SELECT id, op, k, ts, doc FROM _stream_big_orders
  ORDER BY id LIMIT 500 AFTER ('<last id>');

Keep the last id you processed and resume from it. That is the whole consumer protocol, and it is what makes retention a replayable window rather than a queue: any consumer can rewind to any position still inside it. The read streams server-side, so replaying a full retention window does not materialise on the node.

Every driver wraps this loop — see HOWDOI.md.

Live push (MQTT)

With the broker enabled ([mqtt] enabled), each event is also published as JSON to $stream/<db>/<name>:

mosquitto_sub -t '$stream/default/big_orders'
{"id":"00001786…-0000000003-…","op":"put","k":2,"ts":1786…,"doc":{…}}

The $ prefix keeps streams out of ordinary # subscriptions, as with $SYS. Subscribing is governed by the usual topic ACLs (GRANT SUBSCRIBE ON TOPIC …).

What is guaranteed

At-least-once. You may see an event twice; id identifies it, so dedupe on that if it matters.
Per-key order. Changes to one row arrive in order. There is no global order across different keys — nothing in a leaderless cluster can offer one honestly.
Every change to a matching row is reported. A row that enters the predicate, changes while inside it, drops out of it, or is deleted all produce an event (put, put, exit, delete). A derived copy built from the stream therefore stays in step with the source.
The log is authoritative. The MQTT tail is a convenience: it is best-effort (QoS 0), so a subscriber that was disconnected misses the live message. Every event carries its id, so the fix is always to replay the log from the last id you processed. The tail's position survives a restart, so a node that was down still delivers what it missed — unless it fell more than 10,000 events behind, in which case it skips to the end and logs that, rather than flooding every subscriber with history.
A write that lost last-writer-wins never appears. Only changes that actually became visible in the table are captured.
Repair does not replay history. A stream ignores changes older than its creation, so anti-entropy or a catching-up replica cannot inject ancient events into a live stream.
Every event in the log was captured. Nothing else can write there, so an event you read is a change that actually happened to the source table.
A stream never fails your write. If an event cannot be appended, the write to the source table still succeeds — it was already committed — and the lost event is counted by skaidb_stream_events_lost_total. A non-zero value means that stream's log is behind its table, so a replay of that window has a hole in it; the source table is unaffected.

Costs, and when not to use one

A stream costs a write. Every matching change writes a second row, and that row replicates like any other. A predicate matching most writes roughly doubles the table's write volume. Filter narrowly: WHEN is the knob that decides the cost.

Retention costs disk for the same reason — a 24-hour window on a busy table is 24 hours of extra rows.

What retention actually bounds

retention is the log table's TTL, and a TTL is first of all a visibility rule: past it, an event is invisible to every reader, immediately and on every replica. The bytes are a separate question.

Expired rows are physically removed by compaction, and a background sweep runs compaction for expiring tables that would not otherwise reach it — a low-volume stream flushes rarely, so without the sweep its log would sit in memory long after its events stopped being visible. The sweep is on by default and runs every storage.ttl_reclaim_interval_secs (900 by default; 0 disables it), so in practice space comes back within about that window of an event expiring, not at the instant it does.

Two consequences worth planning around:

Retention is not a hard disk ceiling. Size a stream for its retention window plus roughly one sweep interval of events.
A TTL table is not digest-eligible, so anti-entropy reconciles a stream log by stamp-scanning it rather than through the O(1) digest gate. A large log therefore costs something on every repair pass — another reason to keep WHEN narrow and retention no longer than you will actually replay.

Cluster behaviour

Every replica captures events locally, and all of them derive the same event id from the change, so the log converges to one row per event instead of one per replica — no coordination, no extra replication traffic. A replica catching up through repair produces those same ids, so it converges rather than duplicating.

Limits

start = 'earliest' runs inline: the CREATE STREAM returns when the backfill is done, so it takes as long as one pass over the table.
SHOW STREAMS' consumers and lag reflect the node you ask, and are refreshed about once a second.