4 ms·
Can't you just create second index online and remove first when second index is built?
by vbezhenar 7y ago
Can't you just create second index online and remove first when second index is built?
- anarazel 7y agoYou can. But sometimes you can't drop indexes without further consequences. It might be e.g. a primary key, or a unique index used to enforce uniqueness on the referred-to side of a foreign key. You can work around those, but that requires holding an exclusive lock for at least a short amount of time (problematic in itself, but also can increase deadlock risk unless you're really careful).
- masklinn 7y agoYes, though it can be less than easy for constraint indexes or FK indexes. REINDEX CONCURRENTLY is basically a shortcut for hand-rolling that reliably with less exclusive locking: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5dc92b844e680c54a7ecd68de0ba53c949c3d605 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...