The [`pg_textsearch`](https://www.tigerdata.com/blog/pg-textsearch-bm25-full-text-search-postgres)
extension in Cloud SQL for PostgreSQL provides full-text search using the
industry-standard
[BM25 (Best Matching 25)](https://en.wikipedia.org/wiki/Okapi_BM25) scoring
algorithm to deliver highly accurate relevance scoring. The extension is an
[open-source project](https://github.com/timescale/pg_textsearch) available on
GitHub.

The `pg_textsearch` extension requires PostgreSQL 17 or later.

Because `pg_textsearch` supports inverse document frequency, term frequency
saturation, and document length normalization, it can produce more
relevant results than PostgreSQL's built-in `ts_rank` function. Also, because it
operates directly on standard PostgreSQL storage pages, you don't
have to install an external engine like Elasticsearch, or worry about
dual-cluster maintenance and data synchronization. Using `pg_textsearch`,
you can build robust, scalable, and highly relevant search experiences without
leaving the PostgreSQL ecosystem.

## Different ways to search

There are several options for text searching in Cloud SQL for PostgreSQL:

- **Exact matching** : With standard SQL search, you use the `LIKE` and `ILIKE`
  statements to scan for an exact sequence of characters. An example
  query would be, `ILIKE '%smart fitness watches%'`, which would find
  instances like:

  - "Explore our new *smart fitness watches* on sale."
  - "*Smart fitness watches* make great gifts."

  However, it would fail to find:
  - "Runners want a *smart* waterproof *fitness watch*."
  - "This *watch* is particularly *smart* about *fitness*."
- **Full text search** : Using the BM25 algorithm and `tsvector`, full text
  search breaks text into root words, ignores filter words, and
  scores relevance. It understands language rules, including plurals and
  grammar, and takes into account word frequency. A full text search for
  `smart AND fitness AND watches` would find the instances that the example
  exact matching query would miss, and also more complicated instances
  like these:

  - "A *smart watch* is perfect for your daily *fitness* routine."
  - "Not every *watch* is this *smart* when it comes to *fitness*."

  However, it would fail to find:
  - "An intelligent health-aware *watch* can help your exercise program."
  - "This intelligent exercise-tracking band is a bargain."
- **Semantic search** : Using AI models, semantic search converts text into
  vectors and measures similarity distance. It understands meaning, intent,
  context, synonyms, and related concepts. A semantic search for `smart
  fitness watches` would find the example instances that the other search
  methods would miss. It would understand that an "intelligent health-aware
  watch" is the same as a "smart fitness watch", and that an "intelligent
  exercize-tracking band" could also be relevant. It might mistakenly match
  a pedometer band if it didn't prioritize "watch" sufficiently.

## Install the `pg_textsearch` extension

Take the following steps to install and enable `pg_textsearch`:

1. Set the [`cloudsql.enable_pg_textsearch`]() flag to `on` as described
   in [Configure database flags](https://docs.cloud.google.com/sql/docs/postgres/flags.md.txt#config). This adds
   `pg_textsearch` to the `shared_preload_libraries`.

2. Install the `pg_textsearch` extension

   ```sql
     CREATE EXTENSION pg_textsearch;
   ```
3. Verify the installation:

   ```sql
     SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'
   ```

## Search using `pg_textsearch`

Suppose the following sample table has been filled with data:

```sql
CREATE TABLE documents (
    doc_id TEXT PRIMARY KEY,
    content TEXT,
    text_embedding vector(3072)
    GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED 
  );
```

Before using full text search, first create a BM25 index:

```sql
CREATE INDEX idx_docs_bm25
      ON documents
      USING bm25 (content)
      WITH (text_config = 'english');
```

You can then make a full text search query like this:

```sql
SELECT doc_id, content, content <@> 'database system'
      AS score FROM documents
      ORDER BY content <@> 'database system'
      ASC LIMIT 5;
```

## Perform hybrid searches using `pg_textsearch` and `vector`

You can use `pg_textsearch` together with the `vector` semantic search
extension to perform hybrid searches. Install the `vector` semantic search
extension like this:

```sql
CREATE EXTENSION IF NOT EXISTS vector CASCADE;
```

Make a hybrid semantic and full text search query like this:

```sql
WITH
  -- Semantic search results
  vector_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY text_embedding <=>
                             google_ml.embedding('gemini-embedding-001',
                                                 'database')::VECTOR ) AS rank
      FROM documents
      ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
                                                      'database')::VECTOR
    LIMIT 10
  ),
  -- Full text search results
  text_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
    FROM documents
    ORDER BY content <@> 'database' ASC
    LIMIT 10
  )
  -- RRF combining both semantic and full text search results
  SELECT
    COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
    COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
    COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
  FROM vector_search
    FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
  ORDER BY rrf_score DESC
  LIMIT 5;
```

## Flags for configuring the `pg_textsearch` extension

Use the following flags to configure `pg_textsearch` full text search:

- [`pg_textsearch.bulk_load_threshold`](https://github.com/timescale/pg_textsearch): Terms per transaction before auto-spill. Set to 0 to disable. Default: 100,000.
- [`pg_textsearch.compress_segments`](https://github.com/timescale/pg_textsearch): Compress posting blocks in new segments. Default: `on`.
- [`pg_textsearch.default_limit`](https://github.com/timescale/pg_textsearch): The maximum number of documents scored when no `LIMIT` clause is present. Default: 1,000.
- [`pg_textsearch.memtable_pages_threshold`](https://github.com/timescale/pg_textsearch): Number of pages to chain before auto-spill. Set to 0 to disable. Default: 64.
- [`pg_textsearch.segments_per_level`](https://github.com/timescale/pg_textsearch): Segments per level before automatic compaction occurs. Range: 2-64. Default: 8.