Full-text search using pg_textsearch

The pg_textsearch extension in Cloud SQL for PostgreSQL provides full-text search using the industry-standard BM25 (Best Matching 25) scoring algorithm to deliver highly accurate relevance scoring. The extension is an open-source project 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.

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. This adds pg_textsearch to the shared_preload_libraries.

  2. Install the pg_textsearch extension

      CREATE EXTENSION pg_textsearch;

  3. Verify the installation:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

Search using pg_textsearch

Suppose the following sample table has been filled with data:

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:

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:

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:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

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

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: