flâneur

Optimizing Postgres Text Search with Trigrams - Alex Klibisz

alexklibisz.com · 8,564 words · saved by 1 readers

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. For our purposes, “text search” is defined as follows: Postgres is a ubiquitous relational database, but dedicated search systems like Solr, Elasticsearch, and Opensearch are far better-known for text search. Still, Postgres offers some competent text search functionality, with several benefits over a dedicated search system: Having implemented and operated search functionality on both Postgres and Elasticsearch, my current heuristics for choosing between them are: As a case-study, Gitlab has publicly documented their journey in growing from Postgres tri

Introduction 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. Defining “text search” For our purposes, “text search”

related reading