Full text search(Postgres) Vs Elastic search

Question

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?

score 11 · Accepted Answer · answered Aug 25 '17 at 11:41

11

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.

answered Aug 25 '17 at 11:41

Jasen

11,837
2
30
48

For full text search, apart from Postgres database setup, do we need anything else for deployment? – overexchange Aug 25 '17 at 12:02
no other components re required, there is however quite a lot to read and understand before it can be used.. – Jasen Aug 25 '17 at 20:32
1

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... – overexchange Sep 25 '18 at 13:40
NoSQL does not add anything. – Jasen Dec 05 '18 at 20:30
NoSQL gives flexibility in schema design – overexchange Dec 06 '18 at 00:58
Collection of documents in NoSQL is different from bunch of tables in RDBMS, Isn't it? – overexchange Dec 06 '18 at 14:16
documents are records, not tables. – Jasen Dec 07 '18 at 02:15
My bad.. yes you are right.. the structure of each document can be different unlike table row... Isn't it? – overexchange Dec 07 '18 at 13:34
yeah, JSON documents (of mixed structure) can also be stored in the rows of postgresql tables. https://www.postgresql.org/docs/10/datatype-json.html – Jasen Dec 07 '18 at 20:11
1

This [answer](https://stackoverflow.com/a/17955601/3317808) talks about heterogenous data, where MongoDB is preferred over PostGreSQL – overexchange Dec 16 '18 at 20:09
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](https://anton.dollmaier.name/storing-syslogs-mongodb-syslog-ng) where full-text search was slower in MySQL compared to MongoDB. – overexchange Dec 17 '18 at 16:05
comparing apples and oranges. – Jasen Dec 18 '18 at 08:32
ElasticSearch also uses NoSQL database. – overexchange Dec 18 '18 at 15:56

Full text search(Postgres) Vs Elastic search

1 Answers1

Linked