Bloat in primary key indexes has been a long standing issue (although not faced by many), and especially since online rebuild of primary keys was never possible in production environments.
Since version 9.1 we have a nice little feature of being able to change a primary key's underlying index. Look at the 'table_constraint_using_index' clause in ALTER TABLE docs [1]. And example in the same doc specifically shows how to solve the problem in just two commands: <quote> To recreate a primary key constraint, without blocking updates while the index is rebuilt: CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; </quote> [1] http://www.postgresql.org/docs/9.1/static/sql-altertable.html Best regards, PS: Shameless plug: I am credited for this feature :) http://www.postgresql.org/docs/9.1/static/release-9-1.html#AEN107778 On Fri, Jul 6, 2012 at 12:07 PM, rverghese <ri...@hotmail.com> wrote: > We are experiencing a similar problem, even though we are on 8.4 and have > been for a while, and have autovacuum turned on. I have regular concurrent > reindexes on the indexes but the primary key is seriously bloated. I was > considering doing the same thing, that is, create another primary key that > is built on a sequence ( primarily for slony) and then change my current > multi-column primary key to a unique index. Have you been able to work > around the problem in any other way? > > Thanks > RV > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Primary-key-vs-unique-index-tp3883778p5715712.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company