|pg_later - Native Asynchronous Queries Within Postgres
|Written by Nikos Vaggalis
|Thursday, 14 September 2023
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:
Execute a SQL query now:
Come back at some later time and provide the job id:
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:
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?
or email your comment to: email@example.com
|Last Updated ( Thursday, 14 September 2023 )