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@xzilla.net> wrote:
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.
