Optimizing Postgres Text Search with Trigrams
In this post, we’ll implement and optimize a text search system based on Postgres Trigrams.
We’ll start with some fundamental concepts, then define a test environment based on a dataset of 8.9 million Amazon reviews, then cover three possible optimizations.
Our search will start very slow, about 360 seconds. With some thoughtful optimization we’ll end up at just over 100 milliseconds – a ~3600x speedup! These optimizations won’t apply perfectly to every text search use-case, but they should at the very least spark some ideas.
Source: Optimizing Postgres Text Search with Trigrams, an article by Alex Klibisz.