Search that finds typos, synonyms and exact terms: one Postgres table, one Spring Boot app

Search Stevensen and you get Robert Louis Stevenson. Search casement and you get six books, not one of which contains the word. Same table. Same endpoint. No search engine.

Here's that second query, live against the demo:

{
  "content": [
    {
      "title": "The Picture of Dorian Gray",
      "headline": "<mark>window</mark>, producing a kind of momentary Japanese effect, and making him think of those pallid",
      "score": 0.06079271
    },
    {
      "title": "Lady Windermere's Fan",
      "headline": "<mark>window</mark> doing fancy work, and making ugly things for the poor, which I think so useful",
      "score": 0.06079271
    }
  ],
  "page": { "totalElements": 6 }
}

Six books come back. The highlighted word is window, which is not what I typed, and a SELECT count(*) FROM book WHERE excerpt ILIKE '%casement%' over the whole corpus returns 0.

Those <mark> tags are ts_headline, and it does more work here than anything else on the page. Hand it the same tsquery you matched with and it returns an excerpt with the matching lexemes wrapped in whatever you set as StartSel and StopSel. I ask for <mark>, so the UI gets semantic HTML rather than a class name I'd have to style. It's also the reason a synonym match is believable instead of magic: you can see that the word it found is window.

Postgres does all three kinds of search on its own: exact terms through a stored tsvector that Spring Boot never has to maintain, typos through pg_trgm trigrams, and the vocabulary gap through a native synonym dictionary. You can reach every one of them from the JPA Criteria API, compose them as Spring Data Specification chains, and fuse all three into a single ranked list with Reciprocal Rank Fusion. There's exactly one place where the Criteria API runs out and you drop to native SQL.

None of this is new, and that's the point. Full-text search has been in core Postgres since 8.3 (opens in a new tab) and pg_trgm is older still. I built this on PostgreSQL 18 and Spring Boot 4.1 because that's what I run today, not because either version is what makes it work.

One table, three ways in

I wanted a demo where nothing is hidden, run the same way as the Spring Boot and OAuth2 series: one command, one public repo, no hand-waving. So: one book table, 77 public-domain classics pulled from Project Gutenberg, author names that invite typos, and real work text in the excerpts, not blurbs. Three retrievers read that one table.

Each retriever is a different answer to "what counts as a match":

• A tsvector is a document after processing, stored as a sorted list of normalized word roots called lexemes. to_tsvector('english', 'The cats were running') gives 'cat':2 'run':4.
• A tsquery is a search condition over those lexemes. The @@ operator asks whether a tsvector satisfies a tsquery, and that is the entire matching model.
• A trigram is a three-character run of a string. pg_trgm compares the sets of trigrams in two strings and returns a similarity between 0 and 1, which is how it reaches a misspelling.
• A synonym dictionary maps one lexeme to another while a document is being parsed, so a query word can match a document that never contains it.

Everything in this half of the post is plain SQL. Every result block below comes from support/demo.sql (opens in a new tab), which mise run sql runs against the seeded database with psql -e so each statement is echoed above its own output. The schema statements come from the Liquibase changelog. No application is involved in any of it.

The three retrievers catch different things, and the differences are not subtle:

How does Postgres full-text search rank results?

Postgres full-text search from Spring Boot starts with one stored tsvector. Four steps get you a ranked result set, and none of them needs application code:

• Store the vector in a generated column, so Postgres recomputes it only when the row changes.
• Index that column with GIN, which is the lookup @@ performs.
• Weight the fields with setweight, labeling title A and excerpt B.
• Order by ts_rank_cd.

A title match then outranks an excerpt match on its own, because the ranking functions weight those setweight labels differently by default.

ALTER TABLE book
    ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(excerpt, '')), 'B')
        ) STORED;

CREATE INDEX idx_book_search_vector ON book USING GIN (search_vector);

