Earlier quoted context omitted.
I've come to hate "Postgres is all you need." Or at least, "a single Postgres database is all you need."
Why?
[deleted]
Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
81–90 of 140 posts
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#82Earlier quoted context omitted.
Yeah it's very basic and limited. However if I am about to use DB as a job queue for budget reasons, I'd make sure the job doesn't get too complicated.
For me it was a lot of small jobs. I was able to get it up to 3500 jobs an hour and likely could have gone far past that but the load on the MySQL server was not reasonable
I was able to push PqQueuer to 25k jobs a second in my benchmarking script.
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#83The Symfony framework (PHP) provides a similar feature, which also relies on LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED: https://symfony.com/doc/current/messenger.html#doctrine-tran... It also supports many other backends including AMQP, Beanstalkd, Redis and various cloud services. This component, called Messenger, can be installed as a standalone library in any PHP project. (Disclaimer: I’m the author of the PostgreS…
[deleted]
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#84there seems to be a big hype to adapt pg into any infra. I love PG but this seems not be right thing.
[deleted]
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#85Broadcaster is one we use in production for PUB/SUB stuff with OPA/OPAL. https://pypi.org/project/broadcaster/
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#86Earlier 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…
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…
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 constant time operations for enqueue and dequeue which don't blow up at random times.
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#87Cool, congrats on releasing. Have you seen graphile worker? Wondering how this compares or if you're building for different use-cases.
I think graphile worker is Node only. This project is for Python.
There is experimental support for arbitrary executables:
https://worker.graphile.org/docs/tasks#loading-executable-fi...
But you can use a thin JS wrapper to make shell calls from Node. Slightly inconvenient, but works well for my use case.
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#88This 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…
> LISTEN/NOTIFY doesn't work through connection pooling
What's the problem with using it with connection pooling?
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#89Earlier quoted context omitted.
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…
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.
Re: Show HN: PgQueuer – Transform PostgreSQL into a Job Queue
#90Earlier quoted context omitted.
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…
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.