Checkout the slides

Background

Today, Elasticsearch is a very popular datastore for full-text searches. Using Elasticsearch has certain considerations such as: • Maintaining a separate cluster just for keeping a full-text index • A proper schema design, given requirements on performance and Elasticsearch limitations around support for one to many and many to many relationships • Making sure the cluster is kept consistent, given that Elasticsearch doesn’t support full ACID semantics. • Typically, resource requirements of Elasticsearch are know to be high and proper sizing is essential, given requirements on types of queries, indexing throughput and search latencies, etc. PostgreSQL has a rich set of features that support full-text indexing. Databases have some great benefits for full-text indexing, especially when there are several entities with complex relationships among them, with a few fields that require full-text indexing. Some of the these benefits are: 1. Full text search can be combined with other non-text or keyword fields 2. Utilize full power and expressiveness of SQL 3. Get ACID semantics 4. All data is in the same datastore, without having to worry about transferring to another datastore and keeping both datastores consistent 5. Databases are good at doing JOINS efficiently, even with tables that do not fit in memory. This is a much-needed feature as schema gets complex. In contrast, datastores like Elasticsearch/Lucene are not good at expressing even one to many or parent child relationships, leave alone many to many. 6. Full text search can be used in conjunction with other fields such as geo-spatial data, graph data, using the power of SQL to do powerful queries

Overview of the Paper

The goal of this talk is to look briefly at PostgreSQL’s full-text indexing and search capabilities, and take a text-heavy dataset such as Wikipedia data as a sample, and study the behavior of PostgreSQL and share learnings as it performs indexing and searches on this dataset. Also, compare and contrast the capabilities and performance with Elasticsearch in following areas: 1. Schema Design 2. Size of index 3. Doing other queries such as graph queries in conjunction with full-text searches 4. Resource requirements for indexing and search 5. Queries over text as well as other fields