I misspelt 12 footballers' names. Postgres full-text found 4, Meilisearch found all 12.

Type "belerin" into most search boxes and you get nothing. The player is Héctor Bellerín, you dropped an accent and a letter, and the database shrugs. I wanted to know how much that costs and whether the usual answer, "just use Postgres full-text", actually holds. So I loaded 1,760 real footballers into three engines and typed their names badly on purpose.

The short version: Postgres full-text search found the right player in 4 of 12 misspelt queries. Meilisearch found all 12. And the interesting one in the middle, Postgres trigram search, found 8, but two of its answers were the wrong player returned with total confidence.

The setup

The data is every Premier League player from five seasons of the Fantasy league export, deduplicated to 1,760 unique names. Real names, with the accents and hyphens that make this hard: Bellerín, Kolašinac, Papastathopoulos, Cédric, Pépé.

Three engines, same 1,760 rows:

• Postgres full-text, a GIN index over to_tsvector('simple', full_name), queried with plainto_tsquery.
• Postgres trigram, the pg_trgm extension with a GIN index, queried with word similarity.
• Meilisearch, the search engine from the French startup of the same name, indexed with its defaults and nothing tuned.

Then twelve queries, each one a name typed the way a real person types it: an accent dropped, a letter doubled, a syllable transposed.

Full-text search does not tolerate a single mistake

Here is the result that should change what you reach for. Postgres full-text, on the twelve typo queries:

It got four, and every one it got was a name with no accent that the user happened to spell correctly. to_tsvector lowercases and splits on word boundaries, and that is the entire extent of its forgiveness. "martinez" does not match "Martínez" because the í is a different character and full-text does no folding. "mustaffi" does not match "Mustafi" because they are simply different tokens. There is no fuzziness in it at all. It is an exact-token index with a case fold, and a search box built on it silently fails the moment a user misremembers a spelling, which for foreign names is most of the time.

Trigram is better, and that is where it gets dangerous

Reach for pg_trgm next and things improve. It chops each name into three-letter runs and scores how many two strings share, so "mustaffi" and "Mustafi" overlap on almost all of theirs and match. I tuned the word-similarity threshold to 0.45, which is the sort of number you land on after a few tries, and it got 8 of the 12. Some of the accented ones too, which surprised me.

But look at what it did with the four it got wrong:

It did not return nothing. It returned Martinelli for Martínez, and it returned him as the single best match, ranked above the correct player, with no signal that it was guessing. "hector belerin" resolved to Michael Hector because the token "hector" scored cleanly and the mangled "belerin" was ignored. For a user this is worse than an empty result. An empty result tells you to try again. A confident wrong answer tells you the player you are thinking of does not exist, or quietly enters the wrong one.

This is the trap with trigram similarity as a search backend. It always has a best match, because similarity is a continuous score and something is always closest. Tighten the threshold to cut the wrong answers and you lose real matches too. It has no notion of "I am not sure", which is the one thing a search box most needs.

Meilisearch got all twelve, and was faster

Indexed with defaults, no configuration, Meilisearch returned the correct player as the top hit for all twelve queries, accents and doubled letters and transpositions alike. It folds diacritics, tolerates a bounded number of typos scaled to word length, and ranks exact and prefix matches above fuzzy ones so it does not do the Martinelli thing.

It was also the fastest of the three:

The full-text number is fast because returning zero rows is cheap. Meilisearch's sub-millisecond figure is its own reported processing time; the ~1.2ms is what I measured over the network including the HTTP round trip on the same box.

Where this actually matters

The whole premise of a football quiz site like ExtraTime is that you have a player in mind and the game has to know which one. Career Path, The Scout, the guessing games: they live or die on turning "that Arsenal right-back, Spanish, belerin something" into Héctor Bellerín. On full-text that query returns nothing and the game feels broken. On trigram it might return Michael Hector and the game feels wrong, which is worse. This is exactly the class of lookup where the difference between the three engines is the difference between a working feature and a frustrating one.

The honest cost

None of this makes "just use Postgres" bad advice. It makes it advice with a boundary.

Meilisearch is a second service. It is another process to run, another thing to monitor, and critically another copy of your data that has to be kept in sync with the source of truth. Your players live in Postgres; Meilisearch holds a denormalised index of them, and every insert and update and delete now has to reach two systems. That is real operational weight, and for a search box that only ever gets exact IDs or correctly spelled terms it is weight you do not need.

The line is roughly this: if search is a convenience, full-text in the database you already run is fine, and trigram covers a bit more as long as you are comfortable showing the occasional wrong match. If search is the product, if the user's whole task is finding the right thing from a fuzzy memory of it, a dedicated engine stops being over-engineering and starts being the thing that makes the feature work.

What I got wrong on the way

My first trigram run reported 0 out of 12, and I nearly wrote that Postgres trigram was useless. It was my bug. I had written the similarity operator as %% instead of %, doubling it out of habit, and Postgres rejected every query as an unknown operator. The benchmark counted each error as a miss and produced a clean, wrong, damning result: zero.

The tell was that it was too clean. Trigram scoring "mustaffi" against "Mustafi" is basic and I could see by hand it should match. When a whole column of results is a perfect zero, the instrument is usually broken, not the thing under test. I fixed the operator, tuned the threshold the way anyone deploying this would, and trigram came back with a fair 8 out of 12. Reporting the 0 would have been an easy, satisfying, completely false story.

Run it yourself

Meilisearch is one container and the index is one POST:

docker run -d -p 7700:7700 -e MEILI_MASTER_KEY=devkey getmeili/meilisearch:v1.12
curl -X POST localhost:7700/indexes/players/documents \
  -H 'Authorization: Bearer devkey' -H 'Content-Type: application/json' \
  --data-binary @players.json
curl 'localhost:7700/indexes/players/search' -H 'Authorization: Bearer devkey' \
  -H 'Content-Type: application/json' --data '{"q":"belerin"}'

For the Postgres side, the two indexes to compare are:

CREATE INDEX ON players USING gin (to_tsvector('simple', full_name));
CREATE INDEX ON players USING gin (full_name gin_trgm_ops);

Query the trigram one with SET pg_trgm.word_similarity_threshold = 0.45; and WHERE 'belerin' <% full_name ORDER BY word_similarity('belerin', full_name) DESC. Type real names badly and watch which engine finds the person you meant.

What to do about it

Try one typo before you decide. Take the search your users actually run, misspell it once the way a hurried person would, and see what each option returns. If full-text gives you nothing and you are fine with that because your users paste exact values, keep it. If trigram gives you a plausible wrong answer, notice that you would have shipped it. And if the entire point of the feature is finding a half-remembered thing, the dedicated engine is not the heavy option, it is the one that does the job.