LlamaIndex Tutorial (Python): connecting to PostgreSQL for advanced developers

This tutorial shows you how to connect LlamaIndex to PostgreSQL from Python, store and query indexed data, and use PostgreSQL as the persistence layer for a real agent workflow. You need this when you want your index state, metadata, or document-backed retrieval to live in a database your team already operates instead of keeping everything in local files.

What You'll Need

• •Python 3.10+
• •A running PostgreSQL instance
• •A PostgreSQL database and user with read/write access
• •These Python packages:
  • •llama-index
  • •llama-index-vector-stores-postgres
  • •psycopg2-binary
  • •sqlalchemy
• •An OpenAI API key if you want to use LlamaIndex embeddings and chat models
• •Environment variables set for:
  • •OPENAI_API_KEY
  • •PGHOST
  • •PGPORT
  • •PGDATABASE
  • •PGUSER
  • •PGPASSWORD

Step-by-Step

1. •Install the packages and verify your environment.
   For production work, keep the vector store package separate so you can pin versions and control upgrades.

pip install llama-index llama-index-vector-stores-postgres psycopg2-binary sqlalchemy

2. •Create a PostgreSQL connection string and initialize the database connection.
   This example uses SQLAlchemy because it plays well with LlamaIndex’s PostgreSQL vector store integration.

import os
from sqlalchemy import make_url

pg_url = make_url(
    f"postgresql+psycopg2://{os.environ['PGUSER']}:{os.environ['PGPASSWORD']}"
    f"@{os.environ['PGHOST']}:{os.environ.get('PGPORT', '5432')}/{os.environ['PGDATABASE']}"
)

print(pg_url)

3. •Load documents and build a PostgreSQL-backed vector index.
   This is the core step: LlamaIndex chunks your text, embeds it, and stores vectors in PostgreSQL so retrieval can happen from the database.

from llama_index.core import Document, VectorStoreIndex, StorageContext
from llama_index.embeddings.openai import OpenAIEmbedding
from llama_index.vector_stores.postgres import PGVectorStore

documents = [
    Document(text="PostgreSQL is a relational database that supports extensions."),
    Document(text="LlamaIndex can use external vector stores for retrieval."),
]

vector_store = PGVectorStore.from_params(
    database=pg_url.database,
    host=pg_url.host,
    password=pg_url.password,
    port=pg_url.port,
    user=pg_url.username,
    table_name="llamaindex_vectors",
    embed_dim=1536,
)

storage_context = StorageContext.from_defaults(vector_store=vector_store)
index = VectorStoreIndex.from_documents(
    documents,
    storage_context=storage_context,
    embed_model=OpenAIEmbedding(model="text-embedding-3-small"),
)

4. •Query the index using a retriever or query engine.
   In practice, this is where PostgreSQL earns its keep: your app can retrieve semantically relevant chunks without managing local index files.

query_engine = index.as_query_engine()
response = query_engine.query("What role does PostgreSQL play here?")

print(str(response))

5. •Persist and reload the same index from PostgreSQL.
   Advanced teams usually want repeatable startup behavior, so the app should reconnect to existing vectors instead of rebuilding every time.

from llama_index.core import StorageContext, load_index_from_storage

vector_store = PGVectorStore.from_params(
    database=pg_url.database,
    host=pg_url.host,
    password=pg_url.password,
    port=pg_url.port,
    user=pg_url.username,
    table_name="llamaindex_vectors",
    embed_dim=1536,
)

storage_context = StorageContext.from_defaults(vector_store=vector_store)
loaded_index = load_index_from_storage(storage_context)

print(loaded_index.as_query_engine().query("What was stored in PostgreSQL?"))

Testing It

Run the script once with a valid OpenAI key and reachable PostgreSQL instance. If it works, you should see the connection string print first, then a natural-language answer generated from the stored documents.

Check your database as well. You should see a table named llamaindex_vectors created by the vector store integration, along with rows representing chunk embeddings and metadata.

If queries return empty or irrelevant results, verify three things first: your embedding dimension matches the model output, your database credentials are correct, and the table name is consistent across runs. For most failures in production setups, it’s one of those three.

Next Steps

• •Add metadata filters so retrieval only searches specific tenants, cases, or product lines.
• •Swap in a stronger embedding model if your corpus has dense domain language.
• •Wire this into an agent workflow with tool calling and per-request tracing so you can audit retrieval behavior end to end.

Keep learning

• •The complete AI Agents Roadmap — my full 8-step breakdown
• •Free: The AI Agent Starter Kit — PDF checklist + starter code
• •Work with me — I build AI for banks and insurance companies

By Cyprian Aarons, AI Consultant at Topiax.