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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general