7.2.7 — Result Sets, Cursors and Getting Rows Out

A nightly export job runs SELECT * FROM orders against nine million rows and the application process dies with an out-of-memory error. The database is fine — it never went above 200 MB. The query itself was never slow.

The rows had nowhere to go. By default almost every database client collects the entire result in memory before handing you the first row, so a query returning 9 million × 400 bytes tries to build a 3.6 GB structure inside your process. This page is about what a result set actually is, where it lives at each moment, and the two tools that let you read a large one without holding it all.

1. What a result set is

A result set is the sequence of rows a statement produces, plus a description of the columns. It is not a table, it has no name, it is not stored anywhere, and it exists only for as long as something is reading it.

The description part matters more than people expect. Before any row arrives, the server sends a row description: for each column, its name, its data type, its size, and the format the values will be encoded in. That is how your client library knows to turn the bytes 31 30 30 into the number 100 rather than the string "100", and how a tool can draw column headers before it has a single row.

The rows are produced lazily, one at a time, by the plan tree from Chapter 7.2.3. The top node asks its child for a row, which asks its child, down to the leaf that reads a page. A row is pulled all the way up, sent, and then the next one is pulled. Nothing collects the whole result on the server. This is why a SELECT over a huge table uses almost no server memory.

Except when a node has to see everything before it can produce anything. A sort cannot emit its first row until it has read its last input row. A hash aggregate must build the whole hash table. A DISTINCT must know what it has already seen. Those are the blocking nodes, and they are exactly the ones with a large start-up cost in Chapter 7.2.5 section 5. So the honest statement is: rows stream through the plan unless a blocking node sits in the way, and then everything below that node is materialised first.

This has a directly useful consequence. SELECT * FROM orders streams from the first millisecond. SELECT * FROM orders ORDER BY total_minor cannot send anything until the sort finishes, which may mean a temporary file on disk first. Adding an ORDER BY to a streaming export changes it from constant memory to a full materialisation — unless an index already provides the order, in which case the sort node disappears and streaming resumes.

2. Where the rows actually go

Four places, in order, and the memory problem lives in the fourth.

The server never holds the whole result, and the socket holds only a window. Whether the export survives is decided entirely by what the client library does with the rows as they arrive.

One: the plan tree, producing rows one at a time as described above.

Two: the server's send buffer, a few kilobytes. Rows are encoded into wire messages and pushed into it.

Three: the network socket. This is where an important safety property comes from for free. If the client is slow to read, the socket buffers fill, and the operating system stops accepting more from the server, which blocks the server's write. The server then simply stops producing rows until the client catches up. That is backpressure — a slow consumer automatically slowing the producer — and it is the same idea as Chapter 3.8.4's streams, here delivered by TCP without anybody writing code for it.

Four: the client library, and this is the one that kills you. Backpressure only protects you if the client stops reading. The default behaviour of nearly every driver is to read as fast as it possibly can and pile the rows into an array, so the client never applies backpressure and the memory ends up in your process. The database was innocent the whole time.

3. The defaults, per language, because they differ and they matter

Node.js, node-postgres. client.query('SELECT …') buffers everything and resolves with result.rows as a complete array. There is no partial delivery. Nine million rows is nine million JavaScript objects in the heap.

Python, psycopg. A plain cursor buffers the entire result inside the client on execute(), so even for row in cur: — which looks like streaming — is iterating over rows that are already in memory.

Java, JDBC with the PostgreSQL driver. All rows are fetched by default. Streaming requires two things together, and missing either one silently gives you the buffered behaviour: setFetchSize(n) and auto-commit turned off, because the driver implements fetching with a cursor and a cursor needs a transaction.

Java, JDBC with MySQL's Connector/J. Different again: setFetchSize(Integer.MIN_VALUE) switches to true row-by-row streaming, or you set useCursorFetch=true in the connection URL and then setFetchSize(n) works the way you would expect.

Go, database/sql. rows.Next() genuinely streams by default. Go is the pleasant exception.

The rule to carry: check what your driver does before exporting anything large, because "it iterates" does not mean "it streams". The test is simple — run the export against a big table and watch the process's memory. Flat means streaming. A rising line means buffering, and the only question is whether the table is big enough today to reach the limit.

4. Cursors: asking for the rows a handful at a time

A cursor is a named position inside a result set that you can move through in steps. Instead of "give me all the rows", you say "start this query, then give me the next thousand", repeatedly.

