{"id":47443,"date":"2013-08-28T17:29:03","date_gmt":"2013-08-28T14:29:03","guid":{"rendered":"https:\/\/www.altoros.com\/blog\/?p=47443"},"modified":"2022-01-03T14:18:35","modified_gmt":"2022-01-03T11:18:35","slug":"implementing-and-improving-postgresql-fulltext-search","status":"publish","type":"post","link":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/","title":{"rendered":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps"},"content":{"rendered":"<p>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\u2019s name, and comments. In addition, the article explains how to speed up search by eliminating excessive join queries that greatly slow down the system\u2019s performance.<\/p>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#Preparation\" >Preparation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#A_sample_app\" >A sample app<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#Improving_performance\" >Improving performance<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#Results\" >Results<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#Further_reading\" >Further reading<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#About_the_author\" >About the author<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"Preparation\"><\/span>Preparation<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>There already exist ready-made solutions for implementing PostgreSQL full-text search. We will be using <a href=\"https:\/\/github.com\/Casecommons\/pg_search\" rel=\"noopener noreferrer\" target=\"_blank\">this one<\/a>. So, you need to add the following line to your Gemfile.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">gem<\/span> <span class=\"s1\">'pg_search'<\/span><\/code><\/pre>\n<p>Then, add the following line to your <code style=\"color: black; background-color: #e6e6e6;\">application.rb<\/code>.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">config<\/span><span class=\"p\">.<\/span><span class=\"nf\">active_record<\/span><span class=\"p\">.<\/span><span class=\"nf\">schema_format<\/span> <span class=\"o\">=<\/span> <span class=\"ss\">:sql<\/span><\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"A_sample_app\"><\/span>A sample app<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>My sample app will have three models.<\/p>\n<ol>\n<li>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"k\">class<\/span> <span class=\"nc\">Article<\/span> <span class=\"o\">&lt;<\/span> <span class=\"no\">ActiveRecord<\/span><span class=\"o\">::<\/span><span class=\"no\">Base<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">belongs_to<\/span> <span class=\"ss\">:author<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">has_many<\/span> <span class=\"ss\">:comments<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">attr_accessible<\/span> <span class=\"ss\">:content<\/span><span class=\"p\">,<\/span> <span class=\"ss\">:title<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"k\">end<\/span><\/code><\/pre>\n<\/li>\n<li>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"k\">class<\/span> <span class=\"nc\">Author<\/span> <span class=\"o\">&lt;<\/span> <span class=\"no\">ActiveRecord<\/span><span class=\"o\">::<\/span><span class=\"no\">Base<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">attr_accessible<\/span> <span class=\"ss\">:name<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">has_many<\/span> <span class=\"ss\">:articles<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"k\">end<\/span><\/code><\/pre>\n<\/li>\n<li>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"k\">class<\/span> <span class=\"nc\">Comment<\/span> <span class=\"o\">&lt;<\/span> <span class=\"no\">ActiveRecord<\/span><span class=\"o\">::<\/span><span class=\"no\">Base<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">belongs_to<\/span> <span class=\"ss\">:article<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">attr_accessible<\/span> <span class=\"ss\">:content<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"k\">end<\/span><\/code><\/pre>\n<\/li>\n<\/ol>\n<p>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&#8217;s name. In addition, search will have other settings about which you can read at <a href=\"https:\/\/github.com\/Casecommons\/pg_search\" rel=\"noopener noreferrer\" target=\"_blank\">gem\u2019s page<\/a>. So, to enable search for articles you should add the followong to the <code style=\"color: black; background-color: #e6e6e6;\">article<\/code> model.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"kp\">include<\/span> <span class=\"no\">PgSearch<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\"><span class=\"n\">pg_search_scope<\/span> <span class=\"ss\">:search<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">                <span class=\"ss\">against: <\/span><span class=\"p\">[<\/span><span class=\"ss\">:title<\/span><span class=\"p\">,<\/span> <span class=\"ss\">:content<\/span><span class=\"p\">],<\/span><br data-jekyll-commonmark-ghpages=\"\">                <span class=\"ss\">associated_against: <\/span><span class=\"p\">{<\/span><br data-jekyll-commonmark-ghpages=\"\">                  <span class=\"ss\">author: :name<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">                  <span class=\"ss\">comments: :content<\/span><br data-jekyll-commonmark-ghpages=\"\">                <span class=\"p\">},<\/span><br data-jekyll-commonmark-ghpages=\"\">                <span class=\"ss\">using: <\/span><span class=\"p\">{<\/span><br data-jekyll-commonmark-ghpages=\"\">                  <span class=\"ss\">tsearch: <\/span><span class=\"p\">{<\/span><br data-jekyll-commonmark-ghpages=\"\">                    <span class=\"ss\">dictionary: <\/span><span class=\"s1\">'english'<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">                    <span class=\"ss\">any_word: <\/span><span class=\"kp\">true<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">                    <span class=\"ss\">prefix: <\/span><span class=\"kp\">true<\/span><br data-jekyll-commonmark-ghpages=\"\">                  <span class=\"p\">}<\/span><br data-jekyll-commonmark-ghpages=\"\">                <span class=\"p\">}<\/span><\/code><\/pre>\n<p>Then, you can perform search using the <code style=\"color: black; background-color: #e6e6e6;\">Article.search<\/code> method.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">irb<\/span><span class=\"p\">(<\/span><span class=\"n\">main<\/span><span class=\"p\">):<\/span><span class=\"mo\">011<\/span><span class=\"p\">:<\/span><span class=\"mi\">0<\/span><span class=\"o\">&gt;<\/span> <span class=\"no\">Article<\/span><span class=\"p\">.<\/span><span class=\"nf\">search<\/span><span class=\"p\">(<\/span><span class=\"s1\">'title'<\/span><span class=\"p\">)<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"o\">=&gt;<\/span> <span class=\"p\">[<\/span><span class=\"c1\">#&lt;Article id: 3, author_id: 1, title: \"title\", content: \"this is content\", created_at: \"2013-08-26 10:09:01\", updated_at: \"2013-08-27 07:00:18\"&gt;]<\/span><\/code><\/pre>\n<p>Hooray, it found an article by the word <b>title<\/b>! However, look at the following query.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"no\">Article<\/span> <span class=\"no\">Load<\/span> <span class=\"p\">(<\/span><span class=\"mf\">1.9<\/span><span class=\"n\">ms<\/span><span class=\"p\">)<\/span>  <span class=\"no\">SELECT<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"p\">.<\/span><span class=\"nf\">*<\/span><span class=\"p\">,<\/span> <span class=\"p\">((<\/span><span class=\"n\">ts_rank<\/span><span class=\"p\">((<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"title\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"content\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">pg_search_1eb533ea18bbbe0846ef24<\/span><span class=\"p\">.<\/span><span class=\"nf\">pg_search_a612c20e7f822205b5b540<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">pg_search_121ea89914a721445aee70<\/span><span class=\"p\">.<\/span><span class=\"nf\">pg_search_6e76a7a40d9cb3861e7fb2<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))),<\/span> <span class=\"p\">(<\/span><span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''' '<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">'title'<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">' '''<\/span><span class=\"p\">)),<\/span> <span class=\"mi\">0<\/span><span class=\"p\">)))<\/span> <span class=\"no\">AS<\/span> <span class=\"n\">pg_search_rank<\/span> <span class=\"no\">FROM<\/span> <span class=\"s2\">\"articles\"<\/span> <span class=\"no\">LEFT<\/span> <span class=\"no\">OUTER<\/span> <span class=\"no\">JOIN<\/span> <span class=\"p\">(<\/span><span class=\"no\">SELECT<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">AS<\/span> <span class=\"nb\">id<\/span><span class=\"p\">,<\/span> <span class=\"n\">string_agg<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"authors\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"name\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">' '<\/span><span class=\"p\">)<\/span> <span class=\"no\">AS<\/span> <span class=\"n\">pg_search_a612c20e7f822205b5b540<\/span> <span class=\"no\">FROM<\/span> <span class=\"s2\">\"articles\"<\/span> <span class=\"no\">INNER<\/span> <span class=\"no\">JOIN<\/span> <span class=\"s2\">\"authors\"<\/span> <span class=\"no\">ON<\/span> <span class=\"s2\">\"authors\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"o\">=<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"author_id\"<\/span> <span class=\"no\">GROUP<\/span> <span class=\"no\">BY<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span><span class=\"p\">)<\/span> <span class=\"n\">pg_search_1eb533ea18bbbe0846ef24<\/span> <span class=\"no\">ON<\/span> <span class=\"n\">pg_search_1eb533ea18bbbe0846ef24<\/span><span class=\"p\">.<\/span><span class=\"nf\">id<\/span> <span class=\"o\">=<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">LEFT<\/span> <span class=\"no\">OUTER<\/span> <span class=\"no\">JOIN<\/span> <span class=\"p\">(<\/span><span class=\"no\">SELECT<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">AS<\/span> <span class=\"nb\">id<\/span><span class=\"p\">,<\/span> <span class=\"n\">string_agg<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"comments\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"content\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">' '<\/span><span class=\"p\">)<\/span> <span class=\"no\">AS<\/span> <span class=\"n\">pg_search_6e76a7a40d9cb3861e7fb2<\/span> <span class=\"no\">FROM<\/span> <span class=\"s2\">\"articles\"<\/span> <span class=\"no\">INNER<\/span> <span class=\"no\">JOIN<\/span> <span class=\"s2\">\"comments\"<\/span> <span class=\"no\">ON<\/span> <span class=\"s2\">\"comments\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"article_id\"<\/span> <span class=\"o\">=<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">GROUP<\/span> <span class=\"no\">BY<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span><span class=\"p\">)<\/span> <span class=\"n\">pg_search_121ea89914a721445aee70<\/span> <span class=\"no\">ON<\/span> <span class=\"n\">pg_search_121ea89914a721445aee70<\/span><span class=\"p\">.<\/span><span class=\"nf\">id<\/span> <span class=\"o\">=<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">WHERE<\/span> <span class=\"p\">(((<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"title\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"content\"<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">pg_search_1eb533ea18bbbe0846ef24<\/span><span class=\"p\">.<\/span><span class=\"nf\">pg_search_a612c20e7f822205b5b540<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"o\">||<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">pg_search_121ea89914a721445aee70<\/span><span class=\"p\">.<\/span><span class=\"nf\">pg_search_6e76a7a40d9cb3861e7fb2<\/span><span class=\"o\">::<\/span><span class=\"n\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)))<\/span> <span class=\"err\">@@<\/span> <span class=\"p\">(<\/span><span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'simple'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''' '<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">'title'<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">' '''<\/span><span class=\"p\">))))<\/span> <span class=\"no\">ORDER<\/span> <span class=\"no\">BY<\/span> <span class=\"n\">pg_search_rank<\/span> <span class=\"no\">DESC<\/span><span class=\"p\">,<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">ASC<\/span><\/code><\/pre>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Improving_performance\"><\/span>Improving performance<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>I am going to add a new column to the tables with articles. The new column will have the<br \/>\n<a href=\"https:\/\/www.postgresql.org\/docs\/8.3\/datatype-textsearch.html\" rel=\"noopener noreferrer\" target=\"_blank\">tsvector<\/a> type and store all the words by which an article can be searched. I will also add the <a href=\"https:\/\/www.postgresql.org\/docs\/9.1\/textsearch-indexes.html\" rel=\"noopener noreferrer\" target=\"_blank\">GIN index<\/a> to speed up search. So, we need to generate migration.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"k\">class<\/span> <span class=\"nc\">AddTsvectorColumnToArticles<\/span> <span class=\"o\">&lt;<\/span> <span class=\"no\">ActiveRecord<\/span><span class=\"o\">::<\/span><span class=\"no\">Migration<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">def<\/span> <span class=\"nf\">up<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">add_column<\/span> <span class=\"ss\">:articles<\/span><span class=\"p\">,<\/span> <span class=\"ss\">:search_vector<\/span><span class=\"p\">,<\/span> <span class=\"ss\">:tsvector<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">execute<\/span> <span class=\"o\">&lt;&lt;-<\/span><span class=\"no\">EOS<\/span><span class=\"sh\"><br data-jekyll-commonmark-ghpages=\"\">      CREATE INDEX articles_search_vector_idx ON articles USING gin(search_vector);<br data-jekyll-commonmark-ghpages=\"\"><\/span><span class=\"no\">    EOS<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">end<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">def<\/span> <span class=\"nf\">down<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">remove_column<\/span> <span class=\"ss\">:articles<\/span><span class=\"p\">,<\/span> <span class=\"ss\">:search_vector<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">end<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"k\">end<\/span><\/code><\/pre>\n<p>I\u2019ve added the <code style=\"color: black; background-color: #e6e6e6;\">tsvector<\/code> column to the articles named <code style=\"color: black; background-color: #e6e6e6;\">search_vector<\/code>. Now, we need to fill up this vector. For the purpose, I am going to write the <code style=\"color: black; background-color: #e6e6e6;\">postgresql<\/code> 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.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"k\">class<\/span> <span class=\"nc\">CreateFunctionAndTriggerForFillingSearchVectorOfArticles<\/span> <span class=\"o\">&lt;<\/span> <span class=\"no\">ActiveRecord<\/span><span class=\"o\">::<\/span><span class=\"no\">Migration<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">def<\/span> <span class=\"nf\">up<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">execute<\/span> <span class=\"o\">&lt;&lt;-<\/span><span class=\"no\">EOS<\/span><span class=\"sh\"><br data-jekyll-commonmark-ghpages=\"\">      CREATE OR REPLACE FUNCTION fill_search_vector_for_acticle() RETURNS trigger LANGUAGE plpgsql AS $$<br data-jekyll-commonmark-ghpages=\"\">      declare<br data-jekyll-commonmark-ghpages=\"\">        article_author record;<br data-jekyll-commonmark-ghpages=\"\">        article_comments record;<br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">      begin<br data-jekyll-commonmark-ghpages=\"\">        select name into article_author from authors where id = new.author_id;<br data-jekyll-commonmark-ghpages=\"\">        select string_agg(content, ' ') as content into article_comments from comments where article_id = new.id;<br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">        new.search_vector :=<br data-jekyll-commonmark-ghpages=\"\">          setweight(to_tsvector('pg_catalog.english', coalesce(new.title, '')), 'A')                  ||<br data-jekyll-commonmark-ghpages=\"\">          setweight(to_tsvector('pg_catalog.english', coalesce(new.content, '')), 'B')                ||<br data-jekyll-commonmark-ghpages=\"\">          setweight(to_tsvector('pg_catalog.english', coalesce(article_author.name, '')), 'B')        ||<br data-jekyll-commonmark-ghpages=\"\">          setweight(to_tsvector('pg_catalog.english', coalesce(article_comments.content, '')), 'B');<br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">        return new;<br data-jekyll-commonmark-ghpages=\"\">      end<br data-jekyll-commonmark-ghpages=\"\">      $$;<br data-jekyll-commonmark-ghpages=\"\"><\/span><span class=\"no\">    EOS<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">execute<\/span> <span class=\"o\">&lt;&lt;-<\/span><span class=\"no\">EOS<\/span><span class=\"sh\"><br data-jekyll-commonmark-ghpages=\"\">      CREATE TRIGGER articles_search_content_trigger BEFORE INSERT OR UPDATE<br data-jekyll-commonmark-ghpages=\"\">        ON articles FOR EACH ROW EXECUTE PROCEDURE fill_search_vector_for_acticle();<br data-jekyll-commonmark-ghpages=\"\"><\/span><span class=\"no\">    EOS<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"no\">Article<\/span><span class=\"p\">.<\/span><span class=\"nf\">find_each<\/span><span class=\"p\">(<\/span><span class=\"o\">&amp;<\/span><span class=\"ss\">:touch<\/span><span class=\"p\">)<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">end<\/span><br data-jekyll-commonmark-ghpages=\"\"><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">def<\/span> <span class=\"nf\">down<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"n\">execute<\/span> <span class=\"o\">&lt;&lt;-<\/span><span class=\"no\">EOS<\/span><span class=\"sh\"><br data-jekyll-commonmark-ghpages=\"\">      DROP FUNCTION fill_search_vector_for_acticle();<br data-jekyll-commonmark-ghpages=\"\">      DROP TRIGGER articles_search_content_trigger ON articles;<br data-jekyll-commonmark-ghpages=\"\"><\/span><span class=\"no\">    EOS<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"k\">end<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"k\">end<\/span><\/code><\/pre>\n<p>Each time, when an article is created or updated, a new vector will be built for the article. Look at the following example.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"nb\">select<\/span> <span class=\"nb\">name<\/span> <span class=\"n\">into<\/span> <span class=\"n\">article_author<\/span> <span class=\"n\">from<\/span> <span class=\"n\">authors<\/span> <span class=\"n\">where<\/span> <span class=\"nb\">id<\/span> <span class=\"o\">=<\/span> <span class=\"n\">new<\/span><span class=\"p\">.<\/span><span class=\"nf\">author_id<\/span><span class=\"p\">;<\/span><br data-jekyll-commonmark-ghpages=\"\">        <span class=\"nb\">select<\/span> <span class=\"n\">string_agg<\/span><span class=\"p\">(<\/span><span class=\"n\">content<\/span><span class=\"p\">,<\/span> <span class=\"s1\">' '<\/span><span class=\"p\">)<\/span> <span class=\"n\">as<\/span> <span class=\"n\">content<\/span> <span class=\"n\">into<\/span> <span class=\"n\">article_comments<\/span> <span class=\"n\">from<\/span> <span class=\"n\">comments<\/span> <span class=\"n\">where<\/span> <span class=\"n\">article_id<\/span> <span class=\"o\">=<\/span> <span class=\"n\">new<\/span><span class=\"p\">.<\/span><span class=\"nf\">id<\/span><span class=\"p\">;<\/span><\/code><\/pre>\n<p>I am fetching a name from an author and concatenated content of an article. Then, I create a new vector.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">new<\/span><span class=\"p\">.<\/span><span class=\"nf\">search_vector<\/span> <span class=\"p\">:<\/span><span class=\"o\">=<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">setweight<\/span><span class=\"p\">(<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'pg_catalog.english'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">new<\/span><span class=\"p\">.<\/span><span class=\"nf\">title<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)),<\/span> <span class=\"s1\">'A'<\/span><span class=\"p\">)<\/span>                  <span class=\"o\">||<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">setweight<\/span><span class=\"p\">(<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'pg_catalog.english'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">new<\/span><span class=\"p\">.<\/span><span class=\"nf\">content<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)),<\/span> <span class=\"s1\">'B'<\/span><span class=\"p\">)<\/span>                <span class=\"o\">||<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">setweight<\/span><span class=\"p\">(<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'pg_catalog.english'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">article_author<\/span><span class=\"p\">.<\/span><span class=\"nf\">name<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)),<\/span> <span class=\"s1\">'B'<\/span><span class=\"p\">)<\/span>        <span class=\"o\">||<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"n\">setweight<\/span><span class=\"p\">(<\/span><span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'pg_catalog.english'<\/span><span class=\"p\">,<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"n\">article_comments<\/span><span class=\"p\">.<\/span><span class=\"nf\">content<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)),<\/span> <span class=\"s1\">'B'<\/span><span class=\"p\">);<\/span><\/code><\/pre>\n<p>More details about syntax of each function can be found in <a href=\"https:\/\/www.postgresql.org\/docs\/\" rel=\"noopener noreferrer\" target=\"_blank\">this documentation<\/a>.<\/p>\n<p>We also need to update an article each time when a comment is updated. For this purpose, we will add <code style=\"color: black; background-color: #e6e6e6;\">touch true<\/code> for association at the <code style=\"color: black; background-color: #e6e6e6;\">comments<\/code> model.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">belongs_to<\/span> <span class=\"ss\">:article<\/span><span class=\"p\">,<\/span> <span class=\"ss\">touch: <\/span><span class=\"kp\">true<\/span><\/code><\/pre>\n<p>Now, every time when comments are updated, an article will be updated <code style=\"color: black; background-color: #e6e6e6;\">true<\/code>. This will call a trigger that will update the articles&#8217; <code style=\"color: black; background-color: #e6e6e6;\">search_vector<\/code> column.<\/p>\n<p>The last step is to make search use the new <code style=\"color: black; background-color: #e6e6e6;\">search_vector<\/code> column. For the purpose, we need to change <code style=\"color: black; background-color: #e6e6e6;\">pg_search_scope<\/code> to the <code style=\"color: black; background-color: #e6e6e6;\">article<\/code> model.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">pg_search_scope<\/span> <span class=\"ss\">:search<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"ss\">against: :search_vector<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"ss\">using: <\/span><span class=\"p\">{<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"ss\">tsearch: <\/span><span class=\"p\">{<\/span><br data-jekyll-commonmark-ghpages=\"\">      <span class=\"ss\">dictionary: <\/span><span class=\"s1\">'english'<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">      <span class=\"ss\">any_word: <\/span><span class=\"kp\">true<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">      <span class=\"ss\">prefix: <\/span><span class=\"kp\">true<\/span><span class=\"p\">,<\/span><br data-jekyll-commonmark-ghpages=\"\">      <span class=\"ss\">tsvector_column: <\/span><span class=\"s1\">'search_vector'<\/span><br data-jekyll-commonmark-ghpages=\"\">    <span class=\"p\">}<\/span><br data-jekyll-commonmark-ghpages=\"\">  <span class=\"p\">}<\/span><\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Results\"><\/span>Results<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Now, our articles have the <code style=\"color: black; background-color: #e6e6e6;\">tsvector<\/code> column that stores searches by text.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"n\">irb<\/span><span class=\"p\">(<\/span><span class=\"n\">main<\/span><span class=\"p\">):<\/span><span class=\"mo\">051<\/span><span class=\"p\">:<\/span><span class=\"mi\">0<\/span><span class=\"o\">&gt;<\/span> <span class=\"no\">Article<\/span><span class=\"p\">.<\/span><span class=\"nf\">last<\/span><span class=\"p\">.<\/span><span class=\"nf\">search_vector<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"o\">=&gt;<\/span> <span class=\"s2\">\"'astraukh':6B 'comment':9B 'content':4B,10B 'pavel':5B 'titl':1A\"<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"n\">irb<\/span><span class=\"p\">(<\/span><span class=\"n\">main<\/span><span class=\"p\">):<\/span><span class=\"mo\">052<\/span><span class=\"p\">:<\/span><span class=\"mi\">0<\/span><span class=\"o\">&gt;<\/span> <span class=\"no\">Article<\/span><span class=\"p\">.<\/span><span class=\"nf\">search<\/span><span class=\"p\">(<\/span><span class=\"s1\">'title'<\/span><span class=\"p\">)<\/span><br data-jekyll-commonmark-ghpages=\"\"><span class=\"o\">=&gt;<\/span> <span class=\"p\">[<\/span><span class=\"c1\">#&lt;Article id: 3, author_id: 1, title: \"title\", content: \"this is content\", created_at: \"2013-08-27 08:05:55\", updated_at: \"2013-08-27 08:06:18\", search_vector: \"'astraukh':6B 'comment':9B 'content':4B,10B 'pavel'...\"&gt;]<\/span><\/code><\/pre>\n<p>A query looks like this.<\/p>\n<pre><code class=\"language-ruby\" data-lang=\"ruby\"><span class=\"no\">Article<\/span> <span class=\"no\">Load<\/span> <span class=\"p\">(<\/span><span class=\"mf\">0.4<\/span><span class=\"n\">ms<\/span><span class=\"p\">)<\/span>  <span class=\"no\">SELECT<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"p\">.<\/span><span class=\"nf\">*<\/span><span class=\"p\">,<\/span> <span class=\"p\">((<\/span><span class=\"n\">ts_rank<\/span><span class=\"p\">((<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"search_vector\"<\/span><span class=\"p\">),<\/span> <span class=\"p\">(<\/span><span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'english'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''' '<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">'title'<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">' '''<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">':*'<\/span><span class=\"p\">)),<\/span> <span class=\"mi\">0<\/span><span class=\"p\">)))<\/span> <span class=\"no\">AS<\/span> <span class=\"n\">pg_search_rank<\/span> <span class=\"no\">FROM<\/span> <span class=\"s2\">\"articles\"<\/span> <span class=\"no\">WHERE<\/span> <span class=\"p\">(((<\/span><span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"search_vector\"<\/span><span class=\"p\">)<\/span> <span class=\"err\">@@<\/span> <span class=\"p\">(<\/span><span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'english'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''' '<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">'title'<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">' '''<\/span> <span class=\"o\">||<\/span> <span class=\"s1\">':*'<\/span><span class=\"p\">))))<\/span> <span class=\"no\">ORDER<\/span> <span class=\"no\">BY<\/span> <span class=\"n\">pg_search_rank<\/span> <span class=\"no\">DESC<\/span><span class=\"p\">,<\/span> <span class=\"s2\">\"articles\"<\/span><span class=\"o\">.<\/span><span class=\"s2\">\"id\"<\/span> <span class=\"no\">ASC<\/span><\/code><\/pre>\n<p>As you see, the query doesn\u2019t 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.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Further_reading\"><\/span>Further reading<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/deploying-a-rails-5-app-with-mongodb-redis-and-carrierwave-to-ibm-bluemix\/\">Deploying a Rails 5 App with MongoDB, Redis, and CarrierWave to IBM Bluemix<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/accessing-an-external-database-storage-from-a-bluemix-based-ruby-application\/\">Accessing an External Database Storage from a Bluemix-Based Ruby App<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/using-the-postgresql-service-in-an-asp-net-app-on-ge-predix\/\">Using the PostgreSQL Service from an ASP.NET App on GE\u2019s Predix<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"About_the_author\"><\/span>About the author<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/08\/pavel-astraukh.png\" width=\"110\" class=\"alignright size-thumbnail wp-image-64162\" \/><\/p>\n<p><small><a href=\"https:\/\/www.linkedin.com\/in\/pavel-astraukh-8b210b55\/\" rel=\"noopener noreferrer\" target=\"_blank\">Pavel Astraukh<\/a> 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 <a href=\"https:\/\/github.com\/enotpoloskun\" rel=\"noopener noreferrer\" target=\"_blank\">GitHub<\/a>.<\/small><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here I will describe how to use postgresql fulltext search.<\/p>\n","protected":false},"author":34,"featured_media":47539,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":"","_links_to":"","_links_to_target":""},"categories":[214],"tags":[1000,895],"class_list":["post-47443","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-github","tag-research-and-development"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros<\/title>\n<meta name=\"description\" content=\"Learn how to enable a full-text search by a title, content, the author&#039;s name, and comments for a Ruby-on-Rails application.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros\" \/>\n<meta property=\"og:description\" content=\"Here I will describe how to use postgresql fulltext search.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/\" \/>\n<meta property=\"og:site_name\" content=\"Altoros\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-28T14:29:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-01-03T11:18:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png\" \/>\n\t<meta property=\"og:image:width\" content=\"640\" \/>\n\t<meta property=\"og:image:height\" content=\"360\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Alena Vasilenko\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alena Vasilenko\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/\"},\"author\":{\"name\":\"Alena Vasilenko\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/019e8147b835bc8f1b4abd8a4fa42c7f\"},\"headline\":\"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps\",\"datePublished\":\"2013-08-28T14:29:03+00:00\",\"dateModified\":\"2022-01-03T11:18:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/\"},\"wordCount\":600,\"commentCount\":10,\"image\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png\",\"keywords\":[\"GitHub\",\"Research and Development\"],\"articleSection\":[\"Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/\",\"name\":\"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png\",\"datePublished\":\"2013-08-28T14:29:03+00:00\",\"dateModified\":\"2022-01-03T11:18:35+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/019e8147b835bc8f1b4abd8a4fa42c7f\"},\"description\":\"Here I will describe how to use postgresql fulltext search.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png\",\"contentUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png\",\"width\":640,\"height\":360},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/implementing-and-improving-postgresql-fulltext-search\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/\",\"name\":\"Altoros\",\"description\":\"Insight\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/#\\\/schema\\\/person\\\/019e8147b835bc8f1b4abd8a4fa42c7f\",\"name\":\"Alena Vasilenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/06\\\/alena-vasilenko-author-e1561752194994-96x96.jpg\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/06\\\/alena-vasilenko-author-e1561752194994-96x96.jpg\",\"contentUrl\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/06\\\/alena-vasilenko-author-e1561752194994-96x96.jpg\",\"caption\":\"Alena Vasilenko\"},\"description\":\"Alena Vasilenko is Communications Manager at Altoros. She has proven track record of supporting R&amp;D engineers in their research activities on such topics as big data and cloud computing, translating the research results into easy-to-understand stories.\",\"url\":\"https:\\\/\\\/www.altoros.com\\\/blog\\\/author\\\/alena-vasilenko\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros","description":"Learn how to enable a full-text search by a title, content, the author's name, and comments for a Ruby-on-Rails application.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/","og_locale":"en_US","og_type":"article","og_title":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros","og_description":"Here I will describe how to use postgresql fulltext search.","og_url":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/","og_site_name":"Altoros","article_published_time":"2013-08-28T14:29:03+00:00","article_modified_time":"2022-01-03T11:18:35+00:00","og_image":[{"width":640,"height":360,"url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png","type":"image\/png"}],"author":"Alena Vasilenko","twitter_misc":{"Written by":"Alena Vasilenko","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#article","isPartOf":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/"},"author":{"name":"Alena Vasilenko","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/019e8147b835bc8f1b4abd8a4fa42c7f"},"headline":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps","datePublished":"2013-08-28T14:29:03+00:00","dateModified":"2022-01-03T11:18:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/"},"wordCount":600,"commentCount":10,"image":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#primaryimage"},"thumbnailUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png","keywords":["GitHub","Research and Development"],"articleSection":["Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/","url":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/","name":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps | Altoros","isPartOf":{"@id":"https:\/\/www.altoros.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#primaryimage"},"image":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#primaryimage"},"thumbnailUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png","datePublished":"2013-08-28T14:29:03+00:00","dateModified":"2022-01-03T11:18:35+00:00","author":{"@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/019e8147b835bc8f1b4abd8a4fa42c7f"},"description":"Here I will describe how to use postgresql fulltext search.","breadcrumb":{"@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#primaryimage","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2013\/09\/ow-to-add-postgresql-full-text-search-to-ruby-apps-and-optimize-its-performance.png","width":640,"height":360},{"@type":"BreadcrumbList","@id":"https:\/\/www.altoros.com\/blog\/implementing-and-improving-postgresql-fulltext-search\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.altoros.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Implementing and Improving PostgreSQL Full-Text Search in Ruby Apps"}]},{"@type":"WebSite","@id":"https:\/\/www.altoros.com\/blog\/#website","url":"https:\/\/www.altoros.com\/blog\/","name":"Altoros","description":"Insight","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.altoros.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/019e8147b835bc8f1b4abd8a4fa42c7f","name":"Alena Vasilenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/06\/alena-vasilenko-author-e1561752194994-96x96.jpg","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/06\/alena-vasilenko-author-e1561752194994-96x96.jpg","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/06\/alena-vasilenko-author-e1561752194994-96x96.jpg","caption":"Alena Vasilenko"},"description":"Alena Vasilenko is Communications Manager at Altoros. She has proven track record of supporting R&amp;D engineers in their research activities on such topics as big data and cloud computing, translating the research results into easy-to-understand stories.","url":"https:\/\/www.altoros.com\/blog\/author\/alena-vasilenko\/"}]}},"_links":{"self":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/47443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/users\/34"}],"replies":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/comments?post=47443"}],"version-history":[{"count":17,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/47443\/revisions"}],"predecessor-version":[{"id":65994,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/47443\/revisions\/65994"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media\/47539"}],"wp:attachment":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media?parent=47443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/categories?post=47443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/tags?post=47443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}