PostgreSQL pgVector: Complete Guide to Vector Similarity Search

-- Vector similarity search example
SELECT 
    id,
    content,
    embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3]'::vector
LIMIT 5;

-- Available distance operators in pgVector
SELECT 
    '[1,2,3]'::vector <-> '[1,2,4]'::vector AS l2_distance,      -- Euclidean
    '[1,2,3]'::vector <#> '[1,2,4]'::vector AS max_inner_product, -- Negative inner product
    '[1,2,3]'::vector <=> '[1,2,4]'::vector AS cosine_distance;   -- Cosine distance

# Install dependencies
sudo apt update
sudo apt install postgresql postgresql-contrib postgresql-server-dev-15 git build-essential

# Clone and build pgVector
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector

# Compile and install
make
sudo make install

# Restart PostgreSQL
sudo systemctl restart postgresql

# Install dependencies
sudo yum install postgresql15-server postgresql15-devel git gcc make

# Clone and build pgVector
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector

# Set PATH for pg_config
export PATH=/usr/pgsql-15/bin:$PATH

# Compile and install
make
sudo make install

# Restart PostgreSQL
sudo systemctl restart postgresql-15

# Dockerfile for PostgreSQL with pgVector
FROM postgres:15

# Install build dependencies
RUN apt-get update && apt-get install -y \
    build-essential \
    git \
    postgresql-server-dev-15

# Clone and build pgVector
RUN git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git /tmp/pgvector \
    && cd /tmp/pgvector \
    && make clean \
    && make OPTFLAGS="" \
    && make install \
    && rm -rf /tmp/pgvector

# Cleanup
RUN apt-get remove -y build-essential git postgresql-server-dev-15 \
    && apt-get autoremove -y \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# docker-compose.yml
version: '3.8'
services:
  postgres-vector:
    build: .
    container_name: postgres-vector
    environment:
      POSTGRES_DB: vectordb
      POSTGRES_USER: vector_user
      POSTGRES_PASSWORD: vector_pass
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: >
      postgres 
      -c shared_preload_libraries=vector
      -c max_connections=200
      -c shared_buffers=256MB
      -c effective_cache_size=1GB
      -c maintenance_work_mem=64MB
      -c random_page_cost=1.1

volumes:
  postgres_data:

-- Connect to PostgreSQL and enable pgVector
\c vectordb

-- Create extension
CREATE EXTENSION IF NOT EXISTS vector;

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

-- Test basic functionality
SELECT '[1,2,3]'::vector <-> '[1,2,4]'::vector AS distance;

-- Create table with vector column
CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536),  -- OpenAI embedding dimension
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Alternative: Variable dimension vectors
CREATE TABLE flexible_vectors (
    id SERIAL PRIMARY KEY,
    name TEXT,
    features vector,  -- No dimension specified
    category TEXT
);

-- Insert vector data
INSERT INTO embeddings (content, embedding) VALUES 
('Hello world', '[0.1, 0.2, 0.3, 0.4]'),
('Good morning', '[0.2, 0.1, 0.4, 0.3]'),
('How are you?', '[0.3, 0.4, 0.1, 0.2]');

-- Vector arithmetic
SELECT 
    '[1,2,3]'::vector + '[4,5,6]'::vector AS addition,
    '[1,2,3]'::vector - '[4,5,6]'::vector AS subtraction,
    '[1,2,3]'::vector * '[4,5,6]'::vector AS element_wise_multiply;

-- Vector functions
SELECT 
    vector_dims('[1,2,3,4]'::vector) AS dimensions,
    vector_norm('[3,4]'::vector) AS l2_norm,
    '[1,2,3]'::vector <-> '[1,2,4]'::vector AS l2_distance,
    '[1,2,3]'::vector <#> '[1,2,4]'::vector AS negative_inner_product,
    '[1,2,3]'::vector <=> '[1,2,4]'::vector AS cosine_distance;

-- Normalize vectors
UPDATE embeddings 
SET embedding = embedding / vector_norm(embedding)
WHERE vector_norm(embedding) > 0;

-- Vector aggregation functions
SELECT 
    AVG(embedding) AS centroid,
    vector_norm(AVG(embedding)) AS centroid_norm
FROM embeddings;

-- Find vectors within a distance threshold
SELECT 
    id,
    content,
    embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector AS distance
FROM embeddings
WHERE embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector < 0.5
ORDER BY distance;

