Skip to content

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_vector replaces both the vector and the metadata.
  • get_vector returns None when 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).