Full text search(Postgres) Vs Elastic search

Question

Read Query

In Posgres, Full text indexing allows documents to be preprocessed and an index saved for later rapid searching. Preprocessing includes:

• Parsing documents into tokens.

• Converting tokens into lexemes.

• Storing preprocessed documents optimized for searching.

---

tsvector type is used in Postgres for full text search

tsvector type is different than text type in below aspects:

• Eliminates case. Upper/lower case letter are identical

• Removes stop words ( and, or, not, she, him, and hundreds of others)-because these words are not relevant for text search

• Replaces synonyms and takes word stems (elephant -> eleph). In the full text catalogue, it does not have the word elephant but the word elep.

• Can (and should) be indexed with GIST and GIN

• Custom ranking with weights & ts_rank

---

How Elastic search(search engine) has advantage over full text search in Postgres?

Gigantic

Answer 1

fulltext search and elasticsearch are both built on the same basic technology inverted indices so performance is going to be about the same.

FTS is going to be easier to deploy.

ES comes with lucene,

if you want lucene with FTS that will require extra effort.

Coly

For full text search, apart from Postgres database setup, do we need anything else for deployment? – Gigantic

no other components re required, there is however quite a lot to read and understand before it can be used.. – Coly

Major advantage could be Elastic carries NoSQL database where as PostGres is OODBMS... For example: For text search from multiple data sources.. gathering the data from multiple data sources to NoSQL makes more sense... – Gigantic

NoSQL does not add anything. – Coly

NoSQL gives flexibility in schema design – Gigantic

Collection of documents in NoSQL is different from bunch of tables in RDBMS, Isn't it? – Gigantic

documents are records, not tables. – Coly

My bad.. yes you are right.. the structure of each document can be different unlike table row... Isn't it? – Gigantic

yeah, JSON documents (of mixed structure) can also be stored in the rows of postgresql tables. postgresql.org/docs/10/datatype-json.html – Coly

This answer talks about heterogenous data, where MongoDB is preferred over PostGreSQL – Gigantic

The single most important factor in designing the database schema with MongoDB is about, matching the data access patterns of your application. One case-study where full-text search was slower in MySQL compared to MongoDB. – Gigantic

comparing apples and oranges. – Coly

ElasticSearch also uses NoSQL database. – Gigantic