Working with WITH HOLD cursors in PostgreSQL
The recommended pattern for using WITH HOLD cursors in PostgreSQL is to keep the transaction open only as long as it takes to get the first results in front of the user:
- start a transaction
- create a WITH HOLD cursor for the query
- fetch the first N rows — this can be fast — and return them to the client
- commit the transaction
- fetch more rows as needed, which will be fast once the result set has been materialized
The commit step is the slow one: it materializes the result set. That cost is acceptable in this design because the user is already occupied with the first batch of rows by the time it happens.
