SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.

Quick Summary
A cursor processes a result set one row at a time: DECLARE names it, FETCH pulls rows, CLOSE releases it. It is the SQL equivalent of a for loop, and in a relational database it is almost always the slow path. The engine runs the plan once, then switches between the executor and the procedural layer for every row, re-evaluating expressions and often re-checking visibility. A set-based statement expresses the same intent as one plan over the whole set, so the optimiser can use hash joins, batch updates and parallel workers.
Numbers make the difference concrete. Updating 100,000 rows with a cursor can take minutes and hold locks for the full duration; the equivalent UPDATE ... FROM takes seconds and holds row locks for the length of one statement. Cursors also consume memory on the server: a WITH HOLD cursor materialises its entire result into a temporary file, and a client-side cursor in many drivers buffers the whole result in the application's memory.
Cursors are still legitimate in four places: chunked maintenance that must be bounded per batch (delete 5,000 rows, commit, repeat), per-row side effects against an external system where each row genuinely triggers work, procedural code where ordering plus early exit is part of the algorithm, and compatibility with an existing application contract. When a cursor is the right tool, keep it small, keep transactions short, and use FETCH FORWARD counts instead of one row at a time.
Basic syntax and query signatures
BEGIN;
-- Default is FORWARD-only, owned by the transaction
DECLARE cur_orders NO SCROLL CURSOR FOR
SELECT id, total FROM orders WHERE status = 'pending' ORDER BY id;
FETCH FORWARD 100 FROM cur_orders; -- returns at most 100 rows
FETCH NEXT FROM cur_orders;
CLOSE cur_orders;
COMMIT;WITH HOLD survives COMMIT but is expensive: the remaining rows are spooled to disk.
DECLARE cur_export CURSOR WITH HOLD FOR
SELECT * FROM orders ORDER BY created_at;
COMMIT; -- cursor still usable
FETCH FORWARD 1000 FROM cur_export;
CLOSE cur_export;PL/pgSQL usually replaces the explicit cursor with a FOR loop, which is a cursor underneath.
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LOOP
PERFORM process_job(r.id);
END LOOP;
END $$;MySQL requires cursors inside stored programs, with an explicit handler for the end of the set.
DELIMITER //
CREATE PROCEDURE purge_old()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_id BIGINT;
DECLARE cur CURSOR FOR SELECT id FROM sessions WHERE expires_at < NOW();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF done THEN LEAVE read_loop; END IF;
DELETE FROM sessions WHERE id = v_id;
END LOOP;
CLOSE cur;
END //
DELIMITER ;SQL Server separates the fast read-only form from the full one.
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT id FROM Orders WHERE Status = 'Pending';
OPEN cur;
FETCH NEXT FROM cur INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.ProcessOrder @id;
FETCH NEXT FROM cur INTO @id;
END
CLOSE cur;
DEALLOCATE cur;Key comparison table
| Aspect | Set-based statement | Cursor |
|---|---|---|
| Plan count | One plan for the whole set | One plan, executed row by row |
| Round trips | One | One per FETCH |
| Lock duration | Length of the statement | Length of the loop, plus holdable spooling |
| Optimiser freedom | Full (hash join, parallelism) | Limited; row-at-a-time decisions |
| Server memory | Bounded by work_mem per operation | Temp files for WITH HOLD, buffers otherwise |
| Failure atomicity | All or nothing | Partial unless wrapped in one transaction |
| Best at | Bulk transforms | External per-row side effects |
| Cursor mode | Supports | Cost | Use |
|---|---|---|---|
NO SCROLL (default) | FETCH FORWARD only | Cheapest | Streaming reads |
SCROLL | FETCH BACKWARD, ABSOLUTE | Plan materialisation | Interactive paging |
WITH HOLD | Survives COMMIT | Full result spooled to disk | Long exports across requests |
BINARY | Binary row transfer | N/A in SQL | Driver-level optimisation |
| Loop pattern | Equivalent set-based form |
|---|---|
FOR r IN SELECT ... LOOP UPDATE ... | UPDATE ... FROM (SELECT ...) s WHERE t.id = s.id |
| Running total per row | SUM(x) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) |
| Per-row call to another table | JOIN LATERAL or a correlated aggregate |
| Delete in chunks | DELETE ... WHERE id IN (SELECT id ... LIMIT 5000) repeated |
| N-level hierarchy walk | WITH RECURSIVE |
Practical edge cases and deep dives
1. Replacing a per-row update with UPDATE ... FROM
The most common cursor in production updates one table from another.
-- Cursor version: thousands of statements, locks held throughout
FOR r IN SELECT o.id, SUM(i.qty * i.price) AS t
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
LOOP
UPDATE orders SET total = r.t WHERE id = r.id;
END LOOP;
-- Set-based version: one statement, one plan, one lock window
UPDATE orders o
SET total = agg.t
FROM (
SELECT order_id, SUM(qty * price) AS t
FROM order_items GROUP BY order_id
) agg
WHERE agg.order_id = o.id;If the value comes from an external API rather than a table, the set-based door closes and the cursor becomes reasonable — but batch it.
2. Chunked maintenance: the one cursor nobody regrets
Deleting 5 million rows in one statement bloats the table, holds locks for minutes and can exhaust transaction ids. Chunking keeps each transaction short without a cursor at all.
DO $$
DECLARE deleted int;
BEGIN
LOOP
DELETE FROM events
WHERE id IN (
SELECT id FROM events WHERE created_at < now() - interval '180 days'
ORDER BY id LIMIT 5000
);
GET DIAGNOSTICS deleted = ROW_COUNT;
EXIT WHEN deleted = 0;
COMMIT; -- one transaction per chunk
END LOOP;
END $$;Each chunk is a set-based statement; the loop only bounds the transaction. This pattern is what "use a cursor for large deletes" usually means in practice.
3. A cursor that runs forever because of a concurrent write
Under READ COMMITTED, a cursor's snapshot is taken per FETCH in some configurations and per DECLARE in others. In PostgreSQL a non-holdable cursor sees rows committed after its DECLARE only if the query re-reads them, which makes the result nondeterministic when a writer inserts matching rows during the loop. The same loop can terminate or not, depending on timing.
-- Stable: pin the work set before the loop
CREATE TEMP TABLE work ON COMMIT DROP AS
SELECT id FROM jobs WHERE status = 'queued';
FOR r IN SELECT id FROM work LOOP ... END LOOP;4. WITH HOLD writes the entire result to disk
DECLARE cur CURSOR WITH HOLD FOR SELECT * FROM events; -- 40 GB tableAt COMMIT, PostgreSQL must save the remaining rows, so -- 40 GB lands in pgsql_tmp and the COMMIT blocks until it is written. Holdable cursors are for result sets in the thousands of rows, not exports of production tables. For real exports, stream with COPY (SELECT ...) TO STDOUT or paginate with keyset ranges.
-- Keyset pagination: no cursor, constant cost per page
SELECT id, created_at FROM events
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 1000;5. The N+1 that a cursor hides inside an application
A cursor in application code (fetchmany in a loop, psycopg2 named cursor, JDBC setFetchSize) is fine. The pathology is a cursor-shaped loop where each iteration issues its own query.
# 1 + N queries
for row in cur.fetchall():
detail = db.execute("SELECT * FROM items WHERE order_id = %s", (row[0],))One join returns everything in one plan:
SELECT o.id, o.total, i.sku, i.qty
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';6. LATERAL gives you per-row computation without procedural code
SELECT o.id, top.sku, top.qty
FROM orders o
CROSS JOIN LATERAL (
SELECT sku, qty FROM order_items i
WHERE i.order_id = o.id
ORDER BY qty DESC LIMIT 3
) top;This is the set-based answer to "for each order, take the top three items" — the case where a cursor loop is most tempting. LATERAL keeps everything in the planner, so the per-order ORDER BY ... LIMIT is still efficient.
7. Recursive CTE instead of a hierarchy cursor
WITH RECURSIVE tree AS (
SELECT id, parent_id, 1 AS depth, name
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, t.depth + 1, c.name
FROM categories c JOIN tree t ON c.parent_id = t.id
WHERE t.depth < 10 -- guard against cycles
)
SELECT * FROM tree ORDER BY depth, name;A guard on depth prevents infinite loops from a cycle that the data model does not forbid. Cursors walking parent pointers have exactly the same hazard plus a round trip per level.
Common mistakes and runtime errors
| Error / symptom | Cause | Fix |
|---|---|---|
34000 cursor does not exist | Cursor declared in another transaction | Keep DECLARE/FETCH/CLOSE in one transaction |
cursor can only scan forward | FETCH BACKWARD on NO SCROLL | Declare SCROLL or page with keyset ranges |
cannot open a cursor with WITH HOLD inside a function | Holdable cursor in a function/DO block | Use a client-side cursor instead |
| Loop takes minutes for a small table | One statement per row | Rewrite as UPDATE ... FROM |
pgsql_tmp fills the disk | WITH HOLD on a large result | Stream with COPY, keyset paginate |
| Loop never ends | Concurrent inserts matching the predicate | Materialise the work set first |
| Locks held for the entire loop | One long transaction | Commit per chunk |
MySQL 1329 No data - zero rows fetched | Missing NOT FOUND handler | Declare CONTINUE HANDLER FOR NOT FOUND |
| SQL Server cursor is slow | Default dynamic cursor | Use FAST_FORWARD or STATIC |
| Order not guaranteed in the loop | No ORDER BY in the cursor query | Add explicit ordering |
OPEN ... FOR EXECUTE with parameters fails at runtime | Dynamic SQL type mismatch | Cast parameters, verify with EXPLAIN |
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
SQL JSON and JSONB: Functions, Operators, and Indexing Strategies
Why jsonb is the default for queryable documents, when containment can use a GIN index, and the expression-index fixes for scalar lookups.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.