Re: Vacuum takes a really long time, vacuum full required

On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote:
> > Whatever the case, the database still slows down to a halt after a month or
> > so, and I have to go in and shut everything down and do a VACUUM FULL by
> > hand. One index (of many many) takes 2000 seconds to vacuum. The whole
> > process takes a few hours.
>
> Do a REINDEX on that table instead, and regular vacuum more frequently.
>
> > $ pg_config --version
> > PostgreSQL 7.3.2
>
> 7.4.x deals with index growth a little better 7.3 and older did.

I did a REINDEX of the database. The results are pretty insane, the db went
from 16GB to 381MB. Needless to say things are running a lot faster.

I will now take Tom's well-given advice and upgrade to 7.4. But at least
now I have something to tell my users who are not able to do a DB upgrade
for whatever reason.

Thanks for all your help folks!
-m

Before:
# du -h pgsql
135K pgsql/global
128M pgsql/pg_xlog
80M pgsql/pg_clog
3.6M pgsql/base/1
3.6M pgsql/base/16975
1.0K pgsql/base/16976/pgsql_tmp
16G pgsql/base/16976
16G pgsql/base
16G pgsql

After Reindex:
# du /data/pgsql/
131K /data/pgsql/global
128M /data/pgsql/pg_xlog
81M /data/pgsql/pg_clog
3.6M /data/pgsql/base/1
3.6M /data/pgsql/base/16975
1.0K /data/pgsql/base/16976/pgsql_tmp
268M /data/pgsql/base/16976
275M /data/pgsql/base
484M /data/pgsql/

After Vacuum:
# du /data/pgsql/
131K /data/pgsql/global
144M /data/pgsql/pg_xlog
81M /data/pgsql/pg_clog
3.6M /data/pgsql/base/1
3.6M /data/pgsql/base/16975
1.0K /data/pgsql/base/16976/pgsql_tmp
149M /data/pgsql/base/16976
156M /data/pgsql/base
381M /data/pgsql/

netdisco=> select relname, relpages from pg_class order by relpages desc;

Before:
relname | relpages
---------------------------------+----------
idx_node_switch_port_active | 590714
idx_node_switch_port | 574344
idx_node_switch | 482202
idx_node_mac | 106059
idx_node_mac_active | 99842

After:
relname | relpages
---------------------------------+----------
node_ip | 13829
node | 9560
device_port | 2124
node_ip_pkey | 1354
idx_node_ip_ip | 1017
idx_node_ip_mac_active | 846