[shayonj]

Hey Andrew! It's great to see you here. I agree with everything you said, 100%. I'm honestly torn because I was the first one to recommend `if_not_exists` to engineers, but now I'm reconsidering because this can lead to very strange issues in production, especially for ActiveRecord applications. Maintaining an "invalid index reaper job" doesn't sound like a good long-term solution.

I've suggested some proposals to Rails here and would love to propose a PR based on the solution that makes the most sense, too.

https://github.com/rails/rails/issues/52583

[andatki]

Do you do any alerting for INVALID indexes? For example, by default PgHero will display them prominently and I believe PgAnalyze does as well. My thought is to put the energy into making INVALID indexes highly visible. Perhaps combined with a process step. 1. Any CREATE INDEX CONCURRENTLY migrations go out in their own deployment. 2. Any queries that depends on that index being present means a PR has a process step asking the author to verify that the index exists and is valid. That way you wouldn't have to lose if_not_exists.

That all said, still would be cool to make this the default in Active Record. Nice idea!

[shayonj]

Yeah, def, good call.

I have a job that runs nightly or weekly and performs a concurrent reindex on tables, which nicely covers cases like this.

Given that lock timeouts are very much "rescuable," I'm also thinking about addressing the problem right at the start (when adding migrations) by implementing retries. This approach at least would eliminate the need for any follow-up steps by developers and reduce additional cognitive load.

The ideal goal being "The system just works"™ :D

[andatki]

Makes sense. Check out the implementation of automatic lock timeout retries in Strong Migrations.

Lock Timeout Retries [experimental] https://github.com/ankane/strong_migrations