Re: Workqueue performance

Jason Armstrong <ja(at)riverdrums(dot)com> writes:
> My worker processes then 'LISTEN' for the appropriate NOTIFY, select
> the rows from the fileworkqueue.job table according to the
> 'filetype_id', and transfer them. After processing, it deletes the row
> from the workqueue.

> When we are processing without the workers running (ie just insert
> into the log.file table, with the fileworkqueue.job table being filled
> up by the trigger), we see a rate of about 3 milliseconds per insert.
> When it is run with the workers removing data from the
> fileworkqueue.job table, this drops to below 50 Ms.

Not sure if this is the source of your issue, but have you checked how
many dead rows are in pg_listener? Applications that are making heavy
use of NOTIFY/LISTEN tend to need *very* aggressive vacuuming of that
table in order to keep performance up.

(FWIW, 9.0 will have a rewritten notify mechanism that eliminates this
problem. Doesn't help you today though.)

regards, tom lane