# Streaming reads

> STREAM SELECT opens a continuous query that sends one snapshot of the result, then exact changes as commits arrive.

A streaming read is a query that stays open. `STREAM SELECT` sends the current result once,
then sends exact changes to that result as new writes commit on its source. The statement
reference is [STREAM SELECT](/docs/sql/streaming).

## One snapshot, then exact changes

```sql
STREAM SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;
```


When the stream opens, it delivers one snapshot: here, one row per token over every transfer
already committed. After that, each commit on `transfers` produces only the change to the
result, such as the new `volume` and `transfers` for the tokens that moved. The client never
re-reads the table.

The changes are exact: inserts, deletes and updates of result rows, cell updates,
retractions and replacement-snapshot boundaries.

## What a stream can read

A `STREAM SELECT` reads relations, views and joins, with `WHERE`, projections, `GROUP BY`
aggregates, `ORDER BY` and `LIMIT`, or a window over a relation:

- `TUMBLE(rel, time, INTERVAL size)` — fixed, non-overlapping windows;
- `HOP(rel, time, INTERVAL step, INTERVAL size)` — overlapping windows that advance by `step`;
- `SESSION(rel, [time,] INTERVAL gap [, INTERVAL max])` — windows closed by a gap in activity,
  optionally bounded by a maximum duration.

A windowed stream outputs exactly one `window_start` and one `window_end` column and at least
one aggregate.

System relations can also be streamed, but only as a whole relation, without filters,
grouping or other relational operators.

## Time

Window time is either processing time or event time:

- **Processing time**: pass `NOW()` or `NOW64()` as the time argument. It advances by the
  engine's scheduled ticks.
- **Event time**: pass the name of a time column of the relation.

## When results are released

An `EMIT` clause chooses the output form and the trigger:

- `EMIT STREAM` sends append-style output; `EMIT CHANGELOG` sends output that carries
  retractions.
- `AFTER WINDOW CLOSE` releases a window's result when the window closes, optionally after a
  delay and with a timeout.
- `PERIODIC` releases results on a fixed interval, `ON UPDATE [WITH BATCH …]` when the result
  changes, and `PER EVENT` for every event.

## Delivery over WebSocket

Streams are delivered over the WebSocket session protocol (`GET /client`, subprotocol
`nyxdb.public.v1`):

1. The client sends `ExecuteSql` with the statement, its row and byte bounds and an initial
   demand.
2. The server answers `ContinuousOpened`, which carries a resumable cursor, and then
   `ContinuousResult` frames: the snapshot first, then changes.
3. The server sends only as many results as the client's demand allows. The client grants
   more with `Demand` or `ContinuousDemand` frames, so a slow client is never flooded.

Many statements share one socket, each identified by its request id. See
[Protocol](/docs/drivers/protocol).

## Lifetime and limits

- `max_stream_duration` caps the total lifetime of one WebSocket request, including a stream;
  by default the node sets no cap. A session or statement may only lower a configured cap.
- When the last subscriber of a shared continuous arrangement leaves, the arrangement stays
  ready for `subscriber_idle_ttl` so a returning subscriber joins without reseeding.
- Windowed and aggregate state is charged to the stream's share of the node's
  continuous-state memory, and the share is visible in the stream's metrics.

See [Settings](/docs/sql/settings).

## Observing streams

`system.psi_streams` has one row per subscription, with its demand, queued frames, delivered
and acknowledged sequence, resnapshot count and source-to-delivery lag. See
[PSI](/docs/concepts/psi).

---

Source: https://nyxdb.io/docs/concepts/streaming-reads/