PostgreSQL(Full Text Search) vs ElasticSearch

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

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"

Short Answer: Elasticsearch is better

Explanation: PostgreSQL and Elasticsearch are 2 different types of databases. Elasticsearch is powerful for document searching, and PostgreSQL is a traditional RDBMS. No matter how well PostgreSQL does on its full-text searches, Elasticsearch is designed to search in enormous texts and documents(or records). And the more size you want to search in, the more Elasticsearch is better than PostgreSQL in performance. Additionally, you could also get many benefits and great performance if you pre-process the posts into several fields and indexes well before storing into Elasticsearch.

If you surely need the full-text feature, you may consider MSSQL, which may do better than PostgreSQL.

Reply on Comments: It should be commonsense for the properties comparison on those different types of DBs. Since OP didn't provide what amount and size of data are stored. If this is small size data-in-search, Maybe choose Postgres or ES, both are OK. However, if transactions and data repository become larger in future, ES will provide benefits.

You could check this site to know the current ranking of each type DB, and choose the best one for your requirements, architecture and future data growth of your applications.