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

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.

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.

The only way I know to throttle a cursor is to do some work, then sleep.

CREATE OR REPLACE FUNCTION test_cursor()
  RETURNS void AS
$BODY$

DECLARE
    curs1 CURSOR FOR SELECT select * from information_schema.tables limit 5;

BEGIN

    FOR example_variable IN curs1 LOOP
        -- Other pgsql statements

        -- sleep for one second
        perform pg_sleep(1);

    END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

The source code for pg_dump includes pseudo-code for its "throttle" algorithm, but just sleeping for a fixed period is probably good enough.

* If throttle is non-zero, then
*      See how long since the last sleep.
*      Work out how long to sleep (based on ratio).
*      If sleep is more than 100ms, then
*          sleep
*          reset timer
*      EndIf
* EndIf