That STORED on line 5 is not decoration. PostgreSQL 18 changed the default for generated columns to VIRTUAL, and the release notes say so plainly: "Allow generated columns to be virtual, and make them the default [...] The write behavior can still be specified via the STORED option."¹ A virtual column is computed on read, and a column computed on read cannot be indexed. Leave the keyword off and the CREATE INDEX in the next changeset fails:

ERROR: indexes on virtual generated columns are not supported

So on Postgres 18 the missing keyword breaks your index, not your column, which is a confusing way to find out. It's the one PG 18 change this repo actually reproduces, in a test that creates a virtual column and watches the index fail.

One asymmetry to know before it puzzles you: ts_headline only ever scans the excerpt, so a title-only match like time finding Hard Times comes back with no <mark> at all. HeadlineIT scopes its marking assertion to rows whose excerpt contains the word, with a comment naming the case as a real gap.

Parsing what a person actually types

Four functions turn text into a tsquery. Three of them survive raw input, but only one keeps the quoted phrases and exclusions a search box gets:

websearch_to_tsquery accepts the syntax people already type into search engines, and it never throws on malformed input:

SELECT websearch_to_tsquery('english', 'whale "white sea" -captain');
-- 'whale' & 'white' <-> 'sea' & !'captain'

Quoted phrases become the followed-by operator, a leading - becomes negation, and a stray bracket becomes nothing at all. That's the constructor you can wire to a UI and stop worrying about.

ts_rank and ts_rank_cd disagree, so you have to choose

ts_rank weights by term frequency. ts_rank_cd computes cover density, which rewards documents where the query terms sit close together. On a real corpus they genuinely reorder the same match set. Here is the query old time, scored both ways, showing the top five of the 11 matching books:

 id |                     title                      | ts_rank | ts_rank_cd
----+------------------------------------------------+---------+------------
 34 | The Adventures of Tom Sawyer, Complete         | 0.63776 |    0.29379
 35 | A Connecticut Yankee in King Arthur's Court    | 0.30459 |    0.05714
 20 | Bartleby, the Scrivener: A Story of Wall-Street | 0.00863 |    0.02105
 21 | Billy Budd : $b and other prose pieces         | 0.00117 |    0.01600
  3 | Romeo and Juliet                               | 0.00080 |    0.02808

