hydra db migration: CREATE INDEX CONCURRENTLY

[harvhan]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe your problem

During one of our deployments, we noticed that the hydra db migration 'CREATE INDEX' step took too long while holding a write lock to the database. Since this is a database serving live traffic, we cannot afford to have a long down time.

Describe your ideal solution

Can we consider adding CONCURRENTLY to the CREATE INDEX sql statements, so that the db can still function while creating the indexes?

Workarounds or alternatives

We considered running hydra janitor commands regularly, but it didn't help much and we also really want to have near zero down time.

Version

master

Additional Context

No response

[aeneasr]

We can consider that in the future, but online migrations are generally not available when self-hosting open source and only available when using a build from our Ory Enterprise License. This is because releases in OSS bundle many changes, and releases with OEL happening step with releases to Ory Network which are generally online.

While adding concurrent index creation will solve write locks, it may cause other issues with index hints afaik.