Adding an index without a lag (PostgreSQL)

Database Administrators Asked on January 2, 2022


Adding an index to a table with big amount of data causes a lag for both SELECT and INSERT operations on that table, doesn’t it?

While PostgreSQL does not support live (without lags) adding of indexes, how to solve the problem of adding an index to a table without lagging SELECT and INSERT (there is no UPDATEs on that table)?

My idea is to copy the table by small chunks to another table with the same structure, then in a transaction delete the original table and rename the new table to the original table name. How to ensure that the entire data is copied, while INSERT operations to the old table may happen?

One Answer

See Building Indexes Concurrently in PostgreSQL documentation.

CONCURRENTLY option of CREATE INDEX solves the problem in an easy way. Just add it to CREATE INDEX.

Be aware that while CONCURRENTLY option avoids lags, it does slow down building the index and that CREATE INDEX with CONCURRENTLY cannot be used in a transaction.

Answered by porton on January 2, 2022

Add your own answers!

Related Questions

SQL Server :: how to move country code column to another table?

1  Asked on January 9, 2021 by francesco-mantovani


MySQL nested groupby with count other field

0  Asked on January 8, 2021 by jayakusumah



1  Asked on January 3, 2021 by rj_n


Why BCP creates such big files?

1  Asked on January 1, 2021 by racer-sql


Table size and row count

1  Asked on December 31, 2020


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP