Performing search on a column by query

Question

Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how full text index works.

I have an API that returns categories. On the front-end I have an input box that auto-completes the input as the user types. So assuming I have the following categories:

• House keeping
• Entertainment and recreation
• Taxes and bills

When the user types "ta" I'd like to offer them both "Entertainment and recreation" and "Taxes and bills". Needless to say that I've discovered that full text index wont return the given result on the term "ta". Looks like full text index works on actual text like "tax" or "bill" and not on meaningless phrases like "ta" or "ou".

Am I using fulltext index incorrectly and I should use the old good (bad?) LIKE %ta%? Or are there any other ways to achieve described functionality that would also be db efficient (consider the fact the the front-end will query the DB on each character the user inputs).

For references: I'm using PostgreSQL and my query looks like select * from categories where to_tsvector('english', name) @@ plainto_tsquery('english', 'ta')

Take a look at Amazon's search. I type a phrase like "hou" and it offers me results like "house decor" "house of cards" "housewarming". How such search performed? By a regular index on the column + LIKE %term%?

I know there are solutions like elastic search of sphinx. I don't know if they are suited in my case, but for now I want to stay inside the database to minimize costs and time in favor of faster development.

Answer 1

Postgres Text Search supports prefix search

select 'housewarming'::tsvector @@ 'hou:*'::tsquery; --true

I looked at amazon's search, and guess what? ta doesn't return entertainment

Answer 2

When the user types "ta" I'd like to offer them both "Entertainment and recreation" and "Taxes and bills". Needless to say that I've discovered that full text index wont return the given result on the term "ta". Looks like full text index works on actual text like "tax" or "bill" and not on meaningless phrases like "ta" or "ou".

Yes, FTS is useless in this case. However @Akash is right in the comments when he suggests a trigram index.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON categories USING GIST (name gist_trgm_ops);

Now you're LIKE %term% will use the index.