On Thu, 13 Nov 2008, Scott Marlowe wrote:
> On Thu, Nov 13, 2008 at 1:09 PM, David Wilson <david.t.wilson@gmail.com> wrote:
> > On Thu, Nov 13, 2008 at 2:03 PM, <pgsql-general@ian.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