Find 'Abbey Road when type 'Beatles abbey rd': Fuzzy/Semantic search in Postgres

https://rendiment.io/postgresql/2026/01/21/pgtrgm-pgvector-music.html

by nethalo • 6 months ago

83 26 6 months ago

lbrito• 6 months ago

I was just starting to learn about embeddings for a very similar use on my project. Newbie question: what are pros/cons of using an API like gpt Ada to calculate the embeddings, compared to importing some model on Python and running it locally like in this article?

alright2565• 6 months ago

Do you want it to run on your CPU, or someone else's GPU?

Is the local model's quality sufficient for your use case, or do you need something higher quality?

storystarling3• 6 months ago

The main trade-off I found is the RAM footprint on your backend workers. If you run the model locally, every Celery worker needs to load it into memory, so you end up needing much larger instances just to handle the overhead.

With Ada your workers stay lightweight. For a bootstrapped project, I found it easier to pay the small API cost than to manage the infrastructure complexity of fat worker nodes.

gingerlime• 6 months ago

Great post. Explains the concepts just enough that they click without going too deep, shows practical implementation examples, how it fits together. Simple, clear and ultimately useful. (to me at least)

cess112• 6 months ago

I found fuzzy search in Manticore to be straightforward and pretty good. Might be a decent alternative if one perceives the ceremony in TFA as a bit much.

pinkmuffinere2• 6 months ago

The rewritten title is confusing imo. Can I propose:

“Finding ‘Abbey Road’ given ‘beatles abbey rd’ search with Postgres”

pinkmuffinere2• 6 months ago

(The missing close-apostrophe, and the use of “type” are what really confuse me in the original submission)

fsckboy2• 6 months ago

these days i find myself yearning to type "Beatles abbey rd" and find only "Beatles abbey rd"

Manfred• 6 months ago

Especially with small datasets it’s more important to be exact at the expense of a user having to fix a typo.

storystarling3• 6 months ago

I learned this the hard way on a book platform I'm working on. While semantic search is useful for discovery, we found that prioritizing exact matches is critical. It seems users get pretty frustrated if they type a specific title and get a list of conceptually similar results instead of the actual book. We ended up having to tune the ranking to heavily favor literal string matches over the vector distance to keep people from bouncing.

fsckboy2• 6 months ago

everything you are saying rings perfectly true to me but there's an additional problem I encounter. (i'm going to make up my example because i'm lazy to check but you'll get the idea) say you want to look up "Alexander the Great"...

...God help you if Brad Pitt and or the Jonas Brothers ever played a role with exactly that name-match. The web and search (and the culture?) have become super biased toward video especially commercial offerings, and the sorting ranked by popularity means pages and pages of virtually identical content about that which you are not interested in.

drsalt• 6 months ago

why did you have to learn this the hard way?

qingcharles• 6 months ago

I remember eBay 30 years ago when it would showed you whatever you typed in. Compared to 2026 where it only shows you everything except the thing you typed in.

TeamDman• 6 months ago

for 50,000 rows I'd much rather just use fzf/nucleo/tv against json files instead of dealing with database schemas. When it comes to dealing with embedding vectors rather than plaintext then it gets slightly more annoying but still feels like such an pain in the ass to go full database when really it could still be a bunch of flat open files.

More of a perspective from just trying to index crap on my own machine vs building a SaaS

danielfalbo• 6 months ago

Abbey Road
The Dark Side of the Moon
OK Computer

Those are my 3 personal records ever. I feel so average now...

tialaramex• 6 months ago

The other two are popular but "Dark Side of the Moon" in particular was extremely popular. Like, top 10 albums ever level popular.

esafak• 6 months ago

tl,dr: A demo of pg_trgm (fuzzy matcher) + pgvector (vector search).

TurdF3rguson2• 6 months ago

Sounds nice but I'm not sure that trigram brings anything to the table that vector didn't already bring.

timlod• 6 months ago

FWIW, the performance considerations section is a little simplistic, and probably assumes that exact dataset/problem.

For GIN for example, perfomance depends a lot on the size of the search input (the fewer characters, the more rows to compare) as well as the number of rows/size of the index.

It also mentions GiST (another type of index which isn't mentioned anywhere else in the article)..

augusteo2• 6 months ago

On the API vs local model question:

We went with API embeddings for a similar use case. The cold-start latency of local models across multiple workers ate more money in compute than just paying per-token. Plus you avoid the operational overhead of model updates.

The hybrid approach in this article is smart. Fuzzy matching catches 80% of cases instantly, embeddings handle the rest. No need to run expensive vector search on every query.

TurdF3rguson2• 6 months ago

Those text embeddings are dirt cheap. You can do around 1M titles on the cloudflare embedding model I used last time without exceeding daily free tier.

augusteo2• 6 months ago

yeah exactly. even OpenAI/Gemini are really cheap too

exogen• 6 months ago

This could also be applied to record linkage. With search, there will usually be multiple results, and there's always a "top" match even if its confidence/score is quite low. In record linkage, at least if you're automating it, you need to minimize false positives and only automatically link records if confidence is super high that they're a match – and that doesn't just mean the top scoring match has high confidence, but that there's also no 2nd best match with a good score. If that's not the case, leave the records for manual human review.

My experience here is also related to music. Here are some cases to think about:

What's the actual title of the song "Mambo #5" vs. how you might search for it or find it referenced in other records? Mambo #5? Mambo No. 5? Mambo No. Five? Mambo Number 5? Mambo Number Five? And that's not even getting to the fact that the actual title is actually longer, with a parenthetical. This is a case where bigrams, trigrams, or other string similarly metrics wouldn't perform very well. Same with the Beatles song, is it "Dr. Robert" or "Doctor Robert"? Most string similarly algorithms put "Dr" and "Doctor" pretty far apart, but with vectors they should be practically equivalent.

How about "You've Lost that Loving Feeling"? Aren't there some dropped Gs in those gerunds? Is it You've Lost That Lovin' Feeling? You've Lost That Lovin' Feelin'? You've Lost That Loving Feelin'? In this case, string similarity (including trigrams) perform very well.

How about songs with censored titles? Some records will certainly have profanity censored, but would it be like "F*ck", "F**k", "F@$k", or what? And is the censorship actually part of the canonical song title, or just some references to it?

In the "#5" and "Dr." cases, this could be solved pretty effectively by the normalization step described in the article (hardcoding what #, No., and Dr. expand to) – although even that can get pretty complicated: what do you do about numbers? Do you normalize every numerical reference, e.g. "10 Thousand", to digits, or words? What about rarely used abbreviations, or cases where an abbreviation is ambiguous and could mean different things in different contexts? If someone has a song called "PT Cruiser" are you gonna accidentally normalize that to "Part Cruiser"? For this reason, I like to see this not as a "normalization" step, where there's a single normalized form, but rather a "query expansion" step – generate all the possible permutations, and those are your actual comparison strings.

It seems like embeddings could do the job of automatically considering different spellings/abbreviations of words as equivalent. I'm just a casual observer here, but I'm sure this is also a well-explored topic in speech-to-text, since you have to convert someone's utterances to match actual entity names, like movie titles for example.

Find 'Abbey Road when type 'Beatles abbey rd': Fuzzy/Semantic search in Postgres

Related Stories