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

101–110 of 140 posts

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

#101

Post by jeeybee »

ulrikrasmussen wrote: post #88
jashmatthews wrote: post #60

Earlier quoted context omitted.

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…

> LISTEN/NOTIFY doesn't work through connection pooling What's the problem with using it with connection pooling?

asyncpg clears out any listeners you have setup once a connection is returned to pool. This will lead to 'missed' events. I guess its something of the same story with psycopg?

If event(s) any jobs will be picked up by the next event or by a timer that checks every 30 seconds or so (can be set by the dev.)

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

#103

Post by jashmatthews »

felixyz wrote: post #90
jashmatthews wrote: post #86

Earlier quoted context omitted.

By the time you need append-only job statuses it's better to move to a dedicated queue. Append-only statuses help but they also make the polling query a lot more expensive. Deleting older rows is a nightmare at scale. It leaves holes in the earlier parts of the table and nerfs half the advantage of using append-only in the first place. You end up paying 8kb page IO costs for a single job. Dedicated queues have consta…

With a partitioned table you can painlessly remove old rows. Of course, you then have to maintain your partitions, but that's trivial.

It's far from trivial. Autoanalyze doesn't work on partitioned tables, only on the partitions themselves. Partitioning a busy job queue table is a nightmare in itself.

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

#104

Post by jashmatthews »

iTokio wrote: post #89
jashmatthews wrote: post #86

Earlier quoted context omitted.

By the time you need append-only job statuses it's better to move to a dedicated queue. Append-only statuses help but they also make the polling query a lot more expensive. Deleting older rows is a nightmare at scale. It leaves holes in the earlier parts of the table and nerfs half the advantage of using append-only in the first place. You end up paying 8kb page IO costs for a single job. Dedicated queues have consta…

partitions are often used to drop old data in constant time. They can also help to mitigate io issues if you use your insertion timestamp as the partition key and include it in your main queries.

Yeah the ULID/UUIDs which can be be partitioned by time in this way are AWESOME for these use cases.

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

#105

Post by jashmatthews »

ulrikrasmussen wrote: post #88
jashmatthews wrote: post #60

Earlier quoted context omitted.

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…

> LISTEN/NOTIFY doesn't work through connection pooling What's the problem with using it with connection pooling?

Best to just forget about it and listen on a connection not using pooling.

https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html#lis...

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

#106

Post by [deleted] »

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…

[deleted]

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

#108

Post by [deleted] »

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…

[deleted]
Post reply on HN