Semantic search with pgvector: a practical AI & ML guide

Why semantic search matters now

Your users don't search like databases do. They think in meaning, not keywords. When someone searches "fast checkout experience," they expect results about performance optimization, not literal instances of the word "fast." Semantic search with pgvector: a practical AI & ML guide shows you how to deliver exactly that—by storing embeddings directly in PostgreSQL and querying by meaning instead of exact matches. You already run Postgres in production. You don't need a separate vector database. We'll walk through enabling embeddings, storing them efficiently, querying them, and combining semantic search with traditional full-text search for the best of both worlds.

Enable the extension

The pgvector extension brings vector operations directly to PostgreSQL. Installation depends on your environment, but the setup is straightforward. If you're using a managed service like AWS RDS or Azure Database, check the documentation first—some providers have it pre-installed or require activation through parameter groups. For self-managed Postgres, you'll typically compile it from source or install via your package manager.

Once installed, enable it in your database:

CREATE EXTENSION IF NOT EXISTS vector;

That's it. pgvector is now available. Verify it worked by checking the extension list:

SELECT extname FROM pg_extension WHERE extname = 'vector';

If you see vector in the results, you're ready. The extension adds the vector data type and operators for distance calculations. You'll use these operators to find similar embeddings. The most common distance metric is cosine similarity (the <=> operator), which measures the angle between vectors—ideal for comparing text embeddings because it's scale-invariant and semantically meaningful.

Store embeddings—a vector column next to your content

Embeddings are dense arrays of numbers, typically 768 to 1536 dimensions, that represent the semantic meaning of text. Services like OpenAI's text-embedding-3-small (1536 dims) or Ollama's local models generate them. You store these vectors in a dedicated column alongside your content.

Let's say you have an articles table. Add a vector column and an index:

ALTER TABLE articles ADD COLUMN embedding vector(1536);

The dimension (1536) must match whatever embedding model you use. Mismatches will cause runtime errors. Now create an index for fast lookups. HNSW (Hierarchical Navigable Small World) is the go-to for high-dimensional similarity search—much faster than brute-force comparisons:

CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

The vector_cosine_ops tells pgvector to optimize for cosine distance. If you prefer L2 distance (Euclidean), use vector_l2_ops. HNSW indexes are approximate, not exact—they trade a tiny bit of accuracy for massive speed gains. For most production workloads, that trade is worth it.

Now populate embeddings. Here's a Node.js example using OpenAI:

const { OpenAI } = require('openai');
const { Pool } = require('pg');

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

async function embedArticles() {
  const articles = await pool.query('SELECT id, content FROM articles WHERE embedding IS NULL LIMIT 100');
  
  for (const article of articles.rows) {
    const resp = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: article.content,
    });
    
    const embedding = resp.data[0].embedding;
    await pool.query(
      'UPDATE articles SET embedding = $1 WHERE id = $2',
      [JSON.stringify(embedding), article.id]
    );
  }
}

embedArticles().catch(console.error);

Run this in a background job—embedding is I/O-bound and should not block your main application. For large datasets, batch your API calls and consider using a queue system like Bull or Temporal.

In Python, it's similar:

import psycopg2
from openai import OpenAI

client = OpenAI()
conn = psycopg2.connect('dbname=mydb user=postgres')
cur = conn.cursor()

cur.execute('SELECT id, content FROM articles WHERE embedding IS NULL LIMIT 100')
articles = cur.fetchall()

for article_id, content in articles:
    resp = client.embeddings.create(
        model='text-embedding-3-small',
        input=content
    )
    embedding = resp.data[0].embedding
    cur.execute(
        'UPDATE articles SET embedding = %s WHERE id = %s',
        (str(embedding), article_id)
    )
    conn.commit()

conn.close()

Query—the semantic search itself

To search semantically, you embed the query, then find the closest embeddings in your table:

WITH query_embedding AS (
  SELECT $1::vector AS vec
)
SELECT id, title, content, embedding <=> (SELECT vec FROM query_embedding) AS distance
FROM articles
CROSS JOIN query_embedding
ORDER BY embedding <=> (SELECT vec FROM query_embedding)
LIMIT 10;

The <=> operator computes cosine distance. Lower distance means higher similarity. You bind the query vector as $1. Here's how you'd call it from Node.js:

const query = 'How do I optimize database queries?';
const queryResp = await openai.embeddings.create({
  model: 'text-embedding-3-small',
  input: query,
});
const queryVec = queryResp.data[0].embedding;

