Re: Strange VACUUM behaviour - Mailing list pgsql-general

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461