--- title: Synonyms description: Expand search terms with synonym rules you keep in a table, with nothing to reindex when they change canonical: https://www.paradedb.com/docs/reference/tokenizers/synonyms --- Say your catalog calls them couches, but shoppers type `sofa`. With synonyms, a search for `sofa` also finds the rows that say `couch`. You keep the rules in a regular table with an index on it, and a query reads that table when it wants the rules. The next query uses whatever you changed. The index you search stays as it is, so there is nothing to rebuild. ## Creating a Synonym Index Put one rule per row in a column of type `pdb.synonym_rule`, then create a ParadeDB index on that one column. The column type is what makes it a synonym index. ```sql CREATE TABLE shop_synonyms (rule pdb.synonym_rule NOT NULL); INSERT INTO shop_synonyms VALUES ('couch, sofa, divan'), ('sneakers => running shoes'); CREATE INDEX shop_synonyms_idx ON shop_synonyms USING paradedb (rule); ``` Rules use the Solr syntax, which is also what Elasticsearch and OpenSearch synonym files use, so you can bring your file along. There are two kinds of rules: - `couch, sofa, divan` is an equivalence rule. A search for any of them finds all of them. - `sneakers => running shoes` is a directed rule. A search for `sneakers` looks for `running shoes` instead, and a search for `running shoes` stays as it is. A word can be in more than one rule, and a search for it then uses the alternatives of all of them. Rules don't chain, so an alternative is searched as it is, even when another rule lists it. With `sneakers, shoes, trainers` and `shoes, kicks`, a search for `shoes` also finds `sneakers`, `trainers` and `kicks`, but a search for `kicks` finds only `kicks` and `shoes`. A directed rule keeps the word you typed when another rule lists that word. A term can be more than one word, on either side. A multi-word term is matched as a phrase, so the field you search has to keep positions. Every tokenizer keeps them except [ngrams](/reference/tokenizers/available-tokenizers/ngrams), which needs `positions=true`. [Edge ngrams](/reference/tokenizers/available-tokenizers/edge-ngrams) give the grams of one word the same position, so search such a field with a [search tokenizer](/reference/tokenizers/search-tokenizer) that keeps words whole. If a term needs a comma, escape it with a backslash. Comments and blank lines are fine, so a synonym file can go in line by line. A rule that does not parse is rejected at `INSERT`, and the error tells you which rule and why. If you have a synonym file, load it with a delimiter and a quote character that never appear in the file, so each line becomes one rule, backslashes included: ```sql \copy shop_synonyms FROM 'synonyms.txt' WITH (FORMAT csv, DELIMITER E'\x01', QUOTE E'\x02') ``` ### Tokenizers The synonym index runs each rule through its tokenizer and keeps the result, so a rule only matches a query that the same tokenizer produced. Set the tokenizer with a cast, the same way you set it on a text field. With a stemmer, `couches` and `couch` are the same rule: ```sql CREATE INDEX shop_synonyms_en_idx ON shop_synonyms USING paradedb ((rule::pdb.simple('stemmer=english'))); ``` A query uses a synonym index only when it has the tokenizer of the field you search. An option at its default value is the same as no option, so `pdb.simple` and `pdb.simple('lowercase=true')` are one tokenizer. If you search fields with different tokenizers, create one synonym index per tokenizer on the same table, and each query uses the one that fits its field. ### Several Sets in One Table A partial index builds from the rows its `WHERE` clause selects, so one table can hold several sets: ```sql CREATE TABLE synonyms (set_name text NOT NULL, rule pdb.synonym_rule NOT NULL); CREATE INDEX shop_idx ON synonyms USING paradedb (rule) WHERE set_name = 'shop'; CREATE INDEX colors_idx ON synonyms USING paradedb (rule) WHERE set_name = 'colors'; ``` A query picks a set with the same `WHERE`, as shown below. A synonym index does not count toward the one ParadeDB index a table can have, and a query on its table never uses it. ## Searching with Synonyms The index you search needs no setting for synonyms: ```sql CREATE INDEX search_idx ON mock_items USING paradedb (id, description, category); ``` Give the rules to a [match](/reference/full-text/match) query with a subquery on the rules table. `pdb.expand` takes the text you search for and the rule column: ```sql SQL SELECT description FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM shop_synonyms); ``` ```ts Drizzle import { sql } from "drizzle-orm"; import { search } from "@paradedb/drizzle-paradedb"; await db .select({ description: mockItems.description }) .from(mockItems) .where( search.matchAny( mockItems.description, db .select({ query: sql`pdb.expand(${"sofa"}, ${shopSynonyms.rule})` }) .from(shopSynonyms), ), ); ``` ```python Django from django.db.models import F, Func, Subquery, TextField, Value from paradedb import MatchAny, ParadeDB rules = Subquery( ShopSynonym.objects.values( query=Func( Value('sofa'), F('rule'), function='pdb.expand', output_field=TextField(), ) ) ) MockItem.objects.filter( description=ParadeDB(MatchAny(rules)) ).values('description') ``` ```python SQLAlchemy from sqlalchemy import func, select from sqlalchemy.orm import Session from paradedb.sqlalchemy import search rules = select(func.pdb.expand("sofa", ShopSynonym.rule)).scalar_subquery() stmt = ( select(MockItem.description) .where(search.match_any(MockItem.description, rules)) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails rules = ShopSynonym.select( Arel::Nodes::NamedFunction.new( "pdb.expand", [Arel::Nodes.build_quoted("sofa"), ShopSynonym.arel_table[:rule]] ) ) MockItem.search(:description) .match_any(Arel::Nodes::Grouping.new(rules.arel.ast)) .select(:description) ``` ```cs EF Core await dbContext .MockItems.FromSql( $"SELECT * FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM shop_synonyms)" ) .Select(item => new { item.Description }) .ToListAsync(); ``` Any query can read any rules table, and a query without the subquery searches only the words you typed. A subquery that returns no rule does the same. Each run of words in your query that matches a rule becomes the group of its alternatives, and a row matches the group if it contains any of them. With `&&&`, every group has to match. When rules overlap, the longest match wins, so `running shoes` matches a rule on `running shoes` before a rule on `running`. The words of such a run are then searched as a phrase, so a rule on `running shoes` stops `|||` from finding a row that has only `shoes`. A few things stay exact: - A [fuzzy](/reference/full-text/fuzzy) query keeps the words you typed fuzzy, and a prefix query keeps them a prefix, but both match the alternatives exactly. A directed rule drops the words you typed, with or without fuzzy, and a group with a fuzzy word scores with the best of its alternatives. - `pdb.expand` takes text. A text array, such as `ARRAY['sofa']`, has no tokenizer to match the rules with. - The match operators, `|||` and `&&&`, the [phrase](#phrases) operator, `###`, and the [query parser](#query-parser) operator, `@@@`, expand. With [term](/reference/full-text/term), `pdb.expand` is an error. The fuzzy and boost casts go on the subquery: ```sql SQL SELECT description FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM shop_synonyms)::pdb.fuzzy(1); ``` ```ts Drizzle import { sql } from "drizzle-orm"; import { search } from "@paradedb/drizzle-paradedb"; await db .select({ description: mockItems.description }) .from(mockItems) .where( search.matchAny( mockItems.description, search.fuzzy( db .select({ query: sql`pdb.expand(${"sofa"}, ${shopSynonyms.rule})` }) .from(shopSynonyms), 1, ), ), ); ``` ```python Django from django.db.models import F, Func, Subquery, TextField, Value from paradedb import Fuzzy, MatchAny, ParadeDB rules = Subquery( ShopSynonym.objects.values( query=Func( Value('sofa'), F('rule'), function='pdb.expand', output_field=TextField(), ) ) ) MockItem.objects.filter( description=ParadeDB(MatchAny(Fuzzy(rules, 1))) ).values('description') ``` ```python SQLAlchemy from sqlalchemy import func, select from sqlalchemy.orm import Session from paradedb.sqlalchemy import search rules = select(func.pdb.expand("sofa", ShopSynonym.rule)).scalar_subquery() stmt = ( select(MockItem.description) .where(search.match_any(MockItem.description, search.fuzzy(rules, 1))) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails rules = ShopSynonym.select( Arel::Nodes::NamedFunction.new( "pdb.expand", [Arel::Nodes.build_quoted("sofa"), ShopSynonym.arel_table[:rule]] ) ) MockItem.search(:description) .match_any(ParadeDB.fuzzy(Arel::Nodes::Grouping.new(rules.arel.ast), 1)) .select(:description) ``` ```cs EF Core await dbContext .MockItems.FromSql( $"SELECT * FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM shop_synonyms)::pdb.fuzzy(1)" ) .Select(item => new { item.Description }) .ToListAsync(); ``` A change to the rules is transactional. Your own transaction sees it right away, and every other session sees it once it commits. With several sets in one table, the subquery picks a set with the `WHERE` of its partial index: ```sql SQL SELECT description FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM synonyms WHERE set_name = 'shop'); ``` ```ts Drizzle import { eq, sql } from "drizzle-orm"; import { search } from "@paradedb/drizzle-paradedb"; await db .select({ description: mockItems.description }) .from(mockItems) .where( search.matchAny( mockItems.description, db .select({ query: sql`pdb.expand(${"sofa"}, ${synonyms.rule})` }) .from(synonyms) .where(eq(synonyms.setName, "shop")), ), ); ``` ```python Django from django.db.models import F, Func, Subquery, TextField, Value from paradedb import MatchAny, ParadeDB rules = Subquery( Synonym.objects.filter(set_name='shop').values( query=Func( Value('sofa'), F('rule'), function='pdb.expand', output_field=TextField(), ) ) ) MockItem.objects.filter( description=ParadeDB(MatchAny(rules)) ).values('description') ``` ```python SQLAlchemy from sqlalchemy import func, literal, select from sqlalchemy.orm import Session from paradedb.sqlalchemy import search rules = ( select(func.pdb.expand("sofa", Synonym.rule)) .where(Synonym.set_name == literal("shop", literal_execute=True)) .scalar_subquery() ) stmt = ( select(MockItem.description) .where(search.match_any(MockItem.description, rules)) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails synonyms = Synonym.arel_table rules = Synonym.where(synonyms[:set_name].eq("shop")).select( Arel::Nodes::NamedFunction.new( "pdb.expand", [Arel::Nodes.build_quoted("sofa"), synonyms[:rule]] ) ) MockItem.search(:description) .match_any(Arel::Nodes::Grouping.new(rules.arel.ast)) .select(:description) ``` ```cs EF Core await dbContext .MockItems.FromSql( $"SELECT * FROM mock_items WHERE description ||| (SELECT pdb.expand('sofa', rule) FROM synonyms WHERE set_name = 'shop')" ) .Select(item => new { item.Description }) .ToListAsync(); ``` ### Phrases A [phrase](/reference/full-text/phrase) query takes the same subquery. The words of a phrase have to be next to each other, so the query searches one phrase for each combination of alternatives. With the rule `sneakers => running shoes`, a search for `sleek sneakers` looks for the phrase `sleek running shoes`: ```sql SQL SELECT description FROM mock_items WHERE description ### (SELECT pdb.expand('sleek sneakers', rule) FROM shop_synonyms); ``` ```ts Drizzle import { sql } from "drizzle-orm"; import { search } from "@paradedb/drizzle-paradedb"; await db .select({ description: mockItems.description }) .from(mockItems) .where( search.phrase( mockItems.description, db .select({ query: sql`pdb.expand(${"sleek sneakers"}, ${shopSynonyms.rule})`, }) .from(shopSynonyms), ), ); ``` ```python Django from django.db.models import F, Func, Subquery, TextField, Value from paradedb import ParadeDB, Phrase rules = Subquery( ShopSynonym.objects.values( query=Func( Value('sleek sneakers'), F('rule'), function='pdb.expand', output_field=TextField(), ) ) ) MockItem.objects.filter( description=ParadeDB(Phrase(rules)) ).values('description') ``` ```python SQLAlchemy from sqlalchemy import func, select from sqlalchemy.orm import Session from paradedb.sqlalchemy import search rules = select(func.pdb.expand("sleek sneakers", ShopSynonym.rule)).scalar_subquery() stmt = ( select(MockItem.description) .where(search.phrase(MockItem.description, rules)) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails rules = ShopSynonym.select( Arel::Nodes::NamedFunction.new( "pdb.expand", [Arel::Nodes.build_quoted("sleek sneakers"), ShopSynonym.arel_table[:rule]] ) ) MockItem.search(:description) .phrase(Arel::Nodes::Grouping.new(rules.arel.ast)) .select(:description) ``` ```cs EF Core await dbContext .MockItems.FromSql( $"SELECT * FROM mock_items WHERE description ### (SELECT pdb.expand('sleek sneakers', rule) FROM shop_synonyms)" ) .Select(item => new { item.Description }) .ToListAsync(); ``` The [slop](/reference/full-text/phrase#adding-slop) cast goes on the subquery, and it applies to every phrase. Each word with a rule multiplies the phrases. The query searches at most `paradedb.synonyms_max_alternatives` of them. The phrase you typed stays when the rules list its words. ### Query Parser The [query parser](/reference/full-text/query-parser) takes the same subquery when a text or JSON field is on its left side. It expands each word of the query string on its own, and each quoted phrase as a [phrase](#phrases). An operator of the query string, such as `AND` or `-`, applies to a word together with its alternatives: ```sql SQL SELECT description FROM mock_items WHERE description @@@ (SELECT pdb.expand('sleek AND sneakers', rule) FROM shop_synonyms); ``` ```ts Drizzle import { sql } from "drizzle-orm"; const rules = db .select({ query: sql`pdb.expand(${"sleek AND sneakers"}, ${shopSynonyms.rule})`, }) .from(shopSynonyms); await db .select({ description: mockItems.description }) .from(mockItems) .where(sql`${mockItems.description} @@@ ${rules}`); ``` ```python Django MockItem.objects.extra( where=[ "description @@@ (SELECT pdb.expand('sleek AND sneakers', rule) FROM shop_synonyms)" ] ).values('description') ``` ```python SQLAlchemy from sqlalchemy import func, select from sqlalchemy.orm import Session rules = select( func.pdb.expand("sleek AND sneakers", ShopSynonym.rule) ).scalar_subquery() stmt = ( select(MockItem.description) .where(MockItem.description.op("@@@")(rules)) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails MockItem.where( "description @@@ (SELECT pdb.expand('sleek AND sneakers', rule) FROM shop_synonyms)" ).select(:description) ``` ```cs EF Core await dbContext .MockItems.FromSql( $"SELECT * FROM mock_items WHERE description @@@ (SELECT pdb.expand('sleek AND sneakers', rule) FROM shop_synonyms)" ) .Select(item => new { item.Description }) .ToListAsync(); ``` The parser makes one query for each word, so a term of several words expands only in a quoted phrase. With a rule `trail shoes, hikers`, the query string `"trail shoes"` also finds `hikers`, and `trail shoes` does not. A few things differ from the match and phrase operators: - Only the field on the left side expands, and a JSON field expands at each of its paths. A word for another field, such as `category:sofa`, stays as typed, and so does a range, a set or a prefix phrase. - The slop of a phrase goes in the query string, as in `"red sofa"~2`, and not on the subquery. The `lenient` and `conjunction_mode` options of `pdb.parse` are not available. - The parser does not use a [search tokenizer](/reference/tokenizers/search-tokenizer), so the query uses the synonym index that has the tokenizer of the field. ### Which Index a Query Uses The subquery names the table and not an index. The query uses the synonym index on the rule column that holds the rows the subquery reads. That is an index on the whole table for a subquery with no `WHERE`, or a partial index for a subquery with the same `WHERE`. The index also has to use the tokenizer of the field you search. `EXPLAIN` shows the index on a `Synonyms` line. If no synonym index fits, the query still expands. It reads the rules that the subquery returns on every query, and matches them with the tokenizer of the field you search. That is fine for a small set, and a warning tells you why no index is used. Set `paradedb.planner_warnings` to `error` to catch it in your tests, or to `off` to hide it. The same happens when row security on the rules table applies to you. In a prepared statement, the text to expand can be a parameter. Write the `WHERE` of a partial index with its values in the query, and not as parameters. After a few runs, Postgres can plan a prepared statement once for every value, and that plan can't tell which partial index holds the rules. Postgres checks your rights on the rules table, as for any query. Row security applies too, so each role expands with the rules it can see. ## Scoring A group of single words scores as one term. A row that has both `sofa` and `couch` scores a little above a row with one of them, and a rare synonym doesn't score above a common one. A group with a multi-word alternative scores with the best of its alternatives. A directed rule replaces the words you typed, so `sneakers` scores as the phrase `running shoes`. A phrase query scores a row with the best of the phrases that it finds there. A phrase of one word scores as a match on that word. To see which words of a group a row holds, [explain its score](/reference/full-text/score#score-explanation). ## Limits Each alternative is one more term the query has to look up, so a long rule costs more. - `paradedb.synonyms_max_alternatives` (default `32`) caps how many alternatives one run of words expands to, in sorted order. For a [phrase](#phrases) query, it also caps how many phrases the query searches. The words you typed stay in both cases when a rule lists them. The [query parser](#query-parser) caps a word as a match, and a quoted phrase as a phrase query. - `paradedb.synonyms_max_rules_per_key` (default `1024`) caps how many rules one word reads in each segment of the synonym index. A word in more rules reads only the first ones. Without a synonym index, the cap counts the rules that the subquery returns. ## Checking an Expansion `EXPLAIN` shows the query as you wrote it, and a `Synonyms` line names the synonym index. `pdb.synonym_expansion` shows what the synonym index turns the text into, with the synonym index's own tokenizer: ```sql SELECT * FROM pdb.synonym_expansion('shop_synonyms_idx', 'red sofa'); ``` ```csv position | token | alternatives ----------+-------+-------------------- 0 | red | {red} 1 | sofa | {couch,divan,sofa} (2 rows) ```