[andatki]

Hey Shayon. IF NOT EXISTS or the Active Record version "if_not_exists: true" can be pretty handy though when there's a valid index in production, to help drive schema definition consistency in all environments (prod, dev, CI, etc.). As you pointed out though, it only makes sense when the indexes being checked are valid.

In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record migration can be sent out using “if_not_exists: true” to make sure it’s applied everywhere.

Your point that it could be misused unintentionally due to not knowing an index is INVALID is a good one, and I feel it should be part of how it works by default in Active Record. Had you considered trying to propose that to rails/rails? I would certainly support that PR (may be able to collaborate) and could add more examples and validation.

[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