Order by frequency and positions three to five are 20, 21, 3 (the $b in the fourth title is a MARC subfield marker riding along in Gutenberg's own metadata: real corpus, real mess, left in). Order by cover density and those three become 3, 20, 21. Romeo and Juliet mentions the terms rarely but close together, so proximity promotes it two places, past both Melville entries. Neither ordering is correct in the abstract. Both numbers are only meaningful relative to other rows for the same query, which is worth remembering before anyone asks you what a good score is.

I use ts_rank_cd for the lexical retriever and ts_rank for the synonym one, and a test asserts the two disagree on this corpus. Same habit as the virtual threads post: if a claim can be measured, measure it, because the corpus that makes two ranking functions agree would quietly turn that choice into decoration.

How do you make Postgres full-text search tolerate typos?

You reach outside full-text search for this one, to pg_trgm, because the stemmer structurally cannot help you. Stevensen and Stevenson stem to different lexemes, so @@ will never connect them no matter which configuration you use. Trigram similarity ignores language entirely and scores that pair at 0.2692, comparing three-character runs instead of word roots. Match with the % operator, which a gin_trgm_ops index can answer, and rank with similarity(), which it cannot.

Watch the stemmer fail and the trigrams succeed, on the same word, in the same session:

SELECT b.title FROM book b
 WHERE b.search_vector @@ websearch_to_tsquery('english', 'Stevensen');

 title
-------
(0 rows)

SET pg_trgm.similarity_threshold = 0.2;
SET

SELECT b.title, round(similarity(a.name, 'Stevensen')::numeric, 4) AS sim
  FROM book b JOIN author a ON a.id = b.author_id
 WHERE a.name % 'Stevensen'
 ORDER BY sim DESC, b.id;

                    title                    |  sim
---------------------------------------------+--------
 Treasure Island                             | 0.2692
 The strange case of Dr. Jekyll and Mr. Hyde | 0.2692
 Kidnapped                                   | 0.2692
(3 rows)

That SET in the middle is doing real work. % compares against pg_trgm.similarity_threshold, which defaults to 0.3, and similarity('Robert Louis Stevenson', 'Stevensen') is 0.2692. Leave the default in place and all three rows vanish. Spelled correctly, the same pair scores 0.4348.

The predicate and the ranking also use different tools on purpose. % is the match, similarity() is the score, and they are not interchangeable: a gin_trgm_ops index can answer % and cannot answer similarity() >= 0.2. I set the threshold to 0.2, and where I set it turns out to matter later.

The operator class you pick decides what else the index can do. gin_trgm_ops answers % and LIKE. gist_trgm_ops answers those too and adds something GIN cannot do at all: ORDER BY name <-> :q, a nearest-neighbor scan that walks the index in closest-first order. If you ever want "did you mean" ranked straight out of the index instead of sorted afterwards, GiST is the one that gives it to you. This demo doesn't need it.

One honest note while you're here: this particular predicate ORs across book and author, and no single-table index can answer an OR that spans two tables. So fuzzy scans both, at any size. At 77 rows that's free. It's the first thing I'd rethink at scale.

How do you add synonyms to Postgres full-text search?

Three statements and a text file, no application code. You create a dictionary with TEMPLATE = synonym over a source target file, clone the built-in configuration with COPY = english, and then ALTER MAPPING ... WITH book_synonym, english_stem so every token hits your dictionary before it reaches the stemmer. List each pair in one direction only, because the substitution runs at index time as well as at query time:

CREATE TEXT SEARCH DICTIONARY book_synonym (
    TEMPLATE = synonym, SYNONYMS = book_synonym
);

CREATE TEXT SEARCH CONFIGURATION book_synonym_search (COPY = english);

ALTER TEXT SEARCH CONFIGURATION book_synonym_search
    ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part
    WITH book_synonym, english_stem;

Six pairs, one per line, and the file has to sit in the server's $SHAREDIR/tsearch_data before that first statement runs:

avenue street
brook river
casement window
chamber room
apothecary doctor
vessel ship

That third line is the whole casement demo, and it needs no application to work:

SELECT b.title FROM book b
 WHERE to_tsvector('book_synonym_search', b.title || ' ' || b.excerpt)
       @@ websearch_to_tsquery('book_synonym_search', 'casement');

                 title
----------------------------------------
 The Picture of Dorian Gray
 Lady Windermere's Fan
 Wuthering Heights
 The Secret Agent: A Simple Tale
 The Invisible Man: A Grotesque Romance
 Anne of Green Gables
(6 rows)

Every target word occurs somewhere in the 77 books while no source word does, which is deliberate: a pair whose target appears nowhere silently matches nothing, and a feature that silently matches nothing looks exactly like a broken one. A test fails the build if the file and the corpus ever drift apart.

Only the dictionary's exact key gets there before the stemmer, so apothecaries never reaches the apothecary entry and synonym mode is not a superset of lexical mode. The documented fix is a thesaurus dictionary, which I left out on purpose: the synonym template is what makes the index-time symmetry below visible, and that is the thing worth seeing.

Write each pair once, in one direction

My first version of that file listed every pair both ways. It looked right. Symmetry, so either word finds the other.

It matched nothing.

Now the reason. The substitution runs at index time as well as at query time, because indexing and querying both go through the same text search configuration. With sleuth detective and detective sleuth both present, a document saying "the detective solved it" indexes as sleuth, and a query for sleuth is rewritten to detective. Both sides moved. They swapped past each other.

Delete one direction and both queries start working, which is the part that reads as backwards until you see it. With only detective sleuth, the document still indexes as sleuth and the query sleuth is not a key, so it stays sleuth. Both ends converge on the same lexeme. The manual documents the dictionary and the mapping; it does not spell out this consequence, so the test that builds two scratch dictionaries and shows both behaviors is the only citation I have for it.

Can you call Postgres full-text search functions from the JPA Criteria API?

Everything so far happened inside Postgres. Three retrievers, one table, a dictionary file and a psql prompt. No Java has appeared yet, and none of it needed any.

Now the app, which is the part nobody seems to have written down. Yes, you can reach all of it from the Criteria API, and this is where I expected the work to be. It wasn't. Hibernate renders an unregistered cb.function("ts_rank", …) straight through into the SQL as ts_rank(args) and lets Postgres resolve it. Hibernate never validates the function name. It renders it and lets the database decide. So ts_rank, ts_rank_cd, ts_headline, similarity, to_tsvector and websearch_to_tsquery all work with no registration whatsoever, and the whole suite stays green without any of them declared.

But @@ and % are different in kind. They're operators, so there is no fts(a, b) for Hibernate to fall through to, and the query fails at the database. Those two are the only things that need teaching:

public class SearchFunctionContributor implements FunctionContributor {

    private static final int ORDINAL = 1000;

    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        SqmFunctionRegistry registry = functionContributions.getFunctionRegistry();
        BasicTypeRegistry basicTypes =
                functionContributions.getTypeConfiguration().getBasicTypeRegistry();

        registry.registerPattern("fts", "?1 @@ ?2",
                basicTypes.resolve(StandardBasicTypes.BOOLEAN));

        // pg_trgm's similarity operator. Same operator the native RRF query's
        // fuzzy CTE uses, so both share pg_trgm.similarity_threshold and a
        // typo found by FUZZY survives into FUSED.
        registry.registerPattern("trgmSimilar", "?1 % ?2",
                basicTypes.resolve(StandardBasicTypes.BOOLEAN));
    }

    @Override
    public int ordinal() {
        return ORDINAL;
    }

}

