Semantic Search with pgvector: Building Intelligent Search in PostgreSQL

Imagine you’re searching your company’s internal knowledge base for “machine learning best practices” but only get results with those exact words. Meanwhile, your colleague’s brilliant article about “neural network optimization” sits buried, invisible to your search. This is the frustrating reality of keyword-based search—it misses the forest for the trees.

What if your search could understand that “AI model training,” “deep learning workflows,” and “neural network optimization” are all conceptually related to “machine learning”? That’s exactly what semantic search with pgvector enables—intelligent search that finds meaning, not just matching words.

Part of the PostgreSQL Search series: This post provides a deep technical implementation guide for semantic search with pgvector. For deciding when to use semantic vs lexical search, see Semantic vs Keyword Search. For combining search with filters and facets, see Fast Faceted Filtering.

The Problem: Why Keyword Search Falls Short

Traditional keyword-based search has a fundamental flaw: it only finds exact word matches. When you search for “machine learning,” you miss documents about “artificial intelligence,” “neural networks,” or “deep learning” that are conceptually related but use different terminology. This creates a frustrating experience where relevant information remains hidden simply because it uses different words to express the same concepts.

Consider these real scenarios:

• Internal Knowledge Base: Searching for “database optimization” misses articles about “PostgreSQL tuning” or “query performance”
• Product Catalog: Searching for “5-axis CNC for titanium” misses “5-axis VMC” (Vertical Machining Center), “vertical machining center with trunnion,” “high-torque spindle,” or “through-spindle coolant (TSC)”—all critical features for precision titanium machining
• Expertise Discovery: Finding “Python developers” misses people who list “Django,” “Flask,” or “FastAPI” in their skills

Semantic search addresses this by using embeddings—numerical representations of text that capture meaning—to find results based on conceptual similarity rather than exact keyword matches. This approach is particularly powerful for internal company platforms with diverse data, as it can surface connections across technical specifications, people’s interests, and organizational information that keyword search might miss.

Understanding Embeddings and Vector Search

At the core of semantic search are embeddings: high-dimensional vectors (lists of numbers) that encode the meaning of text. If two pieces of text have similar meaning or context, their embeddings will be close together in this vector space ^{[1]Team TigerData. “Similarity Search on PostgreSQL Using OpenAI Embeddings and Pgvector.” TigerData Blog, Aug 22, 2024.}.

By comparing a query’s embedding to embeddings of your data, you can find the closest matches in terms of meaning, not just literal word overlap. This enables searches like finding “cloud database solutions” that return results about “distributed data storage,” “scalable database services,” or “enterprise data platforms” even if those exact terms aren’t present in the content.

pgvector supports multiple vector types and distance metrics, making it flexible for various embedding models and use cases ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}. The extension provides support for single-precision, half-precision, binary, and sparse vectors, along with various distance functions including L2 distance, inner product, cosine distance, L1 distance, Hamming distance, and Jaccard distance.

Why PostgreSQL + pgvector?

PostgreSQL can be transformed into a vector database by installing the pgvector extension ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}. This approach offers several compelling advantages:

• Unified Data Storage: Keep all your data (relational and vector) in one place
• SQL Integration: Leverage standard SQL to query both vectors and relational fields together
• Relational Integrity: Maintain data consistency and leverage existing relationships
• Simplified Architecture: Avoid the complexity of managing separate vector stores
• ACID Compliance: Full transactional support for vector operations
• Point-in-time Recovery: Standard PostgreSQL backup and recovery capabilities
• JOINs: Seamless integration with existing relational data

Scope of this guide: This post focuses on the technical implementation of pgvector—setting up embeddings, creating indexes, optimizing queries, and building RAG systems. If you’re still evaluating whether semantic search is right for your use case, start with Semantic vs Keyword Search to understand the tradeoffs.

Setting Up pgvector

After installing the extension with CREATE EXTENSION vector;, you can add vector columns to your existing tables:

