Setting up native PostgreSQL FTS v7

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.

Use native FTS when you can't install VectorChord-BM25, or when your use case doesn't need BM25's probabilistic term weighting. Note that ts_rank scores are less calibrated than BM25 scores and don't normalize well across queries of different lengths — for large or heterogeneous corpora, BM25 gives more consistent relevance ranking.

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.

   → WrapCopy

   ALTER TABLE my_docs ADD COLUMN search_vector tsvector;

2. 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.

   → WrapCopy

   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');

3. 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.

   → WrapCopy

   CREATE INDEX my_docs_fts_idx ON my_docs USING gin(search_vector);

4. 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:

   → WrapCopy

   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();

5. 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.

   → WrapCopy

   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, new rows are automatically covered by both.

Next steps

• Query patterns — Use your FTS index in hybrid queries, including weighted linear fusion.
• Fallback and degradation — Use native FTS as a fallback when the embedding service is unavailable.