Cursor Tutorial

Cursor Tutorial

Difficulty: Beginner Prerequisites: Basic HeliosDB SQL knowledge

SQL cursors let you iterate over large result sets in batches, avoiding the need to load all rows into memory at once. This is essential for processing large tables, streaming exports, and memory-constrained environments.

---

1. Overview

A cursor is a server-side pointer into a query result set. Instead of returning all rows at once, you fetch them in controlled batches.

Workflow:

1. DECLARE — define the cursor and its query
2. FETCH — retrieve the next N rows
3. CLOSE — release cursor resources

---

2. Declaring a Cursor

DECLARE my_cursor CURSOR FOR
SELECT id, name, email FROM users WHERE active = true;

This creates a cursor named my_cursor that will iterate over active users. The query is not executed until the first FETCH.

Naming Rules

• Cursor names are case-insensitive
• Must be unique within the current session
• Any valid SQL identifier works

---

3. Fetching Rows

-- Fetch the first 10 rows
FETCH 10 FROM my_cursor;

Expected output:

 id | name    | email
----+---------+------------------
  1 | Alice   | alice@example.com
  2 | Bob     | bob@example.com
  3 | Carol   | carol@example.com
...
(10 rows)

Fetching More Rows

Each FETCH continues from where the previous one left off:

-- Fetch the next 10 rows (rows 11-20)
FETCH 10 FROM my_cursor;

-- Fetch the next 5 rows (rows 21-25)
FETCH 5 FROM my_cursor;

End of Results

When there are no more rows, FETCH returns an empty result set:

FETCH 10 FROM my_cursor;
-- (0 rows)

---

4. Closing a Cursor

CLOSE my_cursor;

This releases the cursor’s server-side resources. Always close cursors when you are done to free memory.

Automatic Cleanup

Cursors are automatically closed in these situations:

• The session disconnects
• A transaction is rolled back (if the cursor was opened within a transaction)
• The connection times out

---

5. Complete Example: Batch Processing

Process a million-row table in 1000-row batches:

-- Step 1: Declare the cursor
DECLARE batch_cursor CURSOR FOR
SELECT id, amount, status FROM orders WHERE created_at > '2026-01-01';

-- Step 2: Fetch and process in batches
FETCH 1000 FROM batch_cursor;
-- Process these 1000 rows...

FETCH 1000 FROM batch_cursor;
-- Process next 1000 rows...

-- Repeat until FETCH returns 0 rows

-- Step 3: Clean up
CLOSE batch_cursor;

---

6. Example: Export to CSV

Use cursors to stream a large export without loading everything into memory:

-- Open cursor over the full dataset
DECLARE export_cursor CURSOR FOR
SELECT * FROM analytics_events ORDER BY event_time;

-- Fetch in manageable chunks
FETCH 5000 FROM export_cursor;
-- Write to file...

FETCH 5000 FROM export_cursor;
-- Append to file...

-- Continue until exhausted
CLOSE export_cursor;

---

7. Example: Paginated API

Cursors are ideal for implementing server-side pagination:

-- Page 1
DECLARE page_cursor CURSOR FOR
SELECT id, title, author FROM articles ORDER BY published_at DESC;

FETCH 20 FROM page_cursor;  -- Page 1 (rows 1-20)

-- User clicks "Next"
FETCH 20 FROM page_cursor;  -- Page 2 (rows 21-40)

-- User clicks "Next" again
FETCH 20 FROM page_cursor;  -- Page 3 (rows 41-60)

-- Done browsing
CLOSE page_cursor;

---

8. Cursors vs LIMIT/OFFSET

Approach	Memory	Performance on Page N	Consistency
LIMIT/OFFSET	Low per query	Degrades (re-scans rows 1..N)	May see changes between pages
Cursor	Holds position	Constant (no re-scan)	Stable snapshot

Use cursors when:

• Processing very large result sets
• You need consistent snapshots across pages
• Memory is constrained

Use LIMIT/OFFSET when:

• Result sets are small
• Pages are accessed randomly (not sequentially)
• Simplicity is more important than performance

---

9. Troubleshooting

Issue	Cause	Fix
”cursor not found”	Cursor was closed or never declared	Check cursor name; cursors are session-scoped
Empty results on first FETCH	Query matched 0 rows	Verify the query independently with SELECT
Cursor not cleaned up	Forgot to CLOSE	Always CLOSE; rely on auto-cleanup as a safety net only

---

10. Limitations

• Cursors are forward-only (no FETCH BACKWARD or SCROLL)
• Cursor position cannot be reset; declare a new cursor to restart
• Cursors are session-scoped and cannot be shared between connections

---

Next Steps

• Combine cursors with transactions for consistent batch updates
• Use EXPLAIN on the cursor’s query to verify it uses efficient scan paths
• See the Vacuum & Maintenance tutorial for managing storage after large batch operations