-- Add embedding column to products table
ALTER TABLE products
ADD COLUMN embedding VECTOR(1536);

-- Create a unified semantic index table
CREATE TABLE semantic_index (
    id SERIAL PRIMARY KEY,
    object_type VARCHAR(50),
    object_id INTEGER,
    content_text TEXT,
    embedding VECTOR(1536)
);

The VECTOR(1536) specifies the dimension length, which must match your embedding model’s output size. For OpenAI’s models, this is typically 1536 dimensions.

Generating Embeddings

There are several approaches to generating embeddings for your data. Popular options include:

• OpenAI API: Third-generation models like text-embedding-3-small and text-embedding-3-large offer high quality and cost-effectiveness
• Open Source Models: Sentence Transformers, BERT, or other transformer-based models you can host locally
• Cloud Providers: AWS Bedrock, Google Vertex AI, or Azure OpenAI Service
• Specialized Services: Cohere, Hugging Face Inference API, or other embedding services

The key requirement is that all embeddings must be generated using the same model to ensure they exist in the same vector space for meaningful similarity comparisons.

Preparing Text for Embedding

The quality of your semantic search depends heavily on how you prepare the text for embedding. For each entity, consider including:

• Products: Name, description, technical specifications, and key features
• People: Name, role, expertise areas, and interests
• Organizations: Company name, industry, description, and key products

You can also incorporate relational context. For example, when embedding a person’s profile, include “works at [OrgName]” or list associated products to enrich the description with organizational context.

Implementing Vector Search

Once embeddings are stored, it’s crucial to create proper indexes for performance. pgvector supports multiple indexing methods ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}:

• HNSW (Hierarchical Navigable Small World): Best for approximate nearest neighbor search with good speed/accuracy trade-off
• IVFFlat: Inverted file with flat compression, good for smaller datasets
• Exact Search: No index required, but slower for large datasets

-- Create HNSW index for efficient similarity search
CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops);

-- Alternative: IVFFlat index
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Performing Semantic Search Queries

The actual search process involves two steps:

• Embed the query using the same model used for your data
• Query the database for nearest neighbors using vector similarity

Here’s an example query that finds the most similar objects:

-- Simple similarity search
SELECT object_id,
       object_type,
       content_text,
       embedding <=> $1::vector AS distance
FROM semantic_index
ORDER BY embedding <=> $1::vector
LIMIT 5;

This query finds the 5 most semantically similar objects to your query vector. When you need additional details, join back to the source tables based on object_type and object_id:

SELECT p.id,
       p.name,
       si.content_text,
       si.embedding <=> $1::vector AS distance
FROM semantic_index si
JOIN products p ON p.id = si.object_id
WHERE si.object_type = 'product'
ORDER BY si.embedding <=> $1::vector
LIMIT 5;

pgvector provides several distance operators ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}:

• <->: Euclidean distance (L2)
• <#>: Negative inner product
• <=>: Cosine distance
• <+>: Taxicab distance (L1)

The choice of distance metric depends on your embedding model and use case. Let’s explore each one with practical examples:

Cosine Distance (<=>) - Most Common for Text

Cosine distance measures the angle between vectors, ignoring their magnitude. This makes it ideal for text embeddings where you care about semantic similarity regardless of text length.

-- Find semantically similar documents (ignoring length differences)
SELECT content_text, embedding <=> $1::vector AS cosine_distance
FROM semantic_index
WHERE object_type = 'document'
ORDER BY embedding <=> $1::vector
LIMIT 5;

When to use: Text search, document similarity, recommendation systems where magnitude doesn’t matter.

Euclidean Distance (<->) - Geometric Distance

Euclidean distance measures the straight-line distance between two points in vector space. It considers both direction and magnitude.

-- Find vectors closest in absolute terms
SELECT product_name, embedding <-> $1::vector AS euclidean_distance
FROM products
ORDER BY embedding <-> $1::vector
LIMIT 5;

