Re: Alternative for vacuuming queue-like tables

[Csaba Nagy <nagy(at)ecircle-ag(dot)com>]

On Fri, 2006-04-28 at 15:20, kmh496 wrote:
> 2006-04-28 (금), 14:40 +0200, Csaba Nagy 쓰시길:
> > I placed a cron job to
> > cluster the queue table on it's PK index.
> what does that mean?

Means execute:

CLUSTER pk_queue_table ON queue_table;

See http://www.postgresql.org/docs/8.1/static/sql-cluster.html for more
details on CLUSTER.

This will actually rebuild the table I guess after it can lock it
exclusively. Due to the fact that the queue transactions are very short
lived, the exclusive lock is fast acquired and the clustering operation
is also fast as the table has only a few entries.

I'm not sure how this operation can work in the presence of other long
running transactions which did not touch the queue table yet, but it
actually does work, I can confirm that. Is it violating MVCC maybe ?

Cheers,
Csaba.