Re: Strange VACUUM behaviour

One issue is that pg_toast tables can't vacuum rows until their
respective rows have been deleted by vacuuming the base table. But it's
still odd that the count decreases by 4 each time you run it.

As for the length of time, that could be due to heavily loaded hardware.
You might do better if you increase vacuum_memory (or whatever the
setting was called in 7.4...)

That index does have about 20% bloat though; so a reindex would probably
be a good idea.

You might ask on the slony list...

On Fri, Nov 25, 2005 at 02:34:45PM +0100, Florian G. Pflug wrote:
> Hi
>
> We started a VACUUM (not a VACUUM FULL) on one of your postgres 7.4.9
> databases a few days ago. It's still running yet, and says the
> folloing about once per second:
>
> INFO: index "pg_toast_2144146_index" now contains 1971674 row versions
> in 10018 pages
> DETAIL: 4 index row versions were removed.
> 2489 index pages have been deleted, 0 are currently reusable.
>
> The number of row versions decreases by 4 each time the message is logged.
>
> The file belonging to pg_toast_2144146_index has about 80MB,
> for pg_toast_2144146 there are 6 files, five of them are
> 1GB, the last one is about 5MB in size. The "original" relation
> (the one that references pg_toast_2144146 in it's reltoastrelid field)
> has one datafile of 11MB.
>
> The "original" relation is called image, and is defined the following:
> Table "public.image"
> Column | Type | Modifiers
> ---------------+------------------------+-----------
> id | bigint | not null
> image_code_id | bigint |
> mandant_id | bigint |
> name | text |
> dat | text |
> mime | text |
> size | bigint |
> md5 | bytea |
> path | text |
> copyright | character varying(255) |
> Indexes:
> "image_pkey" primary key, btree (id)
> "i_image_id" btree (id)
> Triggers:
> _gti_denyaccess_17 BEFORE INSERT OR DELETE OR UPDATE ON image FOR
> EACH ROW EXECUTE PROCEDURE _gti.denyaccess('_gti')
>
> The table is part of a slony tableset, which is subscribed on this database.
>
> Is there a reason that this vacuum takes so long? Maybe some lock
> contention because slony replicates into this table?
>
> greetings, Florian Pflug

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461