Table bloat in 8.3

From: pgsql-general(at)ian(dot)org
To: pgsql-general(at)postgresql(dot)org
Subject: Table bloat in 8.3
Date: 2008-11-13 19:03:22
Message-ID: Pine.LNX.4.64.0811131337030.2470@www.sportsmogul.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2008-11-13 19:10:45 Re: Tweaking PG (again)
Previous Message Phoenix Kiula 2008-11-13 18:59:18 Tweaking PG (again)