I couldn't touch the database, so I built search next to it

A production system I work on needed a search box that forgives people. They type kavefozo and expect Kávéfőző. They type hedphones and expect headphones.

The constraints were the interesting part:

• The schema could not change. No new columns, no generated columns, no migrations on tables that weren't ours.
• No new infrastructure. No Elasticsearch, no Meilisearch, no extra service to run, sync, back up and secure.

So search had to happen inside the database that was already there. PostgreSQL turned out to have almost every piece needed. Wiring those pieces together properly was the hard part, and that wiring became an open-source library: Fuzzphony.

Fuzzphony is at v0.4 and still in active development. The core works and is heavily tested, but the API can change before 1.0, and there are rough edges I'll be upfront about at the end.

This post covers the design, the bug that taught me the most, and what I still haven't solved.

What LIKE '%…%' gets wrong

Most search boxes start here:

SELECT * FROM product WHERE name ILIKE '%' || :q || '%' LIMIT 20;

It misses typos (hedphones), accents (creme vs crème) and word forms (drills vs drill). It can't exclude a word, it doesn't rank, and when it misses, it reads the whole table to return nothing.

Postgres ships three tools for exactly these problems:

• Full-text search (tsvector, tsquery, ts_rank_cd): stemming for 28 languages, field weights A to D, relevance ranking.
• unaccent: folds Kávéfőző into Kavefozo.
• pg_trgm: trigram similarity, which is what lets wireles match wireless.

None of them is new. The work is in making them behave together, stay in sync with your data, and be safe to point at user input.

The sidecar table

Since your tables are off-limits, the index lives in its own table next to them.

For every index, Fuzzphony keeps a table such as fuzzphony_products with:

• a weighted tsvector for full-text search, on a GIN index,
• a normalised, unaccented text column for typo tolerance, on a GIN trigram index,
• typed filter columns on btree indexes, so where('price', '<=', 20_000) never has to touch your table,
• the inputs for ranking: a boost column (popularity, say) and a recency column.

The source can be a single table or any SELECT, joins included. With schema: fuzzphony set, the sidecar tables, the queue and the helper functions all live in their own schema, and dropping an index is one command.

To be precise about "off-limits": no column is ever added, but in the default queue mode and in trigger mode Fuzzphony does attach triggers to the tables it watches. That's how it sees writes from raw SQL, imports and other applications. If even a trigger is too much, the orm and manual modes need none.

The cost of the design is obvious: there is now a second copy of the searchable data, and it has to stay in sync.

Keeping it in sync without hurting writes

There are four sync modes:

Three details made the trigger-based modes usable on real tables.

Statement-level triggers with transition tables. A row-level trigger on an UPDATE brand SET … that touches 100 000 rows fires 100 000 times. A statement-level trigger fires once and sees every changed row through a transition table, so queuing the affected products becomes a single set-based statement. In simplified form (not the exact generated SQL):

CREATE TRIGGER brand_sync
AFTER UPDATE ON brand
REFERENCING NEW TABLE AS changed
FOR EACH STATEMENT EXECUTE FUNCTION queue_products_of_changed_brands();

-- inside the function, roughly:
INSERT INTO sync_queue (index_name, doc_id)
SELECT 'products', p.id
FROM changed b
JOIN product p ON p.brand_id = b.id;

Only refresh when something relevant changed. An UPDATE product SET last_viewed_at = now() should not reindex anything. For the index's own table, Fuzzphony checks whether a mapped column actually changed. For joined tables you opt in with columns: ['name'], because a library cannot guess which of a joined table's columns matter to you.

TRUNCATE is not a delete. It fires no DELETE triggers, so early versions left stale documents in the index after a TRUNCATE, and not even a full reindex removed them. I found that one late. Now every watched table also gets an AFTER TRUNCATE trigger, and a full reindex prunes documents whose rows no longer exist.

