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 outsideof a transaction. Two paths forward are evident. The first path, [..],is to create a filename annotation. The second path is to add astatement parser to Flyway that detects non-transactional commands andruns them in the appropriate isolation level. Despite the extra workit would require on their part, the Boxfuse team was steadfast onfollowing the second path. It provides the better user experience forFlyway 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 withnon-transactional statements creates a restriction that may introducean unseen problem for a user in the future. In case this unknownsituation requiring DDL that normally would be run inside of atransaction needs to be handled, a property calledflyway.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:

@Overrideprotected 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;}

Not at this point. All migrations currently run within a transaction. Feel free to file an enhancement request in the issue tracker.

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