Hi there!

I’ll try to explain the process of building a quick and dirty full text search using Sequelize V4 as ORM, and Postgres as the database.
For this, we will rely on the magic of the trigram plugin by Postgres.

Why trigrams?

Trigrams allow a “fuzzier” search. Most full-text search engines rely on inverted indexes, which create an index of documents per word. Explaining those is out of the scope of this post and better explained over here, or here.

A common inverted index looks something like this:

IDtextdocuments
2jellydoc_4, doc_5
1helldoc_3
0hellodoc_1, doc_2

Now searching for all documents containing the word “jelly” becomes very easy. This works great for searching documents containing large amounts of text, say all books containing the word “Database”. For shorter texts, like names, we are more interested in matching partial words, like 3 letters of them(trigrams, duh). For our example, if I search for “hell” I want to see the documents for both hello and hell. This is where trigrams kick in. Instead of indexing by word, we will now index by partial word. It would look somewhat like this:

IDtextdocuments
4llydoc_4, doc_5
3jeldoc_4, doc_5
2llodoc_1, doc_2
1elldoc_1, doc_2, doc_3, doc_4, doc_5
0heldoc_1, doc_2, doc_3

Now we can easily get all the documents that contain a partial match. The way searches are conducted is more complex than this, but we can get an idea of the benefits of using trigrams instead of full words.

 

Installing the plugin and creating Indexes

First wee need to create a migration that installs the plugin. Yes, we can install plugins using a sequelize migration, how cool is that?

 

After that, we just need to create the index for the fields we are interested to search in:

 

There are two types of index we can build. GiST and GIN. There are some fundamental differences between them which are well documented on the Postgres Docs.

For most common cases, GIN indexes should work fine.

After creating these indexes, we can query using the LIKE operator just like we would do without them, or in Sequelize syntax:

Have any comments or questions?, leave them below.

 

One Reply to “Full Text Search on Sequelize with Postgres

  1. Thanks for youг mɑrvelous posting! I quite enjoyed гeading it, you happen to be a great author.I will be
    sure to boⲟkmark your blog and will often come back
    later in life. I want to encoᥙrage you continue yοur great job, have a nice evening!

Leave a Reply

Your email address will not be published. Required fields are marked *