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
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) |