Using trigrams for better searches in Postgres

Having used Elasticsearch in the past, I thought it was the best and easiest way to handle fuzzy searches. Today I discovered an extension for Postgres called “pg_trgm” that might prevent you from needing an Elastic instance after all. Postgres is actually very good at text searches using ILIKE, but they are optimized for terms that are left-anchored (eg. ILIKE 'term%' and not ILIKE '%erm%). Trigrams will work the same no matter where the match is in the column. In addition, it will give a weight to each match expressing how close it is.

CREATE EXTENSION pg_trgm;
CREATE INDEX names_last_name_idx ON names USING GIN(last_name gin_trgm_ops);

To see what the index looks like:

select show_trgm('resudek');
# {  r, re,dek,ek ,esu,res,sud,ude}

(these are the indexed trigrams!)

And to perform a search with weighting:

select last_name, similarity('dek', last_name) from names;
# last_name | similarity
# resudek   | 0.2
# rezutek   | 0.090909
# johnson   | 0

Load data from staging db to local db

Connect to remote server

$ psql
\c staging_server

Extract data from staging to your local filesystem

\copy (SELECT * from post where id=12) to posts.csv csv header
\copy (SELECT * from comments where post_id=12) to comments.csv csv header;

Connect to local server

\c my_app_dev

Import data

\copy posts from posts.csv DELIMITER ',' CSV header;
\copy comments from comments.csv DELIMITER ',' CSV header;

Done!

Handy when you need to extract only some rows from staging or prod to try something locally

Postgres Foreign Key checks permission denied

Foreign key checks are done as the owner as the target table, not as the user issuing the query.

This resulted in a permission error:

ProgrammingError: permission denied for schema example
LINE 1: SELECT 1 FROM ONLY "example"."table" x WHERE "id" OPERATOR(...
                           ^
QUERY:  SELECT 1 FROM ONLY "example"."table" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

When dumping from one environment to local for testing, be sure that the owner of the table has permissions on your local postgres. Since it’s local, just give the owner of the table superuser perms.

ALTER USER username WITH SUPERUSER;