-- Vector clustering with k-means simulation
WITH cluster_centers AS (
    SELECT 
        ROW_NUMBER() OVER () as cluster_id,
        embedding as center
    FROM embeddings
    TABLESAMPLE SYSTEM(10)  -- Random sample as initial centers
),
assignments AS (
    SELECT 
        e.id,
        e.embedding,
        (SELECT cluster_id 
         FROM cluster_centers c
         ORDER BY e.embedding <-> c.center
         LIMIT 1) as assigned_cluster
    FROM embeddings e
)
SELECT 
    assigned_cluster,
    COUNT(*) as cluster_size,
    AVG(embedding) as cluster_centroid
FROM assignments
GROUP BY assigned_cluster;

-- Create IVFFlat index for approximate nearest neighbor search
CREATE INDEX ON embeddings 
USING ivfflat (embedding vector_l2_ops) 
WITH (lists = 100);

-- Different distance metrics
CREATE INDEX ON embeddings 
USING ivfflat (embedding vector_ip_ops) 
WITH (lists = 100);

CREATE INDEX ON embeddings 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

-- Index usage and performance
EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, content
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
LIMIT 10;

-- Create HNSW index (better for high-dimensional data)
CREATE INDEX ON embeddings 
USING hnsw (embedding vector_l2_ops) 
WITH (m = 16, ef_construction = 64);

-- Tuning HNSW parameters
-- m: maximum number of connections (higher = better recall, more memory)
-- ef_construction: size of dynamic candidate list (higher = better quality, slower build)

-- For high precision requirements
CREATE INDEX ON embeddings 
USING hnsw (embedding vector_l2_ops) 
WITH (m = 32, ef_construction = 128);

-- For faster searches with slightly lower precision
CREATE INDEX ON embeddings 
USING hnsw (embedding vector_l2_ops) 
WITH (m = 8, ef_construction = 32);

-- Compare index performance
-- Note: Enable extension for query plan analysis
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;

-- Test IVFFlat performance
SET ivfflat.probes = 1;  -- Faster, lower recall
SELECT COUNT(*) FROM (
    SELECT id FROM embeddings
    ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
    LIMIT 100
) t;

SET ivfflat.probes = 10;  -- Slower, higher recall
SELECT COUNT(*) FROM (
    SELECT id FROM embeddings
    ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
    LIMIT 100
) t;

-- Test HNSW performance
SET hnsw.ef_search = 40;  -- Faster, lower recall
SELECT COUNT(*) FROM (
    SELECT id FROM embeddings
    ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
    LIMIT 100
) t;

SET hnsw.ef_search = 200;  -- Slower, higher recall
SELECT COUNT(*) FROM (
    SELECT id FROM embeddings
    ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
    LIMIT 100
) t;

-- Monitor index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE '%embedding%';

-- Rebuild indexes if needed
REINDEX INDEX embeddings_embedding_idx;

-- Monitor index bloat
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'embeddings' AND attname = 'embedding';

-- Find most similar vectors
SELECT 
    id,
    content,
    embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector AS l2_distance,
    1 - (embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector) AS cosine_similarity
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
LIMIT 10;

-- Find vectors within similarity threshold
SELECT 
    id,
    content,
    1 - (embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector) AS similarity
FROM embeddings
WHERE 1 - (embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector) > 0.8
ORDER BY similarity DESC;

-- Semantic search with metadata filtering
SELECT 
    id,
    content,
    metadata,
    embedding <-> $1::vector AS distance
FROM embeddings
WHERE metadata->>'category' = 'technology'
  AND metadata->>'language' = 'en'
ORDER BY embedding <-> $1::vector
LIMIT 20;

-- Multi-vector search (find items similar to multiple queries)
WITH query_vectors AS (
    SELECT unnest(ARRAY[
        '[0.1, 0.2, 0.3, 0.4]'::vector,
        '[0.2, 0.1, 0.4, 0.3]'::vector,
        '[0.3, 0.4, 0.1, 0.2]'::vector
    ]) AS query_embedding
),
similarities AS (
    SELECT 
        e.id,
        e.content,
        q.query_embedding,
        e.embedding <-> q.query_embedding AS distance
    FROM embeddings e
    CROSS JOIN query_vectors q
)
SELECT 
    id,
    content,
    MIN(distance) AS min_distance,
    AVG(distance) AS avg_distance
