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:

Image Description

pyramation/postgres:17

PostgreSQL 17 with pgvector (recommended)

ghcr.io/constructive-io/docker/postgres-plus:17

PostgreSQL 17 with pgvector and additional extensions

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:

Model Dimensions Use Case

nomic-embed-text

768 General purpose, good balance

all-MiniLM-L6-v2

384 Lightweight, fast

text-embedding-ada-002

1536 OpenAI, high quality

text-embedding-3-small

1536 OpenAI, newer 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

Issue Solution

"type vector does not exist" pgvector extension not installed; use a pgvector-enabled image

"dimension mismatch" Embedding dimension doesn't match VECTOR(n) declaration

Slow queries Add IVFFlat or HNSW index after initial data load

Out of memory Reduce HNSW parameters or use IVFFlat instead

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