Django database model creation and indexing for Postgres

Question

I'm trying to create new table and index the columns in Postgres through Django model. The table creation uses another table's setup and the indexing uses pg_trgm

I can do it directly on Postgres by

CREATE TABLE my_table (like my_other_table);

and Insert data like

INSERT INTO my_table SELECT * FROM MY_OTHER_TABLE TABLESAMPLE BERNOULLI (5);

This will take 5% of the data from my_other_table into my_table

Then I index the column by

CREATE INDEX mycolumn_trgm_idx ON my_table USING GIN (my_column gin_trgm_ops);

There's no issue when I perform these actions on postgres directly, but I don't know how (or if it's possible) to do it through django migration

dghy dghy · Accepted Answer · 2018-10-03T18:03:29

I think something like this should be possible. First create your models.py Classes so they represent tables that you want to take the data from (there is also possibility of auto discovery of existing database - details here). For indexing you can set index_db=True in the required model Class fields. When the model is set up create migrations by ./manage.py makemigrations and ./manage.py migrate. Django should create/update fields in the DB it's connected to. In the end you would need to create your own migration and take required data from one table (Model) and put it in the other table (Model). Here's how to write custom migrations. Don't forget to write necessary functions inside migrations file as Model methods won't work during migration.

Django database model creation and indexing for Postgres

1 Answers