FROM similarities
GROUP BY id, content
ORDER BY min_distance
LIMIT 10;

-- Diversity-aware search (avoid too similar results)
WITH ranked_results AS (
    SELECT 
        id,
        content,
        embedding,
        embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector AS distance,
        ROW_NUMBER() OVER (ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector) as rank
    FROM embeddings
),
diverse_results AS (
    SELECT 
        r1.id,
        r1.content,
        r1.distance,
        r1.rank
    FROM ranked_results r1
    WHERE NOT EXISTS (
        SELECT 1 FROM ranked_results r2
        WHERE r2.rank < r1.rank
          AND r1.embedding <-> r2.embedding < 0.3  -- Diversity threshold
    )
)
SELECT * FROM diverse_results
ORDER BY rank
LIMIT 10;

-- Combine vector similarity with text search
SELECT 
    id,
    content,
    ts_rank(to_tsvector('english', content), plainto_tsquery('machine learning')) AS text_score,
    1 - (embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector) AS vector_score,
    (0.7 * (1 - (embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector)) + 
     0.3 * ts_rank(to_tsvector('english', content), plainto_tsquery('machine learning'))) AS combined_score
FROM embeddings
WHERE to_tsvector('english', content) @@ plainto_tsquery('machine learning')
   OR embedding <=> '[0.1, 0.2, 0.3, 0.4]'::vector < 0.5
ORDER BY combined_score DESC
LIMIT 20;

-- Create hybrid search function
CREATE OR REPLACE FUNCTION hybrid_search(
    query_text TEXT,
    query_vector vector,
    text_weight FLOAT DEFAULT 0.3,
    vector_weight FLOAT DEFAULT 0.7,
    result_limit INT DEFAULT 20
)
RETURNS TABLE(
    id INT,
    content TEXT,
    text_score FLOAT,
    vector_score FLOAT,
    combined_score FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        e.id,
        e.content,
        ts_rank(to_tsvector('english', e.content), plainto_tsquery(query_text)) AS text_score,
        (1 - (e.embedding <=> query_vector)) AS vector_score,
        (text_weight * ts_rank(to_tsvector('english', e.content), plainto_tsquery(query_text)) + 
         vector_weight * (1 - (e.embedding <=> query_vector))) AS combined_score
    FROM embeddings e
    WHERE to_tsvector('english', e.content) @@ plainto_tsquery(query_text)
       OR e.embedding <=> query_vector < 0.5
    ORDER BY combined_score DESC
    LIMIT result_limit;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM hybrid_search(
    'artificial intelligence',
    '[0.1, 0.2, 0.3, 0.4]'::vector,
    0.4,  -- text weight
    0.6,  -- vector weight
    10    -- limit
);

-- Optimize search parameters
SET ivfflat.probes = 10;  -- Increase for better recall
SET hnsw.ef_search = 100; -- Increase for better recall

-- Use covering indexes for better performance
CREATE INDEX ON embeddings (metadata) 
INCLUDE (id, content) 
WHERE metadata->>'category' = 'technology';

-- Parallel query processing
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.1;
SET parallel_setup_cost = 1000;

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, content
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, 0.3, 0.4]'::vector
LIMIT 10;

-- PostgreSQL configuration for vector workloads
-- Add to postgresql.conf

/*
shared_buffers = 256MB                    # Cache frequently accessed data
effective_cache_size = 1GB               # Help query planner
work_mem = 64MB                          # Memory for sorts/hashes
maintenance_work_mem = 256MB             # Memory for maintenance operations
random_page_cost = 1.1                   # SSD-optimized
effective_io_concurrency = 200          # Number of concurrent I/O operations

# Vector-specific settings
max_parallel_workers_per_gather = 4     # Parallel query execution
max_parallel_workers = 8                # Total parallel workers
parallel_tuple_cost = 0.1               # Cost of transferring tuples
parallel_setup_cost = 1000              # Cost of setting up parallel query
*/

-- Monitor memory usage
SELECT 
    name,
    setting,
    unit,
    context
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'effective_cache_size',
    'work_mem',
    'maintenance_work_mem'
);

-- Efficient bulk insert with COPY
COPY embeddings (content, embedding) FROM STDIN WITH CSV;
"Document 1","[0.1,0.2,0.3,0.4]"
"Document 2","[0.2,0.1,0.4,0.3]"
\.

