index bloat - Mailing list pgsql-general

From David Esposito
Subject index bloat
Date
Msg-id 200507072048.j67Kms5f019755@relay1.nnco.com
Whole thread Raw
Responses Re: index bloat
List pgsql-general
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@postgresql.org/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


pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: find objects under a specific tablespace
Next
From: Michael Fuhr
Date:
Subject: Re: A trigger that acts on a column with a given column name