Re: Table bloat in 8.3

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(at)ian(dot)org> wrote:

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>