Invalid But Still Costly

The migration runner wraps every file in BEGIN ... COMMIT. An engineer adds:

CREATE UNIQUE INDEX CONCURRENTLY orders_email_uniq ON orders (lower(email));

First attempt: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. She removes the wrapping transaction and reruns. This time it runs for twenty minutes, then fails on a uniqueness violation. \d orders now shows orders_email_uniq marked INVALID, and write latency on orders has gone up. What is true here?

The runner should keep its transaction and add SET LOCAL lock_timeout instead, since the underlying problem is that the build waited on a long-running transaction.

The invalid index is ignored for querying but still updated on every write, so it costs overhead and returns nothing. Drop it, fix the duplicate addresses, rebuild.

An invalid index is inert until a REINDEX finishes, so it costs nothing and can be left in place until someone has time.

CONCURRENTLY cannot build expression indexes, which is the real cause of both failures; the index must be on email directly.