What is the difference between postgresql rebuild index and recreate index, which one is better?
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.