When a non-blocking read hands back a stream of rows instead of a materialised list, what changes for the caller and the connection?

## Two different things called "the result" When a read returns a **materialised** collection, the sequence is already over: the statement ran, every row was mapped, the connection went back to the pool, and the caller holds plain memory. When a read returns a **stream** — a sequence produced as rows arrive — the caller holds a description of work that has usually not started yet. This is the default shape in layers that may not block, because handing back a finished list would require waiting for the last row. Three properties follow, and each one changes how the calling code must be written. ## Deferred start, and usually single use Building the sequence typically issues no statement at all. The database is touched when something begins consuming it. Practical consequences: - A read that is built and then dropped does nothing — including a read that was supposed to have a side effect. - Consuming the same sequence twice generally re-runs the statement or fails outright; it is not a collection you can iterate again. If two consumers need the rows, materialise deliberately. - Timing measured around the call that *creates* the sequence measures nothing. Measure around consumption. ## The connection is held for the whole consumption This is the operational headline. A statement in progress owns a connection, and if the read runs inside a transaction it owns that too. With a materialised list, the hold time is the statement's own duration. With a stream, the hold lasts until the last row is consumed or the sequence is cancelled — and that is now under the *consumer's* control. | | Materialised list | Streamed result | |---|---|---| | When the statement runs | during the call | when consumption starts | | Connection hold time | the statement's duration | until consumption ends or is cancelled | | Memory | whole result in memory | roughly one row plus buffered rows | | Early exit | pointless, rows already fetched | cancels, releasing the connection | | Where errors appear | at the call | anywhere in the sequence | The classic incident is per-row work inside the consumer: calling another service, writing to a queue, or awaiting anything slow while a pooled connection sits checked out behind you. Under load, the pool empties and unrelated endpoints start timing out waiting for a connection. Two defences: **do not do slow work per row inside the consumption** — buffer to a bounded chunk, finish the read, then process — or accept the hold deliberately and size the pool for it. ## Errors and completion become events With a materialised read, either you get rows or you get a failure. With a stream, a failure can arrive after some rows have already been delivered — a connection dropped mid-result, a statement cancelled, a mapping error on row 500. If those rows were being written straight to a response, part of the answer is already on the wire and the failure cannot be turned into a clean error status. Decisions this forces: 1. Decide whether the endpoint may emit partial output. If not, buffer to a bounded size and only start writing once the read completed. 2. Give the sequence a termination signal your code actually handles — completed, failed, cancelled are three different endings. 3. Make cancellation real: a caller who disconnects should cancel the read, or the connection stays held for a result nobody wants. ## Choosing between the two shapes Streaming is right when the result is large or unbounded, when the consumer can act on rows independently, or when time-to-first-row matters more than total time. Materialising is right when the result is small and bounded, when the caller needs it more than once, when you want the connection released as early as possible, or when the consumer's work per row is slow enough to make holding a connection unacceptable. In many services the honest answer is a hybrid: stream out of the data-access layer, materialise into a bounded page immediately, and do everything else outside the read. One caution on ordering: a stream delivers rows in whatever order the statement produced them, and nothing about streaming makes that order stable. If the caller depends on ordering, the query must specify it explicitly with an ORDER BY.