A practical look at PostgreSQL Text Search (part I)

This article is the first part of a serie of Case Study using Postgres as a (almost complete) full-text search engine, where I’ll firstly describe my motivations.

It has been a while I heard about PostgreSQL Text Search. I’m an assumed fan of Postgres, this so noted and powerful RDBMS.

Since the beginning of my career I’ve worked with web applications and search engines as well. I started with Lucene, a full-featured text search library, then later I took a look at Solr and ElasticSearch as well, which both are built on top of Lucene.

Every project I start from scratch, my first choice is usually a cool RDBMS — Postgres — and a cool full-text search engine — ElasticSearch. It doesn’t mean I never use the other tools, but at first glance I try to be lazy and pragmatic until the chosen tool become a problem.

The main reason I prefer ElasticSearch over Solr is that the former provides integration with a lot of tools, whereas Solr is closed to a likely “Java-world” only. About performance both are quite similar and both have a great support for clustering.

I won’t dig into details on wich tool is better, it’s not the purpose of this article. A good comparison you see here.

On the “Postgres full-text search is good enough!” article, I found a great briefly practical example where I could start exploring the text search on Postgres. As the author clarifies,

Search has became an important feature and we’ve seen a big increase in the popularity of tools like elasticsearch and SOLR which are both based on lucene. They are great tools but before going down the road of Weapons of Mass ~~Destruction~~ Search, maybe what you need is something a bit lighter which is simply good enough!

if we need a bit lighter full-text search engine, why not to take a look at Postgres? Well, I consider myself a Lazy Programmer, where I always do prefer, at first glance, solving problems by using tools that are already part of my current architecture. If I were using MySQL, for example, I would give a try to the MySQL text search. And so on.

The second part of this article shows that PostgreSQL can be used as a RDBMS and a full-text search engine in a not so lighter application. It’s all about a simple “City Directory” where users can search for cities around the world and cities nearby.

Originally published at unlessnot.com on November 2, 2014.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store