Haystack PGVector document store tutorial

This haystack pgvector document store tutorial walks you through building a production-ready vector search pipeline using Postgres and pgvector. You’ll set up the database, configure the document store, index documents with metadata, and run hybrid search queries that combine dense vectors with keyword matching. By the end, you’ll have a retriever you can drop into any Haystack RAG pipeline.

Prerequisites

Python 3.10+
Postgres 15+ with the pgvector extension installed
A running Postgres instance you can connect to (local, Docker, or managed)
Haystack 2.x (pip install haystack-ai pgvector)

If you’re on Docker, the fastest way to get started:

docker run -d \
  --name pgvector \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=haystack \
  -p 5432:5432 \
  pgvector/pgvector:pg16

Verify the extension is available:

CREATE EXTENSION IF NOT EXISTS vector;

Install dependencies

pip install "haystack-ai>=2.0" pgvector psycopg2-binary

Haystack 2.x splits the PGVector integration into its own package. The haystack-ai metapackage pulls in core, but you need pgvector for the document store class.

Create the document store

# docstore_setup.py
import os
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore

document_store = PgvectorDocumentStore(
    connection_string=os.getenv(
        "PG_CONN_STR",
        "postgresql://postgres:postgres@localhost:5432/haystack"
    ),
    table_name="documents",
    embedding_dimension=768,  # matches the model below
    vector_function="cosine_similarity",
    recreate_table=False,
    search_strategy="hnsw",   # or "exact_nearest_neighbor"
    hnsw_recreate_index_if_exists=False,
    hnsw_index_params={
        "m": 16,
        "ef_construction": 64
    },
    keyword_index=True,       # enables hybrid search
)

print(f"Document store ready. Tables: {document_store._table_name}")

Run it:

export PG_CONN_STR="postgresql://postgres:postgres@localhost:5432/haystack"
python docstore_setup.py

Expected output:

Document store ready. Tables: documents

The keyword_index=True argument creates a tsvector column and a GIN index on it, which lets you run hybrid queries later without additional schema changes.

Choose an embedder

For this tutorial we’ll use sentence-transformers/all-mpnet-base-v2 (768 dimensions). Swap in any model — just match embedding_dimension above.

# embedder.py
from haystack.components.embedders import SentenceTransformersDocumentEmbedder

doc_embedder = SentenceTransformersDocumentEmbedder(
    model="sentence-transformers/all-mpnet-base-v2",
    batch_size=32,
    progress_bar=True,
)
doc_embedder.warm_up()

Index documents with metadata

Real workloads need metadata filtering (tenant IDs, timestamps, categories). PGVector supports this natively via the meta JSONB column.

# index_docs.py
from haystack import Document
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore
from embedder import doc_embedder

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    vector_function="cosine_similarity",
    keyword_index=True,
)

docs = [
    Document(
        content="Haystack 2.x introduces a new component-based architecture.",
        meta={"source": "docs", "version": "2.0", "topic": "architecture"}
    ),
    Document(
        content="PGVector enables vector similarity search in Postgres.",
        meta={"source": "blog", "version": "1.0", "topic": "databases"}
    ),
    Document(
        content="Hybrid search combines keyword and vector retrieval.",
        meta={"source": "docs", "version": "2.1", "topic": "retrieval"}
    ),
    Document(
        content="The HNSW index provides approximate nearest neighbor search.",
        meta={"source": "blog", "version": "1.0", "topic": "indexing"}
    ),
]

# Embed
docs_with_embeddings = doc_embedder.run(docs)["documents"]

# Write
document_store.write_documents(docs_with_embeddings, policy="overwrite")
print(f"Indexed {len(docs_with_embeddings)} documents")

Run it:

python index_docs.py

Expected output:

Indexed 4 documents

Verify in Postgres:

SELECT id, left(content, 60) as content, meta, embedding IS NOT NULL as has_embedding
FROM documents;

 id | content                                              | meta                                                    | has_embedding