-- Batch updates for better performance
UPDATE embeddings 
SET embedding = embedding / vector_norm(embedding)
WHERE id BETWEEN 1000 AND 2000 
  AND vector_norm(embedding) > 0;

-- Use transactions for consistency
BEGIN;
INSERT INTO embeddings (content, embedding) VALUES 
('Doc 1', '[0.1,0.2,0.3,0.4]'),
('Doc 2', '[0.2,0.1,0.4,0.3]'),
('Doc 3', '[0.3,0.4,0.1,0.2]');
COMMIT;

-- Create monitoring views
CREATE VIEW vector_index_stats AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexname LIKE '%vector%' OR indexname LIKE '%embedding%';

-- Monitor query performance
CREATE VIEW slow_vector_queries AS
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query LIKE '%vector%' OR query LIKE '%embedding%'
ORDER BY mean_time DESC;

-- Check for unused indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND (indexname LIKE '%vector%' OR indexname LIKE '%embedding%');

# Python application for semantic search
import asyncpg
import numpy as np
from sentence_transformers import SentenceTransformer
import asyncio

class SemanticSearch:
    def __init__(self, db_config, model_name='all-MiniLM-L6-v2'):
        self.db_config = db_config
        self.model = SentenceTransformer(model_name)
        self.pool = None
    
    async def initialize(self):
        """Initialize database connection pool"""
        self.pool = await asyncpg.create_pool(**self.db_config)
    
    async def add_document(self, content, metadata=None):
        """Add document with embedding to database"""
        # Generate embedding
        embedding = self.model.encode(content).tolist()
        
        async with self.pool.acquire() as conn:
            await conn.execute("""
                INSERT INTO embeddings (content, embedding, metadata)
                VALUES ($1, $2, $3)
            """, content, embedding, metadata or {})
    
    async def search(self, query, limit=10, threshold=None):
        """Search for similar documents"""
        # Generate query embedding
        query_embedding = self.model.encode(query).tolist()
        
        sql = """
            SELECT 
                id,
                content,
                metadata,
                1 - (embedding <=> $1::vector) AS similarity
            FROM embeddings
        """
        
        params = [query_embedding]
        
        if threshold:
            sql += " WHERE 1 - (embedding <=> $1::vector) > $2"
            params.append(threshold)
        
        sql += " ORDER BY embedding <=> $1::vector LIMIT ${}".format(
            len(params) + 1
        )
        params.append(limit)
        
        async with self.pool.acquire() as conn:
            rows = await conn.fetch(sql, *params)
            return [dict(row) for row in rows]
    
    async def hybrid_search(self, query, text_weight=0.3, vector_weight=0.7, limit=10):
        """Perform hybrid text + vector search"""
        query_embedding = self.model.encode(query).tolist()
        
        async with self.pool.acquire() as conn:
            rows = await conn.fetch("""
                SELECT 
                    id,
                    content,
                    metadata,
                    ts_rank(to_tsvector('english', content), plainto_tsquery($2)) AS text_score,
                    1 - (embedding <=> $1::vector) AS vector_score,
                    ($3 * ts_rank(to_tsvector('english', content), plainto_tsquery($2)) + 
                     $4 * (1 - (embedding <=> $1::vector))) AS combined_score
                FROM embeddings
                WHERE to_tsvector('english', content) @@ plainto_tsquery($2)
                   OR embedding <=> $1::vector < 0.5
                ORDER BY combined_score DESC
                LIMIT $5
            """, query_embedding, query, text_weight, vector_weight, limit)
            
            return [dict(row) for row in rows]

# Usage example
async def main():
    db_config = {
        'host': 'localhost',
        'port': 5432,
        'user': 'vector_user',
        'password': 'vector_pass',
        'database': 'vectordb'
    }
    
    search_engine = SemanticSearch(db_config)
    await search_engine.initialize()
    
    # Add documents
    documents = [
        "Machine learning is a subset of artificial intelligence",
        "Deep learning uses neural networks with multiple layers",
        "Natural language processing helps computers understand text",
        "Computer vision enables machines to interpret visual information"
    ]
    
    for doc in documents:
        await search_engine.add_document(doc, {'category': 'ai'})
    
    # Search
    results = await search_engine.search("AI and neural networks", limit=5)
    for result in results:
        print(f"Similarity: {result['similarity']:.3f} - {result['content']}")

# Run the example
# asyncio.run(main())

-- User and item embeddings for recommendation
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT,
    user_embedding vector(128),
    preferences JSONB
);

