Altoros is a big data and Platform-as-a-Service specialist that provides system integration for IaaS/cloud providers, software companies, and information-driven enterprises. Areas of expertise include Cloud Foundry, Hadoop, and NoSQL solutions, as well as Microsoft .NET, Java, Ruby on Rails, and mobile technologies.
PostgreSQL is great relational database with a lot of features, and one of features I am going to talk about is a build-in fulltext search.
In this post I will describe the proccess of implemeting postgresql fulltext search to your rails application, and also will show you how to improve it’s performance.
There are already some ready solutions for implementing postgresql fulltext search. We will be using this one.
So you need to add this line to your gemfile:
Then, add this line to your application.rb
My sample app will have 3 models:
and search will be implemented for articles. It will be able to search article by its title and content, also by it’s comments content and author name. Also search will have other settings about which you can read at gem’s page. So, for adding search for Articles you should add this to article model:
And then you can perform search using Article.search method:
Hooray, it found Article by word ‘title’! But if you look at query:
you will see that it have several joins which is not good because it greatly slows performance. I will show you how to avoid this behavior and
improve search performance.
I am going to add new column to articles tables. New column will have
tsvector type and will store all words by which article can be searched.
Also I will add GIN index to speed up search. So we need to generate migration:
I’ve added tsvector column to articles named search_vector. Now we need somehow to fill up this vector. For this I am going to write postgresql function for filling up vector and trigger, which will fill up this vector on insert or update of article. So here it is migration for creating trigger and function.
Each time, when article is created or updated new vector will be built for artricle. As you can see here:
I am fetching name from author and concatenated content of article. And then create new vector:
More detaily about each syntax of each function you can read at documentation.
Also we need to update article, each time when comment is updated. For this we will add touch true for association at comments model.
Now, everytime when comments is updated, it’s article will be updated true, and this will call trigger which will update articles search_vector column.
And the last one step is that we need to to make searching to use new search_vector column. For this we need to change pg_search_scope for artcile model.
Now our articles have tsvector column which stores searched by text in it.
And query looks like:
As you see 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.