pgvector를 활용한 벡터 검색

벡터는 어디에 사용합니까?

pgvector 활성화

CREATE EXTENSION vector;

벡터 열

CREATE TABLE docs (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  embedding vector(1536)
);

-- 1536 is OpenAI text-embedding-3-small dimension; varies by model.

임베딩 삽입

INSERT INTO docs (title, body, embedding)
VALUES ('SQL Course', 'Learn SQL', '[0.013, -0.024, ...]');

거리 연산자

최근접 이웃 검색

SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM docs
ORDER BY embedding <=> $1
LIMIT 10;

IVFFlat 인덱스

CREATE INDEX docs_embedding_idx
  ON docs USING IVFFLAT (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Tune lists by row count: rule of thumb sqrt(N).
-- Set probes per query for accuracy:
SET ivfflat.probes = 10;

HNSW 인덱스(PG 16+)

CREATE INDEX docs_embedding_hnsw
  ON docs USING HNSW (embedding vector_cosine_ops);

SET hnsw.ef_search = 100;

필터링된 벡터 검색

SELECT id, title
FROM docs
WHERE language = 'en'
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 10;
-- For best perf, partial index on the filter.

RAG 패턴

-- 1. Embed user query
-- 2. SELECT top-N docs by cosine
-- 3. Send docs + query to LLM
-- 4. Return LLM's answer to user

하이브리드 검색

SELECT id, title,
  0.6 * (1 - (embedding <=> $1)) + 0.4 * ts_rank(search_doc, $2) AS score
FROM docs
WHERE embedding <=> $1 < 0.7
   OR search_doc @@ $2
ORDER BY score DESC LIMIT 10;

차원 수 선택

요약

빠른 확인