Re: Reindexing primary keys without locking

On Mon, Jun 13, 2011 at 6:47 AM, Stuart Bishop <stuart(at)stuartbishop(dot)net> wrote:
> On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>> Hi Guys,
>> Does anyone know of any tricks to replace primary key indexes without using
>> reindex? Or any other method that will not lock the table for an extended
>> amount of time on 8.4?
>> I've got a bloated primary key index on a table and I'd like to try and
>> clean it up without downtime.
>
> If you are brave, http://pqxx.org/development/libpqxx/wiki/PrimaryKeyTransplant
>
> If you go this route, ensure you have a test environment and ensure
> you can dump and restore your db schema after applying the patch, and
> that the diff of the before and after schemas checks out. If you screw
> up, you can end up with crazy things like multiple indexes with the
> same name and other stuff making your database unrecoverable.

Stuart,

That looks interesting. There's a similar process in the PostgreSQL
Admin Cookbook, though our work was independent.

I guess that's because in 9.0 and below it really is the only way to
make it work correctly.

Will look for your work in future, thanks.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services