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.

.. code:: sql

      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.

.. code:: sql

      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.

.. code:: sql

      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:

.. code:: sql

      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.

.. code:: sql

      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 :ref:`Handling embedding service outages <Handling embedding service outages>` 
for more.

--------------

Next steps
----------

- :ref:`Writing hybrid search queries <Writing hybrid search queries>`  — Use your FTS index in hybrid queries, including
  weighted linear fusion.

- :ref:`Handling embedding service outages <Handling embedding service outages>`  — Use native FTS as a fallback when the embedding
  service is unavailable.