Part 3: Setting Up pgvector with PostgreSQL

← Part 2: Vector Embeddings | Part 4: TypeScript Implementation →

The $400/Month Bill That Made Me Switch

I was running a documentation search system on Pinecone. Everything worked great, until the bill arrived.

Monthly costs:

• Pinecone: $359/month (2M vectors, p1 pod)

• OpenAI embeddings: $18/month

• Total: $377/month for a simple internal docs search

Then I saw this tweet: "Just migrated 2M vectors from Pinecone to pgvector. Monthly cost: $377 → $18. Same performance."

I was skeptical. Could PostgreSQL really replace a specialized vector database?

I tried it. Migration took 2 hours. Results:

• Monthly cost: $377 → $18 (95% reduction)

• Query latency: 89ms → 76ms (faster!)

• Maintenance complexity: Much simpler (one database, not two)

That $359/month saving paid for a lot of coffee. ☕

This article shows you exactly how to set up pgvector, create vector columns, and implement fast indexes.

Installing PostgreSQL with pgvector

macOS (Homebrew)

# Install PostgreSQL
brew install postgresql@15

# Start PostgreSQL
brew services start postgresql@15

# Install pgvector
brew install pgvector

# Or build from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # May need sudo

Linux (Ubuntu/Debian)

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL
sudo apt-get update
sudo apt-get install postgresql-15 postgresql-server-dev-15

# Build pgvector from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

Docker (Easiest for Development)

# Pull official pgvector image
docker pull ankane/pgvector

# Run PostgreSQL with pgvector
docker run -d \
  --name postgres-vector \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=vectordb \
  -p 5432:5432 \
  ankane/pgvector

# Connect
docker exec -it postgres-vector psql -U postgres -d vectordb

Docker Compose (My Production Setup)

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: ankane/pgvector:latest
    container_name: postgres-vector
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: vectordb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

# Start
docker-compose up -d

# Check health
docker-compose ps

Enabling and Verifying pgvector Extension

-- Connect to database
psql -U postgres -d vectordb

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Verify installation
SELECT * FROM pg_extension WHERE extname = 'vector';

-- Check version
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Expected: 0.5.0 or higher

-- Test vector operations
SELECT '[1,2,3]'::vector;
SELECT '[1,2,3]'::vector <-> '[4,5,6]'::vector; -- Euclidean distance
SELECT '[1,2,3]'::vector <=> '[4,5,6]'::vector; -- Cosine distance

If you see results, pgvector is working!

Creating Tables with Vector Columns

Basic Vector Column

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding VECTOR(1536),  -- 1536 dimensions (OpenAI text-embedding-3-small)
  created_at TIMESTAMP DEFAULT NOW()
);

Vector dimension must match your embedding model:

• OpenAI text-embedding-3-small: 1536

• OpenAI text-embedding-3-large: 3072

• Sentence Transformers all-MiniLM-L6-v2: 384

Complete Schema with Metadata

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  price DECIMAL(10, 2),
  tags TEXT[],
  
  -- Vector embedding
  embedding VECTOR(1536),
  
  -- Metadata
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  -- Indexes for hybrid search
  CONSTRAINT products_name_key UNIQUE (name)
);

-- Traditional indexes for filtering
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_created_at ON products(created_at);

-- Full-text search index
CREATE INDEX idx_products_fts ON products USING gin(to_tsvector('english', name || ' ' || description));

Vector Column for Documentation

CREATE TABLE doc_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL,
  chunk_index INTEGER NOT NULL,
  
  -- Content
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  url TEXT,
  
  -- Vector
  embedding VECTOR(1536),
  
  -- Metadata for filtering
  section VARCHAR(255),
  tags TEXT[],
  version VARCHAR(50),
  
  created_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT unique_chunk UNIQUE (document_id, chunk_index)
);

CREATE INDEX idx_chunks_document_id ON doc_chunks(document_id);
CREATE INDEX idx_chunks_tags ON doc_chunks USING gin(tags);

Vector Indexes: HNSW vs IVFFlat

Without indexes, vector search is slow (full table scan). Indexes make queries ~100x faster.

HNSW Index (Recommended)

Hierarchical Navigable Small World - graph-based index.

-- Create HNSW index (recommended for most use cases)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Different distance functions
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);   -- Cosine distance
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops);       -- Euclidean distance
CREATE INDEX ON documents USING hnsw (embedding vector_ip_ops);       -- Inner product

HNSW Configuration:

-- Customize HNSW parameters
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- m: max connections per layer (default: 16, higher = better recall, more memory)
-- ef_construction: size of candidate list during index build (default: 64)

