Pg_later: Asynchronous Queries for Postgres

I don't quite understand why blog writers care to include a random AI at the top of their posts. Just give me the content, I don't want to scroll past a random elephant first.

When your LLM is fed by blind Buddhist monks.

Well it turns out an elephant's nose isn't the only appendage that is prehensile…

I'm confused. Can I already do this today without extensions or do I need to install pgAgent first?

Do the queries still have to be serial? I imagine so. The README is not clear but it can be interpreted as me being able to fire more than one query (say 2, but the first one is still running, like pg_background). Is that the case?

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.

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

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);

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.htm...

It is similar functionality to https://docs.snowflake.com/en/developer-guide/sql-api/refere..., the `async` flag more specifically. You submit the query and are given the job id. A background worker runs it like a normal query, but the caller can go about their business while Postgres handles it for them.

I don't think you'd replace a materialized view with pg_later, but it might help you populate or update your materialized view if you are trying to do that asynchronously. pglater.exec() works with DDL too!

I use it a lot for long running queries when doing data science and machine learning work, and a lot of times when executing queries from a jupyter notebook or CLI. That way if my jupyter kernel dies, my query execution continues even if the network or my environment has an issue. I've started using it a bit more with https://github.com/postgresml/postgresml for model training tasks too, since those can be quite long running depending on the situation.

It should be helpful for maintenance and DDL tasks. Such as I need to build a secondary index over a large table and do something once the index is ready. Start the index creation with pg_later, check the status later and do what you need to do next. Probably, they can support triggers so that once a task is completed I can be notified.

Snowflake has too! I'm not familiar with arangodb but I'll check it out.