Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to rebuild index efficiently

How to rebuild index efficiently

From
Konireddy Rajashekar
Date:
Hi Team,

i have a table of size 2.6TB which is very prone to updates and inserts so we have tuned autovacuum to run on it very aggressively , so the table level bloat is fine . 
Now we are facing issue with indexes on this table. the total size  of all indexes  on this table is around 2.4TB.

There is an unique index of 1.2TB size out of which 850 GB is bloat , creating another index concurrently is taking lot of time and we cannot offer down time to reindex this index. 

Could you please suggest any ideal approach to tackle this ?

Postgres version:9.6.15

postgres=> \dt+ cust_table
                         List of relations
 Schema |      Name       | Type  | Owner  |  Size   | Description
--------+-----------------+-------+--------+---------+-------------
 public | cust_table | table | raj | 2685 GB |
(1 row)


Index bloat:

database_name | schema_name |          table_name           |           index_name            | bloat_pct | bloat_mb |  index_mb   |  table_mb   | index_scans
---------------+-------------+-------------------------------+---------------------------------+-----------+----------+-------------+-------------+-------------
 postgres         | public      | cust_table              | unique_event_type_action_id     |        69 |   884477 | 1285743.648 | 2749094.070 |   342466359




postgres=> \di+ unique_event_type_action_id
                                        List of relations
 Schema |            Name             | Type  | Owner  |      Table      |  Size   | Description
--------+-----------------------------+-------+--------+-----------------+---------+-------------
 public | unique_event_type_action_id | index | raj |cust_table | 1256 GB |


Regards,
Raj

Re: How to rebuild index efficiently

From
Christophe Pettus
Date:

> On Aug 3, 2020, at 10:20, Konireddy Rajashekar <rajkonireddy@gmail.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
oldindex.  The locking that is required here is modest: CREATE INDEX CONCURRENTLY needs to lock the table briefly at a
coupleof points in the operation, and dropping the old index requires a brief lock on the table.  It is, however, much
lessoverall lock time than REINDEX would be. 

--
-- Christophe Pettus
   xof@thebuild.com

Re: How to rebuild index efficiently

From
Ron
Date:
On 8/3/20 12:58 PM, Christophe Pettus wrote
>> On Aug 3, 2020, at 10:20, Konireddy Rajashekar <rajkonireddy@gmail.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
theold index.  The locking that is required here is modest: CREATE INDEX CONCURRENTLY needs to lock the table briefly
ata couple of points in the operation, and dropping the old index requires a brief lock on the table.  It is, however,
muchless overall lock time than REINDEX would be.
 

Of course, you need enough disk space... :)

-- 
Angular momentum makes the world go 'round.

Re: How to rebuild index efficiently

From
Michael Lewis
Date:
creating another index concurrently is taking lot of time 

Could you increase maintenance_work_mem significantly or is that already quite high?

Re: How to rebuild index efficiently

From
Michael Paquier
Date:
On Mon, Aug 03, 2020 at 01:04:45PM -0500, Ron wrote:
> 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.
>
> Of course, you need enough disk space... :)

A SHARE UPDATE EXCLUSIVE lock is taken during a CIC, meaning that
writes and reads are allowed on the parent table while the operation
works, but no DDLs are allowed (roughly).  The operation takes a
couple of transactions to complete, and  there are two wait points
after building and validating the new index to make sure that there
are no transactions remaining around that may cause visiblity issues
once the new index is ready to use and becomes valid.  So the
operation is longer, takes more resources, but it has the advantage to
be non-disruptive.
--
Michael
Attachment