5 ms·

Not so much a big user of Postgres. But, out of curiosity, does Postgres have a way to run async queries _and_ have them run in the background with lowest prior

Not so much a big user of Postgres. But, out of curiosity, does Postgres have a way to run async queries _and_ have them run in the background with lowest priority such that it doesn’t affect other important queries and transactions? As an example, a way to run an upsetting query about a cosmetic change in big table without locking rows for other transactions we care about on the same table..?

drsim 3y ago
I'm not aware of a way to prioritize one process over another. The way I've done it is, as you allude to, by managing locks. You set the transaction isolation level as appropriate. You can also batch statements by using a cursor, rather than having a single large blocking transaction.
samaysharma 3y ago
There isn't an explicit concept of priority / resource allocation between queries in Postgres. However, Postgres does that automatically for certain background processes like autovacuum, background worker etc. by allowing you to configure how fast / slow they go. You could implicitly influence how fast / slow something goes by setting per role / database parameters and giving less resources to certain types of queries (https://www.postgresql.org/docs/15/sql-alterrole.html https://www.postgresql.org/docs/15/sql-alterrole.html) or by using explicit locks + lock_timeout to create some kind of a priority.
gavinray 3y ago
I tested the following and it seems to work: CREATE OR REPLACE FUNCTION run_with_adjusted_settings(query_text text) RETURNS SETOF record LANGUAGE plpgsql AS $$ DECLARE result_record record; BEGIN -- set_config ( setting_name text, new_value text, is_local boolean ) → text -- Sets the parameter setting_name to new_value, and returns that value. -- If is_local is true, the new value will only apply during the current transaction. PERFORM set_config('statement_timeout', '10s', true); PERFORM set_config('work_mem', '1MB', true); PERFORM set_config('maintenance_work_mem', '1MB', true); PERFORM set_config('max_parallel_workers_per_gather', '1', true); -- Execute the provided query dynamically and return the results FOR result_record IN EXECUTE query_text LOOP RETURN NEXT result_record; END LOOP; RETURN; END; $$; -- Example usage: SELECT * FROM run_with_adjusted_settings('SELECT 1 as id, false as some_bool;') AS (id int, some_bool boolean);
andruby 3y ago
There's no concept of nice or priority in Postgres. Queries usually stay out of each other's way, unless they're modifying the same data, causing lock contention. What I've done in the past for "less important background queries" is use very short lock_timeout and short statement_timeout values. The query will fail if it can't acquire the lock quickly (and in turn won't hold extra locks), so you put it in a loop with a sleep. https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-STATEMENT-TIMEOUT https://www.postgresql.org/docs/15/runtime-config-client.htm...

drsim 3y ago

I'm not aware of a way to prioritize one process over another. The way I've done it is, as you allude to, by managing locks. You set the transaction isolation level as appropriate. You can also batch statements by using a cursor, rather than having a single large blocking transaction.

samaysharma 3y ago

There isn't an explicit concept of priority / resource allocation between queries in Postgres. However, Postgres does that automatically for certain background processes like autovacuum, background worker etc. by allowing you to configure how fast / slow they go. You could implicitly influence how fast / slow something goes by setting per role / database parameters and giving less resources to certain types of queries (https://www.postgresql.org/docs/15/sql-alterrole.html https://www.postgresql.org/docs/15/sql-alterrole.html) or by using explicit locks + lock_timeout to create some kind of a priority.

gavinray 3y ago

I tested the following and it seems to work: CREATE OR REPLACE FUNCTION run_with_adjusted_settings(query_text text) RETURNS SETOF record LANGUAGE plpgsql AS $$ DECLARE result_record record; BEGIN -- set_config ( setting_name text, new_value text, is_local boolean ) → text -- Sets the parameter setting_name to new_value, and returns that value. -- If is_local is true, the new value will only apply during the current transaction. PERFORM set_config('statement_timeout', '10s', true); PERFORM set_config('work_mem', '1MB', true); PERFORM set_config('maintenance_work_mem', '1MB', true); PERFORM set_config('max_parallel_workers_per_gather', '1', true); -- Execute the provided query dynamically and return the results FOR result_record IN EXECUTE query_text LOOP RETURN NEXT result_record; END LOOP; RETURN; END; $$; -- Example usage: SELECT * FROM run_with_adjusted_settings('SELECT 1 as id, false as some_bool;') AS (id int, some_bool boolean);

andruby 3y ago

There's no concept of nice or priority in Postgres. Queries usually stay out of each other's way, unless they're modifying the same data, causing lock contention. What I've done in the past for "less important background queries" is use very short lock_timeout and short statement_timeout values. The query will fail if it can't acquire the lock quickly (and in turn won't hold extra locks), so you put it in a loop with a sleep. https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-STATEMENT-TIMEOUT https://www.postgresql.org/docs/15/runtime-config-client.htm...