Full-Text Search and Tokenization

In this lesson, you will learn how to customize PostgreSQL’s search engine by configuring dictionaries and parsers to handle natural language queries effectively.

The Pipeline of Full-Text Search

When you perform a full-text search, PostgreSQL doesn’t just look for exact string matches. Instead, it processes text through a pipeline: Parsing (breaking text into tokens), Normalization (converting tokens into lexemes), and Ranking (determining relevance).

If you want to search a document containing "running" and match it against the query "run," you rely on the dictionary. A dictionary defines the rules for how tokens are transformed. The standard english_stem dictionary, for example, uses the Snowball algorithm to strip suffixes, ensuring "running," "runs," and "run" all reduce to the base lexeme run.

Configuring a Custom Dictionary

You can define your own dictionary to ignore specific stop words (like "the" or "is") or to define synonyms. Here is how you can create a custom dictionary based on the existing ispell template:

CREATE TEXT SEARCH DICTIONARY public.english_ispell (
    TEMPLATE = ispell,
    DictFile = english,
    AffFile = english,
    StopWords = english
);

Once defined, you update your Text Search Configuration—the map that tells PostgreSQL which dictionary to use for each token type:

ALTER TEXT SEARCH CONFIGURATION english
ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;

Why This Matters

The parser breaks your document into pieces based on its type (e.g., tags, URLs, or words). By customizing the dictionary associated with those tokens, you control how the search index interprets the data. Without this, your search results might be "noisy," returning irrelevant documents simply because they contain common stop words, or failing to match variations of the same root word. By fine-tuning these components, you transform raw, messy text into a searchable, linguistic knowledge base.

Key Takeaway: Full-text search effectiveness relies on the configuration pipeline; by mapping specific token types to tailored dictionaries, you ensure that natural language queries map accurately to the underlying indexed lexemes.