When to use: When vector magnitude is meaningful (e.g., user preference intensity, feature importance scores).

Negative Inner Product (<#>) - Direction and Magnitude

Negative inner product combines both direction and magnitude, making it useful when both aspects of similarity matter.

-- Find products with strong directional and magnitude similarity
SELECT product_name, embedding <#> $1::vector AS negative_inner_product
FROM products
ORDER BY embedding <#> $1::vector
LIMIT 5;

When to use: When you need both semantic similarity and intensity matching (e.g., user ratings, feature weights).

Taxicab Distance (<+>) - L1 Distance

Taxicab distance (Manhattan distance) sums the absolute differences of vector components. It’s more robust to outliers than Euclidean distance.

-- Find vectors with component-wise similarity
SELECT user_profile, embedding <+> $1::vector AS taxicab_distance
FROM user_profiles
ORDER BY embedding <+> $1::vector
LIMIT 5;

When to use: When you want to be less sensitive to extreme values in any single dimension.

Practical Example: Imagine you’re searching for “machine learning tutorials” in a knowledge base:

• Cosine distance would find documents about “AI education,” “neural network guides,” and “deep learning resources” regardless of document length
• Euclidean distance might prefer shorter documents with exact keyword matches over longer, more comprehensive guides
• Negative inner product would balance semantic similarity with document “weight” or importance
• Taxicab distance would be less influenced by documents with extremely high scores in just one semantic dimension

For most text-based semantic search applications, cosine distance (<=>) is the recommended choice as it focuses purely on semantic similarity without being influenced by text length or embedding magnitude.

Leveraging Relational Data

One of pgvector’s key advantages is the ability to combine semantic search with relational filters and joins. You can perform vector similarity search while simultaneously filtering by attributes or joining to other tables:

-- Search only within Finance organizations
SELECT o.id,
       o.name,
       si.content_text
FROM semantic_index si
JOIN organizations o ON o.id = si.object_id
WHERE si.object_type = 'organization'
  AND o.industry = 'Finance'
ORDER BY si.embedding <=> :query_embedding
LIMIT 5;

Because the semantic_index table only stores lightweight references, you can enrich results with standard JOINs alongside vector similarity. This unified approach allows you to:

• Apply business logic filters to search results
• Join with related tables for additional context
• Maintain data consistency through transactions
• Respect user permissions and access controls

For complex filtering scenarios with semantic search—such as combining brand, price, and category filters with vector similarity—see Fast Faceted Filtering in PostgreSQL for production-scale filtering strategies and query optimization techniques.

Production Considerations

Embedding Updates

As your data changes, you’ll need a strategy to update embeddings. Consider implementing:

• Database triggers that mark records for re-embedding
• Periodic batch jobs for bulk updates
• Caching strategies to minimize API calls
• Version control for embedding models to ensure consistency

Performance Optimization

For production-scale data ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}:

• Monitor index performance using EXPLAIN plans
• Tune HNSW parameters (M, ef) for your specific use case
• Consider hardware requirements for CPU-intensive vector operations
• Balance recall accuracy with query performance
• Use maintenance_work_mem for parallel index builds
• Monitor memory usage during index creation

Cost Management

Embedding generation can be expensive with large datasets. Strategies include:

• Using cost-effective embedding models
• Caching embeddings to avoid redundant API calls
• Implementing graceful error handling for API failures
• Considering self-hosted alternatives for high-volume scenarios
• Batch processing to optimize API usage

Real-World Applications

Semantic search with pgvector is particularly valuable for:

• Internal Knowledge Bases: Finding relevant documents, experts, or resources
• Product Catalogs: Discovering products by description rather than exact keywords
• Expertise Discovery: Locating people with specific skills or experience
• Content Recommendation: Suggesting related articles, products, or services

The ability to search across heterogeneous data types—products, people, and organizations—in a single query makes this approach especially powerful for internal platforms where users need to explore connections between different types of information.