BEGIN;                                                    -- (1)
DECLARE order_export CURSOR FOR
  SELECT id, customer_id, placed_at, total_minor
  FROM orders ORDER BY id;                                -- (2)

FETCH FORWARD 1000 FROM order_export;                     -- (3)
FETCH FORWARD 1000 FROM order_export;                     -- repeat until fewer than 1000 come back

CLOSE order_export;                                       -- (4)
COMMIT;

(1) A cursor lives inside a transaction. DECLARE outside one fails, because when the transaction ends the cursor's position and its view of the data are gone. Section 6 covers the exception. (2) Declaring does not run the query to completion — it sets up the plan and puts the cursor before the first row. (3) Each FETCH pulls the next batch through the plan. Memory used on both sides is one batch, no matter how many rows exist. (4) CLOSE releases the plan and its resources; COMMIT would do it anyway, and closing explicitly is the habit that keeps long transactions tidy.

The options on DECLARE, and what each costs:

• SCROLL allows fetching backwards (FETCH BACKWARD 100) and jumping around. It is not free — the engine may have to store the rows it has produced so it can hand them back again. NO SCROLL is the default for most plans and is what you want for a straight export.
• WITH HOLD lets the cursor survive the transaction that created it. The price is that at COMMIT the server computes the remaining rows and writes them to a temporary file, because it can no longer rely on the transaction's view of the data. So a WITH HOLD cursor over nine million rows produces a nine-million-row temporary file at commit time. It solves a real problem and it is not the cheap option people assume.
• FOR UPDATE locks each row as the cursor reaches it, which enables the update form in section 7.

In the client, this is all wrapped for you. In Node:

import Cursor from 'pg-cursor';

const cursor = client.query(new Cursor(
  'SELECT id, customer_id, total_minor FROM orders ORDER BY id'));   // (1)

for (;;) {
  const rows = await cursor.read(1000);                              // (2)
  if (rows.length === 0) break;                                      // (3)
  await writeBatchToFile(rows);                                      // (4)
}
await cursor.close();

(1) The query is started, not run to completion. (2) Each read is a FETCH on the wire and returns at most a thousand rows. (3) An empty batch means the result set is exhausted — this is the loop's only exit. (4) Because we await the write, we stop reading while it happens, which is what makes backpressure work end to end: the socket fills, the server blocks, and memory stays flat. Replace this line with a fire-and-forget write and you have rebuilt the original bug with extra steps.

In Python the equivalent is a named cursor, which is the same mechanism with a confusing name:

with conn.cursor(name='order_export') as cur:      # a name makes it server-side
    cur.itersize = 1000
    cur.execute('SELECT id, total_minor FROM orders ORDER BY id')
    for row in cur:                                # fetches 1000 at a time
        handle(row)

The name is the switch. An unnamed cursor buffers everything in the client; a named one is declared on the server and fetched in batches. That single-word difference between an export that works and one that dies is worth remembering exactly.

5. The protocol underneath, briefly

Knowing the message names makes driver documentation readable, and it explains one behaviour that otherwise looks arbitrary.

PostgreSQL's extended query protocol splits a statement into steps: Parse turns SQL text into a prepared statement, Bind attaches parameter values to it and produces a portal — a portal is a statement that is ready to run and has a position in its output — Describe asks for the row description, and Execute pulls rows from the portal. Execute carries a maximum row count, and when the portal still has rows left after that many, the server replies PortalSuspended instead of CommandComplete.

That is a cursor, at the level where it really lives. DECLARE … FETCH in SQL and setFetchSize in JDBC and pg-cursor in Node all end up sending Execute with a row limit and getting PortalSuspended back.

Two practical facts fall out of the protocol.

Values arrive in text format by default. Each value is sent as characters, and the client converts. That is why node-postgres hands you a bigint as the string "9007199254740993" rather than a number — converting it to a JavaScript number would silently lose precision above 2⁵³, so the driver refuses to guess. It is not a bug and the fix is to parse it deliberately.

A parameter is not string-substituted into the SQL. In Bind, the values travel in separate fields from the query text, so there is no string for an attacker to break out of. This is why parameterised queries stop SQL injection at the protocol level rather than by escaping, and it is the strongest version of the argument in Chapter 8.5.1.

6. What an open cursor holds while it is open

This section is the operational one, and it is where cursors cause incidents rather than solve them.