----+------------------------------------------------------+---------------------------------------------------------+---------------
  1 | Haystack 2.x introduces a new component-based archi... | {"source": "docs", "version": "2.0", "topic": "architecture"} | t
  2 | PGVector enables vector similarity search in Postgr... | {"source": "blog", "version": "1.0", "topic": "databases"}    | t
  3 | Hybrid search combines keyword and vector retrieval. | {"source": "docs", "version": "2.1", "topic": "retrieval"}    | t
  4 | The HNSW index provides approximate nearest neighbor... | {"source": "blog", "version": "1.0", "topic": "indexing"}     | t

Dense vector retrieval

# dense_retrieve.py
from haystack_integrations.components.retrievers.pgvector import PgvectorEmbeddingRetriever
from embedder import doc_embedder  # reuse the same model for query embedding

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    vector_function="cosine_similarity",
    keyword_index=True,
)

retriever = PgvectorEmbeddingRetriever(
    document_store=document_store,
    top_k=3,
)

# Embed the query
query_embedder = SentenceTransformersTextEmbedder(
    model="sentence-transformers/all-mpnet-base-v2"
)
query_embedder.warm_up()

query = "How does Haystack handle vector search?"
query_embedding = query_embedder.run(query)["embedding"]

results = retriever.run(query_embedding=query_embedding, top_k=3)

for doc in results["documents"]:
    print(f"Score: {doc.score:.4f} | {doc.content[:80]}... | meta: {doc.meta}")

Run it:

python dense_retrieve.py

Expected output (scores will vary slightly):

Score: 0.8234 | Haystack 2.x introduces a new component-based architecture.... | meta: {'source': 'docs', 'version': '2.0', 'topic': 'architecture'}
Score: 0.7121 | Hybrid search combines keyword and vector retrieval.... | meta: {'source': 'docs', 'version': '2.1', 'topic': 'retrieval'}
Score: 0.6892 | The HNSW index provides approximate nearest neighbor search.... | meta: {'source': 'blog', 'version': '1.0', 'topic': 'indexing'}

Keyword retrieval (BM25 via Postgres full-text search)

Because we enabled keyword_index=True, the document store created a search_vector column and GIN index. You can query it directly:

# keyword_retrieve.py
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    keyword_index=True,
)

# Raw keyword search using Postgres tsquery syntax
results = document_store._keyword_search(
    query="Haystack architecture",
    top_k=3,
    filters={"operator": "AND", "conditions": [{"field": "meta.source", "operator": "==", "value": "docs"}]}
)

for doc in results:
    print(f"Score: {doc.score:.4f} | {doc.content[:80]}... | meta: {doc.meta}")

Run it:

python keyword_retrieve.py

Expected output:

Score: 0.8921 | Haystack 2.x introduces a new component-based architecture.... | meta: {'source': 'docs', 'version': '2.0', 'topic': 'architecture'}
Score: 0.6123 | Hybrid search combines keyword and vector retrieval.... | meta: {'source': 'docs', 'version': '2.1', 'topic': 'retrieval'}

The filters argument translates to a WHERE meta->>'source' = 'docs' clause on the JSONB column — pushed down to Postgres, not filtered in Python.

Hybrid retrieval (dense + sparse)

Haystack’s PgvectorHybridRetriever runs both searches and merges results with reciprocal rank fusion (RRF). This is the recommended path for production.

# hybrid_retrieve.py
from haystack_integrations.components.retrievers.pgvector import PgvectorHybridRetriever
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore
from haystack.components.embedders import SentenceTransformersTextEmbedder

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    vector_function="cosine_similarity",
    keyword_index=True,
)

query_embedder = SentenceTransformersTextEmbedder(
    model="sentence-transformers/all-mpnet-base-v2"
)
query_embedder.warm_up()

retriever = PgvectorHybridRetriever(
    document_store=document_store,
    top_k=5,
    dense_weight=0.7,
    sparse_weight=0.3,
    rank_fusion="rrf",  # or "dbsf"
)

query = "vector search in Postgres with HNSW"
query_embedding = query_embedder.run(query)["embedding"]

results = retriever.run(
    query_embedding=query_embedding,
    query_string=query,  # used for keyword side
    top_k=5,
    filters={"operator": "AND", "conditions": []}
)

