CODEWITHSUNDEEP
postgresqlpostgresql-9.3
joker_zhu
joker_zhu

Reputation: 133

What is the difference between postgresql rebuild index and recreate index, which one is better?

I have a table which index size is too big (about 2G). When I restore the database to a VM, the size is only 200M so I need to rebuild/recreate the index and I will probably do this online.

What is the difference between re-building (reindex) and re-creating the index, and which one is better when I do it online? Particularly, which option allows querying the DB during the operation?

Upvotes: 13

Views: 26908

Answers (2)

lurf jurv
lurf jurv

Reputation: 277

Postgres 12 has added a REINDEX INDEX CONCURRENTLY command, which does what you want here. https://paquier.xyz/postgresql-2/postgres-12-reindex-concurrently/ https://www.depesz.com/2019/03/29/waiting-for-postgresql-12-reindex-concurrently/

Upvotes: 3

user554538
user554538

Reputation:

The REINDEX command requires an exclusive table lock, which means that it'll stall any accesses to the table until the command has completed. If you can afford that kind of maintenance window it's perfectly fine.

The alternative for online rebuilding is to create a new index using CREATE INDEX CONCURRENTLY, then drop the old one. This will take longer to complete, but allows access to the table while rebuilding the index.

Upvotes: 15

Related Questions