HNSW Characteristics:

• ✅ Better recall (more accurate results)

• ✅ Faster queries

• ✅ Good for high-dimensional vectors

• ❌ Slower index build time

• ❌ More memory usage

IVFFlat Index

Inverted File with Flat compression - clustering-based index.

-- Create IVFFlat index
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- lists: number of clusters (rule of thumb: rows / 1000)
-- 10,000 rows → lists = 10
-- 100,000 rows → lists = 100
-- 1,000,000 rows → lists = 1000

IVFFlat Characteristics:

• ✅ Faster index build

• ✅ Lower memory usage

• ✅ Good for millions of vectors

• ❌ Lower recall (less accurate)

• ❌ Slower queries than HNSW

Which Index Should You Use?

Use Case	Index Type	Configuration
< 1M vectors, accuracy critical	HNSW	m=16, ef_construction=64
High-dimensional (>1000)	HNSW	m=24, ef_construction=128
> 10M vectors, memory-constrained	IVFFlat	lists = rows / 1000
Development/testing	None	No index (fast writes, slow reads)

My default: HNSW with cosine distance for text embeddings.

Create Index After Loading Data

-- 1. Load all data first (faster than inserting with index)
INSERT INTO documents (title, content, embedding)
VALUES (...);  -- Bulk insert all rows

-- 2. Then create index
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Monitor index creation progress
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE '%documents%';

Prisma Schema for pgvector

Prisma doesn't natively support pgvector, but we can use Unsupported type and raw SQL:

// schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  extensions = [vector]
}

model Document {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  embedding Unsupported("vector(1536)")?  // pgvector type
  createdAt DateTime @default(now()) @map("created_at")

  @@index([embedding], type: Hnsw, name: "document_embedding_idx")
  @@map("documents")
}

model Product {
  id          Int      @id @default(autoincrement())
  name        String   @unique
  description String?
  category    String?
  price       Decimal  @db.Decimal(10, 2)
  tags        String[]
  
  embedding   Unsupported("vector(1536)")?
  
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  @@index([category])
  @@index([price])
  @@index([embedding], type: Hnsw, name: "product_embedding_idx")
  @@map("products")
}

Note: Prisma doesn't generate TypeScript types for Unsupported fields. Use raw queries for vector operations.

Migration

# Generate migration
npx prisma migrate dev --name add_vector_columns

# This creates SQL migration files

Or create manual migration:

-- migrations/20240101000000_add_pgvector/migration.sql
-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Add embedding column
ALTER TABLE documents ADD COLUMN embedding vector(1536);

-- Create index
CREATE INDEX document_embedding_idx ON documents 
USING hnsw (embedding vector_cosine_ops);

Loading Embeddings into PostgreSQL

Method 1: Prisma with Raw SQL (Recommended)

import { PrismaClient } from '@prisma/client';
import { OpenAI } from 'openai';

const prisma = new PrismaClient();
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });

async function loadDocuments(docs: Array<{ title: string; content: string }>) {
  for (const doc of docs) {
    // Generate embedding
    const response = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: `${doc.title} ${doc.content}`,
    });
    const embedding = response.data[0].embedding;
    
    // Insert with raw SQL (Prisma doesn't support vector type directly)
    await prisma.$executeRaw`
      INSERT INTO documents (title, content, embedding)
      VALUES (${doc.title}, ${doc.content}, ${embedding}::vector)
    `;
  }
}

Method 2: Bulk Insert with COPY (Fastest)

import { exec } from 'child_process';
import { promisify } from 'util';
import * as fs from 'fs';

const execAsync = promisify(exec);

async function bulkLoadDocuments(docs: Array<{
  title: string;
  content: string;
  embedding: number[];
}>) {
  // Create CSV with vector data
  const csvLines = docs.map(doc => {
    const vectorStr = `[${doc.embedding.join(',')}]`;
    return `"${doc.title}","${doc.content}","${vectorStr}"`;
  });
  
  const csvContent = csvLines.join('\n');
  fs.writeFileSync('/tmp/documents.csv', csvContent);
  
  // Use COPY for fast bulk insert
  await execAsync(`
    psql ${process.env.DATABASE_URL} -c "
      COPY documents (title, content, embedding)
      FROM '/tmp/documents.csv'
      WITH (FORMAT csv, DELIMITER ',')
    "
  `);
  
  fs.unlinkSync('/tmp/documents.csv');
}

Method 3: Transaction for Consistency