A cursor inside a transaction keeps that transaction open. An open transaction holds a snapshot, and a held snapshot stops VACUUM from cleaning up any row version newer than it — the mechanism is in Chapter 7.4.2. An export that fetches a batch, writes it to a slow remote service, and fetches the next one can easily stay open for an hour. During that hour the database cannot reclaim dead rows in the whole database, tables bloat, and every other query gets slower. The classic incident is a report that is itself harmless and makes everything else slow, and it is invisible unless you look for long-running transactions:

SELECT pid, state, now() - xact_start AS open_for, left(query, 60)
FROM pg_stat_activity
WHERE xact_start < now() - interval '5 minutes'
ORDER BY xact_start;

Two settings limit the damage. statement_timeout does not help here — it limits a single statement, and each individual FETCH is fast. The one you want is idle_in_transaction_session_timeout, which kills a session that is inside a transaction and doing nothing, which is exactly the state of an export waiting on a slow consumer.

The design lesson: get the rows out fast and process them afterwards. Fetch batches into a file or a queue with no slow work between fetches, close the transaction, then do the slow work. An hour of processing is fine; an hour of holding a snapshot is not.

On the snapshot itself, one detail worth knowing precisely. A cursor's query sees the data as of when the cursor was opened, and that view stays fixed for the cursor's whole life, even under Read Committed isolation where a plain new statement would see fresh data. So a long export produces a consistent picture rather than a mixture — which is usually what you want and is the reason a cursor is a better export tool than paging with OFFSET, where each page sees a different moment.

7. Updating the row the cursor is sitting on

BEGIN;
DECLARE fix CURSOR FOR
  SELECT id, ship_address FROM orders WHERE ship_address IS NULL FOR UPDATE;   -- (1)

FETCH 1 FROM fix;
UPDATE orders SET ship_address = 'unknown' WHERE CURRENT OF fix;               -- (2)
COMMIT;

(1) FOR UPDATE locks each row as the cursor reaches it, so nobody else can change it between your read and your write. (2) WHERE CURRENT OF means "the row this cursor is on" — no key needed, no chance of naming the wrong row.

Be careful with this, because it is a row-at-a-time loop over a set-based database. Updating 100,000 rows this way is 100,000 statements and holds 100,000 locks in one transaction. A single UPDATE … WHERE ship_address IS NULL does the same work in one statement, with one plan, and finishes in a fraction of the time. The cursor version is only right when each row's new value requires a decision that SQL cannot express — a call to an external service, for instance — and in that case you should be batching in chunks with a commit between them, not holding one enormous transaction.

8. Cursors and pagination are two different words that sound the same

This causes real confusion, so here it is directly. A database cursor is server state inside a transaction. It is unusable for a web page, because HTTP requests are separate connections minutes apart and no transaction can span them. "Cursor-based pagination" in a web API means something completely different: the server hands the client an opaque token, the client sends it back on the next request, and the token encodes where to resume — typically the sort key of the last row seen.

-- Page 1
SELECT id, placed_at FROM orders ORDER BY placed_at DESC, id DESC LIMIT 20;

-- Page 2 — resume after the last row of page 1
SELECT id, placed_at FROM orders
WHERE (placed_at, id) < ('2026-08-01 10:00:00', 88213)      -- (1)
ORDER BY placed_at DESC, id DESC LIMIT 20;

(1) The row comparison (placed_at, id) < (…) means "earlier by date, or the same date and a lower id", which is exactly the position after the last row of the previous page. The id tie-breaker is required, for the same reason Chapter 7.2.2 requires one on ROW_NUMBER: without it, rows sharing a timestamp can appear twice or be skipped.

This is keyset pagination, and it beats OFFSET on two counts. LIMIT 20 OFFSET 100000 makes the engine produce and throw away 100,000 rows, so page 5,000 times out while page 1 is instant. And OFFSET is wrong on changing data: insert a row while a user is reading, and every later page shifts by one, so they see a row twice. Keyset pagination is O(1) per page with an index on the sort key, and it cannot duplicate or skip a row. Chapter 9.6.2 derives it as an API design.

9. When a statement returns more than one result set

Some databases let one call return several result sets in a row — a stored procedure in SQL Server or MySQL can run three SELECTs and the client walks the results with getMoreResults(). It saves round trips and it is common in older codebases.

PostgreSQL does not do this. A function or procedure that needs to return several result sets returns several refcursors — cursor names — and the caller fetches from each one:

