Just launched! Get 30% off The Imposter's Frontend Accelerator Get It Now

Creating a Full Text Search Engine in PostgreSQL, 2022

a year ago | Postgres Videos
Writing a full text index in PostgreSQL is an art form. You need to know what your users are looking so you can build the right index AND you need to understand how they write their search terms. Thankfully, Postgres is here to help.

A few years ago I wrote about how to “fine tune” a full text index in PostgreSQL 12, but that was a few years ago and things have changed a bit. The current version of PostgreSQL is 14 and Postgres just keeps getting better and better.

In this video I show you how the process a DBA might take when creating a full text index in Postgres. It’s not enough to throw a tsvector field onto a table, create a trigger and call it a day. You have to know what your users are searching for and how they’re searching for it.

These days we have generated columns and don’t need triggers. We also have websearch_to_query instead of the old plainto_tsquery or it’s languagy big brother, phrase_to_tsquery.

We can use this power to do all we need without having to use a third-party system like Sphinx or Elastic (as good as they are).

Hope you enjoy the video!

The Code and data

If you want to play along you can download the data set here. It’s about 3Mb and is a single SQL file that contains the table definition and structure. To run it, unzip the file and pop it into a database:

createdb scifi
psql scifi < questions.sql

There’s a lot of code in the video, but the main bits are:

--add the search index
alter table questions
add search tsvector
generated always as (
  setweight(to_tsvector('simple',tags), 'A')  || ' ' ||
  setweight(to_tsvector('english',title), 'B') || ' ' ||
  setweight(to_tsvector('english',body), 'C') :: tsvector

) stored;

-- add the index
create index idx_search on questions using GIN(search);

-- the search query
select title, body,
  ts_rank(search, websearch_to_tsquery('english','vader tie fighter star-wars')) + 
  ts_rank(search, websearch_to_tsquery('simple','vader tie fighter star-wars')) as rank
from questions
where search @@ websearch_to_tsquery('english','vader tie fighter star-wars')
or search @@ websearch_to_tsquery('simple','vader tie fighter star-wars')
order by rank desc;

-- turning it into a function
create or replace function search_questions(term text) 
returns table(
  id int,
  title text,
  body text,
  rank real
)
as
$$

select id, title, body,
  ts_rank(search, websearch_to_tsquery('english',term)) + 
  ts_rank(search, websearch_to_tsquery('simple',term)) as rank
from questions
where search @@ websearch_to_tsquery('english',term)
or search @@ websearch_to_tsquery('simple',term)
order by rank desc;

$$ language SQL;

Learn Postgres Using Data from NASA's Cassini Mission

I wrote a fun database tutorial using data from NASA's Cassini Mission. You get to load up your database with actual data from Saturn: A Curious Moon

  • Real data from Cassini
  • Search for traces of alien life (really)

There's More...

The Imposter's Frontend Accelerator

JavaScript client frameworks are powerful and help you create an amazing experience for your end user. Unfortunately, learning how to use them sucks.

🤖 A Real World Approach to Playwright

Writing tests can be tricky, especially using a more complex tool like Playwright. I took some time and dug in over the last year and I'm happy I did.

What Is Your Yeet Threshold?

Solving problems is what we do, but sometimes the solution is to burn it all down and start again, learning from your mistakes. How do you make this choice?