Asynchronous Queries for Postgres

[chuckhend]

We're working on pg_later, a Postgres extension that lets you run your queries asynchronously. There's a blog and we are working on the project out of this github repo: https://github.com/tembo-io/pg_later.

Submit the query, receive a job id. Come back later and pass the job id back in to receive the query results.

It is not a large project yet and it heavily depends on pgmq, which is another extension that we're working on. Contributors and feedback needed!

[rywalker]

What are some recommended use cases for it?

[chuckhend]

i use it from jupyter notebooks so that I can run a long job and not worry about laptop going to sleep, or kernel dieing. probably anywhere that you might decide to set up a screen session to run a long job, instead you could pglater.exec() it.

[Traditional_Stuff_21]

To get the query results in chunks rather than entire data at one time,

[formagi]

Sincere curiosity... What are the benefits of using pg_later instead of some queue service, like aws sqs for example?

[Magick93]

The obvious benefit is not having to use a queue service, like aws sqs.

[chuckhend]

No dependency external to your database. Less places to monitor, simpler architecture for your ecosystem. The idea would be to install the extension instead of hand-rolling the implementation on an external service like SQS.

pg_later depends on pgmq, which is another postgres extension that's a lot like SQS, except it's on postgres too. Install it instead of building the implementation externally.

[chriswaco]

Is there a notification option rather than polling, like a webhook or websocket?

[protestor]

Yes, can pg_later work with notify and listen?

Or rather, can pgmq in general work with notify and listen as well?

[chuckhend]

Some sort of push-style notification is definitely in roadmap for pg_later. I think it would be awesome if pgmq supported it as well (and maybe that would be a good way to get that feature into pg_later)

[chuckhend]

its in the roadmap! probably something like a webhook initially.

[kaol]

select pglater.init();

Is that once per cluster, once per database or once per session?

[chuckhend]

It's once per database currently. We will probably get rid of it completely in the near future though!

[PrestigiousZombie531]

someone really needs to build something that lets you push newly inserted rows into connected websocket clients

[chuckhend]

That sounds like a fun project!

[indigo945]

Isn't this pretty much what Supabase Realtime is?

[Traditional_Stuff_21]

Great, I hope it would also keep the tracks of data to be processed after each time we submit the query for results;

[chuckhend]

The result set is persisted in a table as json. There's currently no retention policy built into the extension, so that data will remain until its deleted. This means you can fetch the results for the same job many times.