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

On Fri, Jul 6, 2012 at 12:07 PM, rverghese <riyav(at)hotmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company