Searching in a Vector Database

In previous articles, we demonstrated how to prepare a large document for vectorization, perform the vectorization process, and now it's time to search within a vector database.

How Does Vector Search Work?

Unlike traditional databases that rely on exact keyword matches, vector search finds semantically similar text chunks based on numerical representations (embeddings). This is useful for tasks like document search, recommendation systems, and AI-powered assistants.

The core of vector search in PostgreSQL with pgvector is the following query:

cur.execute(""" SELECT id, page, chunk, 1 - (embedding <=> %s::vector) AS similarity FROM chunks ORDER BY embedding <=> %s::vector ASC LIMIT %s; """, (query_embedding, query_embedding, top_n))

Breaking Down the Query

This SQL query helps us find the most relevant text chunks to a given input query. Let's analyze how it works:

1️⃣ What Are We Selecting?

SELECT id, page, chunk, 1 - (embedding <=> %s::vector) AS similarity

• id → The unique identifier of the text chunk.
• page → The page number where the chunk appears.
• chunk → The actual piece of text stored in the database.
• 1 - (embedding <=> %s::vector) AS similarity → This calculates the similarity score between the input text (converted into an embedding) and stored text chunks.

2️⃣ Ordering the Results by Similarity

ORDER BY embedding <=> %s::vector ASC

• The <=> operator computes cosine similarity between the input vector and stored embeddings.
• Sorting in ascending order ensures that the most similar text chunks appear first.

3️⃣ Limiting the Output

LIMIT %s;

Returns only the top N most similar results to avoid unnecessary computation.

Optimizing Search with Indexing

While this query works efficiently for small datasets, searching through millions of vectors can be slow. Indexing can help speed up retrieval, but it comes with trade-offs:

✅ Indexing (HNSW or IVFFLAT) speeds up searches significantly
❌ Indexing might slightly reduce accuracy (approximate nearest neighbor search)

To enable indexing in PostgreSQL, use:

CREATE INDEX ON chunks USING hnsw (embedding);

This accelerates search while keeping results reasonably accurate.

Code Example

To see the full implementation, check out the GitHub repository:

🔗 Vector Search Code https://github.com/jaroslavcech/aiblog/blob/main/07CreateEmbedding.py

This script converts text into embeddings, stores them in PostgreSQL, and allows efficient semantic search.

Conclusion

Vector databases revolutionize search by making it possible to find relevant content even when exact keywords don't match. Whether you're building AI search engines, chatbots, or recommendation systems, vector search provides powerful results based on meaning rather than exact words.

🚀 Have you tried vector search in PostgreSQL? Share with me your experience in the comments!