Why You Should Choose Your DBMS's Full Text Search Over a Dedicated Search Engine

At Sophilabs, we are used to seeing the following architecture: relational DBMS for data storage, structured queries and manipulation, and a separate search engine for search. However, many DBMS support full text search. PostgreSQL, our favorite one, supports full text search since at least version 8.3, released in 2008.

If your project already uses a DBMS and you are uncertain whether to use a dedicated search engine, today we will share 3 reasons why you should not.

1. Simplicity

Simple is better than complex.
— The Zen of Python

Okay, so this is an easy one. Why work with two different systems when you can just work with one? We are probably talking about systems which understand different languages, have different dependencies, and will of course consume resources on their own. Each dependency you add to your project is a liability for your team. If you can keep it simple, please do!

2. You won't need to sync your data between systems

There are only two hard things in Computer Science: cache invalidation and naming things.
— Phil Karlton

Keeping more than one copy of your data is hard. Unless you are in a special situation where you can keep your business data just in your DMBS and your searchable data just in your search engine system, you will have to maintain a copy of your searchable data in both systems. This opens a whole new spectrum of problems that you may need to deal with.

Using your DBMS for search will allow you to perform full text search on the last version of the data without the need for writing code to keep both systems in sync. No need to worry about searching old versions of documents, orphaned records, or complex ETL jobs.

3. More flexibility for complex queries

Almost everything we know about good software architecture has to do with making software easy to change.
— Mary Poppendieck

The last reason for using your DBMS for full text search may not be so obvious at first, but it will prove beneficial when implementing new features.

Now that all your data lives in one place, your full text search queries and your regular relational queries are written in the same language and are completely composable. If a new requirement comes in that involves doing a full text search on an entity and filtering by some specific attributes of a related entity, it's no more than a few extra joins on your query. In the past, this could mean that an entire new table of your database needed to be replicated in your search engine, that you would need to re-define the schema of your search engine data, or that you would need to move specific pieces of data between your systems (like ids) on each query.

There are very good reasons to leverage your DBMS to do full text search instead of adding a new component to the mix. This is by no means a comprehensive list, and I'm sure we can come up with a list of reasons to "choose a dedicated search engine over your DBMS" just as we came up with this one. It all boils down to knowing your requirements and knowing your tools, but we hope our experience can help you make the right choice. If still in doubt, revisit #1.