Change Streams (CDC)

Change streams provide durable access to the mutation log. Unlike LIVE SELECT, they survive reconnects, support consumer groups, and deliver to external systems.

CREATE CHANGE STREAM order_events ON orders;

-- With webhook delivery
CREATE CHANGE STREAM order_events ON orders
WITH (URL = 'https://hooks.example.com/orders');

-- With log compaction (keep only latest per key)
CREATE CHANGE STREAM user_state ON users WITH (COMPACTION = 'key', KEY = 'id');

DROP CHANGE STREAM order_events;
-- Drop atomically tears down the stream's consumer groups and persisted offset
-- rows. Recreating a stream with the same name starts from the head; it does
-- not resume at stale offsets.
SHOW CHANGE STREAMS;

Pull-Based Consumption

SHOW CHANGES FOR orders SINCE '2024-01-15T00:00:00Z' LIMIT 1000;

External Delivery

Webhook — HTTP POST with retry, idempotency headers, and HMAC signing.

Kafka bridge — Transactional exactly-once publishing (feature-gated --features kafka):

CREATE CHANGE STREAM order_events ON orders
WITH (DELIVERY = 'kafka', BROKERS = 'localhost:9092', TOPIC = 'orders');

SSE — GET /v1/streams/{stream}/events?group={group} with Accept: text/event-stream.

HTTP long-poll — GET /v1/streams/{stream}/poll?group={group}&limit=100.

The poll response includes gap-detection fields alongside the events:

{
  "events": [...],
  "evicted_since_last_poll": 0,
  "oldest_available_lsn": 19240
}

evicted_since_last_poll — number of events dropped from the buffer since the previous poll call. A non-zero value means the consumer fell behind and the stream's ring buffer wrapped; events in the gap are permanently lost for this consumer.
oldest_available_lsn — the lowest LSN still present in the stream buffer. Consumers can compare this against their last-seen LSN to detect gaps without waiting for the next event to arrive.

The nodedb_cdc_events_dropped_total{tenant,stream} counter tracks drops per named stream (not globally). Alert on this increasing for a stream whose consumer is active.

Streaming Materialized Views

CREATE MATERIALIZED VIEW order_stats STREAMING AS
SELECT time_bucket('5 minutes', event_time) AS bucket, count(*), sum(total)
FROM order_changes WHERE event_type = 'INSERT' GROUP BY bucket;

O(1) per event. Supports COUNT, SUM, MIN, MAX, AVG. Watermark-driven finalization.