Optimizing Postgres full text search in Django

Postgres provides great search capability out of the box. For the majority of Django apps there is no need to run and maintain an ElasticSearch cluster unless you need the advanced features ElasticSearch offers. Django integrates nicely with the Postgres search through the built in Postgres module.

For small data sets the default configuration performs well, however when your data grows in size the default search configuration becomes painfully slow and we need to enable certain optimizations to keep our queries fast.

This page will walk you through setting up Django and Postgres, indexing sample data and performing and optimizing full text search.

The examples go through a Django + Postgres setup, however the advice is generally applicable to any programming language or framework as long as it uses Postgres.

If you're already a Django veteran you can skip the first steps and jump right into the optimization.

Project setup
Creating models and indexing sample data
Optimizing search
Specialized search column and gin indexes
Postgres triggers
Measuring the performance improvement
Drawbacks
Conclusion

Project setup

Create the directories and setup the Django project.

mkdir django_postgres
cd django_postgres
python -m venv venv
source venv/bin/activate
pip install django
django-admin startproject full_text_search
cd full_text_search
./manage.py startapp web

Now we'll need to install 3 dependencies:

psycopg2: the Postgres client library for Python
wikipedia: a client library to retrieve Wikipedia articles
django-extensions: to simplify debugging of SQL queries

pip install psycopg2 wikipedia django-extensions

We also need to run Postgres locally. I'll use a dockerized version of Postgres here since it's easier to set up, but feel free to install a Postgres binary if you'd like.

Open full_text_search/docker-compose.yml

---
version: '2.4'
services:
  postgres:
    image: postgres:11-alpine
    ports:
      - '5432:5432'
    environment:
      # Set the Postgres environment variables for bootstrapping the default
      # database and user.
      POSTGRES_DB: "my_db"
      POSTGRES_USER: "me"
      POSTGRES_PASSWORD: "password"

The project structure should now look like the output below. We'll ignore the venv directory as that is packed with files and irrelevant for now.

$ tree -I venv
.
└── full_text_search
    ├── docker-compose.yml
    ├── full_text_search
    │   ├── __init__.py
    │   ├── __pycache__
    │   │   ├── __init__.cpython-37.pyc
    │   │   └── settings.cpython-37.pyc
    │   ├── settings.py
    │   ├── urls.py
    │   └── wsgi.py
    ├── manage.py
    └── web
        ├── admin.py
        ├── apps.py
        ├── __init__.py
        ├── migrations
        │   └── __init__.py
        ├── models.py
        ├── tests.py
        └── views.py

5 directories, 15 files

We will modify the default database settings to use Postgres instead of SQLite. In settings.py change the DATABASES attribute:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "my_db",
        "USER": "me",
        "PASSWORD": "password",
        "HOST": "localhost",
        "PORT": "5432",
        "OPTIONS": {"connect_timeout": 2},
    }
}

We will also modify our INSTALLED_APPS to include a few apps:

django.contrib.postgres the Postgres module for Django which is required for full text search
django_extensions to print SQL logs when executing queries in Python
our web app

Open full_text_search/settings.py and modify:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Added apps below
    'django.contrib.postgres',
    'django_extensions',
    'web',
]

Start Postgres and Django.

cd full_text_search
docker-compose up -d
./manage.py runserver

If we open our browser and enter http://localhost:8000 we should see a successful installation.

Creating models and indexing sample data

Suppose we have a model which represents a Wikipedia page. For simplicity we'll use two fields: title and content.

Open full_text_search/web/models.py

from django.db import models

class Page(models.Model):
    title = models.CharField(max_length=100, unique=True)
    content = models.TextField()

Now run the migrations to create the model.

cd full_text_search
./manage.py makemigrations && ./manage.py migrate
No changes detected
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  # truncated the other output for brievity

We'll use a script to index random wikipedia articles and save the contents to Postgres.

Edit web/index_wikipedia.py

import logging
import wikipedia
from .models import Page

logger = logging.getLogger("django")

def index_wikipedia(num_pages):
    for _ in range(0, num_pages):
        p = wikipedia.random()
        try:
            wiki_page = wikipedia.page(p)
            Page.objects.update_or_create(title=wiki_page.title, defaults={
                "content": wiki_page.content
            })
        except Exception:
            logger.exception("Failed to index %s", p)

Now let's run our script to index Wikipedia. There will be errors when running the script, but don't worry about those as long as we manage to store a few hundred articles. The script will take a while to run so grab a cup of coffee and return in a few minutes.

./manage.py shell_plus

>>> from web.index_wikipedia import index_wikipedia
>>> index_wikipedia(200)

#
## A bunch of errors will be show here, ignore them.
#

>>> Page.objects.count()
183

Optimizing the search

