Vector SQL: Semantic Search Engine
An Introduction to SQL Filtering with Semantic understanding of Vector Similarity Search
Traditional keyword search has a fundamental limitation: it finds documents that contain the words you type, not documents that mean what you intend. Search for "wireless noise-cancelling headphones" and a keyword engine will miss a product described as "Bluetooth over-ear cans with active sound isolation" — even though it is precisely what you want. This vocabulary mismatch problem has plagued search systems for decades, and it is the gap that Vector SQL: Semantic Search Engine is designed to close.
Vector SQL is a web application that unifies two powerful paradigms: the precision of traditional SQL filtering with the semantic understanding of vector similarity search. Users upload documents, which are embedded into high-dimensional vectors and stored in PostgreSQL via the pgvector extension. They can then run hybrid queries that combine semantic similarity with structured conditions — for example, "Find products similar to 'wireless noise-cancelling headphones' AND price < $200 AND in_stock = true." The result is a production-ready template for retrieval-augmented generation (RAG) systems, recommendation engines, and semantic search applications, all built on familiar SQL syntax.
What It Does
At its core, Vector SQL solves a deceptively difficult problem: finding items that are semantically similar to a query while respecting structured constraints. A pure vector search can tell you which products are most similar to "noise-cancelling headphones," but it cannot natively filter by price or availability in the same operation. A pure SQL query can filter by price and availability, but it cannot understand that "cans with sound isolation" is semantically equivalent to "noise-cancelling headphones."
Vector SQL bridges this divide by expressing both operations within a single SQL query. The semantic component uses vector distance operators — such as <-> for Euclidean distance or <=> for cosine distance — to rank results by similarity to a query embedding. The structured component uses standard WHERE clauses to enforce hard constraints. When executed together, PostgreSQL leverages its query planner to apply filters and vector ranking in a single pass, returning results that satisfy both the semantic intent and the structured requirements.
This hybrid approach is particularly valuable because it mirrors how humans actually search. When a user asks for "recent sci-fi articles about space," they are expressing both a semantic intent (space-related content) and structured constraints (category = sci-fi, published after a certain date). A well-designed Vector SQL system handles both in one query.
Architecture and Data Flow
Document Ingestion and Embedding
The pipeline begins when a user uploads documents through the web interface. These documents — whether product descriptions, articles, FAQs, or knowledge base entries — are processed through several stages:
Chunking: Long documents are split into manageable segments. A common approach is word-based chunking with overlap (for example, 220-word chunks with 40-word overlap) to preserve context across boundaries. More sophisticated systems use semantic chunking, where splits occur at natural topic boundaries rather than fixed token counts.
Embedding generation: Each chunk is passed through a Sentence Transformer model, such as all-MiniLM-L6-v2 (384 dimensions) or a larger model like text-embedding-3-small (1536 dimensions). The model outputs a dense vector that captures the semantic meaning of the text. Crucially, the same model must be used for both document ingestion and query embedding — a mismatch in embedding models renders similarity search meaningless.
Storage: The text, metadata, and embedding vector are stored in PostgreSQL. The schema typically includes the original content, a vector column of the appropriate dimension, and any structured fields that will support filtering (price, category, date, stock status, etc.).
Indexing for Performance
Without an index, vector similarity search requires calculating the distance between the query vector and every row in the table — an operation that grows linearly with dataset size. A table with one million vectors means one million distance calculations per query, resulting in latency measured in seconds rather than milliseconds.
pgvector provides two approximate nearest-neighbor (ANN) indexing strategies to solve this problem:
HNSW (Hierarchical Navigable Small World): This approach builds a multi-layer graph structure where each vector is a node connected to its nearest neighbors. Queries start at a sparse top layer and navigate downward, narrowing the search space with each layer. HNSW typically provides better query performance than IVFFlat, at the cost of higher memory usage and longer build times. It supports incremental updates and can be created on empty tables.
A typical HNSW index creation statement looks like:
sql:
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
The m parameter controls the maximum number of connections per node (higher values improve recall but increase memory), while ef_construction controls the search width during index building. For query-time tuning, SET hnsw.ef_search = 100 increases recall at the cost of slower searches.
IVFFlat: This approach partitions vectors into clusters using k-means. At query time, only the most promising clusters are searched. IVFFlat builds faster and uses less memory than HNSW, but generally achieves lower recall at the same latency. It requires data to be present before index creation and a training step (ANALYZE) to build the clusters.
For most production applications, HNSW is the recommended starting point due to its superior query performance and recall characteristics.
Query Execution
When a user submits a natural language query, the system performs several steps:
- Query embedding: The query text is embedded using the same model used during ingestion.
- Hybrid SQL execution: The system constructs and executes a SQL query that combines vector similarity ranking with structured filters.
- Result formatting: Results are returned with similarity scores, metadata, and source document references.
A representative hybrid query might look like this:
sql:
SELECT id, name, price, description,
1 - (embedding <=> query_embedding) AS similarity
FROM products
WHERE category = 'electronics'
AND price < 200
AND in_stock = true
ORDER BY embedding <=> query_embedding
LIMIT 10;
This query filters by category, price, and availability, then ranks the remaining rows by cosine distance to the query embedding.
Modern Technology Stack
PostgreSQL and pgvector
PostgreSQL serves as the foundation — a battle-tested relational database that now supports vector operations through the pgvector extension. This choice is deliberate: organizations already run PostgreSQL, already understand its operational characteristics, and already have tooling around it. Adding vector search to an existing PostgreSQL instance requires only CREATE EXTENSION vector; — no separate vector database service, no new operational burden.
The pgvector extension provides the vector data type, distance operators (<-> for L2, <=> for cosine, <#> for inner product), and index support for HNSW and IVFFlat. Distance operator classes must match the operator used in queries — a mismatch causes PostgreSQL to fall back to a slow sequential scan.
FastAPI
FastAPI serves as the web framework, providing asynchronous request handling, automatic OpenAPI documentation, and Pydantic-based validation. The async architecture is particularly well-suited to this application, where each request may involve embedding generation (CPU or API-bound) and database queries (I/O-bound). FastAPI's dependency injection system cleanly separates concerns like database session management and authentication.
Sentence Transformers
The embedding layer uses Sentence Transformers, a Python library that provides access to state-of-the-art sentence embedding models. Models like all-MiniLM-L6-v2 offer an excellent balance of quality and speed, producing 384-dimensional embeddings that capture semantic meaning effectively for most use cases. Larger models like text-embedding-3-small (1536 dimensions) provide higher quality at increased computational cost.
The choice of embedding model is consequential: it determines the dimensionality of the vector columns, the quality of semantic matches, and the computational requirements. Once chosen, the same model must be used consistently for both ingestion and querying.
SQLAlchemy with Vector Operators
SQLAlchemy provides the ORM layer, with the pgvector Python package extending it to support vector columns and distance methods. Rather than writing raw SQL with string interpolation, developers can express similarity search using SQLAlchemy's fluent API:
python:
from pgvector.sqlalchemy import Vector
class Product(Base):
__tablename__ = "products"
id = Column(Integer, primary_key=True)
name = Column(String)
embedding = Column(Vector(384))
# Query using cosine distance
results = session.query(Product)\
.order_by(Product.embedding.cosine_distance(query_embedding))\
.limit(10)\
.all()
This approach maintains type safety, prevents SQL injection, and keeps the application code portable across database dialects.
HNSW Indexes for Fast ANN Search
The HNSW index is the performance engine of the system. Its multi-layer graph structure enables logarithmic-time search — a property that makes query latency largely independent of dataset size, within practical limits. For datasets ranging from thousands to millions of vectors, HNSW delivers millisecond-level query times with recall rates above 95%, which is more than sufficient for most search and recommendation applications.
The index parameters are tunable: m and ef_construction control the index structure and build quality, while ef_search controls the query-time trade-off between speed and recall. Production deployments typically start with m=16, ef_construction=64, and ef_search=100, adjusting based on observed recall and latency.
Hybrid Query Patterns
The Case for Hybrid Search
Pure vector search excels at finding semantically similar content, but it can fail when the query includes exact tokens — product codes, error messages, proper nouns, or clause numbers. Consider a query like "refund window in clause 7.3.2." The vector leg understands "refund window" semantically, but "7.3.2" is an exact anchor that embeddings may blur or miss entirely. Conversely, pure keyword search handles the clause number perfectly but may miss documents that discuss refund policies without using that exact phrase.
The solution is hybrid search with result fusion. Both legs — vector and keyword — run independently, and their results are combined using Reciprocal Rank Fusion (RRF), a simple yet effective algorithm that merges ranked lists without requiring score normalization:
text:
rrf_score = 1/(k + vector_rank) + 1/(k + keyword_rank)
Where k is a smoothing constant (typically 60). Documents that rank well in both lists receive the highest combined scores, while documents that excel in only one leg still contribute to the final ranking.
SQL Implementation
The hybrid query can be expressed entirely in SQL using Common Table Expressions (CTEs) and a FULL OUTER JOIN:
sql:
WITH vector_results AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> query_embedding) AS rank
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 30
),
keyword_results AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(content_tsvector, query) DESC) AS rank
FROM documents
WHERE content_tsvector @@ plainto_tsquery('english', query_text)
LIMIT 30
)
SELECT COALESCE(v.id, k.id) AS doc_id,
COALESCE(1.0/(60 + v.rank), 0) + COALESCE(1.0/(60 + k.rank), 0) AS rrf_score
FROM vector_results v
FULL OUTER JOIN keyword_results k ON v.id = k.id
ORDER BY rrf_score DESC
LIMIT 10;
This approach ensures that exact matches are rewarded (via the keyword leg) while semantic matches are discovered (via the vector leg), producing a combined ranking that outperforms either method alone.
Use Cases and Applications
Retrieval-Augmented Generation (RAG)
The most prominent use case for Vector SQL is RAG — the technique of grounding language model responses in retrieved documents. In a RAG pipeline, a user question is embedded, similar documents are retrieved from the vector store, and those documents become context for the LLM's response. This reduces hallucinations and ensures answers are based on actual data rather than the model's training memory.
The Cloud.gov RAG demo, built on pgvector, demonstrates this pattern: upload Markdown files, ask questions, and receive answers grounded in the uploaded content. The system generates query embeddings, retrieves relevant chunks via cosine distance, and passes them to a language model for response generation.
Recommendation Engines
Recommendation systems benefit from the combination of semantic similarity and structured filtering. A movie recommendation engine can find films similar to a user's watch history while filtering by genre, rating, or availability on streaming platforms. The DVD rental lab demonstrates this: given a customer's rental history, the system finds semantically similar Netflix shows to recommend.
Product recommendations follow the same pattern: "Find items similar to what this user purchased AND price < $100 AND in_stock = true." The structured filters ensure recommendations are actionable, while the vector component ensures relevance.
Semantic Search for Knowledge Bases
Support systems and documentation search are natural fits for vector search. A user asking "How do I reset my password?" should find the relevant FAQ even if it is titled "Account recovery procedures." The semantic embedding captures the intent behind the query, while metadata filters can narrow results to a specific product, version, or language.
Product Discovery
E-commerce search benefits enormously from semantic understanding. Traditional keyword search fails when users describe what they want rather than using the exact product terminology. "Lightweight laptop good for video editing" should find relevant machines even if their descriptions use different words. Combined with price and availability filters, this creates a search experience that feels intelligent and responsive.
User Benefits
Democratizing Semantic Search
The primary benefit of Vector SQL is accessibility. Organizations that already run PostgreSQL — which is to say, most organizations — can add vector search without adopting a new database technology. The operational knowledge, backup strategies, monitoring tools, and security practices that apply to PostgreSQL continue to apply. There is no separate vector database to learn, no new failure modes to understand, no additional infrastructure to maintain.
This lowers the barrier to building semantic search from "we need a specialized vector database team" to "we need to enable an extension and write some SQL." The result is faster time-to-value and broader adoption.
A Production-Ready Template
Vector SQL is explicitly designed as a template. The patterns it demonstrates — document ingestion, embedding generation, hybrid query construction, index tuning — are directly reusable in production applications. Developers can fork the codebase, adapt the schema to their domain, and deploy a working semantic search system without reinventing the architecture.
The stack is deliberately conventional: PostgreSQL, FastAPI, SQLAlchemy, Sentence Transformers. These are technologies that developers already know or can learn easily. There is no exotic dependency, no bleeding-edge framework that might be abandoned, no vendor lock-in.
Learning Opportunity
For developers new to vector search, Vector SQL serves as a hands-on learning environment. The ability to see embeddings stored as actual vectors, to experiment with different distance operators, to tune HNSW parameters and observe the effect on latency and recall — these are invaluable experiences that build intuition. The hybrid search patterns, in particular, teach an important lesson: vector search is powerful, but it is not a replacement for keyword search. The best results come from combining them intelligently.
Conclusion
Vector SQL: Semantic Search Engine represents a pragmatic approach to a modern problem. Rather than treating vector search as a separate concern requiring specialized infrastructure, it embeds semantic capabilities within the database that organizations already trust. The result is a system that is simultaneously powerful and familiar — capable of understanding meaning, yet expressible in SQL.
The combination of pgvector, FastAPI, Sentence Transformers, and SQLAlchemy creates a stack that is production-ready today. The hybrid query patterns provide a foundation for building search that handles both semantic intent and exact matching. And the use cases — RAG, recommendations, knowledge base search, product discovery — are immediate and valuable.
For teams building AI-powered applications, Vector SQL offers a clear path: start with the database you have, add the extension you need, and build the semantic search you want.