Thread: Table bloat in 8.3

I am somewhat new to Postgresql and am trying to figure out if I have a
problem here.

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.

A normal VACUUM does not shrink the table size, but FULL does, or dumping
and restoring the database to a test server.

I know that some extra space is useful so disk blocks don't need to be
allocated for every insert, but this seems excessive.

My question is... should I be worrying about this or is this expected
behaviour?  I can run a daily VACUUM but if this is indicating a
configuration problem I'd like to know.

Here is an example table.   The disk size is reported at 14,049,280 bytes.

pg_stat_user_tables for the live db...  table size is 14,049,280 bytes.

seq_scan         | 32325
seq_tup_read     | 39428832
idx_scan         | 6590219
idx_tup_fetch    | 7299318
n_tup_ins        | 2879
n_tup_upd        | 6829984
n_tup_del        | 39
n_tup_hot_upd    | 420634
n_live_tup       | 2815
n_dead_tup       | 0

And after it is dumped and restored... size is now 188,416 bytes.

seq_scan         | 8
seq_tup_read     | 22520
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 2815
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 2815
n_dead_tup       | 0

I checked for outstanding transactions and there are none.

Thanks!

--
Ian Smith

On Thu, Nov 13, 2008 at 02:03:22PM -0500, 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.
>
> A normal VACUUM does not shrink the table size, but FULL does, or dumping
> and restoring the database to a test server.

I'd not expect to use a FULL vacuum as part of routine maintaince.
Normally, tables like this will grow until they reach some steady state
and then stay there.  14MB seems a bit big for something that you'd
expect to fit in 200KB though.  Autovacuum is enabled by default in 8.3,
but has it been disabled for some reason here?

A useful thing to post would be the output of a VACUUM VERBOSE on this
table when it's grown for a day.  It may give some clue as to what's
going on.


  Sam

That is the expected behavior.  Postgres doesn't give back disk like Java doesn't give back memory.  It keeps a map of where the free space is so it can use it again.

It does all this so it doesn't have to lock the table to compact it when VACUUMing.  VACUUM FULL does lock the table to compact it.  In practice, if you keep your free space map large enough and you have enough rows, your tables settle down to a size close to what you'd expect.

I hope that helps,

--Nik

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.
>
> seq_scan         | 32325
> seq_tup_read     | 39428832
> idx_scan         | 6590219
> idx_tup_fetch    | 7299318
> n_tup_ins        | 2879
> n_tup_upd        | 6829984
> n_tup_del        | 39
> n_tup_hot_upd    | 420634
> n_live_tup       | 2815
> n_dead_tup       | 0

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.

--
- David T. Wilson
david.t.wilson@gmail.com

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.
>>
>> seq_scan         | 32325
>> seq_tup_read     | 39428832
>> idx_scan         | 6590219
>> idx_tup_fetch    | 7299318
>> n_tup_ins        | 2879
>> n_tup_upd        | 6829984
>> n_tup_del        | 39
>> n_tup_hot_upd    | 420634
>> n_live_tup       | 2815
>> n_dead_tup       | 0
>
> 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.

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