pg_later is an interesting project and extension built by Tembo which enables Postgres to execute queries asynchronously.  Fire your query - but don't forget to check later for the result.

Until now in order to interrogate your database asynchronously (i. e queries that return control to the user before the query completes) you would use a framework of a programming language, say Spring Framework, Java and CompletableFuture, to call a callback when the database operation completes, or for instance wait for it using the Snowflake Connector driver for Python which supports asynchronous queries. There's more to it though as you'll find out in Hibernate goes Reactive - What Does That Mean? 

This case is about native async queries within PostgreSQL, moving all that from the application layer to the database layer making it a feature of Postgres itself. So what's a good use case for it?

A primary one that is also detailed in the extension's blog, is when 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.

Using pg_later is as simple as:

CREATE EXTENSION pg_later CASCADE;
SELECT pglater.init();

Execute a SQL query now:

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

Come back at some later time and provide the job id:

select pglater.fetch_results(1);pg_later_results
--------------------
{
"query": "select * from pg_available_extensions order by name limit 2",
"job_id": 1,
"result": [
{
"name": "adminpack",
"comment": "administrative functions for PostgreSQL",
"default_version": "2. 1",
"installed_version": null
}
],
"status": "success"
}

Under the hood, pg_later turns PostgreSQL into a messaging queue without the need of any third party queues with their setting up and pesky monitoring. As a matter of fact pg_later depends on pgmq, which is another postgres extension that's a lot like Amazon Simple Queue Service.

Aside allowing you to fire long running or ad-hoc maintenance jobs and acting as a message queue, another cool use case is when

having a long-running analytical query, for example fulfilling an ad-hoc request like seeing how many new users signed up each day for the past month. You can submit that query and have it run in the background while you continue other work.

After you fire the query you've got to go on manually check whether the job is complete to retrieve the results, in polling fashion. However on the project's road map there's work allocated to make it work with Postgres notify and listen in order to provide push notifications upon the job's completion or failure. This would be a great way to emulate the applications layer's calling a callback upon the job's completion.

Other planned features are:

• Status and progress of in-flight queries
• Security and permission models for submitted queries
• Cursor support for finished jobs (fetch results row by row)
• Kill a query that is in the queue or is currently in flight
• Support for transactions
• Configurable concurrency levels for background works to increase the throughput of jobs
• Retention policies for completed jobs

pg_later is available to build from source but also comes pre-packaged as a Docker image.

To conclude, PostgreSQL never ceases to amaze. Now you get native async queries on top too. What will you use it for?

More Information

pg_later

Related Articles

Connecting To The Outside World with Perl and Ingres Database Events

Hibernate goes Reactive - What Does That Mean?

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