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

bash

pgpm docker start --image pyramation/postgres:17
eval "$(pgpm env)"

2. Create Schema and Tables

Create a pgpm module for your vector storage:

bash

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:

sql

-- 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:

sql

-- 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:

sql

-- 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:

sql

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:

sql

-- 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:

sql

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:

sql

-- 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:

sql

-- 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

text

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

bash

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