CREATE TABLE items (
    item_id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT,
    item_embedding vector(128),
    category TEXT,
    metadata JSONB
);

CREATE TABLE user_interactions (
    user_id INT REFERENCES users(user_id),
    item_id INT REFERENCES items(item_id),
    interaction_type TEXT, -- 'view', 'like', 'purchase', etc.
    rating FLOAT,
    timestamp TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, item_id, interaction_type)
);

-- Find similar users
CREATE OR REPLACE FUNCTION find_similar_users(
    target_user_id INT,
    similarity_threshold FLOAT DEFAULT 0.7,
    limit_count INT DEFAULT 10
)
RETURNS TABLE(
    similar_user_id INT,
    username TEXT,
    similarity FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.user_id,
        u.username,
        1 - (tu.user_embedding <=> u.user_embedding) AS similarity
    FROM users u
    CROSS JOIN (SELECT user_embedding FROM users WHERE user_id = target_user_id) tu
    WHERE u.user_id != target_user_id
      AND 1 - (tu.user_embedding <=> u.user_embedding) > similarity_threshold
    ORDER BY similarity DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- Generate recommendations based on similar users
CREATE OR REPLACE FUNCTION recommend_items(
    target_user_id INT,
    recommendation_count INT DEFAULT 10
)
RETURNS TABLE(
    item_id INT,
    title TEXT,
    predicted_rating FLOAT,
    recommendation_strength FLOAT
) AS $$
BEGIN
    RETURN QUERY
    WITH similar_users AS (
        SELECT * FROM find_similar_users(target_user_id, 0.6, 20)
    ),
    user_item_scores AS (
        SELECT 
            ui.item_id,
            i.title,
            AVG(ui.rating * su.similarity) AS weighted_rating,
            COUNT(*) AS recommendation_count,
            AVG(su.similarity) AS avg_similarity
        FROM user_interactions ui
        JOIN similar_users su ON ui.user_id = su.similar_user_id
        JOIN items i ON ui.item_id = i.item_id
        WHERE ui.item_id NOT IN (
            SELECT DISTINCT item_id 
            FROM user_interactions 
            WHERE user_id = target_user_id
        )
        GROUP BY ui.item_id, i.title
        HAVING COUNT(*) >= 2  -- At least 2 similar users liked it
    )
    SELECT 
        uis.item_id,
        uis.title,
        uis.weighted_rating,
        uis.avg_similarity * uis.recommendation_count AS recommendation_strength
    FROM user_item_scores uis
    ORDER BY recommendation_strength DESC, weighted_rating DESC
    LIMIT recommendation_count;
END;
$$ LANGUAGE plpgsql;

-- Content-based recommendations using item similarity
CREATE OR REPLACE FUNCTION recommend_similar_items(
    reference_item_id INT,
    recommendation_count INT DEFAULT 10
)
RETURNS TABLE(
    item_id INT,
    title TEXT,
    category TEXT,
    similarity FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        i.item_id,
        i.title,
        i.category,
        1 - (ri.item_embedding <=> i.item_embedding) AS similarity
    FROM items i
    CROSS JOIN (SELECT item_embedding FROM items WHERE item_id = reference_item_id) ri
    WHERE i.item_id != reference_item_id
    ORDER BY similarity DESC
    LIMIT recommendation_count;
END;
$$ LANGUAGE plpgsql;

# RAG implementation with pgVector
import asyncpg
import openai
from sentence_transformers import SentenceTransformer
import numpy as np

class RAGSystem:
    def __init__(self, db_config, openai_api_key):
        self.db_config = db_config
        self.embedding_model = SentenceTransformer('all-MiniLM-L6-v2')
        openai.api_key = openai_api_key
        self.pool = None
    
    async def initialize(self):
        """Initialize database connection"""
        self.pool = await asyncpg.create_pool(**self.db_config)
        
        # Create knowledge base table
        async with self.pool.acquire() as conn:
            await conn.execute("""
                CREATE TABLE IF NOT EXISTS knowledge_base (
                    id SERIAL PRIMARY KEY,
                    content TEXT,
                    embedding vector(384),
                    source TEXT,
                    metadata JSONB,
                    created_at TIMESTAMP DEFAULT NOW()
                )
            """)
            
            # Create index for fast similarity search
            await conn.execute("""
                CREATE INDEX IF NOT EXISTS knowledge_base_embedding_idx 
                ON knowledge_base USING hnsw (embedding vector_cosine_ops)
                WITH (m = 16, ef_construction = 64)
            """)
    
    async def add_knowledge(self, content, source=None, metadata=None):
        """Add content to knowledge base"""
        embedding = self.embedding_model.encode(content).tolist()
        
        async with self.pool.acquire() as conn:
            await conn.execute("""
                INSERT INTO knowledge_base (content, embedding, source, metadata)
                VALUES ($1, $2, $3, $4)
            """, content, embedding, source, metadata or {})
    
    async def retrieve_context(self, query, top_k=5, similarity_threshold=0.7):
        """Retrieve relevant context for query"""
        query_embedding = self.embedding_model.encode(query).tolist()
        
        async with self.pool.acquire() as conn:
            rows = await conn.fetch("""
                SELECT 
                    content,
                    source,
                    metadata,
                    1 - (embedding <=> $1::vector) AS similarity
                FROM knowledge_base
                WHERE 1 - (embedding <=> $1::vector) > $2
                ORDER BY embedding <=> $1::vector
                LIMIT $3
            """, query_embedding, similarity_threshold, top_k)
            
            return [dict(row) for row in rows]
    
    async def generate_answer(self, query, context_docs, max_tokens=500):
        """Generate answer using retrieved context"""
        context = "\n\n".join([
            f"Source: {doc['source']}\nContent: {doc['content']}"
            for doc in context_docs
        ])
        
        prompt = f"""
        Based on the following context, answer the question. If the context doesn't contain enough information to answer the question, say so.

        Context:
        {context}

        Question: {query}

        Answer:
        """
        
        response = await openai.ChatCompletion.acreate(
            model="gpt-3.5-turbo",
            messages=[{"role": "user", "content": prompt}],
            max_tokens=max_tokens,
            temperature=0.7
        )
        
        return response.choices[0].message.content
    
    async def answer_question(self, query, top_k=5, similarity_threshold=0.7):
        """Complete RAG pipeline: retrieve and generate"""
        # Retrieve relevant context
        context_docs = await self.retrieve_context(query, top_k, similarity_threshold)
        
        if not context_docs:
            return {
                'answer': "I couldn't find relevant information to answer your question.",
                'sources': [],
                'confidence': 0.0
            }
        
        # Generate answer
        answer = await self.generate_answer(query, context_docs)
        
        return {
            'answer': answer,
            'sources': [doc['source'] for doc in context_docs],
            'context_similarity': [doc['similarity'] for doc in context_docs],
            'confidence': max([doc['similarity'] for doc in context_docs])
        }

# Usage example
async def rag_example():
    db_config = {
        'host': 'localhost',
        'port': 5432,
        'user': 'vector_user',
        'password': 'vector_pass', 
        'database': 'vectordb'
    }
    
    rag = RAGSystem(db_config, 'your-openai-api-key')
    await rag.initialize()
    
    # Add knowledge to the system
    knowledge_articles = [
        {
            'content': 'PostgreSQL is a powerful, open source object-relational database system.',
            'source': 'PostgreSQL Documentation',
            'metadata': {'topic': 'databases', 'type': 'definition'}
        },
        {
            'content': 'pgVector is a PostgreSQL extension for vector similarity search.',
            'source': 'pgVector GitHub',
            'metadata': {'topic': 'extensions', 'type': 'definition'}
        }
    ]
    
    for article in knowledge_articles:
        await rag.add_knowledge(**article)
    
    # Ask a question
    result = await rag.answer_question("What is pgVector?")
    print(f"Answer: {result['answer']}")
    print(f"Sources: {result['sources']}")
    print(f"Confidence: {result['confidence']:.3f}")

# Run the example
# asyncio.run(rag_example())

-- Create dedicated vector search role
CREATE ROLE vector_search_user;

-- Grant specific permissions
GRANT SELECT ON embeddings TO vector_search_user;
GRANT USAGE ON SCHEMA public TO vector_search_user;

-- Create read-only user for applications
CREATE USER app_search_user WITH PASSWORD 'secure_password';
GRANT vector_search_user TO app_search_user;

-- Row-level security for multi-tenant applications
ALTER TABLE embeddings ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON embeddings
FOR ALL TO vector_search_user
USING (metadata->>'tenant_id' = current_setting('app.tenant_id'));

-- Function to set tenant context
CREATE OR REPLACE FUNCTION set_tenant_context(tenant_id TEXT)
RETURNS void AS $$
BEGIN
    PERFORM set_config('app.tenant_id', tenant_id, true);
END;
$$ LANGUAGE plpgsql;

-- Create monitoring view for vector operations
CREATE VIEW vector_performance_stats AS
SELECT 
    schemaname,
    tablename,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename LIKE '%embedding%' OR tablename LIKE '%vector%';

-- Monitor index usage and effectiveness
CREATE VIEW vector_index_performance AS
SELECT 
    t.schemaname,
    t.tablename,
    i.indexname,
    i.idx_scan,
    i.idx_tup_read,
    i.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size,
    round(100.0 * pg_relation_size(i.indexrelid) / pg_relation_size(t.relid), 2) AS index_ratio
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables t ON i.relid = t.relid
WHERE i.indexname LIKE '%vector%' OR i.indexname LIKE '%embedding%';

-- Query performance monitoring
CREATE VIEW slow_vector_queries AS
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    stddev_time,
    rows,
    100.0 * shared_blks_hit / 
        nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query ~* 'vector|embedding|<->|<#>|<=>'
ORDER BY mean_time DESC;

#!/bin/bash
# Vector database backup script

DB_NAME="vectordb"
BACKUP_DIR="/backups/vectordb"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Backup with custom format for flexibility
pg_dump -Fc "$DB_NAME" > "$BACKUP_DIR/vectordb_${TIMESTAMP}.dump"

# Backup specific vector tables with data
pg_dump -t embeddings -t knowledge_base --data-only --inserts \
    "$DB_NAME" > "$BACKUP_DIR/vector_data_${TIMESTAMP}.sql"

# Compress old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -exec gzip {} \;

# Remove old backups (keep 30 days)
find "$BACKUP_DIR" -name "*.gz" -mtime +30 -delete

echo "Backup completed: $BACKUP_DIR/vectordb_${TIMESTAMP}.dump"

-- Monitor storage growth
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size,
    n_live_tup as row_count
FROM pg_stat_user_tables
WHERE tablename LIKE '%vector%' OR tablename LIKE '%embedding%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Estimate vector storage requirements
WITH vector_stats AS (
    SELECT 
        COUNT(*) as vector_count,
        vector_dims(embedding) as dimensions,
        AVG(LENGTH(content)) as avg_content_length
    FROM embeddings
    WHERE embedding IS NOT NULL
    LIMIT 1
)
SELECT 
    vector_count,
    dimensions,
    round(avg_content_length) as avg_content_length,
    pg_size_pretty(vector_count * dimensions * 4) as vector_storage,  -- 4 bytes per float
    pg_size_pretty(vector_count * avg_content_length) as text_storage
FROM vector_stats;

-- Monitor query patterns
SELECT 
    date_trunc('hour', query_start) as hour,
    count(*) as query_count,
    avg(duration) as avg_duration_ms,
    max(duration) as max_duration_ms
FROM pg_stat_activity
WHERE query ~* 'vector|embedding|<->|<#>|<=>'
  AND state = 'active'
GROUP BY hour
ORDER BY hour DESC;

-- Performance tuning recommendations

-- 1. Ensure proper indexing
SELECT 
    t.tablename,
    string_agg(i.indexname, ', ') as vector_indexes
FROM pg_stat_user_tables t
LEFT JOIN pg_stat_user_indexes i ON t.relid = i.relid 
    AND (i.indexname LIKE '%vector%' OR i.indexname LIKE '%embedding%')
WHERE t.tablename LIKE '%vector%' OR t.tablename LIKE '%embedding%'
GROUP BY t.tablename;

-- 2. Check for proper vacuum/analyze
SELECT 
    tablename,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / GREATEST(n_live_tup, 1), 2) as dead_tuple_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE tablename LIKE '%vector%' OR tablename LIKE '%embedding%';

-- 3. Monitor connection and memory usage
SELECT 
    current_setting('max_connections') as max_connections,
    current_setting('shared_buffers') as shared_buffers,
    current_setting('work_mem') as work_mem,
    current_setting('effective_cache_size') as effective_cache_size;

-- 4. Vector-specific settings
SELECT 
    name,
    setting,
    context
FROM pg_settings
WHERE name IN ('ivfflat.probes', 'hnsw.ef_search')
   OR name LIKE '%parallel%'
   OR name LIKE '%work_mem%';