pgvector

by damiencronw

Install this Skill

curl -fsSL -o pgvector.zip https://skill.seembox.com/api/v1/skills/pgvector/download && unzip pgvector.zip

SKILL.md About

---
name: pgvector
description: "PostgreSQL vector database skill with pgvector extension. Enables vector similarity search, embeddings storage, RAG (Retrieval-Augmented Generation) pipelines, and hybrid search combining vector and keyword search. Use when: storing/retrieving embeddings, building AI applications with vector search, implementing RAG, similarity matching, semantic search, or any use case requiring vector database functionality."
metadata:
  {
    "openclaw": { "emoji": "🔢" },
    "version": "1.0.0",
  }
---

# pgvector Skill

PostgreSQL + pgvector extension for vector similarity search.

## Quick Connect

```bash
# Connect to pgvector database (default port 5433)
psql -h localhost -p 5433 -U damien -d postgres

# Or use environment variables
export PGHOST=localhost
export PGPORT=5433
export PGUSER=damien
export PGPASSWORD=''
export PGDATABASE=postgres
```

## Environment

- **Host**: localhost
- **Port**: 5433
- **User**: damien
- **Password**: (empty)
- **Database**: postgres

## Core Capabilities

### 1. Create Vector Table

```sql
-- Basic vector table (1536 dimensions for OpenAI embeddings)
CREATE TABLE IF NOT EXISTS documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536) NOT NULL,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Or use IVFFlat index (faster build, slower search)
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
```

### 2. Insert Embeddings

```sql
-- Manual insert (replace with actual embedding)
INSERT INTO documents (content, embedding)
VALUES ('Your text here', '[0.1, 0.2, ..., 0.1536]');

-- With metadata
INSERT INTO documents (content, embedding, metadata)
VALUES (
    'AI is transforming technology',
    '[0.1, 0.3, ..., 0.5]',
    '{"source": "article", "author": "John"}'::jsonb
);
```

### 3. Vector Similarity Search

```sql
-- Cosine similarity (most common)
SELECT id, content, (1 - (embedding <=> '[query_embedding]')) AS similarity
FROM documents
ORDER BY embedding <=> '[query_embedding]'
LIMIT 5;

-- Euclidean distance
SELECT id, content, (embedding <-> '[query_embedding]') AS distance
FROM documents
ORDER BY embedding <-> '[query_embedding]'
LIMIT 5;

-- Inner product (for normalized vectors)
SELECT id, content, (embedding <#> '[query_embedding]') AS similarity
FROM documents
ORDER BY embedding <#> '[query_embedding]'
LIMIT 5;
```

### 4. Hybrid Search (Vector + Keyword)

```sql
-- Combine vector search with full-text search
SELECT id, content,
    (1 - (embedding <=> '[query_embedding]')) AS vector_score,
    ts_rank(to_tsvector('english', content), plainto_tsquery('english', 'search terms')) AS text_score
FROM documents
WHERE content ILIKE '%search terms%'
ORDER BY (vector_score * 0.7 + text_score * 0.3) DESC
LIMIT 10;
```

### 5. RAG Pipeline Example

```sql
-- Store document chunks with embeddings
CREATE TABLE document_chunks (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT REFERENCES documents(id),
    chunk_text TEXT NOT NULL,
    chunk_embedding vector(1536) NOT NULL,
    chunk_index INT NOT NULL
);

-- Retrieve relevant chunks for LLM context
SELECT chunk_text
FROM document_chunks
WHERE document_id = ?
ORDER BY chunk_embedding <=> '[question_embedding]'
LIMIT 5;
```

## Management Commands

### Check pgvector Extension

```sql
SELECT * FROM pg_extension WHERE extname = 'vector';
```

### Table Info

```sql
-- List all tables with vectors
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Check index sizes
SELECT pg_size_pretty(pg_total_relation_size('documents'));
```

### Monitoring

```sql
-- Check query performance
EXPLAIN ANALYZE
SELECT * FROM documents
ORDER BY embedding <=> '[query_embedding]'
LIMIT 5;

-- Index usage stats
SELECT * FROM pg_stat_user_indexes
WHERE indexname LIKE '%embedding%';
```

## Common Operations

### Update Embedding

```sql
UPDATE documents
SET embedding = '[new_embedding]'
WHERE id = 1;
```

### Delete

```sql
DELETE FROM documents WHERE id = 1;
```

### Batch Insert (Python)

```python
import psycopg2
import numpy as np

conn = psycopg2.connect(
    host="localhost",
    port=5433,
    user="damien",
    password="",
    database="postgres"
)

cur = conn.cursor()
for text, embedding in documents:
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
        (text, embedding.tolist())
    )
conn.commit()
```

## Distance Operators

| Operator | Description |
|----------|-------------|
| `<->` | Euclidean distance |
| `<=>` | Cosine distance |
| `<#>` | Inner product |
| `<=>` | Cosine distance (1 - cosine_similarity) |

## Use Cases

1. **Semantic Search** - Find documents by meaning, not keywords
2. **RAG** - Retrieve relevant context for LLM prompts  
3. **Recommendations** - Find similar items/products
4. **Anomaly Detection** - Find outliers in embeddings
5. **Image/Video Search** - Store and query visual embeddings

## Notes

- Vector dimensions must match your embedding model
- HNSW is better for accuracy, IVFFlat better for large datasets
- Normalize vectors for cosine similarity
- pgvector supports up to 16,000 dimensions

_meta.json Metadata

{
  "ownerId": "kn77ykg8y6768p7n721pgxc7an82hq2s",
  "publishedAt": 1772934681294,
  "slug": "pgvector",
  "version": "1.0.0"
}