# Postgres Vectors

> Use when working with embeddings, semantic similarity, vector search, or the <-> <#> <=> operators. Load for pgvector queries, HNSW index creation, embedding storage, or similarity calculations. Covers distance operators, index strategies, and common pitfalls with 384-dimensional vectors.

- Skill: `majiayu000/postgres-vectors` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add majiayu000/postgres-vectors`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/postgres-vectors/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/postgres-vectors

---


# Postgres Vectors (pgvector)

Vector similarity search patterns for semantic matching.

> **Announce:** "I'm using postgres-vectors to implement vector similarity correctly."

## Vector Storage

Embeddings are stored in the `embeddings` table:

```sql
CREATE TABLE embeddings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_text text NOT NULL,
  embedding extensions.vector(384) NOT NULL,  -- 384 dimensions from gte-small
  created_at timestamptz DEFAULT now()
);

-- Unique constraint prevents duplicate embeddings
CREATE UNIQUE INDEX idx_embeddings_source_text 
ON embeddings (source_text);
```

**Key points:**
- Use `extensions.vector(384)` - pgvector is in extensions schema
- 384 dimensions matches gte-small model output
- Deduplicate by source_text

## Distance Operators

| Operator | Distance Type | Use Case |
|----------|---------------|----------|
| `<->` | L2 (Euclidean) | General purpose |
| `<#>` | Inner product (negative) | Normalized vectors (fastest) |
| `<=>` | Cosine distance | When normalization varies |

**For this project:** Use `<=>` (cosine) since we want similarity regardless of vector magnitude.

```sql
-- Cosine similarity = 1 - cosine_distance
SELECT 
  id,
  1 - (embedding <=> query_embedding) AS similarity
FROM embeddings
WHERE 1 - (embedding <=> query_embedding) > 0.5  -- 0.5 threshold
ORDER BY embedding <=> query_embedding  -- ASC for distance
LIMIT 10;
```

## Index Strategy

### When to Use HNSW

```sql
-- HNSW: Good recall, slightly slower builds
CREATE INDEX idx_embeddings_hnsw 
ON embeddings 
USING hnsw (embedding extensions.vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
```

**Use HNSW when:**
- Table has >10,000 rows
- Accuracy matters more than build time
- Queries use cosine distance (`<=>`)

### When to Use IVFFlat

```sql
-- IVFFlat: Faster builds, requires tuning
CREATE INDEX idx_embeddings_ivfflat
ON embeddings
USING ivfflat (embedding extensions.vector_cosine_ops)
WITH (lists = 100);

-- Query with probes
SET ivfflat.probes = 10;
```

**Use IVFFlat when:**
- Rapid index rebuilds needed
- Willing to tune `lists` and `probes`

### This Project Uses HNSW

We prioritize accuracy for semantic matching.

## Common Patterns

### Find Similar Traits

```sql
-- Find traits similar to a query
SELECT 
  t.id,
  t.canonical_text,
  1 - (e.embedding <=> v_query_embedding) AS similarity
FROM traits t
JOIN embeddings e ON e.source_text = t.canonical_text
WHERE 1 - (e.embedding <=> v_query_embedding) > 0.6
ORDER BY e.embedding <=> v_query_embedding
LIMIT 20;
```

### Batch Similarity Calculation

```sql
-- Calculate similarity for multiple places at once
WITH place_similarities AS (
  SELECT 
    pt.place_id,
    1 - (e.embedding <=> v_description_embedding) AS trait_similarity
  FROM place_traits pt
  JOIN embeddings e ON e.id = pt.embedding_id
)
SELECT 
  place_id,
  AVG(trait_similarity) AS avg_similarity,
  MAX(trait_similarity) AS max_similarity
FROM place_similarities
GROUP BY place_id
ORDER BY avg_similarity DESC;
```

## Anti-Patterns

### DON'T: Filter After Ordering

```sql
-- WRONG: Index can't help with post-filter
SELECT * FROM embeddings
ORDER BY embedding <=> query
LIMIT 100
WHERE some_condition;  -- Filter after ORDER BY

-- CORRECT: Filter first, then order
SELECT * FROM embeddings
WHERE some_condition
ORDER BY embedding <=> query
LIMIT 100;
```

### DON'T: Use Wrong Operator for Index

```sql
-- WRONG: Index is cosine_ops but query uses L2
CREATE INDEX ... USING hnsw (embedding vector_cosine_ops);
SELECT * FROM embeddings ORDER BY embedding <-> query;  -- L2!

-- CORRECT: Match operator to index
SELECT * FROM embeddings ORDER BY embedding <=> query;  -- Cosine
```

### DON'T: Forget the Extensions Schema

```sql
-- WRONG: Will fail
embedding vector(384)

-- CORRECT: Prefix with extensions
embedding extensions.vector(384)
```

## Generating Embeddings

Embeddings come from edge function, stored via database function:

```sql
-- Called by edge function after generating embedding
INSERT INTO embeddings (source_text, embedding)
VALUES (v_text, v_embedding)
ON CONFLICT (source_text) DO UPDATE 
SET embedding = EXCLUDED.embedding;
```

## References

See `references/similarity-queries.md` for more query examples.