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
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.