Server-side cursors
CrateDB supports server-side cursors—also known as portals—via the SQL Standard feature F431 (read-only scrollable cursor).
Cursors allow clients to retrieve large query result sets incrementally, fetching a limited number of rows at a time. This is particularly useful when the result set is too large to fit into memory, either on the server or the client.
Table of Contents:
Why Use Cursors?
When dealing with queries that return millions of rows, consuming the entire result set at once can lead to:
Out-of-memory errors on the client or server
Long wait times for results to arrive
Inefficient network usage
Cursors help by allowing you to fetch rows in smaller, manageable chunks, improving responsiveness and resource usage.
Syntax
Declare a Cursor
DECLARE my_cursor CURSOR FOR
SELECT * FROM large_table;This creates a named, server-side cursor associated with the given query.
Fetch Rows
FETCH 100 FROM my_cursor;This retrieves the next 100 rows from the my_cursor result set.
You can continue issuing FETCH commands until all rows are consumed.
Close the Cursor
CLOSE my_cursor;This releases the server-side resources associated with the cursor.
Example
-- Declare the cursor
DECLARE scrolling_cursor CURSOR FOR
SELECT id, name FROM customers ORDER BY id;
-- Fetch the first 50 rows
FETCH 50 FROM scrolling_cursor;
-- Fetch the next 50 rows
FETCH 50 FROM scrolling_cursor;
-- When done, close the cursor
CLOSE scrolling_cursor;Key Characteristics
Read-only: Cursors in CrateDB cannot be used to modify data.
Forward-only: CrateDB supports forward-only cursors; you can only fetch rows in the order returned.
Session-based: Cursors live within the session in which they are declared. Closing the session closes all active cursors.
Memory-efficient: Cursors reduce memory pressure by loading only a portion of the result into memory at a time.
Usage Notes
Cursor declarations and fetch operations must be executed within the same session or connection.
You can only declare one cursor at a time per session in some client drivers; check your client documentation.
If the cursor is not explicitly closed, it will be closed automatically when the session ends
Last updated