What Is a Database Cursor? Row-by-Row Traversal

A database cursor is a control structure that lets you step through the rows of a query’s result set one at a time, rather than pulling the entire result into memory at once. It works like a pointer that starts before the first row and advances forward (or, with a scrollable cursor, in either direction) each time you fetch.

Most application code never touches a cursor directly — an ORM or driver fetches rows in batches behind the scenes. But cursors are the mechanism underneath a lot of database plumbing: server-side pagination, stored procedures that process rows one by one, and the streaming APIs that database drivers expose for large result sets.

Why not just fetch everything

For a query that returns a handful of rows, there’s no reason to think about cursors — just fetch the whole result set and move on. The problem shows up at scale. A query that returns ten million rows, materialized all at once, has to be held in memory somewhere: on the database server building the result, on the network moving it, and on the client parsing it. That’s a lot of memory pressure for a report a human will only skim the first page of, or a batch job that only needs to touch one row at a time.

A cursor solves this by keeping the result set on the server and handing rows to the client in small batches on demand. The client asks for the next N rows, does something with them, and asks again. Memory usage on both ends stays bounded regardless of how large the underlying result is.

Declaring and using a cursor

In PL/pgSQL (Postgres) or T-SQL, a cursor is declared explicitly and stepped through with FETCH:

DECLARE order_cursor CURSOR FOR
  SELECT id, total FROM orders WHERE status = 'pending';

OPEN order_cursor;
FETCH NEXT FROM order_cursor;
-- process the row
FETCH NEXT FROM order_cursor;
-- ... repeat until no rows remain
CLOSE order_cursor;

This explicit form shows up most often inside stored procedures that need row-by-row logic a single SET-based query can’t express cleanly — for example, applying a different calculation to each row based on a running total. Outside of stored procedures, most languages’ database drivers expose cursors implicitly: iterating over a query result in a loop is, under the hood, a client-side cursor pulling batches from the server as you go.

Cursors vs fetching the full result set

	Cursor (batched fetch)	Full result set
Memory usage	Bounded, independent of result size	Grows with result size
Time to first row	Fast — first batch returns immediately	Slow — waits for the whole query
Server-side resources held	A cursor stays open, holding a snapshot or lock	Released once the query completes
Best for	Large results, streaming, row-by-row processing	Small results you’ll use entirely anyway

The tradeoff is that an open cursor holds server-side state for as long as it’s open — in some isolation levels that can mean holding a consistent snapshot of the data, which has its own cost. See what MVCC is for how Postgres and similar databases keep a cursor’s view of the data consistent without blocking writers. Leaving cursors open too long, or too many of them open at once, is a common source of connection and memory pressure — the same category of problem covered in the N+1 query problem, where the fix is usually to fetch in fewer, larger batches rather than one row per round trip.

Cursor-based pagination

The word “cursor” also shows up in API design, in cursor-based (or keyset) pagination — passing an opaque token that encodes “give me rows after this point” instead of an OFFSET. It’s related in spirit (both avoid materializing everything up front) but implemented differently: keyset pagination is usually a WHERE id > :last_seen_id ORDER BY id LIMIT :n query, not a server-side cursor object. The OFFSET-based alternative gets slower as the offset grows, because the database still has to scan and discard the skipped rows — one reason keyset pagination is preferred for deep pagination through large tables, which is where B-tree indexes on the sort column do most of the work.

Scrollable and holdable cursors

Most cursors are forward-only: FETCH NEXT and nothing else. Some databases support scrollable cursors that also allow FETCH PRIOR, FETCH ABSOLUTE n, or moving backward — useful for interactive tools like a query browser where a user might page back and forth. Scrollable cursors typically cost more, since the database may need to buffer more state to support moving in either direction.

A cursor is normally tied to the transaction that opened it and closes automatically when the transaction ends. A WITH HOLD (Postgres) or equivalent option lets a cursor survive past the commit, though at the cost of the database having to keep its underlying result materialized rather than streaming it lazily — worth checking Postgres’s EXPLAIN ANALYZE output if a holdable cursor’s plan looks different from what you expect.

The takeaway

A cursor is a pointer into a query’s result set that lets you fetch rows in bounded batches instead of pulling everything into memory at once. Application code rarely declares one explicitly — drivers and ORMs handle it — but the concept underlies row-by-row stored procedure logic, large result streaming, and (loosely) keyset pagination in APIs. Reach for an explicit cursor when a query has to process rows one at a time with server-side logic; for everything else, let your driver’s default batching handle it.