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..?
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.htm...
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) or by using explicit locks + lock_timeout to create some kind of a priority.
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);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.