I notice there are several modules to create specialized indices in PostgreSQL for searching VARCHAR data. For example, fuzzy, trigram, full text, etc.
I've been googling around but I can't find the optimal method (reasonable speed and size, simplicity) for my use case.
My text searches will always be like the following. User specifies a word (e.g. "John") and I have a field called "FullName" that could return records with "John Doe", "Robert Johnson", "Joe Johnson Smith", etc. I may also extend the search criteria to other fields. So for example the query would always look like this:
SELECT * FROM MyTable WHERE upper(FullName) LIKE upper('%John%');
So you see it is also case insensitive. Pretty simple/standard stuff.
That said, which would be the best extension module to use? A "gist" index on the uppercased column? Or something else? Thanks!