Re: table size growing out of control

On 16 Jul 2002 15:45:08 -0400, Robert Treat <rtreat@webmd.net> wrote:
>> > 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,

Robert, are you sure about those 100 updated rows/5 minutes?

>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

I think I saw you mention that there are 5500 rows.  So you have
approx. 100 rows/page.

>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

This reflects the numbers at the time of your vacuum.

>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?

It did not increase the table size, it did update pg_class with
current numbers.

>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

So in the time between reindex and vacuum your table has grown by 1500
pages or (estimated) 150000 tuples.  That's 30 times the number of
rows, or - in other words - at a rate of 20 rows/minute this growth
would be expected in 100 days.

Now I may be wrong, but ISTM there is a process (or more) running that
does a *lot* of updates.  Can you tell us something about the function
that is supposed to update 100 rows every five minutes?  Is anything
else doing updates you were not aware of at first sight?

Note for example, that
    UPDATE table1 SET col1=col1;
doesn't look like changing anything, but it writes a new version of
every row to the database.

Servus
 Manfred