pgvector Setup

Set up PostgreSQL with pgvector for storing and querying vector embeddings. This is the foundation for building RAG (Retrieval-Augmented Generation) applications.

When to Apply

Use this skill when:

• Setting up vector storage for embeddings
• Creating tables to store document embeddings
• Building semantic search functionality
• Implementing RAG pipelines with PostgreSQL
• Migrating from other vector databases to PostgreSQL

Prerequisites

pgvector must be available in your PostgreSQL instance. Use one of these Docker images:

Quick Start

1. Start PostgreSQL with pgvector

Ensure PostgreSQL is running with a pgvector-enabled image (see pgpm-docker skill) and PG env vars are loaded (see pgpm-env skill).

2. Create Schema and Tables

Create a pgpm module for your vector storage:

pgpm init my-vectors
cd my-vectors
pgpm add schemas/intelligence
pgpm add schemas/intelligence/tables/documents --requires schemas/intelligence
pgpm add schemas/intelligence/tables/chunks --requires schemas/intelligence/tables/documents

Schema Design

Documents Table

Store full documents with their embeddings:

-- deploy/schemas/intelligence/tables/documents.sql
-- Deploy: schemas/intelligence/tables/documents
-- requires: schemas/intelligence

CREATE TABLE intelligence.documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    embedding VECTOR(768),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Chunks Table

Store document chunks for granular retrieval:

-- deploy/schemas/intelligence/tables/chunks.sql
-- Deploy: schemas/intelligence/tables/chunks
-- requires: schemas/intelligence/tables/documents

CREATE TABLE intelligence.chunks (
    id SERIAL PRIMARY KEY,
    document_id INTEGER NOT NULL REFERENCES intelligence.documents(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    embedding VECTOR(768),
    chunk_index INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_chunks_document_id ON intelligence.chunks(document_id);

Chat History Table (Optional)

Track conversation history for RAG sessions:

-- deploy/schemas/intelligence/tables/chat_history.sql
CREATE TABLE intelligence.chat_history (
    id SERIAL PRIMARY KEY,
    session_id TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_chat_history_session ON intelligence.chat_history(session_id);

Vector Dimensions

Choose dimensions based on your embedding model:

Declare the dimension in your VECTOR type:

embedding VECTOR(768)   -- For nomic-embed-text
embedding VECTOR(1536)  -- For OpenAI models

Indexes for Performance

IVFFlat Index (Recommended for Most Cases)

Good balance of speed and accuracy:

-- Create after inserting initial data
CREATE INDEX idx_chunks_embedding ON intelligence.chunks 
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

The lists parameter should be approximately sqrt(num_rows).

HNSW Index (Better Recall)

Higher memory usage but better recall:

CREATE INDEX idx_chunks_embedding_hnsw ON intelligence.chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Revert Scripts

Always include revert scripts for pgpm:

-- revert/schemas/intelligence/tables/documents.sql
DROP TABLE IF EXISTS intelligence.documents;

-- revert/schemas/intelligence/tables/chunks.sql
DROP TABLE IF EXISTS intelligence.chunks;

Verify Scripts

Confirm deployment succeeded:

-- verify/schemas/intelligence/tables/documents.sql
DO $$
BEGIN
  PERFORM 1 FROM pg_tables 
  WHERE schemaname = 'intelligence' AND tablename = 'documents';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Table intelligence.documents does not exist';
  END IF;
END $$;

Complete Module Structure

my-vectors/
├── deploy/
│   └── schemas/
│       └── intelligence/
│           ├── schema.sql
│           └── tables/
│               ├── documents.sql
│               ├── chunks.sql
│               └── chat_history.sql
├── revert/
│   └── schemas/
│       └── intelligence/
│           ├── schema.sql
│           └── tables/
│               ├── documents.sql
│               ├── chunks.sql
│               └── chat_history.sql
├── verify/
│   └── schemas/
│       └── intelligence/
│           ├── schema.sql
│           └── tables/
│               ├── documents.sql
│               ├── chunks.sql
│               └── chat_history.sql
├── pgpm.plan
└── package.json

Deploying

pgpm deploy --database myapp_dev --createdb --yes

Troubleshooting

References

• Related skill: pgvector-embeddings for generating and storing embeddings
• Related skill: pgvector-similarity-search for querying vectors
• Related skill: rag-pipeline for complete RAG implementation
• pgvector documentation