Better way to generate Postgresql covered index with SQLAlchemy and Alembic

Question

Since Postgresql 11 covered index have been introduced. We create a covered index using INCLUDE keyword like this:

CREATE INDEX index_name ON table_name(indexed_col_name) INCLUDE (covered_col_name); Here the official postgresql doc for more details.

I have done some research on Google but did not find how to implement this feature with SQLAlchemy and include it in the migration file generated by Alembic. Any proposition, idea, doc link will be appreciated.

There generally isn't much point in a covering index. They are useful if you want to declare the index unique on some prefix of the columns, or if you want to cover a column which is of a type which doesn't define ordering operators. Otherwise, just include the extra column in the main body of the index `(indexed_col_name, covered_col_name)` — jjanes, Aug 30 '22 at 18:11
If including the column in the main body is sufficient, then this [answer](https://stackoverflow.com/a/73528028/17676984) should help. — ljmc, Aug 30 '22 at 19:47
I want to use covered index to enable index only scan, covered column does not need to be part of the main index. Am not declaring unique constraints index but index on column used by `where` filter in order to speed up the `select` — Rukamakama, Aug 30 '22 at 22:30

score 2 · Answer 1 · answered Nov 28 '22 at 14:48

2

Index("my_index", table.c.x, postgresql_include=['y'])

It's in the postgres-specific part of the documentation

Better way to generate Postgresql covered index with SQLAlchemy and Alembic

1 Answers1