pgvector¶
pgvector adds vector types and similarity search to PostgreSQL. It is a good fit when you already run PostgreSQL, want embeddings next to relational data, or need transactions, backups and SQL around your vectors. Agents use it through PgVectorStore, a direct SDK in agent_addons built on psycopg 3, either on its own or as the "pgvector" provider of EmbeddingsSDK.
It works with any PostgreSQL that has the vector extension: self-hosted (for example the pgvector/pgvector image), Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL, Supabase, Neon and others. Version 0.5.0 or later of the extension is needed, for HNSW indexes.
To run PostgreSQL in your own cluster, use the vector database deployer in WikiGrid (OpenWiki.network docs).
Installation¶
pip install "./libs/agents-addons[pgvector]" # installs psycopg[binary] >= 3.1
No pgvector Python package is needed: vectors are sent to PostgreSQL as text and cast to vector.
On first connection the store runs CREATE EXTENSION IF NOT EXISTS vector. This needs a role that is allowed to create extensions; if yours is not, have an administrator create it once per database, and pass pg_create_extension=False (or create_extension=False).
Connecting¶
from agent_addons.embeddings_store import EmbeddingsSDK
vdb = EmbeddingsSDK(
provider="pgvector",
pg_dsn="postgresql://agent:secret@postgres.vector.svc.cluster.local:5432/agents",
pg_schema="public",
dimension=1536,
metric="cosine",
)
from agent_addons.vector_backends import PgVectorStore
store = PgVectorStore(
"postgresql://agent:secret@postgres.vector.svc.cluster.local:5432/agents",
dimension=1536,
metric="cosine",
)
Both give you the same methods. EmbeddingsSDK adds the text-based helpers (insert_texts, search_text, …), file and S3 ingestion, and works with AgentRAG. Through EmbeddingsSDK, the underlying store is available as vdb.store and the psycopg connection as vdb.client.
PgVectorStore argument |
EmbeddingsSDK argument |
Default | Description |
|---|---|---|---|
dsn |
pg_dsn |
— (required) | PostgreSQL connection string (postgresql://user:pass@host:5432/db?sslmode=require) |
schema |
pg_schema |
"public" |
Schema that holds the collection tables; it must exist |
dimension |
dimension |
None |
Vector dimension, required to create collections (up to 2,000 for an HNSW index) |
metric |
metric |
"cosine" |
"cosine", "dotproduct" (or "ip") or "euclidean" (or "l2") |
create_extension |
pg_create_extension |
True |
Create the vector extension on connect |
hnsw_m |
pg_hnsw_m |
16 |
HNSW m (links per node) for new tables |
hnsw_ef_construction |
pg_hnsw_ef_construction |
64 |
HNSW ef_construction for new tables |
connection |
— | None |
An existing psycopg connection to reuse instead of connecting (use autocommit=True) |
The store opens one connection in autocommit mode and serialises calls on it with a lock, so one client can be shared by the threads of an agent instance.
Collections¶
Each collection is a table in schema:
vdb.create_collection("knowledge_base")
vdb.list_collections() # tables in the schema with an "embedding vector" column
store.count("knowledge_base") # number of rows
vdb.delete_collection("knowledge_base") # DROP TABLE IF EXISTS
create_collection creates:
CREATE TABLE IF NOT EXISTS "public"."knowledge_base" (
id TEXT PRIMARY KEY,
embedding vector(1536) NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS "knowledge_base_embedding_hnsw"
ON "public"."knowledge_base" USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX IF NOT EXISTS "knowledge_base_metadata_gin"
ON "public"."knowledge_base" USING gin (metadata jsonb_path_ops);
The HNSW operator class follows metric (vector_cosine_ops, vector_ip_ops or vector_l2_ops), and the GIN index makes metadata filters fast. create_collection is safe to call again on an existing table. Table names are quoted, so they are case-sensitive; keep them under about 45 characters so that the index names stay within PostgreSQL's 63-character limit.
Writing and Reading¶
vdb.insert_vectors("knowledge_base", [
{"id": "doc-1", "values": [0.12, 0.98, ...], "metadata": {"text": "Vector DBs store embeddings.", "source": "wiki", "tags": ["db"]}},
{"id": "doc-2", "values": [0.33, 0.71, ...], "metadata": {"text": "Acme is based in Delaware.", "source": "legal"}},
])
vdb.get_vector("knowledge_base", "doc-1")
# {"id": "doc-1", "values": [...], "metadata": {"text": "...", "source": "wiki", "tags": ["db"]}}
vdb.update_vector("knowledge_base", "doc-1", new_values, metadata={"text": "...", "source": "wiki"})
vdb.delete_vector("knowledge_base", "doc-2")
- Inserts are upserts (
INSERT … ON CONFLICT (id) DO UPDATE): inserting an ID that already exists replaces that record. update_vectorreplaces both the vector and the metadata.get_vectorreturnsNonewhen the ID does not exist.
Searching¶
results = vdb.search("knowledge_base", query_vector, top_k=5)
{
"matches": [
{ "id": "doc-1", "score": 0.93, "distance": 0.07, "metadata": { "text": "...", "source": "wiki" } },
{ "id": "doc-2", "score": 0.41, "distance": 0.59, "metadata": { "text": "...", "source": "legal" } }
]
}
Matches are sorted best first. distance is the value pgvector returns; score is always "higher is better":
metric |
Operator | distance |
score |
|---|---|---|---|
cosine |
<=> |
Cosine distance (1 − similarity) |
1 − distance (the cosine similarity) |
dotproduct |
<#> |
Negative inner product | −distance (the inner product) |
euclidean |
<-> |
Euclidean distance | 1 / (1 + distance) |
To trade speed for recall, raise HNSW's ef_search (default 40) for a query by calling the store directly. It must be at least top_k to return top_k results:
vdb.store.search("knowledge_base", query_vector, top_k=100, ef_search=200)
Filters¶
filters is a dict matched by JSONB containment, metadata @> filters, and is served by the GIN index:
vdb.search("knowledge_base", qv, top_k=5, filters={"source": "wiki"}) # source = "wiki"
vdb.search("knowledge_base", qv, top_k=5, filters={"source": "wiki", "lang": "en"}) # both must match
vdb.search("knowledge_base", qv, top_k=5, filters={"tags": ["db"]}) # tags array contains "db"
vdb.search("knowledge_base", qv, top_k=5, filters={"owner": {"team": "legal"}}) # nested objects
Containment covers equality, "array contains" and nested matches. For ranges or anything else, use SQL directly (below).
Raw SQL¶
Because the data is an ordinary table, you can query it with SQL, for example to join with your own tables or filter on ranges. Use the store's connection:
rows = vdb.client.execute(
"""
SELECT id, metadata->>'text' AS text, 1 - (embedding <=> %s::vector) AS score
FROM public.knowledge_base
WHERE (metadata->>'year')::int >= 2024
ORDER BY embedding <=> %s::vector
LIMIT 5
""",
(vec, vec),
).fetchall()
Pass the query vector as a string such as "[0.12,0.98,...]" (str(list_of_floats) works) and cast it with ::vector.
Text, Ingestion and RAG¶
Everything in the Usage Guide works unchanged with provider="pgvector":
from agents_embeddings.custom import OpenAIEmbeddingsGenerator
from agent_addons.rag import AgentRAG
embedder = OpenAIEmbeddingsGenerator(model="text-embedding-3-small")
vdb.insert_texts("knowledge_base", [{"text": "Vector DBs store embeddings.", "metadata": {"text": "Vector DBs store embeddings."}}], embedder=embedder)
hits = vdb.search_text("knowledge_base", "What stores embeddings?", embedder=embedder, top_k=3)
rag = AgentRAG(collection="knowledge_base", embeddings_sdk=vdb, embedder=embedder, llm=llm)
rag.add_documents_chunked([{"id": "contract-1", "text": long_contract_text}])
answer = rag.ask("Where is RetailCo based?")
EmbeddingsSDK can also be the sink of S3Ingestor and RedisIngestor (in agent_addons.ingestors), because it has the insert_vectors(collection, payload) method they call.
Registering pgvector in the Subject Specification¶
Record the database as an add-on in integrations.addons (see Registering Vector DB in Subject Specification):
{
"addon_id": "vector-primary",
"addon_type": "vector_db",
"addon_config": {
"provider": "pgvector",
"pg_schema": "agents",
"dimension": 1536,
"metric": "cosine"
}
}
Keep the connection string, which holds the password, out of the specification. Pass it from the environment when you build the client:
import os
addon = next(a for a in self.subject.integrations.addons if a.addon_id == "vector-primary")
vdb = EmbeddingsSDK(**addon.addon_config, pg_dsn=os.environ["PG_DSN"])
Example: Per-Customer Knowledge with Filters¶
One table can hold the knowledge of many customers, and a metadata filter keeps each search inside one customer's data:
import os
from agent_addons.embeddings_store import EmbeddingsSDK
from agents_embeddings.custom import OpenAIEmbeddingsGenerator
embedder = OpenAIEmbeddingsGenerator(model="text-embedding-3-small")
vdb = EmbeddingsSDK(provider="pgvector", pg_dsn=os.environ["PG_DSN"], dimension=1536)
vdb.create_collection("customer_docs") # no-op if it exists
def add_doc(customer_id: str, doc_id: str, text: str):
vdb.insert_texts("customer_docs", [{
"id": f"{customer_id}:{doc_id}",
"text": text,
"metadata": {"customer_id": customer_id, "text": text},
}], embedder=embedder)
def search(customer_id: str, question: str, k: int = 5):
res = vdb.search_text("customer_docs", question, embedder=embedder, top_k=k,
filters={"customer_id": customer_id})
return [(m["score"], m["metadata"]["text"]) for m in res["matches"]]
Ecosystem reference
MemoryGrid also stores lesson embeddings in pgvector; see the Learner. For deployers for vector databases, see WikiGrid (OpenWiki.network docs, GitHub).