Parallel indexing of db table via alembic script

Question

Is it possible to create parallel indexes for a database table via alembic script?

I am using postgres DB and can create parallel table indexes using the sql command on the postgres command line. (at the same time create an index on ();)

But it was not possible to find a way to create the same using the Db (alembic) script migration. If we create a normal index (not parallel), it will block the database table, so it cannot execute any query in parallel. So I just want to know how to create a parallel index via alembic (DB wrapping) script

Answer 1

I do not use Postgres, and I can not test it, but it should be possible. According to:

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html

Parallel 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)) ... 

Answer 2

While parallel indexes are allowed in Postgresql, Alembic does not support simultaneous operations, only one process should be executed at a time.