pg_textsearch를 사용한 전체 텍스트 검색

PostgreSQL용 Cloud SQL의 pg_textsearch 확장 프로그램은 업계 표준 BM25 (Best Matching 25) 점수 알고리즘을 사용하여 전체 텍스트 검색을 제공하여 매우 정확한 관련성 점수를 제공합니다. 이 확장 프로그램은 GitHub에서 사용할 수 있는 오픈소스 프로젝트입니다.

pg_textsearch 확장 프로그램에는 PostgreSQL 17 이상이 필요합니다.

pg_textsearch는 역 문서 빈도, 용어 빈도 포화, 문서 길이 정규화를 지원하므로 PostgreSQL의 기본 ts_rank 함수보다 관련성이 높은 결과를 생성할 수 있습니다. 또한 표준 PostgreSQL 스토리지 페이지에서 직접 작동하므로 Elasticsearch와 같은 외부 엔진을 설치하거나 이중 클러스터 유지관리 및 데이터 동기화에 대해 걱정할 필요가 없습니다. pg_textsearch를 사용하면 PostgreSQL 생태계를 벗어나지 않고도 강력하고 확장 가능하며 관련성이 높은 검색 환경을 구축할 수 있습니다.

PostgreSQL용 Cloud SQL에는 텍스트 검색을 위한 여러 옵션이 있습니다.

  • 정확한 일치: 표준 SQL 검색을 사용하면 LIKE 및 ILIKE 문을 사용하여 정확한 문자 시퀀스를 스캔할 수 있습니다. 예를 들어 ILIKE '%smart fitness watches%' 쿼리는 다음과 같은 인스턴스를 찾습니다.

    • '할인 중인 새로운 스마트 피트니스 시계를 살펴보세요.'
    • '스마트 피트니스 시계는 좋은 선물입니다.'

    하지만 다음은 찾지 못합니다.

    • 러너는 스마트 방수 피트니스 워치를 원합니다.
    • '이 시계는 특히 피트니스에 스마트합니다.'

  • 전체 텍스트 검색: BM25 알고리즘과 tsvector를 사용하여 전체 텍스트 검색은 텍스트를 어근으로 나누고, 필터 단어를 무시하고, 관련성을 점수화합니다. 복수형과 문법을 비롯한 언어 규칙을 이해하고 단어 빈도를 고려합니다. smart AND fitness AND watches에 대한 전체 텍스트 검색은 예시 정확한 일치 쿼리에서 누락되는 인스턴스와 다음과 같은 더 복잡한 인스턴스를 찾습니다.

    • '스마트워치는 일일 피트니스 루틴에 적합합니다.'
    • "피트니스에 관해서는 모든 워치가 이렇게 스마트하지는 않습니다."

    하지만 다음은 찾지 못합니다.

    • '지능형 건강 인식 워치는 운동 프로그램에 도움이 될 수 있습니다.'
    • '이 지능형 운동 추적 밴드는 저렴합니다.'

  • 시맨틱 검색: 시맨틱 검색은 AI 모델을 사용하여 텍스트를 벡터로 변환하고 유사성 거리를 측정합니다. 의미, 의도, 컨텍스트, 동의어, 관련 개념을 이해합니다. smart fitness watches에 대한 시맨틱 검색은 다른 검색 방법에서는 놓칠 수 있는 예시 인스턴스를 찾습니다. '지능형 건강 인식 시계'가 '피트니스 워치'와 동일하고 '지능형 운동 추적 밴드'도 관련이 있을 수 있음을 이해합니다. '시계'에 충분한 우선순위를 부여하지 않으면 만보계 밴드와 잘못 일치할 수 있습니다.

pg_textsearch 확장 프로그램 설치

다음 단계에 따라 pg_textsearch을 설치하고 사용 설정합니다.

  1. 데이터베이스 플래그 구성에 설명된 대로 cloudsql.enable_pg_textsearch 플래그를 on로 설정합니다. 이렇게 하면 pg_textsearch가 shared_preload_libraries에 추가됩니다.

  2. pg_textsearch 확장 프로그램 설치

      CREATE EXTENSION pg_textsearch;

  3. 설치를 확인합니다.

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

pg_textsearch를 사용하여 검색

다음 샘플 테이블이 데이터로 채워져 있다고 가정해 보겠습니다.

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

전체 텍스트 검색을 사용하기 전에 먼저 BM25 색인을 만드세요.

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

그런 다음 다음과 같은 전체 텍스트 검색 쿼리를 실행할 수 있습니다.

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

pg_textsearch 및 vector을 사용하여 하이브리드 검색 실행

pg_textsearch를 vector 시맨틱 검색 확장 프로그램과 함께 사용하여 하이브리드 검색을 실행할 수 있습니다. 다음과 같이 vector 시맨틱 검색 확장 프로그램을 설치합니다.

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

다음과 같은 하이브리드 시맨틱 및 전체 텍스트 검색 쿼리를 만듭니다.

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;

pg_textsearch 확장 프로그램을 구성하기 위한 플래그

다음 플래그를 사용하여 pg_textsearch 전체 텍스트 검색을 구성합니다.