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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org