Two lines of registration for a whole search stack. Register it through META-INF/services/org.hibernate.boot.model.FunctionContributor, which Hibernate reads while building the SessionFactory, before the Spring context exists. That's why the class isn't a bean and can't be injected into. The ordinal() override is belt and braces, since the interface already defaults to 1000; spelling it out documents which range this class belongs in.

Note the comment above trgmSimilar. Registering % here is what keeps the Criteria predicate and the native query on the same threshold, and skipping it cost me something later on.

If you go looking for this, most of what you'll find teaches MetadataBuilderContributor instead. That interface is @since 5.3 and ships in Hibernate 7.4.1 annotated @Deprecated(forRemoval = true). FunctionContributor is the SPI Hibernate 7 picks up, and it has been in Hibernate since 6.

With those two registered, each retriever is an ordinary Specification. The fuzzy one is the whole of it:

case FUZZY -> (root, cq, cb) -> cb.or(
        cb.isTrue(similarEnough(cb, root.get("title"), query)),
        cb.isTrue(similarEnough(cb, root.get("author").get("name"), query))
);

similarEnough is the trgmSimilar pattern from above, so that cb.or renders as title % :q OR author.name % :q: the same % you saw in psql, now on both columns, same threshold, reached through Specification.and and composable with a genre filter.

The Criteria route buys you one more thing on the way out: the query selects straight into a record with cb.construct, so the score and the highlighted snippet arrive as fields on a real type. That is also why I build the CriteriaQuery by hand instead of going through JpaSpecificationExecutor. Score and headline are computed SQL expressions, not mapped attributes, and a Spring Data projection has nothing on the entity to read them from.

One dead end worth saving you the trip. Spring Data JPA 4 also ships a newer PredicateSpecification alongside the classic Specification, and I checked whether it replaces it for ranked search. It can't: its toPredicate never receives the CriteriaQuery, and ORDER BY lives on the CriteriaQuery, so the newer interface structurally cannot rank.² The classic three-argument Specification is the right choice here, not the newer one.

