Creating a Django migration for a GiST / GIN index with a special index operator.

In order to add a GiST index on a Postgres database that could be used to accelerate trigram matches using the pg_trgm module and the special gist_trgm_ops operator, I had to code up a special Django Index

Django will hopefully soon support custom index operators, but if you need the functionality right now, this example will do the trick.

The special GiST index class looks like this:

from django.contrib.postgres.indexes import GistIndex

class GistIndexTrgrmOps(GistIndex):
    def create_sql(self, model, schema_editor):
        # - this Statement is instantiated by the _create_index_sql()
        #   method of django.db.backends.base.schema.BaseDatabaseSchemaEditor.
        #   using sql_create_index template from
        #   django.db.backends.postgresql.schema.DatabaseSchemaEditor
        # - the template has original value:
        #   "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s)%(extra)s"
        statement = super().create_sql(model, schema_editor)
        # - however, we want to use a GIST index to accelerate trigram
        #   matching, so we want to add the gist_trgm_ops index operator
        #   class
        # - so we replace the template with:
        #   "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s gist_trgrm_ops)%(extra)s"
        statement.template =\
            "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s gist_trgm_ops)%(extra)s"

        return statement

Which you can then use in your model class like this:

class YourModel(models.Model):
    some_field = models.TextField(...)

    class Meta:
        indexes = [

The migration will then generate SQL (use sqlmigrate to inspect the SQL) which looks like this:

CREATE INDEX "app_somefield_139a28_gist" ON "app_yourmodel" USING gist ("some_field" gist_trgrm_ops);

You can easily modify this pattern for other (Postgres) indices and their operators.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.