Postgres server-side cursors don’t actually move data row by row; they’re more like bookmarks for a query’s result set, letting you fetch chunks of data on demand.
Let’s see this in action. Imagine a table large_table with millions of rows. We want to process these rows without loading them all into application memory at once.
-- First, create a cursor
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT id, data FROM large_table ORDER BY id;
-- Now, fetch the first 100 rows
FETCH 100 FROM my_cursor;
-- Fetch the next 100 rows
FETCH 100 FROM my_cursor;
-- And so on, until we've processed all rows
-- Finally, close the cursor and commit the transaction
CLOSE my_cursor;
COMMIT;
This DECLARE statement doesn’t execute the query immediately. It just prepares it. The FETCH command is what actually retrieves a batch of rows (100 in this example) from the prepared result set. The cursor’s position is updated, and the next FETCH will pick up where the last one left off. This allows you to iterate through millions of rows without ever holding the entire result set in RAM.
The problem this solves is simple: applications often need to process large datasets, but loading everything into memory is inefficient and can lead to crashes. Server-side cursors provide a mechanism to decouple the query execution from the data retrieval, enabling streaming.
Internally, when you DECLARE a cursor, Postgres plans the query and creates a temporary data structure to hold the plan’s output. When you FETCH, it retrieves a portion of that output. The key is that the entire result set isn’t materialized into a single, massive data structure in memory on the server unless you’re using COPY or a simple SELECT * in a client that immediately pulls all rows. With cursors, it’s more like a managed iterator.
The main levers you control are the cursor name (arbitrary, but descriptive), the query itself, and the FETCH count. A larger FETCH count can reduce network round trips but increases the memory used per fetch on both the server and client. A smaller count increases round trips but reduces memory pressure.
The default behavior of FETCH is to retrieve one row at a time. However, you can specify a number, like FETCH 100 FROM my_cursor;. This tells Postgres to return up to 100 rows in that single network packet. The server still has to manage the entire result set’s state, but it only sends you chunks.
What most people miss is that the cursor’s state is tied to the transaction. If the transaction rolls back, the cursor and all its fetched data are gone. This makes cursors ideal for transactional data processing where you want atomicity over a large operation. If you FETCH 1000 rows, process them, then FETCH another 1000, and then decide to ROLLBACK, all 2000 rows are discarded.
The next concept you’ll bump into is how to handle potential deadlocks or long-running transactions when using cursors in concurrent environments.