DEV Community

Yasser B.
Yasser B.

Posted on • Originally published at rivestack.io

pgvector with LlamaIndex: Build a RAG Pipeline on PostgreSQL

#python #pgvectorllamaindexpostgresql

LlamaIndex is purpose-built for the data layer of LLM applications: ingestion, indexing, retrieval, and query pipelines. If you're already running PostgreSQL, pgvector is the natural vector store choice. Your embeddings, document metadata, and application data all live in one place, with SQL joins, transactions, and your existing backup strategy included.

This guide walks through the LlamaIndex PGVectorStore integration from scratch: setup, document ingestion, metadata filtering, loading an existing index without re-embedding, and reranking results for better retrieval quality.

This is an excerpt. The full guide with all code is at rivestack.io/blog/pgvector-llamaindex

Setup 

pip install llama-index llama-index-vector-stores-postgres llama-index-embeddings-openai psycopg2-binary
Enter fullscreen mode Exit fullscreen mode

Connecting to pgvector 

from llama_index.vector_stores.postgres import PGVectorStore

vector_store = PGVectorStore.from_params(
    host="localhost",
    port=5432,
    database="mydb",
    user="user",
    password="password",
    table_name="documents",
    embed_dim=1536,
)
Enter fullscreen mode Exit fullscreen mode

Indexing Documents 

from llama_index.core import VectorStoreIndex, SimpleDirectoryReader, StorageContext

documents = SimpleDirectoryReader("./docs").load_data()
storage_context = StorageContext.from_defaults(vector_store=vector_store)

index = VectorStoreIndex.from_documents(
    documents,
    storage_context=storage_context,
    show_progress=True,
)
Enter fullscreen mode Exit fullscreen mode

Querying 

query_engine = index.as_query_engine(similarity_top_k=4)
response = query_engine.query("How do I configure connection pooling?")
print(response)
Enter fullscreen mode Exit fullscreen mode

Metadata Filtering 

from llama_index.core.vector_stores import MetadataFilter, MetadataFilters

filters = MetadataFilters(filters=[
    MetadataFilter(key="category", value="transactions"),
])

query_engine = index.as_query_engine(
    similarity_top_k=4,
    filters=filters
)
Enter fullscreen mode Exit fullscreen mode

The full post also covers: loading an existing index without re-embedding, incremental ingestion with IngestionPipeline to skip unchanged documents, cross-encoder reranking, and inspecting your vectors directly in PostgreSQL.

Full guide: https://rivestack.io/blog/pgvector-llamaindex

Top comments (0)