Optimizing Postgres full text search in Django

Dani Hodovic

June 12, 2019

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.

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-alpineports:-'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.

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.pyshell_plus>>>fromweb.index_wikipediaimportindex_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:

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:

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.

fromdjango.dbimportmodelsfromdjango.contrib.postgres.searchimportSearchVectorFieldfromdjango.contrib.postgres.indexesimportGinIndexclassPage(models.Model):title=models.CharField(max_length=100,unique=True)content=models.TextField()# New modifications. A field and an indexcontent_search=SearchVectorField(null=True)classMeta:indexes=[GinIndex(fields=["content_search"])]

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.

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.

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

IndexCond:(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.