Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

github.com

Post reply on HN
Print view View original

21–30 of 140 posts

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#21

Post by jeeybee »

sgarland wrote: post #18
redskyluan wrote: post #9

there seems to be a big hype to adapt pg into any infra. I love PG but this seems not be right thing.

At low-medium scale, this will be fine. Even at higher scale, so long as you monitor autovacuum performance on the queue table. At some point it may become practical to bring a dedicated queue system into the stack, sure, but this can massively simplify things when you don’t need or want the additional complexity.

I agree, there is no need for FANG level infrastructure. Imo. in most cases, the simplicity / performance tradeoff for small/medium is worth it. There is also a statistics tooling that helps you monitor throughput and failure rats (aggregated on a per second basis)

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#24

Post by severino »

CowOfKrakatoa wrote: post #3

How does LISTEN/NOTIFY compare to using select for update skip locked? I thought listen/notify can lose queue items when the process crashes? Is that true? Do you need to code for those cases in some manner?

I think the usage of listen/notify is just a mechanism to save you from querying the database every X seconds looking for new tasks (polling). That has some drawbacks, because if the timeout is too small, you are making too much queries that usually may not return any new tasks, and if it's too big, then you may start processing the task long after it was submitted. This way, it just notifies you that new tasks are ready so you can query the database.

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#25

Post by eknkc »

redskyluan wrote: post #9

there seems to be a big hype to adapt pg into any infra. I love PG but this seems not be right thing.

Instead of SQS, I recently created a basic abstraction on PG that mimics the SQS apis. The intention was to use it during development and we would simply switch to SQS later.

Never did. The production code still uses PG based queue (which has been improved since) and pg just works perfectly fine. Might still need to go with a dedicated queue service at some point but it has been perfectly fine so far.

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#26

Post by kaoD »

fijiaarone wrote: post #16

You can make anything that stores data into a job queue.

But can you make a decent job queue with anything that stores data? Not easily. E.g. you need atomicity if multiple consumers can take jobs, and I think you need CAS for that, not just any storage will do, right?

You probably need ACI and also D if you want your jobs to persist.

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#27

Post by halfcat »

samwillis wrote: post #2

This looks like a great task queue, I'm a massive proponent of "Postgres is all you need" [0] and doubling down on it with my project that takes it to the extreme. What I would love is a Postgres task queue that does multi-step pipelines, with fan out and accumulation. In my view a structured relational database is a particularly good backend for that as it inherently can model the structure. Is that something you ha…

Is multi-step (fan out, etc) typically something a queue or message bus would handle?

I’ve always handled this with an orchestrator solution like (think Airflow and similar).

Or is this a matter of use case? Like for a real-time scenario where you need a series of things to happen (user registration, etc) maybe a queue handling this makes sense? Whereas with longer running tasks (ETL pipelines, etc) the orchestrator is beneficial?

Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue

#30

Post by arp242 »

sgarland wrote: post #18
redskyluan wrote: post #9

there seems to be a big hype to adapt pg into any infra. I love PG but this seems not be right thing.

At low-medium scale, this will be fine. Even at higher scale, so long as you monitor autovacuum performance on the queue table. At some point it may become practical to bring a dedicated queue system into the stack, sure, but this can massively simplify things when you don’t need or want the additional complexity.

Aside from that, the main advantage of this is transactions. I can do:

  begin;
    insert_row();
    schedule_job_for_elasticsearch();
  commit;
And it's guaranteed that both the row and job for Elasticsearch update are inserted.

If you use a dedicated queue system them this becomes a lot more tricky:

  begin;
    insert_row();
    schedule_job_for_elasticsearch();
  commit; // Can fail, and then we have a ES job but no SQL row.

  begin;
    insert_row();
  commit;
  schedule_job_for_elasticsearch(); // Can fail, and then we have a SQL row and no job.
There are of course also situations where this doesn't apply, but this "insert row(s) in SQL and then queue job to do more with that" is a fairly common use case for queues, and in those cases this is a great choice.
Post reply on HN