Re: table size growing out of control

On Wed, 2002-07-17 at 11:14, Manfred Koizar wrote:
> 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?
>

Yesterday I started thinking this as well and after much digging and
swearing that there was no way I was updating more than 300/5 minutes, I
have found an update statement in one of the functions that contains no
where clause. I need to do some more digging, but if I'm right this
means I am updating all 5500 rows every 5 minutes. Actually I am
probably updating more but I think at least 5500 rows are getting
updated!

> >
> >        relname        | relkind | relpages | mb
> >-----------------------+---------+----------+-----
> > health_ex_group       | i       |       21 |   0
> > health_exception_test | r       |    24839 | 194
> >
>
> >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?
>

Everything else falls into place with that many updates. Clearly my FSM
would be too small to remember all of that, so my vacuums had little
chance of being effective. Temporarily I should be able to add a vacuum
every 5 minutes along with the function call to keep things from getting
out of hand until the function is fixed. Thanks to everyone else who
helped out on this, hopefully this thread will prove of use to some
other folks.

Robert Treat