The worker takes a batch with DELETE … FOR UPDATE SKIP LOCKED and refreshes it in the same statement, so a failed refresh rolls the dequeue back and no id is lost, and several workers can run side by side. If you'd rather not run a long-lived process, fuzzphony:worker --once from cron works too.

What happens to a query

From the application side, a search looks like this:

$result = $fuzzphony->in(Product::class)
    ->query('wireles mouse -cable')          // a typo and an exclusion
    ->where('price', '<=', 20_000)
    ->where('in_stock', true)
    ->highlight('name')
    ->get();

foreach ($result as $hit) {
    echo $hit->id, ' ', $hit->score, ' ', $hit->highlights['name'];   // "<mark>Wireless</mark> mouse"
}

Behind that call, the query goes through a few stages:

A few decisions in there are worth explaining.

User input never throws. Unbalanced quotes, stray operators and absurdly long input are repaired and reported in $result->warnings, and $result->interpretedAs shows how the query was understood. Developer mistakes are the opposite: they fail loudly, with a hint. Index "products" has no filter "prise". Did you mean "price"?

Exact first, typo tolerance as a fallback. Trigram matching is slower and noisier than full-text matching, so by default it only runs when exact matching finds fewer than fallback_below hits.

Typo tolerance is per word. The first version compared the whole query string against the fuzzy fields, so one long common word could satisfy it on its own. On the demo catalogue, wireles mice returned 20 000 products (chairs, drills, kettles), of which 1 666 were mice. Now every word has to match on its own, exactly or by trigram similarity, inside the query's real AND / OR / NOT structure. The same query returns exactly the 1 666 wireless mice.

An empty result gets one more try. If a query of two or more words finds nothing, each word is checked on its own, the words that match nothing are dropped, and the search runs again with a warning saying which words were ignored. wireless mouse aluminum returns the wireless mice instead of an empty page.

Ranking is explainable. The score is a plain formula:

relevance = text  × ts_rank_cd(weights A..D)
          + fuzzy × word_similarity(query, fuzzy fields)

score     = relevance
          + exact_bonus + prefix_bonus
          + boost   × boost column
          + recency × 2^(−age / half_life)

min_score applies to relevance only, so a popularity boost can reorder relevant hits but can never pull an irrelevant one into the results. Every hit carries a score breakdown, so "why is this first?" always has an answer.

The bug that taught me the most

A German test query, Tasche für Laptop, did not find a product called Laptop Tasche. In the French catalogue, searching for à alone matched 13 products. Hungarian és misbehaved the same way.

A text search configuration sends every token through a list of dictionaries, in order. Mine ran unaccent before the Snowball stemmer. The stemmer's German stop-word list contains für, but by the time the token reached it, it had become fur, which is not a stop word. So für was indexed as an ordinary word, and every query containing it had to match it.

The fix was to drop stop words before anything else touches the token, with a stop-word dictionary built from the language's own list. In simplified form:

CREATE TEXT SEARCH DICTIONARY fuzzphony_german_stop (
    TEMPLATE = simple, STOPWORDS = german, ACCEPT = false
);

CREATE TEXT SEARCH CONFIGURATION fuzzphony_german (COPY = german);

ALTER TEXT SEARCH CONFIGURATION fuzzphony_german
    ALTER MAPPING FOR asciiword, word, asciihword, hword, hword_asciipart, hword_part
    WITH fuzzphony_german_stop, unaccent, german_stem;

With ACCEPT = false, the stop dictionary discards stop words and passes everything else on to the next dictionary.

Two things came out of this besides the fix. fuzzphony:doctor now reports a configuration that still keeps accented stop words. And the demo got a Languages page that searches small catalogues in English, German, French, Spanish and Hungarian, and shows the lexeme Postgres produced for every query word. I would have caught this bug on day one if that page had existed earlier.

The numbers, with the caveats

A sample run on 200 000 products, PostgreSQL 16, a small cloud VM, 20 results per query:

