Re: table size growing out of control

On Tue, 2002-07-16 at 01:38, Tom Lane wrote:
> Robert Treat <rtreat(at)webmd(dot)net> writes:
> > For the record, we went through this procedure about 2 weeks ago (slow
> > queries, reindex, vacuum, drop/reload) So I am wondering what might be
> > causing the table to grow so large. We run a function against the table
> > about every 5 minutes which updates on average maybe 100 rows and adds
> > rows at the rate of maybe 1 an hour, but otherwise everything else is
> > selects. I wouldn't think that continual updates would have such a
> > adverse effect on table size, and even if so shouldn't vacuum take care
> > of this?
>
> You can do VACUUM FULL if you want to re-shrink the table. If you want
> to stick with plain VACUUMs then you need to do them often enough to
> keep the table size reasonable. You didn't say what your maintenance
> schedule is...

Currently we do a nightly vacuum analyze on the entire database, and
once a week we reindex the table. I suppose that I could increase the
frequency of those vacuums but vacuum itself doesn't seem to be enough
anyway. One thing I picked out from the archives is that vacuum cannot
recover disk space if it cannot obtain an exclusive lock on the table.
If this is still the case (someone confirm this and I'll add a note to
the docs) it might explain part of my problem since that table is almost
continually being updated. I gathered some more statistics that might be
of interest:

sizes from pg_class after the drop/reload of db:

relname | relkind | relpages | mb
-----------------------+---------+----------+----
health_ex_group | i | 20 | 0
health_exception_test | r | 57 | 0

sizes this morning after about 15 hours of use:

relname | relkind | relpages | mb
-----------------------+---------+----------+-----
health_ex_group | i | 6975 | 54
health_exception_test | r | 17053 | 133

as you can see, things have already started to grow. I decided to run a
reindex on the table, and now it shows:

relname | relkind | relpages | mb
-----------------------+---------+----------+-----
health_ex_group | i | 21 | 0
health_exception_test | r | 24839 | 194

which gives me a significant reduction in my index size, but seems to
have actually increased the table size by a large margin as well. Is
this to be considered the norm?

i then ran vacuum analyze on the table which gives me sizes of:

relname | relkind | relpages | mb
-----------------------+---------+----------+-----
health_ex_group | i | 686 | 5
health_exception_test | r | 26331 | 205

still no real benefits. At this point I decided to run vacuum full and
got the following:

rms=# vacuum full analyze verbose health_exception_test;
NOTICE: --Relation health_exception_test--
NOTICE: Pages 26331: Changed 176, reaped 26274, Empty 0, New 0; Tup
5593: Vac 570052, Keep/VTL 0/0, UnUsed 1982957, MinLen 54, MaxLen 78;
Re-using: Free/Avail. Space 204496076/203607088; EndEmpty/Avail. Pages
114/26161.
CPU 1.25s/0.19u sec elapsed 1.43 sec.
NOTICE: Index health_ex_group: Pages 2511; Tuples 5593: Deleted 570052.
CPU 0.33s/2.70u sec elapsed 11.08 sec.
NOTICE: Rel health_exception_test: Pages: 26331 --> 58; Tuple(s) moved:
5593.
CPU 3.39s/3.38u sec elapsed 37.76 sec.
NOTICE: Index health_ex_group: Pages 2519; Tuples 5593: Deleted 5593.
CPU 0.15s/0.04u sec elapsed 1.40 sec.
NOTICE: --Relation pg_toast_11914691--
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_toast_11914691_idx: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Analyzing health_exception_test
VACUUM

this is what I want to see, all of the unused tuples being reclaimed.

checking my sizes again:

relname | relkind | relpages | mb
-----------------------+---------+----------+----
health_ex_group | i | 2519 | 19
health_exception_test | r | 58 | 0

ok, the table seems back in check now, but we still have a slight issue
on the index, but that gets solved by a quick run of reindex:

relname | relkind | relpages | mb
-----------------------+---------+----------+----
health_ex_group | i | 21 | 0
health_exception_test | r | 58 | 0

and now I am back where I belong. I guess my next step now becomes
making a vacuum full & reindex of that table part of my everyday
maintenance. I can do it, but have to admit it seems excessive imho. The
other thing I guess might be to rethink how we are doing updates on that
table, to see if we can optimize it more.

>
> If your overall database is large then you might need to increase the
> size of the free space map (see postgresql.conf).
>

Well, it is large and I do think we need to increase the fsm, is there
any documentation as to the effects of changing it?

Robert Treat