Now suppose we want to allow users to perform a full text search on the content. We'll interactively query our dataset to test the full text search. Open a Django shell session:

$ ./manage.py shell_plus --print-sql

>>> Page.objects.filter(content__search='football')
SELECT t.oid,
       typarray
  FROM pg_type t
  JOIN pg_namespace ns
    ON typnamespace = ns.oid
 WHERE typname = 'hstore'

Execution time: 0.001440s [Database: default]

SELECT typarray
  FROM pg_type
 WHERE typname = 'citext'

Execution time: 0.000260s [Database: default]

SELECT "web_page"."id",
       "web_page"."title",
       "web_page"."content"
  FROM "web_page"
 WHERE to_tsvector(COALESCE("web_page"."content", '')) @@ (plainto_tsquery('football')) = true
 LIMIT 21

Execution time: 0.222619s [Database: default]

<QuerySet [<Page: Page object (2)>, <Page: Page object (7)>...]>

Django performs two preparatory queries and finally executes our search query. Looking at the last query we can at first glance see that the execution was ~315ms for the query execution and serialization alone. That's it far too slow when we want to keep our page load speeds in the double digits in milliseconds.

Let's take a closer look at why this query is performing so slowly. Open a second terminal where we'll use the excellent Postgres query analyzer. Copy the query from above and run EXPLAIN ANALYZE:

$ ./manage.py dbshell
psql (10.8 (Ubuntu 10.8-0ubuntu0.18.10.1), server 11.2)

Type "help" for help.

my_db=# explain analyze SELECT "web_page"."id",
my_db-#        "web_page"."title",
my_db-#        "web_page"."content"
my_db-#   FROM "web_page"
my_db-#  WHERE to_tsvector(COALESCE("web_page"."content", '')) @@ (plainto_tsquery('football')) = true
my_db-#  LIMIT 21
my_db-# ;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..106.71 rows=1 width=643) (actual time=5.001..220.212 rows=18 loops=1)
   ->  Seq Scan on web_page  (cost=0.00..106.71 rows=1 width=643) (actual time=4.999..220.206 rows=18 loops=1)
         Filter: (to_tsvector(COALESCE(content, ''::text)) @@ plainto_tsquery('football'::text))
         Rows Removed by Filter: 165
 Planning Time: 3.336 ms
 Execution Time: 220.292 ms
(6 rows)

We can see that although the planning time is quite fast (~3ms) the execution time is very slow at ~220ms.

->  Seq Scan on web_page  (cost=0.00..106.71 rows=1 width=643) (actual time=4.999..220.206 rows=18 loops=1)

We can note that the query performs a sequential scan over the entire table in order to find matching records. We can likely optimize the query by using an index.

Filter: (to_tsvector(COALESCE(content, ''::text)) @@ plainto_tsquery('football'::text))

Additionally the query normalizes the content column from text to a tsvector using to_tsvector in order to perform the full text search.

WHERE to_tsvector(COALESCE("web_page"."content", '')) @@ (plainto_tsquery('football')) = true

The tsvector type is a tokenized version of our text which normalizes the search column (more on tokenization here). Postgres needs to perform this normalization for every row and each row contains an entire Wikipedia page. This is both a CPU intensive and slow operation.

Specialized search column and gin indexes

In order avoid on-the-fly casting of text to tsvectors we'll create a specialized column which is used only for search. The column should be populated on inserts or updates. When querying is performed we'll avoid the performance penalty of casting types.

Since we can now have a tsvector type we are also able to add a gin index to speed up the query. The gin index ensures that the search will be performed with a indexed scan instead of a sequential scan over all records.

Open our web/models.py file and make modifications to the Page model.

from django.db import models
from django.contrib.postgres.search import SearchVectorField
from django.contrib.postgres.indexes import GinIndex

class Page(models.Model):
    title = models.CharField(max_length=100, unique=True)
    content = models.TextField()

    # New modifications. A field and an index
    content_search = SearchVectorField(null=True)

    class Meta:
        indexes = [GinIndex(fields=["content_search"])]

Run the migrations

Migrations for 'web':
  web/migrations/0002_auto_20190525_0647.py
    - Add field content_search to page
    - Create index web_page_content_505071_gin on field(s) content_search of model page
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, web
Running migrations:
  Applying web.0002_auto_20190525_0647... OK

Postgres triggers

Theoretically our problem is now solved. We have a Gin indexed column which should perform well when we search on it, but by doing so we have introduced another problem: the optimized content_search column needs to be kept manually in sync and updated whenever the content column updates.

Luckily for us Postgres provides an additional feature which solves this problem, namely triggers. Triggers are Postgres functions that fire when a specific action is performed on a row. We will create a trigger that populates content_search whenever a content row is created or updated. That way Postgres will keep the two columns in sync without us having to write any Python code.

