Expected memory impact when using Query::fetch

[sandreae]

I can't find documentation for Query::fetch which explains the exact memory impact one should expect when using it to stream individual items from the database (rather than fetching all at once). I expect it holds only one item in memory at a time, but this is not actually documented anywhere that I can find. Can anyone point me at the relevant docs? Many thanks!

[Jorge-Polanco-Roque]

Short version: at the API level fetch() is lazy. It hands you one row at a time and only one Row can be alive at once (a Row borrows the connection), so your code isn't holding the whole result set the way fetch_all() would, since that one collects everything into a Vec.

The part that's easy to get bitten by, and isn't really spelled out in the docs, is what happens underneath on Postgres. sqlx doesn't open a server-side cursor for you. Postgres sends the entire result set down the wire as soon as the query runs, and fetch() just streams rows out of sqlx's receive buffer as you poll them. So the streaming is happening at the application level, not at the protocol level, and there's no server-side backpressure. For a result that fits comfortably in memory that's fine and you get exactly the one-row-at-a-time behavior you expected. But for a huge result (millions of rows) peak memory can still approach the full set, because the data is already on its way regardless of how slowly you consume it.

If you need a hard memory bound over a very large table on Postgres, use an explicit SQL cursor (DECLARE ... CURSOR inside a transaction, then FETCH n in a loop). That's the only way to get true server-side batching. On MySQL and SQLite the row-by-row behavior of fetch() is closer to what you'd intuitively expect.

[vynazevedo]

The fetch docs describe a stream of rows, but that isn't an exact one-row memory bound. Memory also depends on the driver, row sizes, buffering, and what the consumer keeps.

One correction to the earlier reply: the stream borrows the connection; a returned PostgreSQL row doesn't borrow it. PgRow contains its own DataRow and shared statement metadata. DataRow holds reference-counted Bytes. You can retain multiple rows while continuing to consume the stream, so collecting them will still grow memory usage.

For PostgreSQL, the executor decodes and yields rows as it reads DataRow messages. It doesn't first collect the whole result. The extended-protocol Execute uses limit: 0, so it isn't requesting fixed-size cursor batches, but that doesn't mean the whole result necessarily accumulates in client memory: socket flow control still applies when the consumer stops reading.

For your use case, processing each row and dropping it before advancing avoids collecting the entire result in your application. I'd describe that as incremental consumption, rather than a guarantee that exactly one row's worth of bytes is resident. Explicit cursor batches can control the number of rows requested per batch, but even those aren't a fixed byte limit without a bound on row size. Database-side query execution memory is separate again.