3 ms·

The queue approach of `SELECT...FOR UPDATE SKIP LOCKED` is great for low throughput needs. One thing to note: as your throughput increases you will have to deal

The queue approach of `SELECT...FOR UPDATE SKIP LOCKED` is great for low throughput needs. One thing to note: as your throughput increases you will have to deal with bloat. When the processed message rows are deleted, the pages sitting on the disk are not deleted until vacuum is run. If you run an online vacuum it doesn’t delete the page from disk, and a full vacuum (that will free up the disk space) locks the database while it completes the vacuum (which if you’re now dealing with high throughput, is not great). One approach to address bloat without dealing with vacuum is to setup a timestamp-based partition on your queue table. This way you just drop old partitions and it frees the disk space without needing vacuum.

shakow 2y ago
To add some feedback as someone whose company replaced redis with PgSQL after I lobbied for it (leveraging my experience using it in bioinfo HPC beforehand): - agreed, autovacuum is definitely something to keep a close watch on – but as long as disk I/Os are fast enough (i.e. not EBS), our queue tables could fill the space freed for PgSQL but not the OS (the non full VACUUM mentioned by my GP), so in the end, assuming the queue did not grow infinitely, actual disk usage stabilized, and we never had to VACUUM FULL outside of buggy (on our side) situations; - high-throughput is relative: we ran in the range of hundreds to thousands of tasks per second without any issue nor any particular customization, so sure, PgSQL will not hold the load if you are Google, but I'd bet it should satisfy the needs of at least up to the last centile of companies. - I like your idea about timestamp-defined partitions, I will definitely keep this one in mind if the need arises. Now on the pros: - being able to store & manipulate basically all the data handled by our stack from a single source sounds like a minute detail, but actually helps a lot when we have to debug an issue; - being able to use virtually any language and still be able to access all the persistent data from our stack from a single source revealed itself to be an unexpected advantage to develop a quick'n dirty e.g. python/nim/racket script to explore data/debug/generate stats/etc.; - our PgSQL machines ran much better “by themselves” than we feared at first, and finally were far from being the main point of contention on day-to-day ops – in the end we just altered a couple of settings and just... let them be; - PgSQL provides a lot of tools to inspect it live, which provides additional help to identify bottlenecks in (i) your server setup and (ii) your stack; - PgSQL support of JSON/JSONB is actually very good, and very helpful to store that hard-to-normal-form-ize last part of your data.
- halfcat 2y ago
  Great response! > ”PgSQL provides a lot of tools to inspect it live” Any particular tools you like for this?
  - shakow 2y ago
    “Tool” may not be the right word; but being able to live monitor the vacuumer, to understand what table was taking how much space and why, to track min/mean/max execution time of queries, and, more generally, all the internal metrics that PgSQL exposes through a couple of SELECT has been a boon when debugging or optimizing.
FreakLegion 2y ago
Plain vacuum is generally all you want. At that point the space is freed for new inserts and updates to the table. Full vacuum is for giving the space back to the OS, which rarely makes sense if you'll still be writing more rows. In cases where freeing to the OS really is what you need, though, use something like pg_repack.

shakow 2y ago

To add some feedback as someone whose company replaced redis with PgSQL after I lobbied for it (leveraging my experience using it in bioinfo HPC beforehand): - agreed, autovacuum is definitely something to keep a close watch on – but as long as disk I/Os are fast enough (i.e. not EBS), our queue tables could fill the space freed for PgSQL but not the OS (the non full VACUUM mentioned by my GP), so in the end, assuming the queue did not grow infinitely, actual disk usage stabilized, and we never had to VACUUM FULL outside of buggy (on our side) situations; - high-throughput is relative: we ran in the range of hundreds to thousands of tasks per second without any issue nor any particular customization, so sure, PgSQL will not hold the load if you are Google, but I'd bet it should satisfy the needs of at least up to the last centile of companies. - I like your idea about timestamp-defined partitions, I will definitely keep this one in mind if the need arises. Now on the pros: - being able to store & manipulate basically all the data handled by our stack from a single source sounds like a minute detail, but actually helps a lot when we have to debug an issue; - being able to use virtually any language and still be able to access all the persistent data from our stack from a single source revealed itself to be an unexpected advantage to develop a quick'n dirty e.g. python/nim/racket script to explore data/debug/generate stats/etc.; - our PgSQL machines ran much better “by themselves” than we feared at first, and finally were far from being the main point of contention on day-to-day ops – in the end we just altered a couple of settings and just... let them be; - PgSQL provides a lot of tools to inspect it live, which provides additional help to identify bottlenecks in (i) your server setup and (ii) your stack; - PgSQL support of JSON/JSONB is actually very good, and very helpful to store that hard-to-normal-form-ize last part of your data.

halfcat 2y ago
Great response! > ”PgSQL provides a lot of tools to inspect it live” Any particular tools you like for this?
- shakow 2y ago
  “Tool” may not be the right word; but being able to live monitor the vacuumer, to understand what table was taking how much space and why, to track min/mean/max execution time of queries, and, more generally, all the internal metrics that PgSQL exposes through a couple of SELECT has been a boon when debugging or optimizing.

FreakLegion 2y ago

Plain vacuum is generally all you want. At that point the space is freed for new inserts and updates to the table. Full vacuum is for giving the space back to the OS, which rarely makes sense if you'll still be writing more rows. In cases where freeing to the OS really is what you need, though, use something like pg_repack.