pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReindexing primary keys without locking
4+ messages / 4 participants
[nested] [flat]
* Reindexing primary keys without locking
@ 2011-06-01 12:38 Glyn Astill <glynastill@yahoo.co.uk>
2011-06-01 13:52 ` Re: Reindexing primary keys without locking Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-13 05:47 ` Re: Reindexing primary keys without locking Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 2 replies; 4+ messages in thread
From: Glyn Astill @ 2011-06-01 12:38 UTC (permalink / raw)
To: pgsql-admin
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.
A quick search turned up the following thread that mentioned a possible patch, but I couldn't find much else.
http://archives.postgresql.org/message-id/AANLkTinoaVd9RGgOyyudbLWFQFvJKWGdiwyKxOcxtfxV@mail.gmail.c...
Thanks
Glyn
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reindexing primary keys without locking
2011-06-01 12:38 Reindexing primary keys without locking Glyn Astill <glynastill@yahoo.co.uk>
@ 2011-06-01 13:52 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2011-06-01 13:52 UTC (permalink / raw)
To: pgsql-admin; Glyn Astill <glynastill@yahoo.co.uk>
Glyn Astill <glynastill@yahoo.co.uk> wrote:
> 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.
You could create a unique index concurrently on the same columns.
Perhaps that would help with performance problems from the bloat
until you had a window to drop the redundant index and reindex the
table?
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reindexing primary keys without locking
2011-06-01 12:38 Reindexing primary keys without locking Glyn Astill <glynastill@yahoo.co.uk>
@ 2011-06-13 05:47 ` Stuart Bishop <stuart@stuartbishop.net>
2011-06-13 07:54 ` Re: Reindexing primary keys without locking Simon Riggs <simon@2ndQuadrant.com>
1 sibling, 1 reply; 4+ messages in thread
From: Stuart Bishop @ 2011-06-13 05:47 UTC (permalink / raw)
To: Glyn Astill <glynastill@yahoo.co.uk>; +Cc: pgsql-admin
On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill <glynastill@yahoo.co.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 Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reindexing primary keys without locking
2011-06-01 12:38 Reindexing primary keys without locking Glyn Astill <glynastill@yahoo.co.uk>
2011-06-13 05:47 ` Re: Reindexing primary keys without locking Stuart Bishop <stuart@stuartbishop.net>
@ 2011-06-13 07:54 ` Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2011-06-13 07:54 UTC (permalink / raw)
To: Stuart Bishop <stuart@stuartbishop.net>; +Cc: Glyn Astill <glynastill@yahoo.co.uk>; pgsql-admin
On Mon, Jun 13, 2011 at 6:47 AM, Stuart Bishop <stuart@stuartbishop.net> wrote:
> On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill <glynastill@yahoo.co.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-06-13 07:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-01 12:38 Reindexing primary keys without locking Glyn Astill <glynastill@yahoo.co.uk>
2011-06-01 13:52 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-13 05:47 ` Stuart Bishop <stuart@stuartbishop.net>
2011-06-13 07:54 ` Simon Riggs <simon@2ndQuadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox