Support for exclusive connection while executing migration

Context

GitLab's container registry uses sql-migrate to manage database migrations. Our database usage has grown to a point where we need to run post-deployment migrations such as creating indexes on partitioned tables. This requires the use of CONCURRENTLY in Postgres.

We started by creating indexes on just two partitions which worked just fine, with a total runtime of ~30s. We now wanted to execute the post-deployment migrations that will create the remaining partition indexes but it failed due to exceeding the statement timeout limit of 15s that we configure for our Postgres instance.

Problem

We initially thought that we could simply turn off the statement timeout off before executing the statement. However, this won't work for production because with the connection pool on the registry side and PgBouncer's pooling after that, index creation statements and the preceding SET statement_timeout TO 0 would most likely be executed in different sessions, so the latter wouldn't have an effect on the former.

We cannot rely on transactions for these migrations either (as a way to ensure that all statements within would be executed in the same session) because creating an index CONCURRENTLY cannot be done within transactions.

Solution

The ideal solution in our case, is to simply use an exclusive connection for certain statements rather than relying on the pool abstraction.

At the API level, this could be implemented by adding a new e.g. ExclusiveConn parameter to the Migration struct, similar to DisableTransaction*, which we're already using. If this was set to true on the migration's definition, the lib would grab and use an exclusive connection to execute all its statements.

We are happy to make the contribution ourselves, but would like to get a maintainer's opinion on the solution to see if it's something that would be accepted into the project.

Related to https://gitlab.com/gitlab-org/container-registry/-/issues/889.

Start in migrate.go around the Migration struct and DisableTransaction fields referenced by the issue, then trace how migration statements obtain and use database connections. The work is done when a migration can request an exclusive connection for all its statements, including statements that cannot run inside a transaction.