Is it possible to create concurrent indexes for DB table through alembic script?
I'm using postgres DB, and able to create concurrent table indexes through sql command on postgres prompt.(create index concurrently on ();)
But couldn't find way to create same through Db migration(alembic) script. If we create normal index(not concurrent) , it'll lock DB table so can't perform any query in parallel. So just want to know how to create concurrent index through alembic(DB migration) script
Alembic supports PostgreSQL concurrently indexes creation
def upgrade():
op.execute('COMMIT')
op.create_index('ix_1', 't1', ['col1'], postgresql_concurrently=True)
I'm not using Postgres and I am not able to test it, but it should be possible. According to:
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html
Concurrent indexes are allowed in the Postgres dialect from version 0.9.9. However, a migration script like this should work with older versions (direct SQL creation):
from alembic import op, context
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import text
# ---------- COMMONS
# Base objects for SQL operations are:
# - use op = INSERT, UPDATE, DELETE
# - use connection = SELECT (and also INSERT, UPDATE, DELETE but this object has lot of logics)
metadata = MetaData()
connection = context.get_bind()
tbl = Table('test', metadata, Column('data', Integer), Column("unique_key", String))
# If you want to define a index on the current loaded schema:
# idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True)
def upgrade():
...
queryc = \
"""
CREATE INDEX CONCURRENTLY test_idx1 ON test (data, unique_key);
"""
# it should be possible to create an index here (direct SQL):
connection.execute(text(queryc))
...
Whereas concurrent indexes are allowed in Postgresql, Alembic does not support concurrent operations, only one process should be running at a time.
