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
Code language: Bash (bash)

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;
Code language: SQL (Structured Query Language) (sql)

March 17, 2022

Importing data into PostgreSQL can be time consuming and painful – unless you toss the GUI tools and use scripts.

Importing a CSV Into PostgreSQL Like a Pro

April 17, 2020

This is part 3 of a series of posts I’m writing for Friendo, a web person who wants to get their hands a lot dirtier with Node and Postgres.

Postgres For Those Who Can’t Even, Part 3 – In The Real World

February 5, 2020

Getting up to speed with Postgres and Node can be daunting but in this post I’ll dive into how you can easily work with both – including JSON document storage… in Postgres!

Postgres For Those Who Can’t Even, Part 2 – Working with Node and JSON