How to reduce the impact of long-term and / or intensive query in PostgreSQL?

Question

This post assumes that I can use the cursor to retrieve from the request at throttle speed. How can I do it? My goal is to reduce the impact of this low priority request on other higher priority requests.

Answer 1

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

 DECLARE my_cursor CURSOR FOR select * from foo; 

And then read its results again with the FETCH command:

 FETCH 10 FROM my_cursor; 

Sleeping between the FETCH command, you effectively limit the speed of the request.

After that, you can get rid of the cursor by calling COMMIT , ROLLBACK or CLOSE my_cursor

Please note that some types of queries cannot be passed directly using the cursor, but will be run until completion before they print the first line of output. An example would be queries with hash aggregates and large non-indexed sorts. You can lower the cursor_tuple_fraction setting (0.1 by default) to prevent the scheduler from choosing these types of plans, but this is not always possible.

Answer 2

The only way I know to throttle the cursor is to do some work and 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 contains 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