Setting up native PostgreSQL FTS

Contents

Setting up native PostgreSQL FTS#

Configure your source table with a tsvector column, a GIN index, and an autoupdate trigger to use native PostgreSQL full-text search (FTS) as the keyword leg in hybrid search queries. No additional extensions are required — all steps below are SQL commands run in your database. The setup includes field weighting so title matches rank higher than body matches, and a trigger so the search column stays current as rows are inserted or updated.

  1. Add a tsvector column

Add a tsvector column to store the pre-processed text representation of each row. This is the column the FTS index operates on.

ALTER TABLE my_docs ADD COLUMN search_vector tsvector;

  1. Populate the column with weighted fields

Populate the column using weighted fields to control how different fields affect relevance. Use weight A (highest) through D (lowest) — assign higher weights to fields like title and summary, and lower weights to longer body fields.

UPDATE my_docs
SET search_vector =
    setweight(to_tsvector(english, coalesce(title,   )), A) ||
    setweight(to_tsvector(english, coalesce(summary, )), B) ||
    setweight(to_tsvector(english, coalesce(body,    )), C);

  1. Create a GIN index

Create a GIN (Generalized Inverted Index) on the search_vector column to enable fast full-text lookups. Without this index, FTS queries perform a sequential scan and won’t scale.

CREATE INDEX my_docs_fts_idx ON my_docs USING gin(search_vector);

  1. Create a trigger to keep the column current

Set up a trigger to tokenize new and updated rows automatically, so the FTS index stays in sync without manual updates:

CREATE OR REPLACE FUNCTION my_docs_search_vector_update()
RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector(english, coalesce(NEW.title,   )), A) ||
        setweight(to_tsvector(english, coalesce(NEW.summary, )), B) ||
        setweight(to_tsvector(english, coalesce(NEW.body,    )), C);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_docs_search_vector_trigger
    BEFORE INSERT OR UPDATE ON my_docs
    FOR EACH ROW EXECUTE FUNCTION my_docs_search_vector_update();

  1. Run a test query

Use plainto_tsquery for plain phrase matching, or websearch_to_tsquery for Google-style syntax ("exact phrase" , -exclude , OR ). Use whichever matches your application’s query input format.

SELECT id, title,
       ts_rank_cd(search_vector, query) AS score
FROM my_docs,
     plainto_tsquery(english, connection pooling) query
WHERE search_vector @@ query
ORDER BY score DESC
LIMIT 10;

For an AIDB pipeline running alongside a native FTS index, AIDB handles embedding generation and vector search. The FTS trigger and the pipeline operate independently, so new rows are automatically covered by both.

!!!note The trigger pattern above ensures FTS coverage is always current, even for rows that arrive before embeddings are generated. This makes native FTS a reliable fallback for new content. See Handling embedding service outages for more.

Next steps#

Contents