Hi, I have a table with the following usage characteristic: INSERT bulk data using INSERT INTO ... SELECT ... FROM -- this table uses a varchar(50) for the PK -- the PK is rarely (effectively never unless a mistake was made) altered -- always appending to the existing table; some bulk deletions in the past -- no toasting of values UPDATE these records many, many times (but never the PK) At the moment the table itself (and thus all columns) is showing as size of just under 5GB The corresponding index for the PK (a single column, also present on the table...) is between 8-10GB [1] I have never REINDEXed this table that I can recall. I am guessing that it is the need for the index to point to new versions of the physical record that the index is churning so much and causing this kind of bloat? [1] I actually inadvertently have two indexes, one unique and one not, over the same field. The sizes of these two indexes are the rough endpoints of the 8-10GB range I provided. It appears that both indexes are being chosen for use by the planner. I am preparing to REINDEX the unique index and DROP the non-unique one over the same field - probably Tuesday evening. Does everything I am saying here sound kosher or would someone like me to provide additional information? The deletions were fairly voluminous when they occurred and so I would have expected that enough empty pages would have been freed to avoid any significant index bloat. I have mostly left VACUUMing to auto-vacuum though the occasional manual one has been performed. Never performed VACUUM FULL. Been running 9.0 on Ubuntu 10.04 since day one with a pg_restore load. A while ago and no comparative metrics available. I greatly appreciate any sanity checks you can provide. Thanks! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Primary-Key-Index-Bloat-tp5778840.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
