We developed a home-grown queue system using Postgres, but its performance was largely hindered by que tables bloating and the need to continuously vacuum them. It did not scale whatsoever. With some workarounds, we ended up designing three sets of queue tables, switching between them based on some queue stats, vacuum the inactive set, and repeat.
We kept this queue system for low SLA app components. For others, we switched to Kafka. Investing in learning and implementing purpose built queue systems pays off for the long term.
On Fri, Mar 22, 2024 at 8:31 AM Robert Treat <rob@xxxxxxxxxx> wrote:
On Fri, Mar 22, 2024 at 8:05 AM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
>
> On Fri, Mar 22, 2024 at 12:58 PM ushi <ushi@xxxxxxxxxxx> wrote:
>>
>> i am playing with the idea to implement a job queuing system using PostgreSQL.
>
>
> FYI, two bookmarks I have on this subject, that I plan to revisit eventually:
> * https://news.ycombinator.com/item?id=20020501
> * https://www.enterprisedb.com/blog/listening-postgres-how-listen-and-notify-syntax-promote-high-availability-application-layer
>
> If others have links to good articles on this subject, or good input to give in this thread, I'd be interested. Thanks, --DD
This is a well worn topic within the postgres community, with a number
of different implementations, but a couple of links that are probably
worth looking at would be:
- https://wiki.postgresql.org/wiki/PGQ_Tutorial, probably the first
queue system that gained wide adoption
- https://brandur.org/river, a new queue system based on postgres/go,
which also has a link to an article about why the authors had ditched
postgres based queueing in favor of redis some years before which is
worth a read to understand some of the issues that Postgres has as the
basis for a queue system.
And yeah, I suspect this may become a hot topic again now that Redis
is moving away from open source:
https://redis.com/blog/redis-adopts-dual-source-available-licensing/
Robert Treat
https://xzilla.net
----------------------------------------
Thank you
Thank you