In order to add a trigger we need to craft a manual Django migration. This will add the trigger function and update all of our Pages rows to ensure the trigger is fired and content_search is populated at migration time for our existing records. If you have an extremely large data set you might not want to do this in production.

Add a new migration in web/migrations/0003_create_text_search_trigger.py. Make sure to modify the previous migration in dependencies because the previously autogenerated migration is likely different for you.

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        # NOTE: The previous migration probably looks different for you, so
        # modify this.
        ('web', '0002_auto_20190524_0957'),
    ]

    migration = '''
        CREATE TRIGGER content_search_update BEFORE INSERT OR UPDATE
        ON web_page FOR EACH ROW EXECUTE FUNCTION
        tsvector_update_trigger(content_search, 'pg_catalog.english', content);

        -- Force triggers to run and populate the text_search column.
        UPDATE web_page set ID = ID;
    '''

    reverse_migration = '''
        DROP TRIGGER content_search_update ON web_page;
    '''

    operations = [
        migrations.RunSQL(migration, reverse_migration)
    ]

Run the migrations

$ ./manage.py makemigrations && ./manage.py migrate
No changes detected
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, web
Running migrations:
  Applying web.0003_create_text_search_trigger... OK

Measuring the performance improvement

Finally on to the fun part, let's verify that are query performs faster than before. Open a Django shell again, but when filtering the rows use the indexed content_search column rather than the normal content column.

./manage.py shell_plus --print-sql

>>> from django.contrib.postgres.search import SearchQuery
>>> Page.objects.filter(content_search=SearchQuery('football', config='english'))
SELECT t.oid,
       typarray
  FROM pg_type t
  JOIN pg_namespace ns
    ON typnamespace = ns.oid
 WHERE typname = 'hstore'

Execution time: 0.000829s [Database: default]

SELECT typarray
  FROM pg_type
 WHERE typname = 'citext'

Execution time: 0.000310s [Database: default]

SELECT "web_page"."id",
       "web_page"."title",
       "web_page"."content",
       "web_page"."content_search"
  FROM "web_page"
 WHERE "web_page"."content_search" @@ (plainto_tsquery('english'::regconfig, 'football')) = true
 LIMIT 21

Execution time: 0.001359s [Database: default]

<QuerySet []>

Query execution time down from 0.220s to 0.001s!

Let's analyze the query again to see how Postgres executes it. Copy the query from above and run it through EXPLAIN ANALYZE.

./manage.py dbshell

my_db=# explain analyze SELECT "web_page"."id",
my_db-#        "web_page"."title",
my_db-#        "web_page"."content",
my_db-#        "web_page"."content_search"
my_db-#   FROM "web_page"
my_db-#  WHERE "web_page"."content_search" @@ (plainto_tsquery('english'::regconfig, 'football')) = true
my_db-#  LIMIT 21
my_db-# ;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8.01..12.02 rows=1 width=675) (actual time=0.022..0.022 rows=0 loops=1)
   ->  Bitmap Heap Scan on web_page  (cost=8.01..12.02 rows=1 width=675) (actual time=0.020..0.020 rows=0 loops=1)
         Recheck Cond: (content_search @@ '''football'''::tsquery)
         ->  Bitmap Index Scan on web_page_content_505071_gin  (cost=0.00..8.01 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)
               Index Cond: (content_search @@ '''football'''::tsquery)
 Planning Time: 3.061 ms
 Execution Time: 0.165 ms
(7 rows)

Here are the interesting bits:

->  Bitmap Index Scan on web_page_content_505071_gin  (cost=0.00..8.01 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)

Instead of a sequential scan Postgres uses an index on the content_search column.

Index Cond: (content_search @@ '''football'''::tsquery)

We also no longer perform an expensive to_tsquery operation for each row and instead use the content_search column as is.

Drawbacks

Unfortunately there are tradeoffs when using this optimization technique.

Because we're maintaining another column of our text for the sole purpose of search speed our table size takes up significantly more space. Additionally the gin index on the content_search column takes up space on it's own.
Since the search column is updated on every UPDATE or INSERT it also slows down writes to the database.

If you're constrained by memory and disk or need quick writes this technique may not be suitable for your use case. However I suspect that the majority of CRUD apps out there are OK with sacrificing disk and write speed for lightning fast search.

Conclusion

Postgres offers excellent full text search capability, but it's a little slow out of the box. In order to speed up text searches we add a secondary column of type tsvector which is a search-optimized version of our text.

We add a Gin index on the search column to ensure Postgres performs an index scan rather than a sequential scan. This reduces the query execution time by an order of magnitude.

In order to keep the text column and the search column in sync we use a Postgres trigger which populates the search column on any modifications to our text column.

The full code example can be found at Github.

Table of Contents