How can I reduce the impact of a long-running i/o-intensive query in PostgreSQL?

Question

This post suggests I can use a cursor to fetch from a query at a throttled rate. How do I do this?

My aim is to reduce the impact of this low-priority query on other higher-priority queries.

Answer 1

You can do this by declaring server-side cursors, with the DECLARE command:

DECLARE my_cursor CURSOR FOR select * from foo;

And then read its results using the FETCH command repeatedly:

FETCH 10 FROM my_cursor;

By sleeping between the FETCH command, you're effectively limiting how fast the query can execute.

After you're done with it, you can get rid of the cursor by calling COMMIT, ROLLBACK, or CLOSE my_cursor

Do note that some kinds of queries cannot be directly streamed via a cursor, but will be ran to completion before they produce the first row of output. Queries with hash aggregates and large non-indexed sorts are an example. You can lower the cursor_tuple_fraction setting (default 0.1) to discourage the planner to choose these sorts of plans, but it's not always possible.