Fusing three retrievers with RRF in one query

Reciprocal Rank Fusion combines ranked lists by throwing away the scores and keeping only the positions. RRF scores a document as the sum of 1 / (k + rank) over every retriever that returned it. That sidesteps the real problem, which is that a ts_rank_cd of 0.08 and a trigram similarity of 0.62 mean nothing to each other.

The constant k is 60, straight from the paper that introduced the method. Cormack, Clarke and Buettcher say they fixed it "during a pilot investigation and not altered during subsequent validation", and report that it turned out to be near-optimal, though "the choice was not critical".³ I left it untuned, because inventing a different number would mean claiming an evaluation I never ran.

RRF needs three ranked candidate sets, a ROW_NUMBER() window function over each, and a FULL OUTER JOIN to keep a book that only one retriever found. The Criteria API can express none of those, so the fused query is a single @Query(nativeQuery = true), and I left the seam visible:

WITH lexical AS (
    SELECT b.id,
           ROW_NUMBER() OVER (ORDER BY ts_rank_cd(
               b.search_vector, websearch_to_tsquery('english', :q)) DESC,
               b.id) AS rank
    FROM book b
    WHERE b.search_vector @@ websearch_to_tsquery('english', :q)
    ORDER BY rank LIMIT :limitPerRetriever
),
fuzzy AS ( ... ROW_NUMBER() OVER (ORDER BY GREATEST(
    similarity(b.title, :q), similarity(a.name, :q)) DESC, b.id) ... ),
synonym AS ( ... ROW_NUMBER() OVER (ORDER BY ts_rank( ... )) ... ),
fused AS (
    SELECT COALESCE(l.id, f.id, s.id) AS book_id,
           COALESCE(1.0 / (:k + l.rank), 0)
         + COALESCE(1.0 / (:k + f.rank), 0)
         + COALESCE(1.0 / (:k + s.rank), 0) AS fused_score
    FROM lexical l
    FULL OUTER JOIN fuzzy f ON f.id = l.id
    FULL OUTER JOIN synonym s ON s.id = COALESCE(l.id, f.id)
)

COALESCE(l.id, f.id) in that second join is load-bearing. After the first join an id lives in l.id or in f.id but not reliably in either, so joining synonym on l.id alone would split a book that fuzzy and synonym both found, but lexical missed, into two rows, each carrying only part of its score. The obvious-looking s.id = l.id OR s.id = f.id isn't equivalent. Postgres won't even run it: a FULL JOIN needs a hashable or mergeable condition.

Here's what the arithmetic buys, on q=great house:

{
  "title": "The House of the Seven Gables",
  "score": 0.0476,
  "rankContributions": [
    { "retriever": "LEXICAL", "rank": 2, "contribution": 0.01613 },
    { "retriever": "FUZZY",   "rank": 5, "contribution": 0.01538 },
    { "retriever": "SYNONYM", "rank": 2, "contribution": 0.01613 }
  ]
},
{
  "title": "Far from the Madding Crowd",
  "score": 0.0328,
  "rankContributions": [
    { "retriever": "LEXICAL", "rank": 1, "contribution": 0.01639 },
    { "retriever": "FUZZY",   "rank": null, "contribution": null },
    { "retriever": "SYNONYM", "rank": 1, "contribution": 0.01639 }
  ]
}

The House of the Seven Gables wins while being top of nothing. It's second, fifth and second. Far from the Madding Crowd is first in two retrievers and still loses, because three moderate agreements out-total two confident ones. That damping is the mechanism, not a side effect: rank 1 contributes 1/61 and rank 5 contributes 1/65, so no single retriever's favorite can run away with the result.

What two paths cost me

Now the bill. Having a Criteria path and a native path means having two definitions of the same retriever, and they drifted. The Criteria fuzzy predicate used similarity() >= 0.2. The native fuzzy CTE used %, whose default threshold is 0.3. So Stevensen returned three books through fuzzy mode and zero through fused mode, and nothing failed anywhere. No error, no warning, no red test.

