如何用SQLAlchemy和Alembic生成PostgreSQL覆盖索引？

PostgreSQL 11及以上版本支持覆盖索引（covered index），可通过INCLUDE关键字创建，示例SQL如下：

CREATE INDEX index_name ON table_name(indexed_col_name) INCLUDE (covered_col_name);

一、SQLAlchemy中创建覆盖索引 #

SQLAlchemy从1.3版本开始支持PostgreSQL的INCLUDE子句，可通过Index类的include参数实现：

1. 模型定义中直接声明
   在模型的__table_args__里定义带覆盖列的索引：

from sqlalchemy import Column, Integer, String, Index
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class YourTable(Base):
    __tablename__ = 'your_table'
    id = Column(Integer, primary_key=True)
    indexed_col = Column(String)
    covered_col = Column(String)
    
    __table_args__ = (
        Index('idx_your_table_indexed_col', 'indexed_col', include=['covered_col']),
    )

2. 运行时动态创建
   如果需要在代码中动态生成索引，可使用如下方式：

from sqlalchemy import create_engine
from sqlalchemy.schema import CreateIndex

engine = create_engine('postgresql://user:password@host/db')
index = Index('idx_your_table_indexed_col', YourTable.indexed_col, include=[YourTable.covered_col])
with engine.connect() as conn:
    conn.execute(CreateIndex(index))
    conn.commit()

二、集成到Alembic迁移文件 #

方式1：自动生成迁移（推荐）

确保Alembic版本≥1.4、SQLAlchemy版本≥1.3，执行alembic revision --autogenerate命令时，工具会自动识别模型中带include参数的索引，生成对应的迁移代码：

生成的迁移代码示例：

from alembic import op
import sqlalchemy as sa

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index('idx_your_table_indexed_col', 'your_table', ['indexed_col'], unique=False, include=['covered_col'])
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('idx_your_table_indexed_col', table_name='your_table')
    # ### end Alembic commands ###

方式2：手动编写迁移脚本

若自动生成未生效（如版本过低），可直接在迁移文件中编写原生SQL：

from alembic import op

def upgrade():
    op.execute("""
        CREATE INDEX idx_your_table_indexed_col ON your_table(indexed_col) INCLUDE (covered_col);
    """)

def downgrade():
    op.execute("DROP INDEX idx_your_table_indexed_col;")

注意事项 #

• 覆盖索引的INCLUDE列仅用于支持索引仅扫描（index-only scan），不会参与索引查找逻辑，适合查询需返回该列但无需用其过滤的场景。
• 必须保证依赖库版本达标，否则include参数无法被识别。

内容的提问来源于stack exchange，提问作者Rukamakama