pgvector Similarity Search

Query vector embeddings using pgvector's similarity operators. This skill covers the retrieval phase of RAG pipelines.

When to Apply

Use this skill when:

Finding semantically similar documents or chunks
Implementing the retrieval step of RAG
Building semantic search features
Querying embeddings stored in PostgreSQL

Distance Operators

pgvector supports three distance metrics:

Operator	Distance Type	Use Case
`<=>`	Cosine distance	Most common, normalized vectors
`<->`	Euclidean (L2)	When magnitude matters
`<#>`	Inner product	Dot product similarity

Cosine distance is recommended for text embeddings as it measures angle between vectors, ignoring magnitude.

Basic Similarity Search

Direct Query

SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM intelligence.chunks
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 5;

The 1 - distance converts cosine distance to similarity (0 to 1 scale).

With Threshold

Filter results below a similarity threshold:

SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM intelligence.chunks
WHERE embedding IS NOT NULL
  AND 1 - (embedding <=> $1::vector) > 0.7
ORDER BY embedding <=> $1::vector
LIMIT 5;

Similarity Search Function

Create a reusable PostgreSQL function:

-- deploy/schemas/intelligence/procedures/find_similar_chunks.sql
CREATE FUNCTION intelligence.find_similar_chunks(
    p_embedding VECTOR(768),
    p_limit INTEGER DEFAULT 5,
    p_similarity_threshold FLOAT DEFAULT 0.7
)
RETURNS TABLE (
    id INTEGER,
    content TEXT,
    similarity FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.id,
        c.content,
        1 - (c.embedding <=> p_embedding) AS similarity
    FROM intelligence.chunks c
    WHERE c.embedding IS NOT NULL
      AND 1 - (c.embedding <=> p_embedding) > p_similarity_threshold
    ORDER BY c.embedding <=> p_embedding
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

Revert Script

-- revert/schemas/intelligence/procedures/find_similar_chunks.sql
DROP FUNCTION IF EXISTS intelligence.find_similar_chunks(VECTOR(768), INTEGER, FLOAT);

TypeScript Implementation

import { Pool } from 'pg';
import { OllamaClient } from './utils/ollama';

const formatVector = (embedding: number[]): string => `[${embedding.join(',')}]`;

export class SimilaritySearch {
  private pool: Pool;
  private ollama: OllamaClient;

  constructor(pool: Pool, ollamaBaseUrl?: string) {
    this.pool = pool;
    this.ollama = new OllamaClient(ollamaBaseUrl);
  }

  async findSimilar(
    query: string,
    limit: number = 5,
    threshold: number = 0.7
  ): Promise<Array<{ id: number; content: string; similarity: number }>> {
    // Generate embedding for the query
    const queryEmbedding = await this.ollama.generateEmbedding(query);

    // Search for similar chunks
    const result = await this.pool.query(
      `SELECT id, content, similarity
       FROM intelligence.find_similar_chunks($1::vector, $2, $3)
       ORDER BY similarity DESC`,
      [formatVector(queryEmbedding), limit, threshold]
    );

    return result.rows;
  }

  async getContext(query: string, limit: number = 5): Promise<string> {
    const chunks = await this.findSimilar(query, limit);
    return chunks.map(c => c.content).join('\n\n');
  }
}

Aggregating Context

For RAG, combine retrieved chunks into a single context string:

SELECT string_agg(content, E'\n\n') as context
FROM intelligence.find_similar_chunks($1::vector, $2, $3);

In TypeScript:

async function getRAGContext(query: string, pool: Pool, ollama: OllamaClient): Promise<string> {
  const queryEmbedding = await ollama.generateEmbedding(query);

  const result = await pool.query(
    `SELECT string_agg(content, E'\n\n') as context
     FROM intelligence.find_similar_chunks($1::vector, $2)`,
    [formatVector(queryEmbedding), 5]
  );

  return result.rows[0].context || '';
}

Testing Similarity Search

import { getConnections, PgTestClient } from 'pgsql-test';
import { OllamaClient } from '../src/utils/ollama';

let pg: PgTestClient;
let teardown: () => Promise<void>;
let ollama: OllamaClient;

const formatVector = (embedding: number[]): string => `[${embedding.join(',')}]`;

beforeAll(async () => {
  ({ pg, teardown } = await getConnections());
  ollama = new OllamaClient();
});

afterAll(() => teardown());

test('should find semantically similar chunks', async () => {
  // Seed document about machine learning
  const mlContent = 'Machine learning enables systems to learn from data.';
  const mlEmbedding = await ollama.generateEmbedding(mlContent);

  await pg.client.query(
    `INSERT INTO intelligence.documents (title, content, embedding)
     VALUES ($1, $2, $3::vector)
     RETURNING id`,
    ['ML Basics', mlContent, formatVector(mlEmbedding)]
  );

  // Create chunk with embedding
  const docResult = await pg.client.query('SELECT id FROM intelligence.documents LIMIT 1');
  const docId = docResult.rows[0].id;

  await pg.client.query(
    `INSERT INTO intelligence.chunks (document_id, content, embedding, chunk_index)
     VALUES ($1, $2, $3::vector, 0)`,
    [docId, mlContent, formatVector(mlEmbedding)]
  );

  // Query for similar content
  const query = 'How do systems learn from data?';
  const queryEmbedding = await ollama.generateEmbedding(query);

  const results = await pg.client.query(
    `SELECT content, similarity
     FROM intelligence.find_similar_chunks($1::vector, 5, 0.3)
     ORDER BY similarity DESC`,
    [formatVector(queryEmbedding)]
  );

  expect(results.rows.length).toBeGreaterThan(0);
  expect(results.rows[0].similarity).toBeGreaterThan(0.3);
  expect(results.rows[0].content).toContain('Machine learning');
});

Performance Optimization

Add Indexes

For large datasets, add an index after initial data load:

-- IVFFlat index (good balance)
CREATE INDEX idx_chunks_embedding ON intelligence.chunks 
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- HNSW index (better recall, more memory)
CREATE INDEX idx_chunks_embedding_hnsw ON intelligence.chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Set Search Parameters

For IVFFlat, increase probes for better recall:

SET ivfflat.probes = 10;

For HNSW, adjust ef_search:

SET hnsw.ef_search = 100;

Filtering with Metadata

Combine vector search with metadata filters:

SELECT c.id, c.content, 1 - (c.embedding <=> $1::vector) AS similarity
FROM intelligence.chunks c
JOIN intelligence.documents d ON c.document_id = d.id
WHERE c.embedding IS NOT NULL
  AND d.metadata->>'category' = 'technical'
  AND 1 - (c.embedding <=> $1::vector) > 0.7
ORDER BY c.embedding <=> $1::vector
LIMIT 5;

Similarity Thresholds

Recommended thresholds by use case:

Use Case	Threshold	Notes
Strict matching	0.8+	High precision, may miss relevant results
General search	0.6-0.7	Good balance
Exploratory	0.4-0.5	High recall, more noise
RAG context	0.5-0.7	Depends on document quality

Troubleshooting

Issue	Solution
No results returned	Lower the similarity threshold
Irrelevant results	Raise the threshold or improve embeddings
Slow queries	Add IVFFlat or HNSW index
"Operator does not exist"	Ensure pgvector extension is installed
Dimension mismatch	Query vector must match stored vector dimensions

References

Related skill: pgvector-setup for database schema setup
Related skill: pgvector-embeddings for generating embeddings
Related skill: rag-pipeline for complete RAG implementation
pgvector documentation

pgvector-similarity-search