Stop Using `LIKE '%term%'` – AI Gives You Semantic Search for Free
Replace brittle pattern matching with meaning-aware retrieval using pgvector.
Substring matching with LIKE '%term%' is slow, inflexible, and completely blind to meaning. AI semantic search transforms your database by using embedding vectors to understand what users mean. With free PostgreSQL extensions like pgvector and pgvectorscale, you can build search experiences comparable to dedicated semantic search systems directly inside your existing database.
The Black Friday Outage: When Pattern Matching Brought Down Production
It was 2:00 AM on a Black Friday, and our primary PostgreSQL database was melting down. The culprit? A seemingly innocent search query: SELECT id, name, description FROM products WHERE description LIKE '%winter coat%'. Because of the leading wildcard, PostgreSQL couldn't use the B-tree index. It was forced to perform a full sequential scan across 15 million rows. The query took over 8 seconds. Multiply that by 500 concurrent users, and the connection pool exhausted itself. The site went down.
This incident highlighted how critical it is to stop slow DB queries with AI workload management before they degrade the entire database ecosystem.
We had two choices: make a costly cloud infrastructure mistake by spending $2,000/month on an Elasticsearch cluster, or fundamentally change how we search. We chose the latter. By implementing AI semantic search using the open-source pgvector extension, we observed query times drop from 8.4 seconds to under 5 milliseconds in our specific deployment, eliminating the need for external search infrastructure and finally allowing users to find "insulated parkas" when they searched for "winter coats".
What You Need Before We Start
- PostgreSQL 15 or higher (for optimal HNSW index support).
- Python 3.8+ with
psycopg2andsentence-transformersinstalled. - Basic understanding of SQL and relational database concepts.
Why Traditional Databases Are Blind to Meaning
Traditional database search relies on exact lexical matching. If the user types "affordable laptop", the database looks for the exact string. If the product is named "budget notebook", it returns zero results. The database is blind to synonyms, context, and intent.
AI semantic search solves this by converting text into embedding vectors—high-dimensional arrays of floating-point numbers that capture the semantic meaning of the text. In this vector space, "affordable laptop" and "budget notebook" are mathematically close to each other.
The Math Behind the Magic: HNSW vs. DiskANN
To search millions of vectors in milliseconds, we use Approximate Nearest Neighbor (ANN) indexes and advanced vector retrieval techniques.
- HNSW (Hierarchical Navigable Small World): This algorithm builds a multi-layered graph. The top layers contain sparse, long-range connections for quick global navigation, while the bottom layers contain dense, short-range connections for precise local search. It is incredibly fast but requires significant RAM.
- DiskANN (via pgvectorscale): For billion-scale datasets, HNSW's RAM requirement becomes prohibitive. DiskANN uses a disk-based graph structure, keeping only the most frequently accessed nodes in RAM while storing the rest on SSDs.
Visualizing the Pipeline: From Words to Vectors
Figure 1: AI semantic search architecture — from natural‑language query to meaning‑aware results, showing each transformation step.
A Hard-Won Lesson: The HNSW Memory Leak Crisis
Let me share a hard-won lesson from the trenches. Early last year, a client migrated their RAG pipeline to PostgreSQL using pgvector with an HNSW index. Initially, performance was blazing fast. But within three weeks, the database started experiencing random Out-Of-Memory (OOM) crashes because they were guessing their database memory and buffer pool size instead of using automated configurations.
The Investigation: I dug into the memory allocation logs. The HNSW index loads the entire graph structure into RAM. As their vector dataset grew past 15 million embeddings, the memory footprint exceeded the instance's 64GB RAM limit. Without adaptive work memory allocation, Postgres collapsed under the unexpected index load.
The Fix: We migrated the vector workload to the pgvectorscale extension, which uses the DiskANN algorithm. DiskANN is designed for massive scale and uses a disk-based architecture. The OOM crashes stopped immediately, and query latency remained under 50ms.
Getting Your Hands Dirty: A 15-Minute Implementation
Phase 1: Schema Setup
To safely alter your tables for AI integrations without manually triggering database lockups, consider implementing modern approaches to automated AI database schema evolution. Below is the SQL configuration required to define your vector column:
-- Install pgvector for standard vector operations
CREATE EXTENSION IF NOT EXISTS vector;
-- Add a 384-dimensional embedding column (matches all-MiniLM-L6-v2)
ALTER TABLE products ADD COLUMN embedding vector(384);
-- Create an HNSW index for fast approximate search
CREATE INDEX idx_products_embedding ON products
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);Phase 2: Generating Vectors with Python
from sentence_transformers import SentenceTransformer
import psycopg2
model = SentenceTransformer('all-MiniLM-L6-v2')
conn = psycopg2.connect("dbname=mydb user=postgres password=secret")
cur = conn.cursor()
cur.execute("SELECT id, name, description FROM products WHERE embedding IS NULL LIMIT 1000;")
rows = cur.fetchall()
for row in rows:
text = f"{row[1]} {row[2]}"
# Normalize embeddings for accurate cosine similarity
embedding = model.encode(text, normalize_embeddings=True)
cur.execute("UPDATE products SET embedding = %s WHERE id = %s", (embedding.tolist(), row[0]))
conn.commit()Phase 3: The Query
To process search queries efficiently under load, apply intelligent SQL query processing structures. If you are drafting these lookups in dynamic application environments, review our AI prompts database engineer SQL optimization guide. Here is the operational lookup query:
-- In your application, generate the query vector first:
-- query_vec = model.encode("warm winter coat", normalize_embeddings=True)
-- Then execute this SQL:
SELECT id, name, 1 - (embedding <=> :query_vec) AS similarity
FROM products
WHERE 1 - (embedding <=> :query_vec) > 0.7 -- Filter by relevance threshold
ORDER BY embedding <=> :query_vec
LIMIT 20;
Solving the "SKU Problem" with Hybrid Search
Pure semantic search is powerful but can sometimes miss exact matches (like a specific SKU or product code). The solution is Hybrid Search, which combines the conceptual recall of vector search with the exact precision of full-text search using Reciprocal Rank Fusion (RRF).
-- Hybrid Search using RRF
WITH semantic AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> :query_vec) as rank
FROM products WHERE embedding IS NOT NULL LIMIT 50
),
keyword AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(to_tsvector(name), plainto_tsquery(:term)) DESC) as rank
FROM products WHERE to_tsvector(name) @@ plainto_tsquery(:term) LIMIT 50
)
SELECT COALESCE(s.id, k.id) as id,
COALESCE(1.0/(60 + s.rank), 0) + COALESCE(1.0/(60 + k.rank), 0) as rrf_score
FROM semantic s FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY rrf_score DESC LIMIT 20;Don't Forget Security: Protecting Your Vector Data
Embeddings are mathematical representations of your text. If an attacker gains access to the vector column, they can potentially reconstruct the original text using model inversion techniques. To secure your semantic search:
- Column-Level Permissions: Revoke access to the embedding column for application users. Only the search service account should query it.
- PII Redaction: Never embed raw PII. Implement a preprocessing layer or apply AI database data masking to redact emails, phone numbers, and names before passing text to the embedding model.
- Encryption at Rest: Ensure your PostgreSQL storage layer uses standard TDE (Transparent Data Encryption) or a dynamically adjusting AI database adaptive encryption strategy to protect high-dimensional vector data on disk.
The Real Cost: Storage, RAM, and Compute
Understanding the cost of adding semantic search is essential for planning. Here is the breakdown for a 384-dimensional vector (using all-MiniLM-L6-v2):
| Component | Size / Cost (per 1M rows) |
|---|---|
| Vector Data (384 x 4 bytes) | ~1.5 GB |
| HNSW Index Overhead | ~2-3 GB * |
| Embedding Generation (CPU) | ~2-4 hours (One-time) |
* Note: Actual index overhead varies significantly by dataset dimensionality, index parameters (m, ef_construction), PostgreSQL version, fillfactor, and metadata.
For a 10M row table, you're looking at ~15-30 GB of additional storage. At typical cloud storage prices, that's less than $10/month. The ROI is immediate when you eliminate the need for an external cluster, demonstrating that you do not need a separate dedicated data warehouse or search engine when your main transactional DB can handle it.
Handling the Edge Cases
- What if my users search in multiple languages? Use a multilingual embedding model like
paraphrase-multilingual-MiniLM-L12-v2. It maps 50+ languages into a shared vector space. - What if my dataset grows to 1 billion rows? Switch to the
pgvectorscaleextension and use the DiskANN index. It stores the graph on disk and uses SSD-optimized I/O. - What if I need to update embeddings in real-time? Use a database trigger to push changed IDs to a Redis queue, and have a background worker regenerate the embeddings asynchronously.
The Bottom Line
LIKE '%term%'forces full table scans and is fundamentally unsuited for modern, scalable search.- AI semantic search uses embedding vectors to understand user intent, matching concepts rather than exact strings.
- The
pgvectorextension brings native vector search to PostgreSQL, eliminating the need for expensive external clusters. - HNSW indexes provide millisecond query latency but require significant RAM; monitor memory usage closely.
- For billion-scale workloads,
pgvectorscale(DiskANN) offers a disk-based alternative that drastically reduces memory overhead. - Hybrid search (RRF) combines the exact precision of full-text search with the conceptual recall of vector search.
- Always secure your vector columns and redact PII before embedding to prevent model inversion attacks.
Questions I Get Asked Constantly
How accurate is semantic search compared to exact pattern matching?
Depending on the dataset and evaluation methodology, semantic search can significantly improve relevance over keyword-only matching. It automatically handles synonyms and context. For exact SKU searches, a hybrid approach ensures precision.
What's the storage cost of adding embedding vectors?
A 384-dimensional vector consumes ~1.5KB per row. For a million-row table, that's about 1.5GB of raw data. The pgvector HNSW index adds another 30-50% overhead. It is highly efficient compared to external search clusters.
How do I keep embeddings updated when my data changes?
Embeddings should be regenerated whenever the source text changes. This can be done synchronously in the save transaction, asynchronously via a message queue, or in batch nightly. Asynchronous queues are recommended for production.
Can I use semantic search with languages other than English?
Yes. Multilingual embedding models like paraphrase-multilingual-MiniLM-L12-v2 support 50+ languages. They map text from different languages into a shared vector space, allowing seamless cross-lingual search.
How does pgvector compare to dedicated search engines like Elasticsearch?
Elasticsearch excels at massive-scale text search with complex relevance tuning but adds operational overhead. For most applications with up to tens of millions of documents, pgvector provides comparable semantic quality with dramatically simpler operations.
Quick Reference: The Jargon Decoded
- AI Semantic Search
- A search methodology that understands the intent and contextual meaning of a query, rather than just matching exact keywords, typically powered by embedding vectors.
- Embedding Vectors
- High-dimensional arrays of floating-point numbers that represent the semantic meaning of text, images, or other data in a continuous vector space.
- pgvector
- An open-source PostgreSQL extension that adds vector data types and operators for approximate nearest neighbor (ANN) search.
- pgvectorscale
- An advanced PostgreSQL extension developed by Timescale that introduces the DiskANN algorithm for highly efficient, disk-based vector search at a billion-row scale.
- HNSW (Hierarchical Navigable Small World)
- A graph-based algorithm for approximate nearest neighbor search that builds multi-layered connections for fast, memory-intensive vector retrieval.
- DiskANN
- A disk-based Approximate Nearest Neighbor algorithm that stores vector graphs on SSDs, drastically reducing RAM requirements while maintaining high recall and low latency.
- Cosine Similarity
- A metric used to measure how similar two vectors are, based on the cosine of the angle between them. Ranges from -1 (opposite) to 1 (identical).
- Reciprocal Rank Fusion (RRF)
- A method for combining multiple result sets (e.g., keyword search and semantic search) into a single ranked list based on the reciprocal of their ranks.
- Prompt Engineering
- The practice of designing and refining inputs (prompts) to guide AI models and large language models to generate accurate, context-aware outputs, a core focus of modern AI-DBMS integration.
- Autonomous Database Systems
- Database architectures that leverage AI and machine learning to automate tuning, security, backup, and optimization without manual human intervention.
Where Do We Go From Here?
The era of relying on brittle LIKE queries is over. By embracing AI semantic search with pgvector and pgvectorscale, you can deliver search experiences comparable to dedicated semantic search systems directly inside your PostgreSQL database.
To continue your journey into AI-driven database management, explore these related deep-dives:
- AI Database Postmortem: AI That Diagnoses Itself
- Autonomous Tuning – Why You Can't Afford Manual Tuning Anymore
- Time Series + AI – Why Your Current Database Is Failing
- Conversational Databases: Query with Natural Language
- AI Memory Layer – Why Vector Databases Are Not Enough
For a complete index of all our technical guides, visit the Complete Blog Index.
Further Reading & Academic Sources
- pgvector GitHub Repository — Open-source vector similarity search for Postgres. github.com/pgvector
- Timescale Blog — "pgvectorscale: Instant vector search at scale". Details the DiskANN implementation for billion-scale workloads. timescale.com
- HuggingFace — Model cards and documentation for state-of-the-art embedding models (e.g., all-MiniLM-L6-v2). huggingface.co
- Google Cloud AI — Documentation on text embeddings and vector search implementations. cloud.google.com
- PostgreSQL Official Documentation — CREATE INDEX, HNSW/IVFFlat parameters, and B-tree limitations with leading wildcards. postgresql.org
- Microsoft Research — DiskANN: Fast, Accurate, Billion-point Nearest Neighbor Search on a Single Node (NeurIPS 2019). learn.microsoft.com
- IEEE TPAMI (2018) — Malkov, D. A., & Yashunin, D. A. "Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs." arxiv.org/abs/1603.09320
- SIGIR (2009) — Cormack, C. L., Clarke, C. L., & Buettcher, S. "Reciprocal rank fusion outperforms Condorcet and individual Rank Learning Methods." plg.uwaterloo.ca
- Stanford University — Manning, C. D., Raghavan, P., & Schütze, H. "Introduction to Information Retrieval" (Cosine Similarity mathematics). nlp.stanford.edu
- IEEE Computer Society — IEEE 754-2008 Standard for Floating-Point Arithmetic (Storage calculation: 384 dimensions × 4 bytes/float = 1536 bytes).
- Reddy, A. P. (2024). Database Management Using AI: A Comprehensive Guide. Amazon / Google Play
- Reddy, Purushotham A. (2025). Advancing Database Management Through Artificial Intelligence. DOI: 10.55041/ISJEM05102. DOI Link
- Reddy, A. P. (2025). Mastering AI Prompt Engineering and Database Systems. Zenodo. zenodo.org
- Reddy, A. P. (2025). Prompt Gigs in 30 Days. Google Play Books
- Reddy, A. P. (2026). Learn AI Skills and Earn Online. Internet Archive. archive.org
Breaking Down the Visuals
Figure 1 illustrates the end‑to‑end flow of a semantic search system. It shows how a user's natural-language query is processed through NLP, converted into an embedding vector, searched against a vector database using ANN algorithms, and finally reranked by AI. This architecture demonstrates how modern databases move beyond simple keyword matching to truly understand user intent, all without requiring a separate, expensive search infrastructure.
Figure 2 demonstrates the practical outcome of AI semantic search in action. The user typed a conversational question about recovering from a database crash. This context of automated database recovery is highly relevant to modern systems employing AI checkpoint scheduling and recovery optimization alongside automated AI database backup and recovery plans. Notice that the results returned—"Point-in-Time Recovery" and "High availability failover"—do not contain the exact words "crash" or "recover". Yet, they are the most conceptually relevant answers. This visual proves that semantic search understands the underlying meaning and context of a query, delivering highly accurate results that traditional LIKE queries would completely miss.
Figure 3 captures the dramatic business impact of migrating from pattern matching to AI semantic search. It contrasts the frustration of low relevance (32%) and slow speeds with the delight of 94% relevance and instant responses. This transformation isn't just a technical upgrade; it directly translates to higher user satisfaction, increased conversion rates, and reduced operational overhead, proving that the ROI of implementing pgvector is realized almost immediately.
Note: All diagrams in this article were created by the author using AI-assisted design tools for illustrative purposes.
: