PostgreSQL Indexing: GIN, Django, and Trigram Similarity

PostgreSQL supports a special type of index called GIN – Generalized Inverted Index. This type of index is especially helpful for improving the performance of queries against composite fields, particularly for applications such as full-text search or any relatively static data.

In Django, there is support for GIN and related technology for querying against these indexes called Trigram Similarity. To enable support for this feature, one must use the extension TrigramExtension. In addition, if one is using model fields that are not natively supported by GIN such as character fields – anything that is not jsonb, array, and tsvector – the BtreeGinExtension must also be enabled [2].

These extensions can be placed in a Django migration to enable database support. Once in place, subsequent migration operations can add GIN indexes to fields using the GinIndex module. For example, if one were to index a name field - which should be relatively static in the database – with GIN, it may look like the following (assuming a Django model enclosing the Meta class):

class Meta:
    indexes = [
        GinIndex(fields=['name']),
    ]

Note that GIN does not support NULL values, so the column must be null=False in the model definition.

Once in place, one can then efficiently query the data using a trigram similarity annotation:

results = MyModel.objects.annotate(
    similarity=TrigramSimilarity('name', ‘Joe’
).filter(similarity__gt=0.3).order_by('-similarity')