A nightly export streams ten million rows, yet memory climbs steadily and one connection is held for an hour. What went wrong?

## Read the two symptoms separately "Streaming, but memory grows" and "one connection held for an hour" are not one bug. Diagnosing them together is what makes this scenario hard; taken apart, each has a short list of causes. ## Symptom 1 — memory grows with the row count **Cause A: the rows are tracked.** This is the common one. Streaming addresses the *driver's* buffer; it says nothing about what the layer does with each row after it arrives. If the read produces mapped objects, each one is registered in the identity map and snapshotted for dirty checking, and the unit of work retains both until it ends. Ten million rows streamed one block at a time still leaves ten million live objects. Confirm it by checking what the read returns and whether the unit of work is ever cleared; the memory profile is a slow, near-linear climb with no plateau. The fixes, best first: 1. **Read untracked**, or select a **projection** — a transfer model, tuple or scalar. Untracked results take no snapshot and enter no identity map, so each row becomes garbage as soon as you drop it. 2. **Release as you go.** Where mapped objects are genuinely needed, detach or clear periodically so the retained set stays bounded. Blunt but effective. 3. **Do not accumulate results yourself.** A stream consumed into a list you build in the loop has all the costs of a materialised read and none of the simplicity. **Cause B: nothing streamed.** Streaming usually has preconditions — an open transaction, an explicit fetch size, a result consumed in order — and when one is unmet many drivers quietly fall back to buffering the entire result before the first row is handed over. The tell is different from Cause A: memory spikes early and the first row is slow to appear, rather than climbing steadily throughout. Verify by watching when the first row is processed relative to the query's start. **Cause C: your own retention.** Accumulating per-row state — a map keyed by every row seen, a growing batch that is never flushed, log lines held in a buffer — will out-grow anything the layer does. Look here when the read is already untracked. ## Symptom 2 — one connection held for an hour This one is not a bug in the streaming; it is the price of it. A cursor is server-side state inside the query's transaction. The connection is occupied and the transaction open until the last row is consumed, and the engine must retain whatever it needs to keep that read consistent for the whole window. So the duration is governed by the slowest thing in the consumption loop: | Work done per row | Effect on the boundary | |---|---| | Writing to a local file or buffer | small, usually acceptable | | A call to another service | multiplies the window by network latency times row count | | Another query on the same unit of work | extends it and may contend with the cursor | | Per-row transactional writes | keeps a write transaction open across the whole read | The restructurings, in increasing order of surgery: 1. **Get the slow work out of the loop.** Consume the stream into batches, then do the slow per-batch work after the block is drained rather than between rows. 2. **Split the read from the processing.** Stream only to produce a compact intermediate — identifiers, or a projection — then close the boundary and process without holding it. 3. **Chunk by key range.** Process bounded ranges of the key under separate short transactions. This gives up a globally consistent snapshot of the whole export, which you must state explicitly, and in exchange no boundary lives longer than one chunk. It also makes the job restartable, which an hour-long single transaction is not. ## What good looks like A large export that behaves: an untracked projection read, an explicit and sensible fetch size, a loop that does nothing per row except write to a sink, batching so the sink is touched every few thousand rows, and either a short total runtime or key-range chunking so no single boundary is long-lived. Flat memory, and a connection held for seconds to minutes rather than an hour. ## What to say in the interview Split the symptoms first — that alone distinguishes a candidate who has debugged this. Then name tracked-object retention as the memory cause and the transaction-scoped cursor as the connection cause, and note that they call for different fixes: untracked reads for one, restructuring the loop or chunking for the other. Add the observation that connection occupancy is a shared-capacity problem: the job may finish fine while everything else queues behind a pool that is one connection short.