Postgres Full Text Search
Tags: rails, postgres, tutorial, database
Published: 2023-01-31 21:28:28

## Why you might not need Elasticsearch

This blog post is another in my series “Yeah, Postgres can do that”, (check out my other posts on [enum types](https://www.thegnar.com/blog/enum-types-in-postgres), and [history tracking](https://www.thegnar.com/blog/history-tracking-with-postgres)). Postgres is a humble workhorse with powerful features waiting to be unlocked.

If you’re a web developer and you want to add a search bar to your application, your first instinct might be to reach for a standalone service such as [Elasticsearch](https://en.wikipedia.org/wiki/Elasticsearch) or [Solr](https://en.wikipedia.org/wiki/Apache_Solr). These tools are impressive, but they require considerable time to set up and maintain. Your platform engineers will need to manage yet another service, and you will need to write and test the logic to synchronize the search engine with your database. None of this is trivial.

Postgres has the tools to host a search engine inside your database which automatically stays in sync with your underlying data. In this article I’m going to walk through the basics of how Postgres implements its full text search. Then I’ll cover how to integrate it into your Rails app.

## Writing Postgres Search Queries

Before we implement full text search in our Rails app, let’s see how Postgres does it under the hood.  Let’s imagine that we have a website with blog posts, where the body field contains the contents of the article, and we’re looking for all blog posts related to 'cooking’.

### Basic Search Queries

```sql
SELECT id, author_id, title, body

FROM blog_posts

WHERE to_tsvector('english', body) @@ to_tsquery('english', 'cooking');
```

This sort of syntax is likely unfamiliar to you. The [`tsvector`](https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSVECTOR) data type is how Postgres breaks a string of text into tokens that are used for searching. The [`tsquery`](https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY) is how it searches against those tokens.  The `@@` operator returns a boolean. Either the query matches or it doesn’t.

I encourage you to connect to a `psql` session (or equivalently `rails dbconsole`) and play around with these values yourself.

Try entering this in your `psql` terminal:

```sql
SELECT to_tsvector('english', 'The complete guide to cooking at home');
```

This function `to_tsvector` takes a string and converts it into a built-in Postgres data type called `tsvector`. This is what you’ll get back: 

```sql
'complet':2 'cook':5 'guid':3 'home':7
```

The words are broken into tokens, and each token contains a list of numbers. Those are the positions where the original words existed. “complete” is stored as the token “complet” and it’s the second word, hence the `:2`. “Cooking” is stored as the token “cook” and is the fifth word, hence the `:5`. Common words such as “the”, “to”, and “at” are what are called “stop words”, because they don’t generally help a user find what they’re looking for, so they’re ignored.

We can now search for the term "cooking” within our new `ts_vector` type. The following will return true:

```sql
SELECT

to_tsvector('english', 'The complete guide to cooking at home')

@@

to_tsquery('english', 'cooks');
```

Searching for “puppies” will return false:

```sql
SELECT

to_tsvector('english', 'The complete guide to cooking at home')

@@

to_tsquery('english', 'puppies');

```

This solution alone is more robust than any sort of regular expression-based text matching.

But it only gets us part of the way there. A binary search result of `TRUE`/`FALSE` isn’t all that useful. What we really want to be able to do is to rank the search results.


### Ranking Search Results

To rank search results, Postgres provides the function [`ts_rank`](https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING). It accepts a `tsvector` and `tsquery` and returns a non-negative floating point number.

```sql
SELECT ts_rank(
  to_tsvector('english', 'The complete cook''s guide to cooking at home'),
  to_tsquery('english', 'cooks')
);
``` 

So if we want to order our search results from most relevant to least relevant, we’ll simply order by the `ts_rank` from highest to lowest. Let’s go back to our blog posts example:

```sql
SELECT id, author_id, title, body
FROM blog_posts
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'cooking')
ORDER BY ts_rank(to_tsvector('english', body), to_tsquery(‘english’, ‘cooking’)) DESC;
```
This all works, but not quickly. All the data is tokenized and scanned at the time the query is executed, which can have performance implications. Our next enhancement is to use database indexes.

### Indexing Search Results

We can add an index to just about any field in Postgres, and we can add an index to any `FUNCTION` in Postgres as well.

```sql
CREATE INDEX idx_blog_posts_search
ON blog_posts USING GIN

((to_tsvector('english', body));
```

FYI: The name of the index is arbitrary. Just make sure that it doesn’t collide with anything else you’ve defined in your Postgres database.

#### GIN Indexes:

[GIN index](https://www.postgresql.org/docs/current/gin-intro.html) stands for “Generalized Inverted Index”. If you’re wondering how it’s implemented, think about the index at the end of your textbook from school. There’s an alphabetical listing of all the terms. When you find the term you’re looking for, it lists all the pages where that term is mentioned. Because it’s alphabetized, you can find the term relatively quickly, even if the number of possible terms grows large.

The GIN index is implemented using a btree, which is the same data structure used for most regular indexes. The difference is that a standard btree is one to one. Each record has one node in the btree index. Each node in the btree index maps back to a single record. With a GIN index, if the record breaks into 7 tokens, the record will show up in 7 different places on the GIN index, and each node in the GIN index can point back to multiple records.

#### Indexes on a Function:

The output of any Postgres function can be indexed, and that’s what we do here:

```sql
to_tsvector('english', body)
```

The function above accepts a single field on a table and outputs a vector result.

### Multicolumn Searching

Sometimes we may want to search for content in multiple fields. Let’s say we also want to be able to search by the title of the blog post. We’d simply concatenate the title and body fields together. This is done by the `||` operator. Let’s take a look:

``` sql
SELECT 'hello' || 'world';
```

This will display `'helloworld'` in your `psql` console. Along with using string literals, we can also concatenate two fields together. Let’s go back to our `blog_posts` table and search by title in addition to body:

``` sql
SELECT id, author_id, title, body
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'cooking')
ORDER BY ts_rank(to_tsvector('english', title || ' ' || body), to_tsquery(‘english’, ‘cooking’)) DESC;
```

#### Concatenating with `COALESCE`

Using the statements above, if any one item being concatenated is `NULL`, the entire concatenation will return `NULL`. This is a disaster for full text search because it means that a single `NULL` value means that the record won’t be found in the search bar. This is where the `COALESCE` function comes into play. We have to make sure that we handle every field that could possibly be `NULL`:

```sql
SELECT id, author_id, title, body \
FROM blog_posts \
WHERE to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')) @@ to_tsquery('english', 'cooking') \
ORDER BY ts_rank(to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')), to_tsquery(‘english’, ‘cooking’)) DESC;
```

Now we are covered if any field happens to be `NULL`. In this example we’d probably want every blog post to have a title and a body, but imagine that we’re searching for authors based on first, middle and last name, and not all authors have specified their middle names.

If you’re thinking this SQL query is looking unwieldy, don’t worry. We’re about to fix that. The first step is to store `to_tsquery(‘english’, ‘cooking’))` in its own variable:

```sql
SELECT id, author_id, title, body
FROM blog_posts, to_tsquery('english', 'cooking') as q
WHERE to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')) @@ q
ORDER BY ts_rank(to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')), q) DESC;
```

So we’ve gotten rid of one source of duplication. Now let’s see how we can DRY (Don’t Repeat Yourself) up the `to_tsvector` function.

#### Using Postgres Computed Columns

In the previous example we DRY’d up the `tsquery` variable. But, we have to concatenate the searchable fields together twice, the first is in the `WHERE` clause, the second is in the `ORDER BY` clause.

```sql
SELECT id, author_id, title, body
FROM blog_posts, to_tsquery('english', 'cooking') as q
WHERE to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')) @@ q
ORDER BY ts_rank(to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')), q) DESC;
```

This doesn’t even mention the fact that we need to create a GIN index for this concatenated string:

```sql
DROP INDEX IF EXISTS idx_blog_posts_search ON blog_posts;

CREATE INDEX idx_blog_posts_search
ON blog_posts USING GIN

((to_tsvector('english', COALESCE(title, '')  || ' ' || COALESCE(body, '')));
```

So we now have this very complicated, easy-to-make-a-mistake pattern in three separate places. We can fix this with a [Postgres generated column](https://www.postgresql.org/docs/current/ddl-generated-columns.html).

Generated columns are the Postgres equivalent of formulas in a spreadsheet. If I look at a cell on a spreadsheet, it looks the same to me whether it’s a hard-coded value or a formula. The same thing is true with generated columns. Let’s generate a column to make our blog posts searchable. In a database migration (or your `psql` terminal if you’re just experimenting), we'll need to execute the following code:

```sql
ALTER TABLE blog_posts ADD COLUMN search_index tsvector

GENERATED ALWAYS AS

(to_tsvector('english', (coalesce(title::text, '') || ' ' || coalesce(body::text, ''))))

STORED;

DROP INDEX IF EXISTS idx_blog_posts_search_index

CREATE INDEX idx_blog_posts_search_index ON blog_posts USING GIN(search_index);
```

The generated column is called `search_index`. (The name is arbitrary). It’s on the table `blog_posts`, and it’s of type `tsvector`. Then we create a GIN index on that generated column. Now when we search for blog posts, all we have to do is reference that generated column.

```sql
SELECT id, author_id, title, body
FROM blog_posts, to_tsquery('english', 'cooking') AS q
WHERE search_index @@ q
ORDER BY ts_rank(search_index, q) DESC;
```

So now we’ve played with the basics of Postgres full text search. There are other even more advanced topics about search which I’ll mention at the end of this post, but the most important thing to leave you with is how to integrate this into Rails.


## Rails Integration:

I don’t know of a perfect gem that will do this, so my preferred method is to use custom scopes on an Active Record model and to use a plain old database migration to manually update the database.

Let’s type into our rails console:

```ruby
rails generate migration AddSearchIndexToBlogPosts
```

Now we can open the latest migration file and paste in the following code:

```ruby
class AddSearchIndexToBlogPosts < ActiveRecord::Migration[6.1]
 def change
   reversible do |migrate|
     migrate.up do
       execute <<~SQL
         ALTER TABLE blog_posts ADD COLUMN search_index tsvector
         GENERATED ALWAYS AS (
           to_tsvector('english', (
             coalesce(title::text, '')
             || ' ' ||
             coalesce(body::text, ''))
           )
         ) STORED;

         CREATE INDEX idx_blog_posts_search_index

         ON blog_posts USING GIN(search_index);
       SQL
     end

     migrate.down do
       execute <<~SQL
         DROP INDEX idx_blog_posts_search_index;
         ALTER TABLE blog_posts DROP COLUMN search_index;
       SQL
     end
   end
 end
end
```

Now in our Rails model, we can reference this generated, indexed column using a [scope](https://www.rubyguides.com/2019/10/scopes-in-ruby-on-rails/). I’ll break down all the components in a second.

```ruby
class BlogPost < ApplicationRecord
 scope :search, -> (query_string) {
   to_query = sanitize_sql([
     "websearch_to_tsquery('simple', ?)",
     query_string
   ])

   self
     .where(Arel.sql("search_index @@ #{to_query}"))
     .order(Arel.sql("ts_rank(search_index, #{to_query}) DESC"))
 }
end
```

Postgres provides an excellent function [`websearch_to_tsquery`](https://www.postgresql.org/docs/current/textsearch-controls.html) which allows users to enter queries in roughly the same style that they would in a Google search bar. We still have to make sure that the user’s input is first sanitized for Active Record, which is where `sanitize_sql` comes into play. This immunizes us against [SQL injections](https://en.wikipedia.org/wiki/SQL_injection).

Now that we’ve manually sanitized the user input, we can concatenate it worry-free. However, to keep Active Record from fussing at us, `Arel.sql` is a way to let Active Record know that we’re manually sanitizing everything and that it’s safe to use.


## Next Steps:

As powerful as this full text search example is, we've only scratched the surface. There are other topics such as weighting different search terms more heavily than others, being able to control your search queries more finely, and adding your own non-standard tokenizers, and accounting for spelling errors. I encourage you to check out [Postgres full text search documentation](https://www.postgresql.org/docs/current/textsearch.html), which I have already linked to heavily in this blog post.