Contents
1267
10 Comments

Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps

by Pavel AstraukhAugust 28, 2013
Learn how to enable a full-text search by a title, content, the author's name, and comments for a Ruby-on-Rails application.

PostgreSQL is a great relational database with a lot of features. This blog post describes how to implement PostgreSQL full-text search and add it to your Ruby-on-Rails app. I will demonstrate how it works on a test app that searches articles by a title, content, the author’s name, and comments. In addition, the article explains how to speed up search by eliminating excessive join queries that greatly slow down the system’s performance.

 

Preparation

There already exist ready-made solutions for implementing PostgreSQL full-text search. We will be using this one. So, you need to add the following line to your Gemfile.

Then, add the following line to your application.rb.

 

A sample app

My sample app will have three models.

With these models, search will be implemented for articles. It will become possible to search an article by its title, content, comments, and the author’s name. In addition, search will have other settings about which you can read at gem’s page. So, to enable search for articles you should add the followong to the article model.

Then, you can perform search using the Article.search method.

Hooray, it found an article by the word title! However, look at the following query.

Above, you can see that the query has several joins, which is not good, because it greatly slows performance. I will show you how to avoid this behavior and improve search performance.

 

Improving performance

I am going to add a new column to the tables with articles. The new column will have the
tsvector type and store all the words by which an article can be searched. I will also add the GIN index to speed up search. So, we need to generate migration.

I’ve added the tsvector column to the articles named search_vector. Now, we need to fill up this vector. For the purpose, I am going to write the postgresql function for filling up the vector and a trigger that will fill up this vector when inserting or updating an article. Below is an example of migration for creating a trigger and a function.

Each time, when an article is created or updated, a new vector will be built for the article. Look at the following example.

I am fetching a name from an author and concatenated content of an article. Then, I create a new vector.

More details about syntax of each function can be found in this documentation.

We also need to update an article each time when a comment is updated. For this purpose, we will add touch true for association at the comments model.

Now, every time when comments are updated, an article will be updated true. This will call a trigger that will update the articles’ search_vector column.

The last step is to make search use the new search_vector column. For the purpose, we need to change pg_search_scope to the article model.

 

Results

Now, our articles have the tsvector column that stores searches by text.

A query looks like this.

As you see, the query doesn’t have any joins, and this behavior greatly improves search performance. Now, you know how to implement and improve your search using PostgreSQL full-text search.

 

Further reading

 

About the author

Pavel Astraukh developed a number of projects using Ruby on Rails and other technologies. He worked in small and large teams utilizing the Agile methodology. Find him on GitHub.

   
Posted In
Tutorials
Tags
GitHub, Research and Development
,
Posted by:

Alena Vasilenko is Communications Manager at Altoros. She has proven track record of supporting R&D engineers in their research activities on such topics as big data and cloud computing, translating the research results into easy-to-understand stories.