Database Administrators Asked on January 2, 2022
PostgreSQL:
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 UPDATE
s 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?
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP