You don't need Elasticsearch! Fuzzy Search with PostgreSQL and Spring Data
summary
Demonstrates that many fuzzy search needs can stay inside PostgreSQL using the Postgres trigram extension and its similarity operators (returns 0–1), integrated with Spring Data JPA via a custom Hibernate function; built a demo API, populated data, ran the three access patterns (JPQL, native SQL, Specification) and all tests are green, with a recommendation to prefer GIN index is ~3x faster (at cost of 2–3x larger index) and to avoid Specifications because they break prepared-statement planning.
AI-written summary · about
chapters (9)
- 0:00Introduction
- 10:04Fuzzy search requirements
- 16:29LIKE operator limits
- 22:12Trigram similarity in PostgreSQL
- 29:09Trigram indexes setup
- 33:40Spring Data JPA fuzzy search
- 37:13Security and performance
- 40:08Alternatives to trigram
- 41:50Summary
related talks
28mElasticsearch-Quality Full-Text Search in Postgres via Tantivy
47mAdvanced (Elastic) Search for Your Legacy Application
41mPostgreSQL is (probably) all you need
33mFuzzy Search Algorithms How and When to Use Them
50mAdvanced Search for Your Legacy Application
26mOn The Look-Out For Your Data
34mFull-Text Search in PostgreSQL
43mFull-Text-Search Explained from A to Z with French Food