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 <r...@xzilla.net> wrote: > On Fri, Mar 22, 2024 at 8:05 AM Dominique Devienne <ddevie...@gmail.com> > wrote: > > > > On Fri, Mar 22, 2024 at 12:58 PM ushi <u...@mailbox.org> 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
