Re: How to rebuild index efficiently

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: Konireddy Rajashekar <rajkonireddy(at)gmail(dot)com>
Cc: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: How to rebuild index efficiently
Date: 2020-08-03 17:58:13
Message-ID: 5131947A-0470-46C0-93B8-7AE07BBC22FA@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> On Aug 3, 2020, at 10:20, Konireddy Rajashekar <rajkonireddy(at)gmail(dot)com> wrote:
> Could you please suggest any ideal approach to tackle this ?

You can do CREATE INDEX CONCURRENTLY to build a new index with the same definition, and when that is complete, drop the old index. The locking that is required here is modest: CREATE INDEX CONCURRENTLY needs to lock the table briefly at a couple of points in the operation, and dropping the old index requires a brief lock on the table. It is, however, much less overall lock time than REINDEX would be.

--
-- Christophe Pettus
xof(at)thebuild(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2020-08-03 18:04:45 Re: How to rebuild index efficiently
Previous Message Konireddy Rajashekar 2020-08-03 17:20:36 How to rebuild index efficiently