Building RAG Systems with pgvector

One of the most compelling applications of pgvector is in Retrieval-Augmented Generation (RAG) systems. RAG combines semantic search with large language models to create intelligent question-answering systems that can access and reason about your specific data.

How RAG Works

A typical RAG system follows this pattern:

• Document Ingestion: Split documents into chunks and generate embeddings
• Storage: Store chunks and embeddings in PostgreSQL with pgvector
• Query Processing: Convert user questions to embeddings and find relevant chunks
• Context Assembly: Retrieve the most relevant document chunks
• Generation: Send chunks + question to an LLM for contextual answers

Key Implementation Steps

Database Schema: Create a document_chunks table with chunk_text, metadata, and embedding columns. Use an HNSW index on the embedding column and, if you store structured metadata in a JSONB column, a GIN index on that JSONB field for fast filtering.

CREATE INDEX document_chunks_embedding_hnsw_idx
    ON document_chunks USING hnsw (embedding vector_cosine_ops);

CREATE INDEX document_chunks_metadata_gin_idx
    ON document_chunks USING gin (metadata);

Retrieval Query: Use vector similarity search (embedding <=> $query_vector) to find the most relevant chunks, optionally filtering by metadata like document type or date ranges.

Advanced Patterns:

• Hybrid Search: Combine vector similarity with PostgreSQL’s full-text search for better recall. See Semantic vs Keyword Search for implementation patterns and score fusion strategies (like Reciprocal Rank Fusion)
• Contextual Retrieval: Use JOINs to include document metadata (title, author) with retrieved chunks
• Relevance Thresholding: Filter results by similarity score to ensure quality

This makes pgvector an excellent choice for RAG systems, especially when you need to combine semantic search with complex business logic or when you’re already using PostgreSQL for other parts of your application.

❌ When pgvector Might Not Be Right for You

While pgvector is excellent for many use cases, it’s not always the best choice. Here are scenarios where you might want to consider alternatives:

Rule of Thumb: If you’re already using PostgreSQL, have less than 10 million vectors, and need to combine vector search with relational data, pgvector is likely your best bet. For specialized use cases or massive scale, evaluate dedicated vector databases. For deployments near the upper end of that range, review the pgvector performance guidance to size RAM, CPU, and index parameters appropriately.

To implement semantic search with pgvector ^{[1]pgvector/pgvector. “Open-source vector similarity search for Postgres.” GitHub, 2024.}:

• Install pgvector in your PostgreSQL database using your preferred method (Docker, package manager, or source)
• Choose an embedding model that fits your needs and budget
• Design your embedding strategy for each entity type
• Create vector columns with appropriate dimensions for your chosen model
• Create indexes using HNSW or IVFFlat based on your data size and performance requirements
• Implement embedding generation for your data using your chosen service
• Build search queries that combine vector similarity with relational filters
• Iterate and optimize based on user feedback and performance metrics

The beauty of this approach is that it starts simple—you can begin with a single entity type and expand as you gain experience. The unified PostgreSQL architecture means your semantic search capabilities grow alongside your existing relational data, providing a solid foundation for building intelligent search applications.

pgvector’s active development and strong community support make it a reliable choice for production vector search applications, with regular updates and improvements to performance and functionality.

This post provided the technical implementation guide for semantic search. To build a complete PostgreSQL search solution:

• Choosing your search strategy: See Semantic vs Keyword Search to decide when to use semantic search vs lexical (FTS) vs hybrid approaches. Includes decision frameworks, cost comparisons, and score fusion techniques like Reciprocal Rank Fusion.

• Combining search with filters: See Fast Faceted Filtering in PostgreSQL for production-scale techniques to combine semantic search with metadata filters (brand, price, category, etc.). Includes query optimization, index strategies, and performance monitoring.

Together, these posts show how PostgreSQL can serve as a complete search platform without requiring external dependencies like Elasticsearch or Pinecone for many use cases.