Postgres Full Text Search

postgres_full_text_search.md

Postgres Full Text Search

Do you really need Elastic Search?

• https://www.postgresql.org/docs/current/pgtrgm.html
• https://scoutapm.com/blog/how-to-make-text-searches-in-postgresql-faster-with-trigram-similarity
• https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/
• https://mazeez.dev/posts/pg-trgm-similarity-search-and-fast-like
• https://alexklibisz.com/2022/02/18/optimizing-postgres-trigram-search.html
• https://www.freecodecamp.org/news/fuzzy-string-matching-with-postgresql/
• https://pganalyze.com/blog/gin-index
• https://hasura.io/blog/full-text-search-with-hasura-graphql-api-postgres/
• https://www.crunchydata.com/blog/postgres-full-text-search-a-search-engine-in-a-database
• https://bigmachine.io/2022/06/12/creating-a-full-text-search-engine-in-postgresql-2022/
• https://supabase.com/docs/guides/database/full-text-search
• https://xata.io/blog/postgres-full-text-search-engine

postgres_full_text_search.sql

CREATE EXTENSION unaccent;

ALTER TABLE client
ADD COLUMN full_text_search VARCHAR;

-- Do an initial update and apply the UNACCENT function searchable content
UPDATE client
  SET full_text_search = tsvector_to_array(to_tsvector('english', UNACCENT(name || ' ' || COALESCE(notes, ' ') || ' ' || COALESCE(location_address, ' '))));

-- Create an AFTER INSERT OR UPDATE trigger (to maintain the column data for inserted/updated rows)
CREATE OR REPLACE FUNCTION client_full_text_search_refresh()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    	NEW.full_text_search := (tsvector_to_array(to_tsvector('english', UNACCENT(NEW.name || ' ' || COALESCE(NEW.notes, ' ') || ' ' || COALESCE(NEW.location_address, ' ')))));
    END IF;
RETURN NEW;
END;
$function$;

CREATE TRIGGER client_full_text_search_refresh
BEFORE INSERT OR UPDATE OF name, notes, location_address
ON public.client
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE client_full_text_search_refresh();

-- Add GIN index
CREATE INDEX client_search_gin_idx ON client USING GIN (full_text_search gin_trgm_ops);

-- Function (required for Hasura: https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/custom-functions.html)
CREATE FUNCTION search_client(search text)
    RETURNS SETOF client
    AS $$
    SELECT
        *
    FROM
        client
    WHERE
        -- full_text_search column already has UNACCENT applied to it, so we don't have to re-apply
        UNACCENT(search) <% full_text_search
    ORDER BY
        SIMILARITY (search, (full_text_search)) DESC;
$$
LANGUAGE sql
STABLE;