Postgres 벡터 스토어
Postgres 벡터 스토어
PostgreSQL과 pgvector를 LlamaIndex의 벡터 스토어로 사용하는 방법을 보여드릴게요. HNSW 인덱스 구성, 하이브리드 검색, 메타데이터 필터, 커스텀 쿼리까지 실제 노트북 코드로 차근차근 따라가 볼 수 있습니다. 벡터·텍스트 검색을 한 곳에서 다루는 데이터베이스를 원하신다면 이 조합이 아주 유용해요.
출처: 문서
본문
이 노트북에서는 Postgresql과 pgvector를 사용해 LlamaIndex에서 벡터 검색을 수행하는 방법을 보여드립니다.
Colab에서 노트북을 여신다면 LlamaIndex 🦙를 설치해야 할 수 있어요.
%pip install llama-index-vector-stores-postgres
!pip install llama-index
다음 셀을 실행하면 Colab에 PGVector가 포함된 Postgres가 설치됩니다.
!sudo apt update
!echo | sudo apt install -y postgresql-common
!echo | sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
!echo | sudo apt install postgresql-15-pgvector
!sudo service postgresql start
!sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'password';"
!sudo -u postgres psql -c "CREATE DATABASE vector_db;"
# import logging
# import sys
# Uncomment to see debug logs
# logging.basicConfig(stream=sys.stdout, level=logging.DEBUG)
# logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))
from llama_index.core import SimpleDirectoryReader, StorageContext
from llama_index.core import VectorStoreIndex
from llama_index.vector_stores.postgres import PGVectorStore
import textwrap
OpenAI 설정
첫 번째 단계는 OpenAI 키를 설정하는 거예요. 인덱스에 로드된 문서의 임베딩을 만드는 데 사용됩니다.
import os
os.environ["OPENAI_API_KEY"] = "sk-..."
데이터 다운로드
!mkdir -p 'data/paul_graham/'
!wget 'https://raw.githubusercontent.com/run-llama/llama_index/main/docs/examples/data/paul_graham/paul_graham_essay.txt' -O 'data/paul_graham/paul_graham_essay.txt'
문서 로드하기 (Loading documents)
SimpleDirectoryReader를 사용해 data/paul_graham/에 저장된 문서를 로드합니다.
documents = SimpleDirectoryReader("./data/paul_graham").load_data()
print("Document ID:", documents[0].doc_id)
Document ID: 56e70c8c-0fb7-4250-99be-b953d0185a01
데이터베이스 만들기
localhost에서 실행 중인 기존 postgres를 사용해 사용할 데이터베이스를 만듭니다.
import psycopg2
connection_string = "postgresql://postgres:***@localhost:5432"
db_name = "vector_db"
conn = psycopg2.connect(connection_string)
conn.autocommit = True
with conn.cursor() as c:
c.execute(f"DROP DATABASE IF EXISTS {db_name}")
c.execute(f"CREATE DATABASE {db_name}")
인덱스 만들기
여기서는 앞서 로드한 문서를 사용해 Postgres 백엔드의 인덱스를 만듭니다. PGVectorStore는 몇 가지 인자를 받아요. 아래 예시는 vector_cosine_ops 방식을 사용해 m = 16, ef_construction = 64, ef_search = 40인 HNSW 인덱스로 PGVectorStore를 구성합니다.
from sqlalchemy import make_url
url = make_url(connection_string)
vector_store = PGVectorStore.from_params(
database=db_name,
host=url.host,
password=url.password,
port=url.port,
user=url.username,
table_name="paul_graham_essay",
embed_dim=1536, # openai embedding dimension
hnsw_kwargs={
"hnsw_m": 16,
"hnsw_ef_construction": 64,
"hnsw_ef_search": 40,
"hnsw_dist_method": "vector_cosine_ops",
},
)
storage_context = StorageContext.from_defaults(vector_store=vector_store)
index = VectorStoreIndex.from_documents(
documents, storage_context=storage_context, show_progress=True
)
query_engine = index.as_query_engine()
Parsing nodes: 0%| | 0/1 [00:00<?, ?it/s]
Generating embeddings: 0%| | 0/22 [00:00<?, ?it/s]
2025-09-11 16:47:21,725 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
인덱스 질의하기
이제 인덱스를 사용해 질문할 수 있어요.
response = query_engine.query("What did the author do?")
2025-09-11 16:47:30,412 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
2025-09-11 16:47:31,665 - INFO - HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
print(textwrap.fill(str(response), 100))
The author worked on writing essays, programming, building microcomputers, predicting rocket
heights, developing a word processor, and giving talks on starting a startup.
response = query_engine.query("What happened in the mid 1980s?")
2025-09-11 16:47:37,531 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
2025-09-11 16:47:38,352 - INFO - HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
print(textwrap.fill(str(response), 100))
AI was in the air in the mid 1980s, and two things that influenced the desire to work on it were a
novel by Heinlein called The Moon is a Harsh Mistress, which featured an intelligent computer called
Mike, and a PBS documentary that showed Terry Winograd using SHRDLU.
기존 인덱스 질의하기
vector_store = PGVectorStore.from_params(
database="vector_db",
host="localhost",
password="password",
port=5432,
user="postgres",
table_name="paul_graham_essay",
embed_dim=1536, # openai embedding dimension
hnsw_kwargs={
"hnsw_m": 16,
"hnsw_ef_construction": 64,
"hnsw_ef_search": 40,
"hnsw_dist_method": "vector_cosine_ops",
},
)
index = VectorStoreIndex.from_vector_store(vector_store=vector_store)
query_engine = index.as_query_engine()
response = query_engine.query("What did the author do?")
print(textwrap.fill(str(response), 100))
The author worked on writing essays, programming, creating microcomputers, developing software,
giving talks, and starting a startup.
하이브리드 검색 (Hybrid Search)
하이브리드 검색을 활성화하려면 다음을 해야 해요:
PGVectorStore를 구성할 때hybrid_search=True를 넘긴다 (원하는 언어로text_search_config도 선택적으로 설정 가능)- 쿼리 엔진을 구성할 때
vector_store_query_mode="hybrid"를 넘긴다 (이 설정은 내부적으로 리트리버에 전달됨). 또한sparse_top_k를 선택적으로 설정해 희소 텍스트 검색에서 가져올 결과 수를 정할 수 있다 (기본값은similarity_top_k와 동일).
from sqlalchemy import make_url
url = make_url(connection_string)
hybrid_vector_store = PGVectorStore.from_params(
database=db_name,
host=url.host,
password=url.password,
port=url.port,
user=url.username,
table_name="paul_graham_essay_hybrid_search",
embed_dim=1536, # openai embedding dimension
hybrid_search=True,
text_search_config="english",
hnsw_kwargs={
"hnsw_m": 16,
"hnsw_ef_construction": 64,
"hnsw_ef_search": 40,
"hnsw_dist_method": "vector_cosine_ops",
},
)
storage_context = StorageContext.from_defaults(
vector_store=hybrid_vector_store
)
hybrid_index = VectorStoreIndex.from_documents(
documents, storage_context=storage_context
)
hybrid_query_engine = hybrid_index.as_query_engine(
vector_store_query_mode="hybrid", sparse_top_k=2
)
hybrid_response = hybrid_query_engine.query(
"Who does Paul Graham think of with the word schtick"
)
print(hybrid_response)
Roy Lichtenstein
QueryFusionRetriever로 하이브리드 검색 개선하기
텍스트 검색과 벡터 검색의 점수 계산 방식이 다르기 때문에, 텍스트 검색으로만 찾은 노드는 훨씬 낮은 점수를 가질 수 있어요.
QueryFusionRetriever를 사용하면 노드 순위를 정할 때 상호 정보(mutual information)를 더 잘 활용해 하이브리드 검색 성능을 자주 개선할 수 있어요.
from llama_index.core.response_synthesizers import CompactAndRefine
from llama_index.core.retrievers import QueryFusionRetriever
from llama_index.core.query_engine import RetrieverQueryEngine
vector_retriever = hybrid_index.as_retriever(
vector_store_query_mode="default",
similarity_top_k=5,
)
text_retriever = hybrid_index.as_retriever(
vector_store_query_mode="sparse",
similarity_top_k=5, # interchangeable with sparse_top_k in this context
)
retriever = QueryFusionRetriever(
[vector_retriever, text_retriever],
similarity_top_k=5,
num_queries=1, # set this to 1 to disable query generation
mode="relative_score",
use_async=False,
)
response_synthesizer = CompactAndRefine()
query_engine = RetrieverQueryEngine(
retriever=retriever,
response_synthesizer=response_synthesizer,
)
response = query_engine.query(
"Who does Paul Graham think of with the word schtick, and why?"
)
print(response)
Paul Graham thinks of Roy Lichtenstein when using the word "schtick" because Lichtenstein's distinctive signature style in his paintings immediately identifies his work as his own.
메타데이터 필터
PGVectorStore는 노드에 메타데이터를 저장하고, 검색 단계에서 그 메타데이터를 기준으로 필터링하는 것을 지원해요.
git 커밋 데이터셋 다운로드
!mkdir -p 'data/git_commits/'
!wget 'https://raw.githubusercontent.com/run-llama/llama_index/main/docs/examples/data/csv/commit_history.csv' -O 'data/git_commits/commit_history.csv'
import csv
with open("data/git_commits/commit_history.csv", "r") as f:
commits = list(csv.DictReader(f))
print(commits[0])
print(len(commits))
{'commit': '44e41c12ab25e36c202f58e068ced262eadc8d16', 'author': 'Lakshmi Narayanan Sreethar<[email protected]>', 'date': 'Tue Sep 5 21:03:21 2023 +0530', 'change summary': 'Fix segfault in set_integer_now_func', 'change details': 'When an invalid function oid is passed to set_integer_now_func, it finds out that the function oid is invalid but before throwing the error, it calls ReleaseSysCache on an invalid tuple causing a segfault. Fixed that by removing the invalid call to ReleaseSysCache. Fixes #6037 '}
4167
커스텀 메타데이터로 노드 추가하기
# Create TextNode for each of the first 100 commits
from llama_index.core.schema import TextNode
from datetime import datetime
import re
nodes = []
dates = set()
authors = set()
for commit in commits[:100]:
author_email = commit["author"].split("<")[1][:-1]
commit_date = datetime.strptime(
commit["date"], "%a %b %d %H:%M:%S %Y %z"
).strftime("%Y-%m-%d")
commit_text = commit["change summary"]
if commit["change details"]:
commit_text += "\n\n" + commit["change details"]
fixes = re.findall(r"#(\d+)", commit_text, re.IGNORECASE)
nodes.append(
TextNode(
text=commit_text,
metadata={
"commit_date": commit_date,
"author": author_email,
"fixes": fixes,
},
)
)
dates.add(commit_date)
authors.add(author_email)
print(nodes[0])
print(min(dates), "to", max(dates))
print(authors)
Node ID: 9c2c2f17-d763-4ce8-bb02-83cb176008e4
Text: Fix segfault in set_integer_now_func When an invalid function
oid is passed to set_integer_now_func, it finds out that the function
oid is invalid but before throwing the error, it calls ReleaseSysCache
on an invalid tuple causing a segfault. Fixed that by removing the
invalid call to ReleaseSysCache. Fixes #6037
2023-03-22 to 2023-09-05
{'[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]'}
vector_store = PGVectorStore.from_params(
database=db_name,
host=url.host,
password=url.password,
port=url.port,
user=url.username,
table_name="metadata_filter_demo3",
embed_dim=1536, # openai embedding dimension
hnsw_kwargs={
"hnsw_m": 16,
"hnsw_ef_construction": 64,
"hnsw_ef_search": 40,
"hnsw_dist_method": "vector_cosine_ops",
},
)
index = VectorStoreIndex.from_vector_store(vector_store=vector_store)
index.insert_nodes(nodes)
2025-09-11 16:48:11,383 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
print(index.as_query_engine().query("How did Lakshmi fix the segfault?"))
2025-09-11 16:48:15,149 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
2025-09-11 16:48:15,687 - INFO - HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
Lakshmi fixed the segfault by removing the invalid call to ReleaseSysCache that was causing the issue.
메타데이터 필터 적용하기
이제 노드를 검색할 때 커밋 작성자나 날짜로 필터링할 수 있어요.
from llama_index.core.vector_stores.types import (
MetadataFilter,
MetadataFilters,
)
filters = MetadataFilters(
filters=[
MetadataFilter(key="author", value="[email protected]"),
MetadataFilter(key="author", value="[email protected]"),
],
condition="or",
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("What is this software project about?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:31,673 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-27', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-07-13', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-30', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-23', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-10', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-07-25', 'author': '[email protected]', 'fixes': ['5892']}
{'commit_date': '2023-08-21', 'author': '[email protected]', 'fixes': []}
filters = MetadataFilters(
filters=[
MetadataFilter(key="commit_date", value="2023-08-15", operator=">="),
MetadataFilter(key="commit_date", value="2023-08-25", operator="<="),
],
condition="and",
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("What is this software project about?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:40,347 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-23', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-17', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-24', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-23', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-21', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-20', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-21', 'author': '[email protected]', 'fixes': []}
중첩 필터 적용하기
위 예시에서는 AND나 OR로 여러 필터를 결합했어요. 이제 필터 세트 자체를 여러 개 결합할 수도 있습니다.
예를 들어 SQL로 표현하면:
WHERE (commit_date >= '2023-08-01' AND commit_date <= '2023-08-15') AND (author = '[email protected]' OR author = '[email protected]')
filters = MetadataFilters(
filters=[
MetadataFilters(
filters=[
MetadataFilter(
key="commit_date", value="2023-08-01", operator=">="
),
MetadataFilter(
key="commit_date", value="2023-08-15", operator="<="
),
],
condition="and",
),
MetadataFilters(
filters=[
MetadataFilter(key="author", value="[email protected]"),
MetadataFilter(key="author", value="[email protected]"),
],
condition="or",
),
],
condition="and",
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("What is this software project about?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:45,021 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-10', 'author': '[email protected]', 'fixes': []}
위 코드는 IN 연산자를 사용하면 더 간단해집니다. PGVectorStore는 요소를 리스트와 비교하는 데 in, nin, contains를 지원해요.
filters = MetadataFilters(
filters=[
MetadataFilter(key="commit_date", value="2023-08-01", operator=">="),
MetadataFilter(key="commit_date", value="2023-08-15", operator="<="),
MetadataFilter(
key="author",
value=["[email protected]", "[email protected]"],
operator="in",
),
],
condition="and",
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("What is this software project about?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:49,129 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-07', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-10', 'author': '[email protected]', 'fixes': []}
# Same thing, with NOT IN
filters = MetadataFilters(
filters=[
MetadataFilter(key="commit_date", value="2023-08-01", operator=">="),
MetadataFilter(key="commit_date", value="2023-08-15", operator="<="),
MetadataFilter(
key="author",
value=["[email protected]", "[email protected]"],
operator="nin",
),
],
condition="and",
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("What is this software project about?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:51,587 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-09', 'author': '[email protected]', 'fixes': ['5805']}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-15', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-11', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-09', 'author': '[email protected]', 'fixes': ['5923', '5680', '5774', '5786', '5906', '5912']}
{'commit_date': '2023-08-03', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-03', 'author': '[email protected]', 'fixes': ['5908']}
{'commit_date': '2023-08-01', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-10', 'author': '[email protected]', 'fixes': []}
{'commit_date': '2023-08-10', 'author': '[email protected]', 'fixes': []}
# CONTAINS
filters = MetadataFilters(
filters=[
MetadataFilter(key="fixes", value="5680", operator="contains"),
]
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("How did these commits fix the issue?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 16:48:56,822 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-09', 'author': '[email protected]', 'fixes': ['5923', '5680', '5774', '5786', '5906', '5912']}
쿼리 커스터마이징하기
다른 테이블을 조인하는 것 같은 더 복잡한 쿼리를 만들 수도 있어요. 이는 customize_query_fn 인자에 함수를 넣어서 합니다. 먼저 사용자 테이블을 만들고 데이터를 채워볼게요.
from sqlalchemy import (
Table,
MetaData,
Column,
String,
Integer,
create_engine,
insert,
)
engine = create_engine(url=connection_string + "/" + db_name)
metadata = MetaData()
user_table = Table(
"user",
metadata,
Column("id", Integer, primary_key=True, autoincrement=True),
Column("name", String, nullable=False),
Column("email", String, nullable=False),
)
user_table.drop(engine, checkfirst=True)
user_table.create(engine)
with engine.begin() as conn:
stmt = insert(user_table)
conn.execute(
stmt, [{"name": "Konstantina", "email": "[email protected]"}]
)
그런 다음 쿼리 커스터마이징 함수를 만들고 customize_query_fn과 함께 PGVectorStore를 인스턴스화할 수 있어요.
from typing import Any
from sqlalchemy import Select
def customize_query(query: Select, table_class: Any, **kwargs: Any) -> Select:
# Join the user table on the email addresses and add the name column to the select statement
return query.add_columns(user_table.c.name).join(
user_table,
user_table.c.email == table_class.metadata_["author"].astext,
)
vector_store = PGVectorStore.from_params(
database=db_name,
host=url.host,
password=url.password,
port=url.port,
user=url.username,
table_name="metadata_filter_demo3",
embed_dim=1536, # openai embedding dimension
hnsw_kwargs={
"hnsw_m": 16,
"hnsw_ef_construction": 64,
"hnsw_ef_search": 40,
"hnsw_dist_method": "vector_cosine_ops",
},
customize_query_fn=customize_query,
)
index = VectorStoreIndex.from_vector_store(vector_store=vector_store)
그런 다음 벡터 스토어에 질의하고, select 문에 추가된 추가 필드를 노드 메타데이터의 custom_fields라는 딕셔너리에서 가져올 수 있어요.
filters = MetadataFilters(
filters=[
MetadataFilter(key="fixes", value="5680", operator="contains"),
]
)
retriever = index.as_retriever(
similarity_top_k=10,
filters=filters,
)
retrieved_nodes = retriever.retrieve("How did these commits fix the issue?")
for node in retrieved_nodes:
print(node.node.metadata)
2025-09-11 17:06:43,812 - INFO - HTTP Request: POST https://api.openai.com/v1/embeddings "HTTP/1.1 200 OK"
{'commit_date': '2023-08-09', 'author': '[email protected]', 'fixes': ['5923', '5680', '5774', '5786', '5906', '5912'], 'custom_fields': {'name': 'Konstantina'}}
PgVector 쿼리 옵션
IVFFlat Probes
IVFFlat probes 개수를 지정합니다 (기본값 1)
인덱스에서 검색할 때 적절한 수의 IVFFlat probe를 지정할 수 있어요 (recall이 더 중요하면 높게, 속도가 더 중요하면 낮게)
retriever = index.as_retriever(
vector_store_query_mode="hybrid",
similarity_top_k=5,
vector_store_kwargs={"ivfflat_probes": 10},
)
HNSW EF Search
검색을 위한 동적 후보 리스트 크기를 지정합니다 (기본값 40)
retriever = index.as_retriever(
vector_store_query_mode="hybrid",
similarity_top_k=5,
vector_store_kwargs={"hnsw_ef_search": 300},
)