Hi all,
Short background: postgres does not support very well queue type tables
in an environment where these queue tables are small in size but heavily
inserted/updated/deleted, while there are activities in the system which
cause long running transactions. The reason is that the queue table
cannot be vacuumed properly due to the long running transactions, and
suffers bloat.
This was a quite big problem for our postgres systems, until I
discovered that there is an alternative solution to vacuuming if the
transactions on the queue table are always short. Beside vacuuming
aggressively the queue table (which works fine most of the time, when I
have no long running transaction currently) I placed a cron job to
cluster the queue table on it's PK index. That seems to work well, the
table is kept small even in the presence of long running transactions.
The only time this is NOT working if some long running transaction
actually accesses the queue table. An example is a full DB backup.
I know this made one of my systems drop it's load significantly.
HTH,
Csaba.