A Gentle Introduction to PostgreSQL Full Text Search

The slides for a talk presented by Remi Mercier, at ParisRB Meetup in Paris, on June 2025.

The talk gives an introduction to PostgreSQL full text search:
- what are tokens and lexemes?
- how does PostgreSQL matches user queries with its own dictionaries?
- how to integrate full text search in your Rails app?
- and much more.

An English (and written) version of this talk exist here: https://remimercier.com/postgresql-full-text-search-for-beginners/

Transcript

1. PostgreSQL Full Text Search Rémi Mercier ➜ remimercier.com a gentle introduction to
2. 1. How do we search without full text? 2. How do we search with full text? 3. Turning our data into lexemes (lexe-what?). 4. Fifty shades of parsing queries (well, more like four). 5. How to add full text search to your Rails app? Rémi Mercier ➜ remimercier.com What we’ll talk about:
3. Software developer, writer and former stained glass maker (yup). Now an independent software engineer. Need help with you project? Let’s talk! I write about building software at remimercier.com. You can also f ind me on ruby.social/@remi. Rémi Mercier ➜ remimercier.com Who am I? 👋 I’m Rémi Mercier.
4. Rémi Mercier ➜ remimercier.com How to do search without full text search?
10. Rémi Mercier ➜ remimercier.com How to do search with full text search?
12. Rémi Mercier ➜ remimercier.com How does full text search work?
13. Rémi Mercier ➜ remimercier.com 1. Turning documents to tokens full text search work? How does
15. Rémi Mercier ➜ remimercier.com 2. Matching tokens with dictionaries full text search work? How does
18. Rémi Mercier ➜ remimercier.com 3. Turning tokens into lexemes full text search work? How does
22. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries full text search work? How does
23. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries full text search work? How does to_tsquery() PRO: granular control over the query CON: needs preliminary normalisations
27. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries full text search work? How does plainto_tsquery() PRO: delegates normalisation to PostgreSQL CON: no logical operators beyond ‘&’
30. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries full text search work? How does phraseto_tsquery() PRO: handle meaningful group of words CON: no logical operators
32. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries full text search work? How does websearch_to_tsquery() PRO: natural language and some logical operators CON: ¯\_(π)_/¯
34. Rémi Mercier ➜ remimercier.com Ranking results Frequency or density?
35. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries Ranking results Frequency or density? ts_rank() ➜ Ranks vectors based on the frequency of their matching lexemes.
37. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries Ranking results Frequency or density? ts_rank_cd() ➜ Computes the cover density ranking for the given document vector and query. ➜ Adds a normalization option to adjust density based on the length of the document.
39. Rémi Mercier ➜ remimercier.com 4. Fifty shades of parsing queries Ranking results Frequency or density? setweight() ➜ Adds weighting options for each column.
41. Rémi Mercier ➜ remimercier.com full text search to your Rails app? How to add
43. Thank you! Rémi Mercier ➜ remimercier.com Questions about this presentation? In need of a product engineer for your project? Let’s talk!