Re: using a postgres table as a multi-writer multi-updater queue

On 11/23/15 6:12 AM, Ladislav Lenart wrote:
> I suggest an excellent read on this topic:
>
> http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/
>
> Highly recommended if you haven't read it yet.

One thing it doesn't mention that you need to be aware of is the vacuum
workload on a queue table. In a busy queue, it will be difficult or even
impossible for vacuum to keep the amount of dead rows down to something
manageable. That's why PgQ and Slony don't even attempt it; instead,
they rotate through a fixed set of tables. Once all the entries in a
table have been processed, the table is truncated.

If you go the delete route, make sure you don't index any fields in the
queue that get updated (otherwise you won't get HOT updates), and run a
very aggressive manual vacuum so the table stays small.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com