pg_disatch is meant to be a TLE-compliant alternative to pg_later but built on top of pg_cron. What makes it different?

A couple of years back we've explored the pg_later extension which allows you to:

Run native async queries within PostgreSQL, thus shifting this functionality from the application layer to the database layer making it a feature of Postgres itself.

A primary use of it would be when, say, on a laptop you fire a long-running job and walk away, only to find out later on that the job was interrupted due to laptop going to sleep, or kernel dieing. Running asynchronously avoids that issue.

Along those lines, comes pg_dispatch. It's useful when writing database-native server-side logic (in something like PL/pgSQL) and wanting to dispatch side-effects asynchronously in a separate transaction. For instance according to the official documentation, you can use it when:

you have an AFTER INSERT trigger on a user profiles table that is called every time a new user hops in by calling an RPC (remote procedure call). You can offload the bulky and asynchronous side-effects (written as PostgreSQL functions), such as sending notifications to other users or updating large tables storing analytics, thereby unblocking your main RPC, for which the client is still waiting for a response from.

So far so good. But what really makes it different from pg_later?
First of, pg_later is based on pgmq/Postgres Message Queue while pg_disatch is based on pg_cron; but that's an implementation detail of no concern to the end user. Hence, the big difference is that pg_disatch was build to work on TLE sanboxed environments such as Supabase and AWS RDS which are often limited in terms of installing custom native extensions like pg_later.

We've looked at TLEs or Trusted Language Extensions in "Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud" :

PostgreSQL allows user defined functions to be written in various programming languages, that way you can extend your database with powerful features not found in SQL. For instance you can write a PL/Perl procedure to accept a string from your SQL to apply regular expressions to it in order to tokenize it.

But it goes without saying that these procedural functions are not allowed to run on a Cloud environment since installing a new PostgreSQL extension involves having access to the underlying filesystem, which is a no-no. TLE was build for that purpose; it allows you to build high performance PostgreSQL extensions and safely run them on your RDS for PostgreSQL DB instance.

Trusted Language Extensions do not provide access to the filesystem and are designed to prevent access to unsafe resources for the extensions that you create using TLE. Its runtime environment limits the impact of any extension defect to a single database connection. TLE also gives database administrators fine-grained control over who can install extensions, and provides a permissions model for running them.

The maker of pg_disatch when found the need to run async SQL in that kind of environment, ended up writing pg_dispatch as a pg_tle-compliant alternative that can be used in such sandboxes. Supabase has special support for pg_cron and TLE extensions and their package manager DbDev, supports installing them in your PostgreSQL instance as easy as NPM does for Javascript packages.

For the sake of it, just trying to look up pg_later at DbDev yields no results. On the contrary, pg_disatch appears under the maker's namespace "Snehil_Shah/pg_dispatch". Still there's some more prerequisites for installing it before jumping into DbDev:

PostgreSQL >= v13
pg_cron >= v1.5
pgcrypto

After that the installation can be easily done via DbDev/database.dev:

CopySELECT dbdev.install(Snehil_Shah@pg_dispatch>);

You're now ready to dispatch SQL commands for asynchronous execution:

CopySELECT pgdispatch.fire('SELECT pg_sleep(40);');

And here is another difference that depends from the underlying infrastructure. pg_later works as a message queue which in order to come back at some later time to retrieve the results, you do the fetching with fetch_results by giving it the job id:

select pglater.exec(
'select * from pg_available_extensions order by name limit 2' ) as job_id;

select pglater.fetch_results(1);

On the contrary with pg_disatch which is based on pg_cron, firstly you have to schedule the job under Cron semantics, and secondly you don't get any results back since pgdispatch.fire's signature returns VOID. So as it currently stands, pg_disatch  exclusively implements a light fire-and-forget API, although the plan is to extend it with support for tracking job statuses and results, aiming for full feature parity with pg_later. Very soon with version 2.0 it looks like this functionality will be in place.

Until then, there's workarounds. For instance for the pg_later query above, you can manually insert its result into a custom jobs table, like :

SELECT pgdispatch.fire($$
INSERT INTO custom_jobs_table (result)
   SELECT jsonb_agg(row_to_json(t))
   FROM (
     SELECT * FROM pg_available_extensions
    ORDER BY name
    LIMIT 2
  ) t;
$$);

For now you can execute any sort of tasks that you don't care about the output for, without blocking the main transaction. This can include tasks like:

1. Logging
2. Maintenance like cleaning up older records
3. Triggering jobs with logic that interact with external services via webhooks.
4. Heavy SQL writes.

Snehil succinctly states two real life use cases :

Say a user signs up in an application using a Postgres RPC call. (This is a common in setups where business logic is inside the database itself). You can use pgdispatch to trigger a post profile creation process. This can be creating new empty records for that user, creating new accounts or links with external services like Stripe API, etc.

Elaborating one of our use cases, our server is fully database native (we use Postgres RPCs as API endpoints). Our notification service is a separate service that requires two API calls. The first call to trigger the notification, the second to get the receipts which we use to remove invalid notification tokens from our Postgres database. Writing this bulky logic as part of our main RPC will drastically increase latency for our client devices. Hence, we use pgdispatch to create a new notification sending process from the RPC itself when certain events happen.

Superb. So if you find yourself in the same situation with Snehil, needing to run async queries on a sandboxed PostgreSQL environment, then pg_disatch is a no brainer. Try it out and see for yourself.

PS: We'll do an update when version 2.0 comes out. Keep watching.

More Information

pg_dispatch on Github 

pg_dispatch at DbDev

Related Articles

pg_later - Native Asynchronous Queries Within Postgres

Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud

The DbDev Package Manager For PostgreSQL TLEs 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info