The fix was to stop having two thresholds:

spring:
  datasource:
    hikari:
      connection-init-sql: SET pg_trgm.similarity_threshold = 0.2

Hikari doesn't reset session state between borrows, so that GUC is set once per physical connection and both paths inherit it. Registering % as a pattern function was the other half, so the Criteria predicate uses the same operator as the CTE instead of a comparison that only looks equivalent.

The functional synonym index is fragile in two directions

idx_book_synonym_vector indexes to_tsvector('book_synonym_search', title || ' ' || excerpt), and a functional index is only usable when the query's expression matches it exactly. Change how that expression is built in Java and you silently lose the index without breaking a single test.

The second direction is worse, because it changes answers rather than speed. A database session reads a .syn file once, the first time it uses that dictionary in the session. Edit the file on a live database and any session that already touched the dictionary keeps the old contents while a fresh session sees the new one. Two connections to the same database, disagreeing. The manual's remedy for the sessions⁴ is a dummy ALTER TEXT SEARCH DICTIONARY book_synonym ( dummy ), an option removal that's allowed to remove nothing, and the index still needs its own REINDEX on top, because its stored lexemes came from the old mapping. Until both have happened, an index scan and a sequential scan of the same table can return different rows for the same query, with no error anywhere. For a demo I take the blunt path and rebuild the container.

What this gives you, and where it stops

Three retrievers compose. Fusing them does not, and knowing exactly where that flips is worth more than any abstraction that hides it. Everything up to the fusion is ordinary Spring Data: Specification chains, one CriteriaQuery, two registered operators. The moment you need CTEs and window functions you are writing SQL, and the honest move is to write it in one place and leave it visible.

The rest of it is less work than its reputation suggests. A stored tsvector, a six-pair synonym file and one pg_trgm extension gave me typo tolerance, phrase queries, negation, highlighting and a vocabulary bridge over one table, with nothing to keep in sync and nothing extra to run.

My corpus is 77 books, so here is the same shape at a size mine will never reach. Instacart moved its search off Elasticsearch and onto Postgres (opens in a new tab) for billions of items across thousands of retailers, and reports a "10x reduction in write workload compared to the denormalized data model" it left behind, with the Postgres version "twice as fast". The primitives are the ones in this post: GIN indexes for text retrieval and a modified ts_rank for ranking, with pgvector added next to them for the semantic leg they built later.

Which makes the boundaries worth naming, because this is a retrieval layer and not a search product:

• This is not BM25. ts_rank and ts_rank_cd score a row from that row's tsvector alone. Postgres full-text search keeps no corpus-wide statistics, so there is no inverse-document-frequency term at all: a word appearing in 2 of 77 books weighs exactly the same as one in 70.
• There is no reranker in the database. RRF is rank arithmetic. It fuses positions and knows nothing about relevance beyond them. A cross-encoder or an LLM reranker is a stage after this one ends, and so are pgvector and embeddings.
• The demo endpoint has no auth and no rate limit, and a ranking query is not a cheap thing to leave open.
• One PG 18 upgrade note I did not reproduce. The release notes change full-text search to read configuration files and dictionaries through the cluster's default collation provider rather than always libc, and recommend that clusters on a non-libc provider upgrading via pg_upgrade "reindex all indexes related to full-text search and pg_trgm after the upgrade".¹ This repo runs the image default, so I never saw it. I'm passing on the release note, not a result.

The whole thing is at github.com/lukas-grigis/spring-postgres-fts (opens in a new tab). mise run demo starts Postgres, builds the app and serves the UI; 36 Testcontainers tests run against a real Postgres 18 with no mocked database anywhere. The README maps the load-bearing findings to the test that proves each one. The collation upgrade note and the synonym-index fragility section are sourced to the release notes and the manual, and say so.

Next up is the leg I cut on purpose: pgvector and semantic search, which answers a different question than any of these three. If something here breaks on your machine, open an issue.