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