On Mon, 2026-10-05 at 12:58 +0200, Thomas de Zeeuw wrote: > Not great that the first thing I read that this a design error, especially > since > you’re not familiar with the entire system. In any case, allow me to expand on > why we do it this way.
I could only go by what you told us, and as you said "strings", I assumed you were talking about character strings, not binary data. Full-text search is no good for binary data, even if encoded - it is only useful for natural language strings. > We have various columns that we want to search, most of them are English text > (or at least some kind of human language). However, one of them is a public > key > encoded as a hex string. We want to search all those columns in single query > and since most of them are English text we decided on full text search. We > imagined one of the words in the search vector would simply be the hex encoded > public key. This actually works pretty well for our use case, with the > exception > of the case I’ve described in the original email. > > The trigram search doesn’t work well for the English text columns we have. > We could do the trigram search just for the hex encoded public key, but we > thought it would return too many false positives given that we’re only > interested in exact and prefixed matches. The similarity searching property > of trigrams would be negative here as we could include public keys that’s > we're not searching for. If all you want to do is prefix matches and exact matches, I would use an ordinary LIKE with a regular B-tree index. That should work well, unless the public keys are large. In that case, you could try the ^@ operator and an SP-GiST index. Or resort to trigrams. > We’ll resort to converting the hex encoded public key to a vector/query > by casting instead of using the various functions for this. No idea what a vector/query is, but good if it works for you. Yours, Laurenz Albe