Read this carefully, because a benchmark against ILIKE is easy to make look good:

• On plain words, ILIKE is much faster. LIMIT 20 without an ORDER BY just returns the first 20 rows the scan happens to hit. Fast, but not the best 20.
• The ILIKE baseline has no trigram index, which is why its misses are full table scans. A gin_trgm_ops index would make those rows fast. It would not make them correct: ILIKE '%hedphones%' finds nothing no matter how it is indexed.
• ILIKE has no concept of exclusion, so on the last row it silently ignores -headphones.

Run the numbers on your own data before believing anyone's benchmark, including this one. The benchmark script is in the repository, and CI runs it on every push.

When not to use it

Fuzzphony is not the right tool if you need hundreds of millions of documents or thousands of searches per second on one index, analytics-style aggregations, semantic or vector search (look at pgvector or a dedicated engine), or a database other than PostgreSQL.

It fits best where the database is not yours to change (a legacy system, an ERP, tables another team owns), where you are replacing LIKE in admin panels and back offices, or where the data has to stay in the database for compliance reasons.

What I haven't solved yet

Two things are genuinely open, and I'd like opinions on both.

Typo tolerance is too lenient on short words. At the default similarity threshold, mouse also matches monitor: short words have few trigrams, so a single shared one weighs a lot. The plan is length-aware thresholds, probably with trigrams only generating candidates from a vocabulary table and an edit-distance check deciding. How do you tune pg_trgm for short words?

Ranking of very frequent words. A GIN index can find matches but can't return them in relevance order, so for a word that matches tens of thousands of documents, Fuzzphony ranks the first candidate_limit (2000 by default) that the index returns. That's not necessarily the best 2000. For most back-office searches this doesn't show, but it's the real ceiling of doing search inside Postgres, and I'd rather say so than hide it behind "approximate ordering".

Rough edges I'm working on before 1.0

Writing the docs for v0.4 surfaced a few things I'd design differently, so they're on the list:

• Trigger privileges. The sync functions run with the privileges of whoever writes the row. In a shared database, every role that writes to a watched table therefore needs grants on Fuzzphony's queue, or its writes fail. Moving the enqueue step into a SECURITY DEFINER function removes that coupling.
• A batch that always fails. A refresh that fails deterministically (a document over Postgres' ~1 MB tsvector limit, for example) is rolled back and retried forever, which blocks the queue behind it. It needs a dead-letter path.
• Pruning as a default. A full reindex removes documents whose rows it can't see. If the reindexing role sees fewer rows than the application (row-level security, a different search_path), that removes real documents. It should stop and ask above a threshold.
• Field scoping leaks into typo tolerance. name:sony can return Sony-brand products, because the fuzzy fields share one trigram column. Per-field columns are planned for 0.5.

None of these show up in a single-app setup with one database role, which is how most people will try it. But they're exactly what bites in the environments Fuzzphony is aimed at, so they come before new features.

Try it

composer require fuzzphony/fuzzphony

Or run the demo, a full Symfony app on a seeded catalogue, with ILIKE and Fuzzphony side by side, a ranking playground with sliders, and a configuration wizard:

git clone https://github.com/er2es/fuzzphony
cd fuzzphony/demo && docker compose up --build    # http://localhost:8000

It requires PHP 8.4+ and PostgreSQL 15+, and is tested on Symfony 7.4 and 8.0 against PostgreSQL 15 to 18. The current release is v0.4 and development is ongoing: breaking changes can still happen before 1.0, and each one is listed in the CHANGELOG with upgrade steps in UPGRADE.md.

I built this with a lot of help from Claude Code. The design decisions are written down as ADRs in the repository, and the heavy testing (PHPStan at max level, 100% line coverage, mutation testing) is partly because of that: when you didn't type every line yourself, tests are how you know what the code actually does.

Feedback is very welcome, in the comments or as a GitHub issue, especially on the two open problems above. If the library looks useful, a star on GitHub helps other people find it.