The default behavior of most database client libraries looks innocent: execute a query, get back results, iterate through them. What’s hidden in that simple pattern is a memory trap that catches developers off guard when datasets grow.

The Buffering Problem

When you run a query that returns a million rows, the naive approach loads all million rows into memory before your application code sees a single one. The database sends results over the wire, the client library accumulates them in a buffer, and only after the entire result set arrives does control return to your code.

This happens because the client library treats the query result as a complete collection. It’s optimizing for convenience: you get a list-like object you can iterate over, measure the length of, or even traverse multiple times. But that convenience comes with a cost. A result set with a million rows of modest width can easily consume several gigabytes of RAM, and that’s before your application logic does anything with the data.

The failure mode is sudden. A query that worked fine in development with a few thousand rows causes out-of-memory crashes in production with realistic data volumes. The application doesn’t scale with the size of the query result, it cliff-dives.

Cursors and Server-Side State

The solution is to fetch results incrementally. Instead of materializing the entire result set in client memory, the database maintains a cursor on the server side: a pointer to the current position in the query result. The client fetches a batch of rows, processes them, then asks for the next batch.

This is what database cursors provide. In PostgreSQL you declare a cursor with a CURSOR statement, then repeatedly FETCH small batches. In MySQL you can use a server-side cursor by setting connection flags. Most ORMs and database libraries expose this as a streaming mode or a configurable fetch size.

The memory footprint becomes constant. Instead of growing linearly with result set size, it stays bounded by the batch size. Fetch 1,000 rows at a time and your client memory usage stays around the size of 1,000 rows regardless of whether the full result is a million or a billion rows.

The Transaction Tradeoff

Cursors introduce a constraint: they require holding a transaction open for the duration of the fetch. The server needs to maintain a consistent snapshot of the data as it existed when the query started, so rows remain stable across multiple fetch operations. If you close the transaction, the cursor becomes invalid.

This creates tension with connection pool management. A long-running cursor ties up a database connection while your application iterates through results. If processing each batch takes time—writing to a file, calling an API, performing computation—that connection stays busy for seconds or minutes. In high-concurrency systems, this can exhaust the connection pool.

Some databases offer scrollable or hold-able cursors that can survive transaction commits, but they’re not universally supported and often come with performance penalties. The typical pattern is to process batches quickly and minimize cursor lifetime.

Implicit Streaming in Drivers

Modern database drivers increasingly make streaming the default or provide it as an easy opt-in. The Go database/sql package streams by default. JDBC allows setting fetch size on statements. Python’s psycopg2 offers named cursors for server-side iteration.

The API often looks the same whether you’re streaming or buffering. You still write a loop over rows. The difference is whether the driver materializes the full result before that loop starts or fetches batches lazily as you iterate.

Some libraries make this distinction visible through different result types: one for buffered results that support length and random access, another for streaming results that only support forward iteration. Others use configuration flags that change behavior behind a uniform interface.

When Buffering Makes Sense

Not every query needs streaming. If you’re fetching a handful of rows for a web request, the overhead of a server-side cursor outweighs the benefit. Buffering the entire result is simpler and faster: one round-trip, no held transaction, no cursor state on the server.

The inflection point depends on row size and total volume. A query returning 100 rows of narrow data fits comfortably in memory. A query returning 10,000 rows of wide JSON columns might not. The rule of thumb: if the result set could plausibly exceed tens of megabytes, consider streaming.

Batch jobs and data exports are the classic streaming use case. You’re processing everything anyway, you don’t need random access, and result sets are large by design. ETL pipelines, report generation, and data migration scripts all benefit from cursor-based fetching.

Implementation Visibility

Understanding whether your database library streams or buffers is not always obvious. The difference is rarely prominent in documentation, and the behavior sometimes changes based on subtle query characteristics or connection settings.

Testing with realistic data volumes is the clearest signal. Monitor client memory usage while running queries with large result sets. If memory spikes to the full size of the result before processing starts, you’re buffering. If it stays flat and proportional to batch size, you’re streaming.

The choice between buffering and streaming is a fundamental design decision in any system that queries large datasets. Getting it wrong doesn’t just waste memory—it sets a hard ceiling on how much data your application can handle.