Table bloat in 8.3 - Mailing list pgsql-general

From pgsql-general@ian.org
Subject Table bloat in 8.3
Date
Msg-id Pine.LNX.4.64.0811131337030.2470@www.sportsmogul.com
Whole thread Raw
Responses Re: Table bloat in 8.3  (Sam Mason <sam@samason.me.uk>)
Re: Table bloat in 8.3  ("Nikolas Everett" <nik9000@gmail.com>)
Re: Table bloat in 8.3  ("David Wilson" <david.t.wilson@gmail.com>)
List 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

pgsql-general by date:

Previous
From: Sam Mason
Date:
Subject: Re: sort_mem param of postgresql.conf
Next
From: Sam Mason
Date:
Subject: Re: Table bloat in 8.3