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

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 data type is how Postgres breaks a string of text into tokens that are used for searching. The 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:

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:

'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:

SELECT
to_tsvector('english', 'The complete guide to cooking at home')
@@
to_tsquery('english', 'cooks');

Searching for “puppies” will return false:

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. It accepts a tsvector and tsquery and returns a non-negative floating point number.

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:

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.

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 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:

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:

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:

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:

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:

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.

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:

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.

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:

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.

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.