How do I create indexes concurrently with flyway in postgres?

This was implemented in Flyway 4.1, see ticket 851 and Non-Transactional PostgreSQL Support in Flyway (link broken).

This functionality works by detecting that a statement should be executed without a transaction. As far as I know, this is currently only supported for PostgreSQL.

The first hurdle is detecting when a migration needs to be run outside of a transaction. Two paths forward are evident. The first path, [..], is to create a filename annotation. The second path is to add a statement parser to Flyway that detects non-transactional commands and runs them in the appropriate isolation level. Despite the extra work it would require on their part, the Boxfuse team was steadfast on following the second path. It provides the better user experience for Flyway users in the long term.

By default a migration that is executed this way, should only contain statements that need to be executed without a transaction. Mixing it with transactional statements is not allowed by default, and needs to be enabled with a property.

Creating a condition that migrations must not mix transactional with non-transactional statements creates a restriction that may introduce an unseen problem for a user in the future. In case this unknown situation requiring DDL that normally would be run inside of a transaction needs to be handled, a property called flyway.allowMixedMigrations is added to the configuration. By default it is false. I strongly suggest to never turn it on.

The statements that are handled non-transactionally are determined by code in PostgreSQLParser:

@Override
protected Boolean detectCanExecuteInTransaction(String simplifiedStatement, List<Token> keywords) {
    if (CREATE_DATABASE_TABLESPACE_SUBSCRIPTION_REGEX.matcher(simplifiedStatement).matches()
            || ALTER_SYSTEM_REGEX.matcher(simplifiedStatement).matches()
            || CREATE_INDEX_CONCURRENTLY_REGEX.matcher(simplifiedStatement).matches()
            || REINDEX_REGEX.matcher(simplifiedStatement).matches()
            || VACUUM_REGEX.matcher(simplifiedStatement).matches()
            || DISCARD_ALL_REGEX.matcher(simplifiedStatement).matches()) {
        return false;
    }

    boolean isDBVerUnder12 = true;
    try {
        isDBVerUnder12 = !parsingContext.getDatabase().getVersion().isAtLeast("12");
    } catch (Exception e) {
        LOG.debug("Unable to determine database version: " + e.getMessage());
    }
    
    if (isDBVerUnder12 && ALTER_TYPE_ADD_VALUE_REGEX.matcher(simplifiedStatement).matches()) {
        return false;
    }

    return null;
}

For flyway >= 9.1.2, it tries to be compatible with pgbouncer by default, and creating concurrent index will hang the process.

To fix it we need to set postgresqlTransactionalLock - https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock

Example for gradle

flyway {
    pluginConfiguration = [
        postgresqlTransactionalLock: "false"
    ]
    //...
}

Example for custom runner

FluentConfiguration flywayConfiguration = Flyway.configure()
        .baselineOnMigrate(true)
        .dataSource(url, username, password) // FROM CONFIG
        .outOfOrder(true)
        .table("flyway_schema_history");
flywayConfiguration.getPluginRegister()
        .getPlugin(PostgreSQLConfigurationExtension.class)
        .setTransactionalLock(false);
Flyway flyway = flywayConfiguration.load();
flyway.migrate();

Taken from github issue: https://github.com/flyway/flyway/issues/3508

According to this post:

Flyway migration hangs for postgres CREATE INDEX CONCURRENTLY

You can just add NT as a prefix to your migration so

V201609121806__create_index_for_table.sql

becomes

NTV201609121806__create_index_for_table.sql