Re: introducing pgasync and pgflow

On Fri, Sep 4, 2026 at 2:52 PM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> Hello,
>
> I'd like to introduce two libraries I've been working on: pgasync and
> pgflow. Both libraries are implemented 100% in SQL and therefore can be
> deployed in just about any environment, only needing the dblink extension
> to be available (although pg_cron is super nice to have as well).
> Both libraries are in beta; they are battle tested but not yet stable
> enough for production guarantees, especially through version upgrades
>

Next, I'd like to provide some additional details regarding pgflow. pgflow
extends the pgasync library by adding dependency processing via a DAG.
There are various solutions in the market that provide similar features
such as Airflow, but AFAIK pgflow is the only implementation that is 100%
in SQL. An all SQL implementation provides various advantages, especially
if the orchestrated work is in or adjacent to the database.

pgflow is stricter than pgasync in that only stored procedures can be
orchestrated, and those stored procedures must have one very specific
argument: flow.callback_arguments_t, which contains various data elements
describing the flow and the arguments within it. Each flow is configured
as a set of nodes, organized through dependencies. Each node may have zero
or more steps, which further subdivide the node into various tasks that run
at the same dependency order. Each node has a routine, which is the name
of the procedure the orchestration service calls, and an optional step
routine, which is called when steps are invoked.

By layering on top of the pgasync, flows adhere to the underlying
concurrency limits. This allows for precise control over processing depth
so that large, complex processing chains can be evaluated holistically for
specific goals: resource utilization tradeoffs, processing time, shared
work, etc. pgflow also offers synchronous execution (a task is considered
complete when the procedure resolves). and asynchronous (task is considered
complete when flow.finish() is called). Synchronous nodes are preferred
for heavy data crunching (and are simpler) while asynchronous nodes are
preferred when invoking functionality external to the database, such as
Lambda functions or API requests.

pgflow can emit DOT diagrams representing the DAG. For example,

If I set up:
CREATE TABLE daily_routine(
task_id BIGINT,
node TEXT,
step_arguments JSONB,
audit TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE PROCEDURE daily_routine(_args flow.callback_arguments_t)
AS
$$
BEGIN
INSERT INTO daily_routine VALUES(_args.task_id, _args.node,
_args.step_arguments);
END;
$$ LANGUAGE PLPGSQL;

SELECT flow.configure_flow('daily_routine',
$j${
"nodes": [
{
"node": "wake_up",
"target": "SELF",
"routine": "daily_routine"
},
{
"node": "get_dressed",
"target": "SELF",
"routine": "daily_routine",
"dependencies": [ {"parent": "wake_up"} ]
},
{
"node": "deliver_children",
"target": "SELF",
"routine": "",
"step_routine": "daily_routine",
"steps": ["Jessy", "Sally", "Billy"],
"dependencies": [ {"parent": "get_dressed"} ]
},
{
"node": "pick_up_coworker",
"target": "SELF",
"routine": "daily_routine",
"dependencies": [ {"parent": "get_dressed"} ]
},
{
"node": "buy_coffee",
"target": "SELF",
"routine": "daily_routine",
"dependencies": [ {"parent": "get_dressed"} ]
},
{
"node": "go_to_work",
"target": "SELF",
"routine": "daily_routine",
"dependencies": [
{"parent": "deliver_children"},
{"parent": "pick_up_coworker"},
{"parent": "buy_coffee"}
]
}
]
}
$j$);

And run it with:

select flow.create_flow('daily_routine', '{}');
create_flow
-------------
5
(1 row)

It will create this diagram for you during or after execution:
<https://imgur.com/a/mFTyrG0>

And I can see the following data in "daily_routine":

postgres(at)test=# select * from daily_routine ;
task_id | node | step_arguments | audit
----------+------------------+----------------+-------------------------------
27500352 | wake_up | {} | 2026-09-14
09:28:13.811968-06
27500359 | get_dressed | {} | 2026-09-14
09:28:13.824336-06
27500361 | pick_up_coworker | {} | 2026-09-14
09:28:13.829852-06
27500362 | buy_coffee | {} | 2026-09-14
09:28:13.829898-06
27500363 | deliver_children | "Billy" | 2026-09-14
09:28:13.829976-06
27500364 | deliver_children | "Sally" | 2026-09-14
09:28:13.829988-06
27500365 | deliver_children | "Jessy" | 2026-09-14
09:28:13.829976-06
27500366 | go_to_work | {} | 2026-09-14
09:28:13.834404-06

All tasks will honor dependency requirements and also ensure that the
maximum concurrency to 'SELF' across all tasks/flows is never exceeded.
There is tons of functionality in this small package :). I'm looking for
some help in building out some UI admin capabilities. We have one for our
internal tooling but it could not be open sourced.

merlin