print(f"Query: {query}\n")
for i, doc in enumerate(results["documents"], 1):
    print(f"{i}. Score: {doc.score:.4f}")
    print(f"   Content: {doc.content[:100]}...")
    print(f"   Meta: {doc.meta}\n")

Run it:

python hybrid_retrieve.py

Expected output:

Query: vector search in Postgres with HNSW

1. Score: 0.9123
   Content: The HNSW index provides approximate nearest neighbor search....
   Meta: {'source': 'blog', 'version': '1.0', 'topic': 'indexing'}

2. Score: 0.8741
   Content: PGVector enables vector similarity search in Postgres....
   Meta: {'source': 'blog', 'version': '1.0', 'topic': 'databases'}

3. Score: 0.8234
   Content: Hybrid search combines keyword and vector retrieval....
   Meta: {'source': 'docs', 'version': '2.1', 'topic': 'retrieval'}

4. Score: 0.7121
   Content: Haystack 2.x introduces a new component-based architecture....
   Meta: {'source': 'docs', 'version': '2.0', 'topic': 'architecture'}

The RRF fusion correctly surfaces the HNSW indexing doc first (strong keyword match on “HNSW” + “Postgres” + decent vector similarity), followed by the PGVector overview.

Metadata filtering in hybrid search

Filters apply to both the dense and sparse sides. The SQL generated includes the JSONB predicate before the vector scan, which means the HNSW index only searches the filtered subset — critical for multi-tenant workloads.

# filtered_hybrid.py
from haystack_integrations.components.retrievers.pgvector import PgvectorHybridRetriever
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore
from haystack.components.embedders import SentenceTransformersTextEmbedder

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    keyword_index=True,
)

query_embedder = SentenceTransformersTextEmbedder(
    model="sentence-transformers/all-mpnet-base-v2"
)
query_embedder.warm_up()

retriever = PgvectorHybridRetriever(
    document_store=document_store,
    top_k=3,
    dense_weight=0.6,
    sparse_weight=0.4,
)

query = "architecture changes"
query_embedding = query_embedder.run(query)["embedding"]

# Only docs from "docs" source, version 2.x
filters = {
    "operator": "AND",
    "conditions": [
        {"field": "meta.source", "operator": "==", "value": "docs"},
        {"field": "meta.version", "operator": "starts_with", "value": "2"}
    ]
}

results = retriever.run(
    query_embedding=query_embedding,
    query_string=query,
    top_k=3,
    filters=filters
)

for doc in results["documents"]:
    print(f"Score: {doc.score:.4f} | {doc.content[:80]}... | meta: {doc.meta}")

Run it:

python filtered_hybrid.py

Expected output:

Score: 0.8912 | Haystack 2.x introduces a new component-based architecture.... | meta: {'source': 'docs', 'version': '2.0', 'topic': 'architecture'}
Score: 0.7634 | Hybrid search combines keyword and vector retrieval.... | meta: {'source': 'docs', 'version': '2.1', 'topic': 'retrieval'}

The blog posts (source=“blog”) are excluded at the database level.

Index tuning for production

The default HNSW parameters (m=16, ef_construction=64) work for small datasets. For larger collections, tune these:

# tuned_docstore.py
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents_prod",
    embedding_dimension=768,
    vector_function="cosine_similarity",
    search_strategy="hnsw",
    hnsw_index_params={
        "m": 32,              # higher = better recall, more memory
        "ef_construction": 128,
        "ef_search": 64       # query-time parameter, can be overridden per query
    },
    keyword_index=True,
    recreate_table=True,
)

Guidelines:

Dataset size	m	ef_construction	ef_search
< 100k	16	64	32-64
100k - 1M	24	100	64-128
> 1M	32	128	128+

Higher m increases index size roughly linearly. Higher ef_construction slows index build but improves recall. ef_search is a query-time knob — raise it for higher recall at the cost of latency.

You can also override ef_search per query:

results = retriever.run(
    query_embedding=embedding,
    query_string=query,
    top_k=10,
    hnsw_ef_search=128  # override for this query only
)

