So, for my use case I simply need to search for a case insensitive substring. It need not be super exact. It seems like there are two ways I can do this:
CREATE INDEX idx_users_name ON users USING GIST(lower(name) gist_trgm_ops);
SELECT * FROM users WHERE lower(name) LIKE '%john%';
Or I can do it like this:
CREATE INDEX idx_users_name ON users USING GIST(name gist_trgm_ops);
SELECT * FROM users WHERE name % 'john';
Unfortunately I cannot find any documentation on the trade-offs between these two approaches. For my test dataset of 75K records the query speed seems pretty damn similar.
So, I guess my question is, what is the difference for querying and insert for the two approaches?
Thanks!