Spots 
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:
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. 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):
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.
A production system I work on needed a search box that forgives people.