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

61–70 of 140 posts

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

#61

Post by jashmatthews »

arp242 wrote: post #30
sgarland wrote: post #18

Earlier quoted context omitted.

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; ins…

Transactional Outbox solves this. You use a table like in the first example but instead of actually doing the ElasticSearch update the Outbox table is piped into the dedicated queue.

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

#62

Post by jashmatthews »

nostrebored wrote: post #49
arp242 wrote: post #30

Earlier quoted context omitted.

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; ins…

Most of these two phase problems can be solved by having separate queue consumers. And as far as I can tell, this is only a perk when your two actions are mutate the collocated database and do X. For all other situations this seems like a downgrade.

Do you mean like the consumer for the first phase enqueues a job for the second phase?

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

#64

Post by mickeyp »

jashmatthews wrote: post #60
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…

Putting low throughput queues in the same DB is great both for simplicity and for getting exactly-once-processing. Putting high throughput queues in Postgres sucks because... No O(1) guarantee to get latest job. Query planner can go haywire. High update tables bloat like crazy. Needs a whole new storage engine aka ZHEAP Write amplification as every update has to update every index LISTEN/NOTIFY doesn't work through c…

Update-related throughput and index problems are only a problem if you update tables. You can use an append-only structure to mitigate some of that: insert new entries with the updated statuses instead. You gain the benefit of history also. You can even coax the index into holding non-key values for speed with INCLUDE to CREATE INDEX.

You can then delete the older rows when needed or as required.

Query planner issues are a general problem in postgres and is not unique to this problem. Not sure what O(1) means in this context. I am not sure pg has ever been able to promise constant-time access to anything; indeed, with an index, it'd never be asymptotically upper bounded as constant time at all?

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

#66

Post by bgentry »

earthnail wrote: post #13
cklee wrote: post #4

I’ve been thinking about the potential for PostgreSQL-backed job queue libraries to share a common schema. For instance, I’m a big fan of Oban in Elixir: https://github.com/sorentwo/oban Given that there are many Sidekiq-compatible libraries across various languages, it might be beneficial to have a similar approach for PostgreSQL-based job queues. This could allow for job processing in different languages while main…

This would be so immensely useful. I’d estimate that there are so many cases where the producer is Node or Rails and the consumer is Python.

River ( https://riverqueue.com ) is a Postgres background job engine written in Go, which also has insert only clients in other languages. Currently we have these for Ruby and Python:

https://github.com/riverqueue/riverqueue-ruby

https://github.com/riverqueue/riverqueue-python

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

#67

Post by philippta »

whateveracct wrote: post #53
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…

I've come to hate "Postgres is all you need." Or at least, "a single Postgres database is all you need."

Why?

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

#68

Post by DemocracyFTW2 »

est wrote: post #65

The most simple job queue in MySQL: update job_table set key=value where ... limit 1 It's simple and atomic. Unfortunately PG doesn't allow `update ... limit` syntax

> It's simple and atomic

and almost certainly incorrect, gotta read at least https://www.pgcon.org/2016/schedule/attachments/414_queues-p... which discusses FOR UPDATE SKIP LOCKED

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

#70

Post by est »

DemocracyFTW2 wrote: post #68
est wrote: post #65

The most simple job queue in MySQL: update job_table set key=value where ... limit 1 It's simple and atomic. Unfortunately PG doesn't allow `update ... limit` syntax

> It's simple and atomic and almost certainly incorrect, gotta read at least https://www.pgcon.org/2016/schedule/attachments/414_queues-p... which discusses FOR UPDATE SKIP LOCKED

that's a nice read, but does it also apply to MySQL (InnoDB)?
Post reply on HN