Definition
- Row-by-row processing – a cursor lets procedural code traverse query results one row at a time: declare, open, fetch in a loop, close.
- Opposite of set-based – SQL's natural model operates on entire sets at once; cursors are inherently iterative.
- Last resort – cursors are far slower than set-based statements, hold memory and server state, and can keep locks open for the entire loop.
- Legitimate uses – routing each row to a different procedure, sequential calculations that window functions cannot express, per-row side effects.
- Senior rule – think in sets, not loops; reach for a cursor only when per-row logic truly cannot be a set operation.
A set-based query is like telling the whole room at National Bank of India "everyone with a balance over ten thousand, step forward" and it happens at once. A cursor is like walking down a line of customers one by one, dealing with each individually. For most tasks the "all at once" command is dramatically faster; the one-by-one walk is only needed when each customer's handling truly depends on something you can only decide as you go.
Understanding the Concepts
What is a cursor and what is its lifecycle?
A cursor follows a lifecycle: declare over a SELECT, open to execute the query, fetch each row into variables, loop while rows remain, and close to release.
-- PL/pgSQL cursor at National Bank of India
DO $$
DECLARE
acct RECORD;
cur CURSOR FOR
SELECT id, balance, account_type FROM accounts
WHERE status = 'ACTIVE';
BEGIN
OPEN cur;
LOOP
FETCH cur INTO acct;
EXIT WHEN NOT FOUND;
-- Per-row processing
IF acct.account_type = 'SAVINGS' THEN
-- Route to savings interest procedure
PERFORM apply_savings_interest(acct.id, acct.balance);
ELSIF acct.account_type = 'CURRENT' THEN
-- Route to current account fee procedure
PERFORM apply_current_fee(acct.id, acct.balance);
END IF;
END LOOP;
CLOSE cur;
END $$;
Why is a cursor slow compared to set-based SQL?
A cursor pays per-fetch overhead for every row. Set-based SQL lets the engine optimize and execute in bulk.
-- Set-based: one statement, engine optimizes the whole operation
UPDATE accounts
SET balance = balance * 1.04
WHERE account_type = 'SAVINGS' AND status = 'ACTIVE';
-- Processes millions of rows in one bulk operation
-- Cursor: one fetch per row, per-row overhead
-- On 2 million rows: orders of magnitude slower
┌─────────────────┬───────────────┬───────────────────┐ │ Approach │ 2M rows time │ Lock duration │ ├─────────────────┼───────────────┼───────────────────┤ │ Set-based UPDATE │ ~2 seconds │ Brief per page │ │ Cursor loop │ ~120 seconds │ Entire loop (mins) │ └─────────────────┴───────────────┴───────────────────┘
What resources does a cursor hold?
An open cursor holds memory for its result set and server-side state for its entire lifetime. An updatable or FOR UPDATE cursor can hold row locks for the entire loop.
-- FOR UPDATE cursor: locks every fetched row until close/commit
DECLARE cur CURSOR FOR
SELECT id, balance FROM accounts
WHERE status = 'ACTIVE'
FOR UPDATE;
-- Every fetched row is locked. Other transactions wait.
-- If the loop takes 5 minutes, locks are held for 5 minutes.
When is a cursor legitimately needed?
Cursors are justified when the logic is inherently row-dependent and cannot be expressed as a set operation.
-- Legitimate: route each flagged account to a different review procedure
DO $$
DECLARE
acct RECORD;
cur CURSOR FOR
SELECT id, flag_reason FROM flagged_accounts;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO acct;
EXIT WHEN NOT FOUND;
CASE acct.flag_reason
WHEN 'FRAUD' THEN PERFORM route_to_fraud_team(acct.id);
WHEN 'KYC' THEN PERFORM route_to_kyc_team(acct.id);
WHEN 'DORMANT' THEN PERFORM route_to_reactivation(acct.id);
END CASE;
END LOOP;
CLOSE cur;
END $$;
The different procedure calls per row cannot be expressed as a single UPDATE.
How do you replace a cursor with set-based SQL?
Many cursor patterns can be replaced with window functions, recursive CTEs, or MERGE.
-- Wrong: cursor to apply interest account by account
-- (This is what a junior at National Bank wrote)
-- Correct: single set-based UPDATE
UPDATE accounts
SET balance = CASE
WHEN account_type = 'SAVINGS' THEN balance * 1.04
WHEN account_type = 'CURRENT' THEN balance - 500 -- monthly fee
ELSE balance
END
WHERE status = 'ACTIVE';
How do you implement a read-only forward-only cursor for safety?
When a cursor is unavoidable, use the safest configuration.
-- Read-only, forward-only (no locking, minimal resources)
DECLARE cur NO SCROLL CURSOR FOR
SELECT id, balance FROM accounts
WHERE status = 'DORMANT';
package com.bank.service;
import java.sql.*;
public class DormantAccountProcessor {
public void processDormantAccounts(Connection conn) throws SQLException {
// JDBC: forward-only, read-only cursor with fetch size
String sql = "SELECT id, balance FROM accounts WHERE status = 'DORMANT'";
try (PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY)) {
ps.setFetchSize(100); // fetch 100 rows at a time, not all
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long id = rs.getLong("id");
java.math.BigDecimal balance = rs.getBigDecimal("balance");
routeToDormantReview(id, balance);
}
}
}
}
private void routeToDormantReview(long id, java.math.BigDecimal balance) {
// Per-row side effect that cannot be a set operation
}
}
What are alternatives to cursors in application code?
Instead of a server-side cursor, fetch in batches with keyset pagination.
package com.bank.service;
import java.sql.*;
public class BatchProcessor {
public void processInBatches(Connection conn) throws SQLException {
long lastId = 0;
int batchSize = 1000;
String sql = "SELECT id, balance FROM accounts "
+ "WHERE id > ? ORDER BY id LIMIT ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
while (true) {
ps.setLong(1, lastId);
ps.setInt(2, batchSize);
try (ResultSet rs = ps.executeQuery()) {
int count = 0;
while (rs.next()) {
lastId = rs.getLong("id");
// process row
count++;
}
if (count < batchSize) break;
}
}
}
}
}
What should a senior engineer know?
- Default to set-based SQL. Reach for a cursor only when per-row logic truly cannot be a set operation.
- When forced to use a cursor, keep the result small, use read-only forward-only, fetch in batches, and keep the transaction short.
- Many cursor patterns are replaceable with
CASEexpressions, window functions, recursive CTEs, orMERGE. - The one-line principle: think in sets, not loops.
Visual Overview
Set-Based (preferred) Cursor (last resort)
───────────────────── ────────────────────
UPDATE accounts DECLARE cur CURSOR
SET balance = balance * 1.04 OPEN cur
WHERE type = 'SAVINGS'; LOOP
FETCH cur INTO row
┌─────────────┐ process(row)
│ Engine │ END LOOP
│ optimizes │ CLOSE cur
│ bulk │
│ execution │ ┌─────────────┐
│ ~2 seconds │ │ Per-fetch │
└─────────────┘ │ overhead │
│ ~120 seconds │
│ Holds locks │
└─────────────┘
Key Takeaway
A cursor is a database object that lets procedural code walk through a query's result rows one at a time. This is inherently iterative, the opposite of SQL's natural set-based model where one statement operates on an entire set and the optimizer picks the most efficient plan.
A cursor is legitimately needed only when the logic is genuinely row-dependent and cannot be expressed as a single set operation: routing each row to a different procedure, a sequential calculation that even window functions cannot capture, or per-row side effects. Even many of these can now be rewritten with CASE expressions, window functions, or recursive CTEs.
Cursors are usually a last resort because they pay per-fetch overhead for every row, hold memory and server state for the entire loop, and can keep row locks for the whole duration. At the National Bank of India a junior wrote a cursor to apply month-end interest account by account, which locked rows and ran for minutes, when the correct solution was a single set-based UPDATE that produced the identical result in seconds.
The senior guidance is to default to set-based SQL. When forced to use a cursor, keep the result small, use a read-only forward-only cursor, fetch in batches, and keep the transaction short.
Remember one sentence: think in sets, not loops: a cursor processes rows one at a time and is a slow, resource-holding last resort.
---