Hello all, Executive summary: I have btree index bloat ... I have read all of the threads I could find on the problem and wanted to confirm that there are no tuning parameters that could at least reduce the severity of the problem Detail: PostgreSQL 8.0.1 on RHEL3 Overall Database Size: 9GB Size of "problem" table: 6 million rows Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1 million/day postgresql.conf: shared_buffers = 32768 work_mem = 4096 maintenance_work_mem = 262144 max_fsm_pages = 1000000 max_fsm_relations = 10000 (all other tuning parameters are at their defaults) Index size is in MB 'Clean DB' 'Live DB' 'Slack' ------------------------------------------------------ campaign_patron_unq 215.5 498.1 282.6 campaign_email_pkey 143.1 295.3 152.1 email_patron_idx 143.1 290.8 147.7 referral_idx 95.2 223.7 128.5 email_campaign_idx 143.1 221.5 78.4 email_detail_last_mod_idx 126.1 161.6 35.5 The way I've measured the 'slack' in the index is by restoring a pg_dump of the Live DB to a clean machine and comparing the relpages SELECT oid, relowner, relname, relpages FROM pg_class ORDER BY relpages DESC; I do a nightly VACUUM (not VACUUM FULL) and have my max_fsm_pages and max_fsm_relations set to high levels ... I've never seen any log entries suggesting that I bump either of these values up ... Needless to say, these indexes take over an hour to REINDEX on our live server which is a large problem due to the exclusive locking .. I saw this guy's post (dated April 2005) but saw no responses to it ... Is non-exclusive-locking REINDEX in the works the same way that non-exclusive-locking VACUUM was introduced a few versions ago? http://www.mail-archive.com/pgsql-general@xxxxxxxxxxxxxx/msg59655.html Until then, are there any other tuning parameters I can set to at least minimize the severity of the problem? Thanks in advance, Dave ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
