PostgreSQL + AI: Vector Search with pgvector for Semantic Similarity

In today’s data-driven world, the ability to search for information based on meaning rather than exact keyword matching is becoming increasingly important. This is where semantic similarity search comes into play, and PostgreSQL—combined with the pgvector extension—provides a powerful platform for implementing these advanced search capabilities.

What is Semantic Similarity?

Semantic similarity refers to the measure of how close two pieces of text, images, or other content are in terms of their meaning. Unlike traditional search methods that rely on exact keyword matching, semantic search understands the context and intent behind a query.

For example, in a traditional search system:

• “How to treat a cold” and “cold remedy” would be treated as entirely different queries
• But in a semantic search system, these would be recognized as closely related concepts

Enter Vector Embeddings

At the heart of semantic search are vector embeddings—numerical representations of data where similar items are positioned closer together in a high-dimensional space. These embeddings are typically generated by machine learning models trained to capture semantic relationships.

Vector embeddings convert words, sentences, images, or any type of data into lists of numbers (vectors) that capture their semantic essence. The closer these vectors are to each other (usually measured by cosine similarity or Euclidean distance), the more semantically similar the original items are.

pgvector: Adding Vector Search to PostgreSQL

PostgreSQL, already known for its robustness and flexibility, becomes even more powerful with the pgvector extension. This extension enables PostgreSQL to store and query vector embeddings efficiently.

Key Features of pgvector

1. Vector Data Type: pgvector introduces a new data type (vector) for storing embedding vectors
2. Distance Metrics: Supports multiple similarity metrics:
   • Euclidean distance (L2 distance)
   • Inner product
   • Cosine distance
3. Indexing Methods: Offers approximate nearest neighbor (ANN) indexing for faster queries:
   • HNSW (Hierarchical Navigable Small World) index
   • IVFFlat (Inverted File with Flat Compression) index

Setting Up pgvector

Let’s walk through the process of setting up pgvector and creating a basic semantic search system:

1. Installation

-- Install the extension
CREATE EXTENSION vector;

2. Create a Table with Vector Column

-- Create a table to store documents and their embeddings
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(384)  -- Dimension depends on your embedding model
);

3. Insert Data with Embeddings

To insert data, you’ll need to generate embeddings using an AI model (like BERT, USE, or OpenAI’s embeddings). Here’s a conceptual example:

-- Insert a document with its pre-calculated embedding
INSERT INTO documents (content, embedding)
VALUES (
    'PostgreSQL is an advanced object-relational database management system',
    '[0.1, 0.2, 0.3, ..., 0.4]'::vector
);

4. Create an Index for Faster Queries

-- Create an HNSW index for approximate nearest neighbor search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Or create an IVFFlat index
-- CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

5. Query for Similar Documents

-- Find documents similar to a given query embedding
SELECT content, 
       1 - (embedding <=> '[0.15, 0.23, 0.31, ..., 0.42]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.15, 0.23, 0.31, ..., 0.42]'::vector
LIMIT 5;

The <=> operator calculates the distance between vectors (smaller means more similar). For cosine similarity, the distance is 1-cosine similarity, so we subtract from 1 to get the actual similarity score.

Practical Implementation with Python

Here’s how you might implement a complete semantic search system using Python, PostgreSQL, and pgvector:

import psycopg2
import numpy as np
from sentence_transformers import SentenceTransformer

# Connect to PostgreSQL
conn = psycopg2.connect("dbname=mydb user=postgres password=password")
cur = conn.cursor()

# Load a pre-trained embedding model
model = SentenceTransformer('all-MiniLM-L6-v2')  # 384-dimension embeddings

# Function to insert a document with its embedding
def add_document(content):
    # Generate embedding
    embedding = model.encode(content)
    
    # Convert numpy array to a format PostgreSQL can understand
    embedding_str = str(embedding.tolist()).replace('[', '{').replace(']', '}')
    
    # Insert document and embedding
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
        (content, embedding_str)
    )
    conn.commit()

# Function to search for similar documents
def search(query, limit=5):
    # Generate embedding for the query
    query_embedding = model.encode(query)
    query_embedding_str = str(query_embedding.tolist()).replace('[', '{').replace(']', '}')
    
    # Search for similar documents
    cur.execute(
        """
        SELECT content, 1 - (embedding <=> %s) AS similarity
        FROM documents
        ORDER BY embedding <=> %s
        LIMIT %s
        """,
        (query_embedding_str, query_embedding_str, limit)
    )
    
    return cur.fetchall()

# Add some documents
add_document("PostgreSQL is an advanced object-relational database management system")
add_document("Vector databases are designed for high-dimensional data storage")
add_document("pgvector adds vector similarity search to PostgreSQL")
add_document("Machine learning models can generate embeddings from text")

# Search for similar documents
results = search("How does PostgreSQL handle vector data?")
for content, similarity in results:
    print(f"Similarity: {similarity:.4f} - {content}")

Real-world Applications

The combination of PostgreSQL and pgvector enables numerous advanced applications:

1. Semantic Document Search: Find documents based on meaning, not just keywords
2. Recommendation Systems: Recommend products, articles, or content based on semantic similarity
3. Duplicate Detection: Identify semantically similar content
4. Image Similarity: Store image embeddings and find visually similar images
5. Question Answering: Match questions to the most semantically similar answers
6. Chatbot Enhancement: Improve response retrieval in retrieval-augmented generation (RAG) systems

Performance Considerations

When working with pgvector at scale, consider the following:

1. Embedding Dimension: Higher dimensions provide more information but require more storage and computation
2. Index Type Selection: HNSW is typically faster but uses more memory; IVFFlat uses less memory but may be slower
3. Index Parameters: Tune parameters like HNSW’s m (max connections) and ef_construction (exploration factor) for your specific use case
4. Database Size: Monitor database size as vector data can consume significant storage
5. Query Performance: Batch embedding generation and use appropriate indexes for optimal performance

Conclusion

The integration of PostgreSQL with pgvector represents a significant advancement in making sophisticated AI-powered search capabilities accessible within traditional database systems. By leveraging vector embeddings, PostgreSQL can now handle semantic similarity queries efficiently, opening up new possibilities for intelligent data retrieval and analysis.

For organizations already using PostgreSQL, adding semantic search capabilities is now just an extension away—no need to adopt specialized vector databases or complex external services. This integration simplifies architecture while providing powerful AI-enhanced search capabilities.

As AI continues to evolve, the ability to search and analyze data based on meaning rather than exact matches will become increasingly vital. With pgvector, PostgreSQL is well-positioned to meet these emerging needs, combining the reliability of a trusted database system with the power of modern AI techniques.