Re: Primary key vs unique index

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




--