Database Administrators Asked on January 2, 2022
Adding an index to a table with big amount of data causes a lag for both
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
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?
See Building Indexes Concurrently in PostgreSQL documentation.
CONCURRENTLY option of
CREATE INDEX solves the problem in an easy way. Just add it to
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
0 Asked on January 10, 2021
1 Asked on January 10, 2021 by hadrian-blackwater
1 Asked on January 9, 2021 by francesco-mantovani
2 Asked on January 9, 2021 by mark-mcwiggins
0 Asked on January 8, 2021 by jayakusumah
1 Asked on January 8, 2021
1 Asked on January 8, 2021 by pg2286
1 Asked on January 7, 2021 by conanthegerbil
0 Asked on January 6, 2021 by vrace
1 Asked on January 5, 2021 by kulstad
2 Asked on January 4, 2021 by stuart-j-cuthbertson
1 Asked on January 4, 2021 by jonggu
1 Asked on January 4, 2021 by spedo-de-la-rossa
1 Asked on January 3, 2021 by slim
1 Asked on January 1, 2021 by user2578332
1 Asked on January 1, 2021 by manvith
1 Asked on January 1, 2021 by racer-sql
1 Asked on December 31, 2020 by programmer
Get help from others!