PostgreSQL(Full Text Search) vs ElasticSearch

Question

Hi I am doing some research before I implement search feature into my service. I'm currently using PostgreSQL as my main storage. I could definitely use PostgreSQL's built-in Full-Text-Search but the problem is that I have data scattered around several tables.

My service is an e-commerce website. So if a customer searches "good apple laptop", I need to join Brand table, post table and review table(1 post is a combination of several reviews + short summary) to fully search all posts. If I were to use elasticsearch, I could insert complete posts by preprocessing.

From my research, some people said PostgreSQL's FTS and elasticsearch have similar performance and some people said elasticsearch is faster. Which would be better solution for my case?

Thanks in advance

Answer 1

If PostgreSQL is already in your stack the best option for you is using the PostgreSQL full-text search.

Why full-text search (FTS) in PostgreSQL ?

Because otherwise you have to feed database content to external search engines.

External search engines (e.g. elasticsearch) are fast BUT:

• They can't index all documents - could be totally virtual
• They don't have access to attributes - no complex queries
• They have to be maintained — headache for DBA
• Sometimes they need to be certified
• They don't provide instant search (need time to download new data and reindex)
• They don't provide consistency — search results can be already deleted from database

If you want to read more about FTS in PostgreSQL there's a great presentation by Oleg Bartunov (I extracted the list above from here): "Do you need a Full-Text Search in PostgreSQL ?"

This as a short example how you can create a "Document" (read the text search documentation) from more than one table in SQL:

SELECT to_tsvector(posts.summary || ' ' || brands.name)  FROM posts INNER JOIN brands ON (brand_id = brands.id); 

If you are using Django for your e-commerce website you can also read this article I wrote on "Full-Text Search in Django with PostgreSQL"