Re: Table bloat in 8.3

On Thu, 13 Nov 2008, Scott Marlowe wrote:
> On Thu, Nov 13, 2008 at 1:09 PM, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com> wrote:
> > On Thu, Nov 13, 2008 at 2:03 PM, <pgsql-general(at)ian(dot)org> wrote:
> >> I have several tables that when I run VACUUM FULL on, they are under 200k,
> >> but after a day of records getting added they grow to 10 to 20 megabytes.
> >> They get new inserts and a small number of deletes and updates.
> >
> > Can you define "small number of deletes and updates"? The stats above
> > would disagree with "small". Remember that every update creates a new,
> > updated version of the row, which is where the increase is coming
> > from.
>
> And don't forget to look into failed inserts. Those too create dead tuples.

I finally figured it out. I have three tables, A with child B who has a
child C. I had a query that would count all the rows in C and update the
total to the rows they linked to in B, and then do the same with B to A.
It basicly updated every row in A and B whenever it ran. It was supposed
to only run if the counts got out of sync but a copy slipped into code
that got run a LOT and so that was causing those tables to grow out
of control.

With that query removed I am no longer seeing the db expand.

Thanks for the hints!

--
Ian Smith
www.ian.org