Why not have sl_event_[1,2] and sl_confirm_[1,2]?  They seem
to have similar levels of churn.

SkyTools PGQ rotates their queues between three tables every 5 minutes.
See do_maintenance function:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/skytools/skytools/python/pgq/maint.py?rev=1.2&content-type=text/x-cvsweb-markup

The do_maintenance function calls these plpgsql functions  
(maint_rotate_tables_step1, maint_rotate_tables_step2,  
maint_tables_to_vacuum) defined here:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/skytools/skytools/sql/pgq/functions/pgq.maint_rotate_tables.sql?rev=1.3&content-type=text/x-cvsweb-markup

They're design was to "We use table rotation to decrease hard disk io."


On Feb 8, 2008, at 9:00 AM, Christopher Browne wrote:

> "Jan Wieck" <JanWieck at Yahoo.com> writes:
>> It not only can, it definitely will hurt performance. A Slony
>> replica experiences the same updates, row per row, as the origin
>> did. This means that the need for vacuuming is exactly the same.
>
> Well, there are *some* differences.
>
> Any transactions that roll back on the origin won't be reflected at
> all on subscribers, so if a system sees a lot of transaction
> ROLLBACKs, that will a point of non-correspondance.
>
> But Christian pointed out three specific Slony-I-managed tables that
> are worth at least a quick look...
>
> - sl_log_2 consisted 91% of dead tuples
> - sl_event had 57% dead tuples
> - sl_confirm was composed of 86% dead tuples
>
> In the 1.2 branch, we do periodically rotate between sl_log_1 and
> sl_log_2 so that *that* space will eventually get reclaimed via a
> TRUNCATE.  In the 2.0 branch, we'll be moving to a strategy of solely
> running TRUNCATE against sl_log_{*}, so there should never be any dead
> tuples except insofar as some replicable activity got recently rolled
> back.
>
> The same is not true for the other two tables.
>
> It seems to me that sl_confirm and sl_confirm could really use a
> CLUSTER (or similar), here, as the tables are mostly comprised of
> "dead space."
>
> Mind you:
>
> - They are regularly vacuumed by the cleanup loop, probably every 10
>   minutes
>
> - Access usually offers reasonable possibilities for index usage,
>   which should allow access to be reasonably efficient even if they
>   have some "bloat."
>
> The existing structure of Slony-I tries to vacuum them immediately
> after purging data from them; I can't see autovacuum doing a better
> job than that.
> -- 
> (format nil "~S@~S" "cbbrowne" "linuxfinances.info")
> http://cbbrowne.com/info/multiplexor.html
> "Nondeterminism means never having to say you're wrong."  -- Unknown