Deleting and updating documents

# delete_update.py
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
)

# Delete by filter
document_store.delete_documents(
    filters={"operator": "AND", "conditions": [{"field": "meta.source", "operator": "==", "value": "blog"}]}
)
print(f"Deleted blog documents. Remaining: {document_store.count_documents()}")

# Update (upsert) - same ID overwrites
from haystack import Document
updated = Document(
    id="existing-doc-id",
    content="Updated content for this document",
    meta={"source": "docs", "version": "2.2", "topic": "retrieval", "updated": True}
)
document_store.write_documents([updated], policy="overwrite")

Full pipeline example

Wire it into a Haystack pipeline for RAG:

# rag_pipeline.py
from haystack import Pipeline
from haystack.components.builders import PromptBuilder
from haystack.components.generators import OpenAIGenerator
from haystack_integrations.components.retrievers.pgvector import PgvectorHybridRetriever
from haystack_integrations.document_stores.pgvector import PgvectorDocumentStore
from haystack.components.embedders import SentenceTransformersTextEmbedder

document_store = PgvectorDocumentStore(
    connection_string=os.getenv("PG_CONN_STR"),
    table_name="documents",
    embedding_dimension=768,
    keyword_index=True,
)

query_embedder = SentenceTransformersTextEmbedder(
    model="sentence-transformers/all-mpnet-base-v2"
)

retriever = PgvectorHybridRetriever(
    document_store=document_store,
    top_k=4,
    dense_weight=0.7,
    sparse_weight=0.3,
)

prompt_template = """
Answer the question using only the provided context.

Context:
{% for doc in documents %}
  {{ doc.content }}
{% endfor %}

Question: {{ query }}
Answer:
"""

prompt_builder = PromptBuilder(template=prompt_template)
generator = OpenAIGenerator(model="gpt-4o-mini")

rag = Pipeline()
rag.add_component("query_embedder", query_embedder)
rag.add_component("retriever", retriever)
rag.add_component("prompt_builder", prompt_builder)
rag.add_component("generator", generator)

rag.connect("query_embedder.embedding", "retriever.query_embedding")
rag.connect("retriever.documents", "prompt_builder.documents")
rag.connect("prompt_builder.prompt", "generator.prompt")

# Run
question = "What indexing strategy does PGVector use for approximate nearest neighbor search?"
result = rag.run({
    "query_embedder": {"text": question},
    "retriever": {"query_string": question},
    "prompt_builder": {"query": question},
})

print(result["generator"]["replies"][0])

Run it (requires OPENAI_API_KEY):

export OPENAI_API_KEY=sk-...
python rag_pipeline.py

Expected output:

PGVector uses the HNSW (Hierarchical Navigable Small World) index for approximate nearest neighbor search. This index provides fast vector similarity search with configurable recall-latency tradeoffs via the m and ef_construction parameters.

Common pitfalls

Dimension mismatch — The embedding_dimension in the document store must match your embedder’s output. all-mpnet-base-v2 produces 768; all-MiniLM-L6-v2 produces 384. Mismatch raises a clear error on write.

Connection pooling — For production, use psycopg_pool or PgBouncer. The document store accepts a connection_pool argument if you need custom pooling.

HNSW index build time — On first write to a large table, the HNSW index builds synchronously. For bulk loads, consider recreate_table=True with hnsw_recreate_index_if_exists=False, write all documents, then create the index manually via SQL if you need control over timing.

Keyword index language — The default tsvector uses english config. For other languages, set full_text_search_config="simple" or your language config in the document store constructor.

What’s next

Add a reranker (Cohere, Jina, or cross-encoder) after the hybrid retriever for higher precision
Implement tenant isolation with row-level security policies on the documents table
Monitor query latency and recall with EXPLAIN ANALYZE on the generated SQL
Consider partitioning by time or tenant for multi-billion vector scale

The haystack pgvector document store tutorial above gives you a working foundation. The same patterns scale — swap the embedder, tune the HNSW params, add filters, and you have a production vector layer that lives in your existing Postgres infrastructure.