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.
Here are some more details on pgasync architecture (pgflow is coming up). It's worth comparing to libraries like pgmq which rely on contention around the lock table to prevent concurrent requests to the same task. pgasync is single-threaded, pushing tasks out rather than waiting for requestors to ask for them. This completely eliminates contention on the lock table, allowing for improved throughput under certain conditions at the cost of some complexity because a separate orchestrator process must be maintained. Any process can push tasks, but task completion handling is transferred to the orchestrator process by 'pushing' a task finish event whenever async.finish() is invoked.
Tasks can be created with or without a routine (query), and can operate in asynchronous mode (the task is complete when a special finish routine is invoked), or synchronous mode (the task is complete when the query completes). Asynchronous mode is normally useful when the task invokes some kind of non-database process, for example, we make heavy use of the aws_lambda extension, so that the lambda function is responsbile for later invoking async.finish(). From this, we can note a couple of interesting differentiations relative to classic orchestration frameworks:
*) typically orchestration frameworks have to support a large number of targets, with target invocation method being very different for each type of target (database, web request etc)
*) orchestration targets invoke some kind of code which often calls into the database, or perhaps many databases
pgasync, however, only supports ONE target (postgres), allowing the developer to invoke arbitrary endpoints utilizing the rich postgres ecosystem to do the actual work. This flips the classic approach to orchestration: the database invokes code rather than the other way around. This is IMNSHO a great pattern that offers many advantages. For example, you can rely on implicit transaction guarantees (especially in synchronous tasks) to handle work success/failure which can lead to simplified processing in batch environments.
A single-threaded orchestration process brings some other challenges. dblink does not offer asynchronous connections or any kind of poll() feature. This pushes all connection management into SQL. Hangup risks can be mitigated but not completely removed; however, in practice, they tend not to be a problem.
ok, that's enough for now, I'll follow up with some details on the pgflow extension later.
merlin
