Building a Production RAG Agent with LangChain, pgvector, and PostgreSQL on Ubuntu

Why PostgreSQL and pgvector for Vector Search?

Dedicated vector databases often introduce architectural complexity and separate backup routines. The open-source pgvector extension brings vector similarity search directly into standard PostgreSQL, enabling unified transactional queries and semantic search.

Step 1: Installing pgvector on PostgreSQL 16

Install the required build dependencies and compile the extension:

sudo apt update
sudo apt install -y postgresql-16 postgresql-server-dev-16 build-essential git
git clone --branch v0.7.0 https://github.com/pgvector/pgvector.git
cd pgvector
make && sudo make install

Step 2: Enabling the Extension in Your Database

Log in to PostgreSQL and enable pgvector for your application database:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    document_title VARCHAR(255) NOT NULL,
    chunk_content TEXT NOT NULL,
    embedding vector(768),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ON document_embeddings USING hnsw (embedding vector_cosine_ops);

Step 3: Connecting with LangChain in Python

Use Python to embed documents and perform semantic retrieval in under 20ms.