PostgreSQL Full-Text Search: Simplify Explained

Explain to 10th: Understanding How PostgreSQL Full-Text Search Work

4 min readNov 4, 2024

Introduction

We will learn about PostgreSQL Full-Text Search with simple use case to making your recipe website searchable.

Mama Saidah ask for my help to building her a recipe-sharing website, my approach to search was embarrassingly simple:

SELECT * FROM recipes 
WHERE name LIKE '%chicken%' OR ingredients LIKE '%chicken%';

It worked… until it didn’t. Her started complaining:

“I searched for ‘pancake’ but it didn’t show ‘fluffy pancakes’” “Why can’t I find ‘spaghetti’ when I type ‘spagetti’?” “The search is so slow!”

These complaints highlight a fundamental truth about search functionality: users don’t think in exact database terms. They make typos, use partial words, and expect intelligent results. The LIKE operator, while simple, falls short in several ways

Performance Issues:

Each search requires a full table scan
Adding indexes doesn’t help with ‘%keyword%’ patterns
Search time grows linearly with data size

Usability Problems:

No handling of plurals (soup vs. soups)
No support for word variations (cook, cooking, cooked)
No relevancy ranking (which recipe is a better match?)
Case sensitivity headaches

Feature Limitations:

Can’t prioritize matches in recipe names over ingredients
No support for partial word matches
No way to handle common cooking terms and synonyms

Think about how users actually search for recipes:

“chicken pasta” (looking for any chicken pasta dish)
“quik dinner” (misspelling ‘quick’)
“gluten-free pancakes” (compound terms)
“grandmas choc chip cookies” (informal terms and abbreviations)

PostgreSQL Full-Text Search: The Building Blocks

PostgreSQL’s full-text search is built on several key concepts:

1. Text Search Types

PostgreSQL introduces two special data types:

tsvector: Represents a document optimized for text search
tsquery: Represents a text search query

2. Text Search Functions

The core functions you’ll use frequently:

to_tsvector(): Converts text to searchable format
to_tsquery(): Converts search string to query format
plainto_tsquery(): Creates query from unformatted text
phraseto_tsquery(): Creates query that matches exact phrase
websearch_to_tsquery(): Handles web-style search syntax

Let’s see these in action with a practical example.

Setting Up Our Recipe Database

CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    ingredients TEXT NOT NULL,
    instructions TEXT NOT NULL,
    cooking_time INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Now, let’s add the search capability. We want matches in recipe names to be more important than matches in ingredients or instructions:

-- Add a column for search
ALTER TABLE recipes ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(ingredients, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(instructions, '')), 'C')
    ) STORED;

-- Add an index for faster searching
CREATE INDEX recipe_search_idx ON recipes USING GIN (search_vector);

Let’s break this down:

tsvector: PostgreSQL's special format for text searching
setweight: Makes some words more important than others
‘A’: Recipe name (most important)
‘B’: Ingredients (medium importance)
‘C’: Instructions (least important)
GIN: The type of index that makes searches fast

Real-World Example

Let’s add some recipes and try searching:

-- Add some recipes
INSERT INTO recipes (name, ingredients, instructions, cooking_time) VALUES
('Classic Tomato Soup', 
 'tomatoes, onion, garlic, vegetable broth, cream', 
 'Sauté onions and garlic. Add tomatoes and broth. Simmer and blend.',
 30),
('Creamy Chicken Pasta',
 'chicken breast, pasta, garlic, cream, parmesan',
 'Cook pasta. Cook chicken. Make sauce. Combine.',
 45),
('Garlic Shrimp Scampi',
 'shrimp, garlic, butter, white wine, pasta',
 'Cook shrimp with garlic. Toss with pasta.',
 20);

Searching For Recipes

Simple Search

Looking for chicken recipes:

SELECT name, cooking_time
FROM recipes
WHERE search_vector @@ to_tsquery('chicken');

Smarter Search

Looking for “tomato soup” where words might not be next to each other:

SELECT name, 
       cooking_time,
       ts_rank(search_vector, query) as relevance
FROM recipes, to_tsquery('tomato & soup') query
WHERE search_vector @@ query
ORDER BY relevance DESC;

Partial Word Search

Looking for recipes with ingredients that start with “garl” (will match “garlic”):

SELECT name
FROM recipes
WHERE search_vector @@ to_tsquery('garl:*');

Complex Search

Finding pasta recipes with garlic but without shrimp:

SELECT name
FROM recipes
WHERE search_vector @@ to_tsquery('pasta & garlic & !shrimp');

Search with relevance data with pasta & garlic

-- Search for pasta dishes with garlic
SELECT 
    name,
    cooking_time,
    ts_rank(search_vector, query) as relevance
FROM recipes,
     to_tsquery('pasta & garlic') query
WHERE search_vector @@ query
ORDER BY relevance DESC;

Making Search Results Better

Want to show why a recipe matched? Use ts_headline , This will highlight where “garlic” appears in the ingredients list.

SELECT 
    name,
    ts_headline('english', ingredients,
        to_tsquery('garlic'),
        'StartSel = <b>, StopSel = </b>'
    ) as matched_ingredients
FROM recipes
WHERE search_vector @@ to_tsquery('garlic');

Quick Tips

Handle Misspellings

CREATE EXTENSION pg_trgm;
CREATE INDEX recipes_name_trgm_idx ON recipes USING GIN (name gin_trgm_ops);

-- Now you can find similar words
SELECT name 
FROM recipes 
WHERE name % 'chiken';  -- Will find "chicken"

Make Common Words Searchable

-- Create your own dictionary without stopwords
CREATE TEXT SEARCH CONFIGURATION recipe_text (COPY = english);
ALTER TEXT SEARCH CONFIGURATION recipe_text 
  DROP MAPPING FOR asciihword, asciiword, word, hword;
ALTER TEXT SEARCH CONFIGURATION recipe_text 
  ADD MAPPING FOR asciihword, asciiword, word, hword 
  WITH simple;

Summary

With just a few lines of SQL, we’ve created a powerful recipe search engine that:

Handles misspellings
Searches across all recipe fields
Ranks results by relevance
Performs well for thousands of recipes

The best part? It’s all built into PostgreSQL — no extra services needed!

If you want to read deep dive how this feature works in PostgreSQL, kindly check my previous article here. I’ve explained some core concept and how to do performance analysis.

https://iniakunhuda.medium.com/postgresql-full-text-search-a-powerful-alternative-to-elasticsearch-for-small-to-medium-d9524e001fe0

If you’re interested in diving deeper into system design and backend development, be sure to follow me for more insights, tips, and practical examples. Together, we can explore the intricacies of creating efficient systems, optimizing database performance, and mastering the tools that drive modern applications. Join me on this journey to enhance your skills and stay updated on the latest trends in the tech world! 🚀

Read the design system in bahasa on iniakunhuda.com