async function loadDocumentsTransaction(docs: Array<{ title: string; content: string }>) {
  await prisma.$transaction(async (tx) => {
    for (const doc of docs) {
      const embedding = await getEmbedding(`${doc.title} ${doc.content}`);
      
      await tx.$executeRaw`
        INSERT INTO documents (title, content, embedding)
        VALUES (${doc.title}, ${doc.content}, ${embedding}::vector)
      `;
    }
  });
}

Testing Vector Queries

Basic Similarity Search

-- Find similar documents to a query vector
SELECT 
  id,
  title,
  content,
  1 - (embedding <=> '[0.1, 0.2, ...]'::vector) as similarity
FROM documents
WHERE 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) > 0.7
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

With TypeScript

async function searchDocs(query: string, limit: number = 10) {
  const queryVector = await getEmbedding(query);
  
  const results = await prisma.$queryRaw<Array<{
    id: number;
    title: string;
    content: string;
    similarity: number;
  }>>`
    SELECT 
      id,
      title,
      content,
      1 - (embedding <=> ${queryVector}::vector) as similarity
    FROM documents
    WHERE 1 - (embedding <=> ${queryVector}::vector) > 0.7
    ORDER BY embedding <=> ${queryVector}::vector
    LIMIT ${limit}
  `;
  
  return results;
}

// Test
const results = await searchDocs("How to deploy containers?");
console.log(results);

Verify Index Usage

-- Check if index is being used
EXPLAIN ANALYZE
SELECT id, title, 1 - (embedding <=> '[...]'::vector) as similarity
FROM documents
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

-- Look for "Index Scan using hnsw_..." in output
-- If you see "Seq Scan", index is not being used!

Performance Tuning

Set Work Memory for Index Building

-- Increase work memory for faster index creation
SET maintenance_work_mem = '2GB';

CREATE INDEX document_embedding_idx ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Reset
RESET maintenance_work_mem;

Query Performance Tuning

-- Set probes for IVFFlat (higher = more accurate, slower)
SET ivfflat.probes = 10;  -- Default is 1

-- Set ef_search for HNSW (higher = more accurate, slower)
SET hnsw.ef_search = 40;  -- Default matches ef_construction

Monitor Query Performance

async function searchWithProfiler(query: string) {
  const start = Date.now();
  
  const queryVector = await getEmbedding(query);
  const embeddingTime = Date.now() - start;
  
  const searchStart = Date.now();
  const results = await prisma.$queryRaw`
    SELECT id, title, 1 - (embedding <=> ${queryVector}::vector) as similarity
    FROM documents
    ORDER BY embedding <=> ${queryVector}::vector
    LIMIT 10
  `;
  const searchTime = Date.now() - searchStart;
  
  console.log(`Embedding: ${embeddingTime}ms, Search: ${searchTime}ms, Total: ${embeddingTime + searchTime}ms`);
  
  return results;
}

Common Issues and Solutions

Issue: Index not being used

-- Check table statistics
ANALYZE documents;

-- Rebuild index
REINDEX INDEX document_embedding_idx;

Issue: Slow index creation

-- Increase resources
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;

-- Use fewer construction steps
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 8, ef_construction = 32);  -- Lower values = faster build

Issue: Out of memory

-- Use IVFFlat instead of HNSW
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Or increase PostgreSQL memory limits in postgresql.conf
shared_buffers = 4GB
effective_cache_size = 12GB

Complete Setup Script

#!/bin/bash
# setup-pgvector.sh

# 1. Start PostgreSQL with Docker
docker run -d \
  --name postgres-vector \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=vectordb \
  -p 5432:5432 \
  ankane/pgvector

# 2. Wait for PostgreSQL to start
sleep 5

# 3. Initialize database
docker exec -it postgres-vector psql -U postgres -d vectordb <<EOF
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding VECTOR(1536),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX document_embedding_idx ON documents 
USING hnsw (embedding vector_cosine_ops);

SELECT 'Setup complete!' as status;
EOF

echo "PostgreSQL with pgvector is ready!"

chmod +x setup-pgvector.sh
./setup-pgvector.sh

What's Next

In this article, you learned:

• ✅ Installing PostgreSQL with pgvector (Docker, macOS, Linux)

• ✅ Enabling pgvector extension

• ✅ Creating tables with vector columns

• ✅ HNSW vs IVFFlat indexes (and when to use each)

• ✅ Prisma schema for vectors

• ✅ Loading embeddings into PostgreSQL

• ✅ Query performance tuning

Next: We'll build a complete TypeScript application with semantic search, hybrid queries, and production-ready error handling.

---

← Part 2: Vector Embeddings | Part 4: TypeScript Implementation →