BEGIN;
CALL order_summary(42, 'orders_out', 'refunds_out');   -- opens two cursors
FETCH ALL FROM orders_out;
FETCH ALL FROM refunds_out;
COMMIT;

Chapter 7.2.8 writes the procedure side of this. The reason it is mentioned here is that it is the one situation where a cursor is a return value rather than a memory-management tool, and it is why the type refcursor exists at all.

What the interviewer will push on

"Your export process runs out of memory. The database is fine. Why?" The client library buffered the whole result set. Say where the rows live at each stage — plan tree, send buffer, socket, client — and note that TCP already provides backpressure, so the only broken link is a client that reads as fast as it can into an array. Then fix it with a cursor and a fetch size. The weak answer suggests adding memory.

"What is a cursor, and what does it cost while it is open?" A named position in a result set that you advance in batches, so memory stays constant. Then the operational half, which is what they are really after: it holds a transaction open, which holds a snapshot, which stops vacuum reclaiming dead rows database-wide. Long-running exports are a bloat cause, and idle_in_transaction_session_timeout is the guard — not statement_timeout, because each individual FETCH is fast.

"Does SELECT on a huge table use a lot of server memory?" No — rows are pulled one at a time through the plan. Then give the exception that shows you understand plans: a blocking node such as a sort, a hash aggregate or DISTINCT must consume all its input first, so adding ORDER BY to an export turns constant memory into a full materialisation unless an index supplies the order.

"OFFSET or keyset pagination?" Keyset. Two reasons, and the correctness one is stronger than the speed one: OFFSET makes the engine produce and discard every skipped row, and on data that is changing it duplicates or skips rows as pages shift. Then name the tie-breaker requirement in the sort key.

"Why do parameterised queries prevent SQL injection?" Because the parameters travel in separate protocol fields from the query text at Bind time, so there is no string for an input to break out of. That is a level below "it escapes the quotes", and it is the answer that shows you know what the driver is doing.

One thing to volunteer: mention that "cursor" means two unrelated things — server state inside a transaction, and an opaque resume token in a web API — and that the second cannot be built from the first, because no transaction survives between two HTTP requests. People conflate them constantly in design discussions, and separating them in one sentence saves an hour.

Recall

• A result set is a sequence of rows plus a row description of the columns. It is produced lazily through the plan tree, so the server holds almost nothing — unless a blocking node (sort, hash aggregate, DISTINCT) must consume all its input first.
• Rows travel: plan tree → send buffer → TCP socket → client. TCP gives backpressure for free, but only if the client stops reading. The default in most drivers is to buffer every row into an array, which is where the out-of-memory error comes from.
• Defaults differ and the difference is invisible: node-postgres buffers, unnamed psycopg cursors buffer, JDBC PostgreSQL needs setFetchSize and auto-commit off, MySQL Connector/J needs Integer.MIN_VALUE or useCursorFetch=true, Go streams.
• A cursor is a named position advanced with FETCH, so memory is one batch regardless of table size. It lives inside a transaction; WITH HOLD survives commit by writing the remaining rows to a temporary file.
• An open cursor holds a snapshot, which stops vacuum reclaiming dead rows database-wide. statement_timeout does not catch it — each FETCH is fast. Use idle_in_transaction_session_timeout, and do slow work after closing the transaction.
• Underneath it is Parse → Bind → Describe → Execute, with Execute carrying a row limit and the server replying PortalSuspended. Parameters travel in separate fields from the SQL text, which is why parameterisation defeats injection at the protocol level.
• WHERE CURRENT OF updates the row the cursor is on, and is almost always the wrong tool — a single set-based UPDATE beats a row-at-a-time loop.
• Two unrelated meanings of "cursor": server state in a transaction, versus an opaque resume token in a web API. The second is keyset pagination — WHERE (sort_key, id) < (last_seen) — which is constant time per page and cannot skip or duplicate rows, unlike OFFSET.

Self-test: Where exactly does the memory go when an export dies? · Why does ORDER BY change an export's memory profile? · What does a cursor hold open, and which timeout catches it? · What does the server send when a portal has rows left? · Why is OFFSET wrong rather than merely slow? · What makes a psycopg cursor server-side?

Next: 7.2.8 moves the code itself into the database — stored procedures and functions, what they buy in round trips and atomicity, what SECURITY DEFINER does and how it is attacked, and the honest case against putting business logic there.