const results = await pool.query(
  `SELECT id, title, content, embedding <=> $1::vector AS distance
   FROM articles
   ORDER BY embedding <=> $1::vector
   LIMIT 10`,
  [JSON.stringify(queryVec)]
);

console.log(results.rows);

Real-world scenario: You run an e-commerce search. A customer searches "comfortable shoes for running." Without semantic search, you'd need exact keyword matches or complex synonym lists. With semantic search, the embedding captures the intent—comfort, running context—and returns relevant products even if the product description uses different wording. HNSW indexes make this instant, even with millions of products.

Hybrid search—combine semantic and full-text for best relevance

Semantic search is powerful, but combining it with full-text search often gives better results. Full-text search is excellent for exact terms and specific jargon. Semantic search shines at intent and nuance. Together, they're stronger.

Here's a hybrid approach: compute a relevance score from both methods, then blend them:

WITH query_embedding AS (
  SELECT $1::vector AS vec
),
semantic_results AS (
  SELECT 
    id, 
    title, 
    content,
    embedding <=> (SELECT vec FROM query_embedding) AS semantic_distance,
    ROW_NUMBER() OVER (ORDER BY embedding <=> (SELECT vec FROM query_embedding)) AS semantic_rank
  FROM articles
  ORDER BY embedding <=> (SELECT vec FROM query_embedding)
  LIMIT 50
),
fulltext_results AS (
  SELECT
    id,
    title,
    content,
    ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) AS ft_rank,
    ROW_NUMBER() OVER (ORDER BY ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) DESC) AS ft_rank_pos
  FROM articles
  WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
  LIMIT 50
)
SELECT
  COALESCE(s.id, f.id) AS id,
  COALESCE(s.title, f.title) AS title,
  COALESCE(s.content, f.content) AS content,
  COALESCE(1.0 - (s.semantic_distance / 2.0), 0) * 0.6 +
  COALESCE(f.ft_rank, 0) * 0.4 AS combined_score
FROM semantic_results s
FULL OUTER JOIN fulltext_results f ON s.id = f.id
ORDER BY combined_score DESC
LIMIT 10;

The weights (0.6 for semantic, 0.4 for full-text) are tunable. Adjust based on your domain and user feedback. If jargon and exact matches matter more, increase the full-text weight. If you want more exploration and discovery, lean semantic.

A practical example: A SaaS support center. A user searches "my dashboard is slow." Full-text alone might miss articles about performance optimization that don't use the exact word "dashboard." Semantic search captures the intent. But if you have an article titled "Dashboard Performance Tuning," full-text search boosts it to the top immediately. Hybrid search returns both high-relevance semantic results and exact-match results.

Common mistakes

• Dimension mismatch. Your embedding model outputs 1536 dimensions, but you defined the column as vector(768). This will fail silently or throw an error at insert time. Always verify your model's output dimension and match it exactly.
• Missing embeddings for new content. You add semantic search to an existing system, but forget to embed new articles as they're created. Your application inserts into the articles table, but doesn't call the embedding API. Set up a trigger or middleware to ensure every new row gets an embedding before it's live.
• No index on large tables. Querying a 10 million row table without an HNSW index will scan the whole table and be painfully slow. Always create the index. Even with smaller tables, it doesn't hurt.
• Embedding stale data. You update an article's content, but the embedding column stays the same. Your search results become misleading. Implement a version check: if content changes, re-embed it. A simple updated_at timestamp can help track this.
• Wrong distance metric for your use case. Cosine distance is best for text embeddings in most cases. But if you're embedding numerical data or images, L2 distance might be more appropriate. Understand your embedding model's design and choose the right metric.
• Ignoring approximate index behavior. HNSW is approximate, not exact. In rare cases, a very similar vector might not be in the top 10 results. For most applications this is fine. If you need absolute correctness, run a secondary exact check on the top candidates.

Key takeaways

• pgvector lets you do semantic search in PostgreSQL without a separate database. Enable the extension, add a vector column, and create an HNSW index.
• Embeddings capture meaning, not keywords. Use an embedding API (OpenAI, local Ollama) to convert text to 768–1536 dimensional vectors, then store them in Postgres.
• Query by computing the distance between your search query's embedding and stored embeddings. The <=> operator with cosine distance is the standard approach.
• Hybrid search combines semantic and full-text search for best relevance. Weight them based on your domain—exact matches vs. intent discovery.
• Batch embeddings in background jobs, create HNSW indexes on large tables, and re-embed when content changes. Keep embeddings fresh and indexed.
• Test on your actual data and user queries. Tweak weights, embedding models, and distance metrics based on real feedback, not theory.