The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Declare forward-only cursor | DECLARE export_rows NO SCROLL CURSOR FOR
SELECT id, created_at
FROM app.events
ORDER BY id; | View examples |
| Fetch a bounded batch | FETCH FORWARD 500 FROM export_rows; | View examples |
| Close a cursor | CLOSE export_rows; | View examples |
| Inspect session cursors | SELECT name, is_holdable, is_scrollable, creation_time
FROM pg_cursors; | View examples |
| Declare scrolling cursor | DECLARE audit_scan SCROLL CURSOR FOR
SELECT event_id, occurred_at
FROM app.audit_events
ORDER BY event_id; | View examples |
| Fetch one absolute row | FETCH ABSOLUTE 1000 FROM audit_scan; | View examples |
| Move without returning rows | MOVE FORWARD 500 FROM audit_scan; | View examples |
| Keep a cursor after commit | DECLARE export_hold NO SCROLL CURSOR
WITH HOLD FOR
SELECT id, payload
FROM app.events
ORDER BY id; | View examples |
| Lock rows as fetched | DECLARE jobs NO SCROLL CURSOR FOR
SELECT job_id
FROM app.jobs
WHERE state = 'ready'
ORDER BY job_id FOR
UPDATE SKIP LOCKED; | View examples |
| Update current cursor row | UPDATE app.jobs
SET state = 'claimed'
WHERE CURRENT OF jobs; | View examples |
| Return a refcursor | OPEN c FOR
SELECT id
FROM app.events
ORDER BY id; RETURN c; | View examples |
| Fetch a resumable keyset batch | SELECT id, payload
FROM app.events
WHERE id > 50000
ORDER BY id
LIMIT 500; | View examples |
| Bound cursor transaction time | SET LOCAL idle_in_transaction_session_timeout = '60s'; | View examples |
PostgreSQL cursors let one session consume a query result in bounded batches without returning every row to the client at once. They are transaction- and connection-scoped portals, not durable queues or stateless page tokens. Choose forward-only cursors by default, understand snapshot and row-lock lifetime, budget temporary storage for held cursors, close resources explicitly, and prefer keyset checkpoints when a job must survive connection loss or primary failover.
Step by step
Detailed examples
Keep the cursor, transaction, and connection lifecycle together
DECLARE opens a server portal. A cursor without WITH HOLD must be declared inside a transaction and closes automatically at COMMIT or ROLLBACK; it also disappears when its session ends. Fetching reduces client-side peak memory, but the executor can still sort, hash, or materialize according to the plan. Use NO SCROLL unless reverse movement is required, fetch a measured batch size, and CLOSE promptly. Transaction-pooling proxies cannot safely hand a session cursor to a later request unless they pin the same server connection.
BEGIN READ ONLY;
SET LOCAL statement_timeout = '15min';
SET LOCAL idle_in_transaction_session_timeout = '60s';
DECLARE export_rows NO SCROLL CURSOR FOR
SELECT event_id, occurred_at, payload
FROM app.events
ORDER BY event_id;
FETCH FORWARD 500 FROM export_rows;
-- Repeat FETCH on the same connection while actively consuming rows.
CLOSE export_rows;
COMMIT;Note: Do not insert application think-time between batches. An open transaction retains resources and can impede vacuum even when the cursor is idle.
Use explicit scrolling only when traversal semantics require it
SCROLL permits backward and positional fetches, but may add executor work or materialization. ABSOLUTE navigation walks intervening rows and negative positions can require reading the result to its end; it is not an indexed page jump. Volatile functions can be evaluated again when a scrollable cursor revisits rows, producing surprising results. Prefer NO SCROLL for volatile expressions and sequential batch consumers, and use an indexed keyset predicate for interactive pagination.
BEGIN READ ONLY;
DECLARE audit_scan SCROLL CURSOR FOR
SELECT event_id, occurred_at, actor_id
FROM app.audit_events
ORDER BY event_id;
FETCH FORWARD 25 FROM audit_scan;
FETCH PRIOR FROM audit_scan;
MOVE ABSOLUTE 0 FROM audit_scan;
FETCH NEXT FROM audit_scan;
CLOSE audit_scan;
COMMIT;Note: Avoid volatile functions such as clock_timestamp() in a cursor that can re-fetch rows.
Budget held cursors as materialized session state
WITH HOLD keeps a cursor after a successful commit in the same session. PostgreSQL copies its result into memory or temporary files, so the cursor no longer tracks later table changes and can consume significant temp storage and I/O. It cannot be combined with FOR UPDATE or FOR SHARE. Held cursors survive commits but not connection loss, rollback of their creating transaction, failover, or explicit CLOSE. They are useful for short cross-transaction reads, not durable resumability.
BEGIN READ ONLY;
SET LOCAL temp_file_limit = '2GB';
DECLARE export_hold NO SCROLL CURSOR WITH HOLD FOR
SELECT event_id, occurred_at, payload
FROM app.events
WHERE occurred_at < TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY event_id;
COMMIT;
FETCH FORWARD 500 FROM export_hold;
CLOSE export_hold;Note: temp_file_limit is per process and privilege-controlled. Size it from tested plans and observe temp-byte and storage headroom.
Make row-lock scope explicit for claiming workflows
A cursor query with FOR UPDATE or FOR SHARE locks rows when they are fetched. SCROLL and WITH HOLD are incompatible with locking cursors. WHERE CURRENT OF is dependable when the cursor is declared FOR UPDATE and targets one updatable table; otherwise concurrent changes or plan shape can make it fail or affect no row. Locks remain until transaction end, not CLOSE, so process small batches and commit promptly. SKIP LOCKED supports queue-like workers but provides an intentionally inconsistent view and is unsuitable for general reporting.
BEGIN;
SET LOCAL lock_timeout = '2s';
DECLARE jobs NO SCROLL CURSOR FOR
SELECT job_id
FROM app.jobs
WHERE state = 'ready'
ORDER BY job_id
FOR UPDATE SKIP LOCKED;
FETCH NEXT FROM jobs;
UPDATE app.jobs
SET state = 'claimed', claimed_by = current_user, claimed_at = clock_timestamp()
WHERE CURRENT OF jobs;
CLOSE jobs;
COMMIT;Note: Commit releases row locks. For throughput, applications usually claim multiple rows with one UPDATE RETURNING rather than issuing one update per fetch.
Return named portals from PL/pgSQL without losing their transaction
PL/pgSQL cursor variables have type refcursor, which is a text name for an open portal. A function can OPEN a cursor and return its name, but the caller must keep the same transaction and session while fetching it. Bound cursors can take typed parameters; unbound cursors can open dynamic queries, which require the same injection discipline as other dynamic SQL. PostgreSQL has no server OPEN statement for SQL-level cursors because DECLARE opens them immediately; PL/pgSQL OPEN follows its own syntax.
CREATE FUNCTION app.open_customer_events(p_customer_id bigint)
RETURNS refcursor
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = pg_catalog, app
AS $$
DECLARE
c refcursor := 'customer_events';
BEGIN
OPEN c FOR
SELECT event_id, occurred_at, payload
FROM app.events
WHERE customer_id = p_customer_id
ORDER BY event_id;
RETURN c;
END;
$$;
BEGIN;
SELECT app.open_customer_events(42);
FETCH FORWARD 100 FROM customer_events;
COMMIT;Note: Use a predictable portal name only when the caller controls the session and avoids name collisions. Keep SECURITY INVOKER unless a reviewed privilege boundary requires otherwise.
Choose keyset checkpoints when work must resume
A cursor position is ephemeral and cannot be transferred to another backend, replayed on a standby, or restored after failover. Keyset batching records the last processed unique ordering key and issues a new indexed query for the next batch. It survives reconnects and limits transaction duration, but each batch gets a new snapshot under READ COMMITTED. Define whether newly inserted or updated rows belong in the run, use a unique tie-breaker for compound orderings, and persist the checkpoint only after downstream effects are idempotently committed.
SELECT occurred_at, event_id, payload
FROM app.events
WHERE (occurred_at, event_id) >
(TIMESTAMPTZ '2026-08-01 12:30:00+00', 50000)
AND occurred_at < TIMESTAMPTZ '2026-08-02 00:00:00+00'
ORDER BY occurred_at, event_id
LIMIT 500;Note: Back this pattern with an index on (occurred_at, event_id). Fix an upper bound when the export must represent a closed interval.
Account for vacuum, replicas, pooling, and version boundaries
Long cursor transactions retain a snapshot that can delay dead-tuple removal and increase replica conflicts or WAL retention indirectly through workload effects. Cursors themselves are session state and are neither WAL-replicated nor migrated during promotion. Read-only cursors on a hot standby can be canceled by recovery conflicts; clients must reconnect and restart from a durable checkpoint. Set transaction and idle timeouts, label connections with application_name, monitor xact_start and temp usage, and test driver fetch-size behavior against every supported PostgreSQL and pooler version.
SELECT pid, usename, application_name, state,
clock_timestamp() - xact_start AS transaction_age,
wait_event_type, wait_event
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
SELECT name, is_holdable, is_scrollable, creation_time, statement
FROM pg_cursors
ORDER BY creation_time;Note: pg_cursors reports only cursors available to the current session, while pg_stat_activity helps operators find old transactions cluster-wide subject to visibility privileges.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.