Add or Retire an Index Without Taking the Table Down
A plain CREATE INDEX locks the table against writes for as long as the build takes, which on a large production table means an outage. CREATE INDEX CONCURRENTLY builds it in two passes without blocking writes, at the cost of being slower and of leaving an invalid index behind if it fails. For the other direction, MySQL's invisible indexes (and marking an index unused in PostgreSQL before dropping it) let you test life without an index before you commit to dropping it.
Questions this Concept answers
- Why are MySQL's invisible indexes useful before dropping an index?
Cleaning Up INVALID Indexes After a Failed Concurrent Build
PostgreSQL can build an index while the table keeps taking writes, using CREATE INDEX CONCURRENTLY. To do that it splits the work across several transactions instead of one, so if the build is interr…