I'm going to use one system throughout this post — a customer support platform for an outdoor gear retailer. Specific enough to be concrete, complex enough to show the real tensions, and the code examples are real patterns you could adapt today. I'll follow the architecture through what SQL handles well, what it handles badly, what vector search handles well, and what happens when you mix them up.
The goal is not to declare a winner. It's to understand what each tool is actually for.
The System We're Building
The support platform has: customers, orders, tickets, agents, articles in a knowledge base, and SLA rules. Agents need to find relevant articles when handling tickets. Managers need SLA compliance reports. The system auto-tags incoming tickets and suggests resolutions.
This is not an exotic system. Hundreds of companies have some version of it. And it's a perfect example because it has data that needs strict consistency and data that benefits enormously from semantic search. Both exist in the same application. Neither replaces the other.
What SQL Is Doing — And Why You Cannot Replace It
When a customer submits a ticket, this needs to happen atomically. Either everything succeeds or nothing changes:
BEGIN;
INSERT INTO tickets (customer_id, subject, body, status, priority, created_at)
VALUES (8472, 'Wrong item shipped', 'I ordered climbing rope...', 'open', 'high', NOW())
RETURNING id INTO ticket_id;
INSERT INTO ticket_assignments (ticket_id, agent_id, assigned_at)
SELECT ticket_id, id, NOW()
FROM agents
WHERE status = 'available'
ORDER BY current_load ASC
LIMIT 1;
UPDATE agents
SET current_load = current_load + 1
WHERE id = (SELECT agent_id FROM ticket_assignments WHERE ticket_id = ticket_id);
INSERT INTO sla_tracking (ticket_id, priority, deadline)
VALUES (ticket_id, 'high', NOW() + INTERVAL '4 hours');
COMMIT;If the agent assignment fails — no available agents, or a network error halfway through — everything rolls back. No half-created ticket. No orphaned SLA record. No incremented load counter for a nonexistent assignment.
This is what ACID means in practice. No vector database provides this today. Not because they're poorly engineered — because their indexes (HNSW graphs, IVF quantization) are optimized for approximate nearest-neighbor search, a fundamentally different operation that is architecturally incompatible with multi-row transactional consistency.
What Happens When You Do It Wrong
The vector-first failure mode, made concrete:
async def create_ticket_wrong(customer_id: int, subject: str, body: str):
# Step 1: store in vector DB
embedding = await embed(body)
ticket_id = await vector_store.upsert({
'customer_id': customer_id,
'subject': subject,
'body': body,
'embedding': embedding,
'status': 'open'
})
# Step 2: assign agent
agent = await find_available_agent()
# ← What if the service is unavailable here?
# ticket_id exists in the vector store. Agent assignment never happens.
# No error surfaced. No rollback. No way to query 'unassigned tickets'
# without scanning the entire high-dimensional index.
await vector_store.upsert({'ticket_id': ticket_id, 'agent_id': agent.id})
# Step 3: SLA
# If this fails: ticket exists, is assigned, but SLA is never tracked.
# Shows up in compliance reports six weeks later. Not as an error.
await vector_store.upsert({'ticket_id': ticket_id, 'deadline': calculate_deadline()})When step 2 fails in SQL, you roll back. When it fails here, you have an orphaned record in a high-dimensional index with no way to find it except by scanning everything. 'Schema migrations no longer exist' is replaced by 'data consistency no longer exists.' That is a bad trade.
What Vector Search Is Actually Good For
Same system. An agent opens a ticket about a damaged carabiner. They need relevant knowledge base articles. The ticket says 'the locking gate is bent and won't close' but the relevant article is titled 'Carabiner inspection and retirement criteria.' No keyword match. Full-text search returns nothing.
Vector search solves this. Here's the actual pattern using pgvector — PostgreSQL's vector extension, which is the most practical choice for most teams because it runs in the database you already have:
-- Articles table with vector column
CREATE TABLE knowledge_base (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
category TEXT,
updated_at TIMESTAMPTZ DEFAULT NOW(),
embedding VECTOR(1536) -- text-embedding-3-small output dimension
);
-- HNSW index: fast approximate nearest-neighbor, tuned for recall/speed tradeoff
CREATE INDEX knowledge_base_embedding_idx
ON knowledge_base
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);-- Find articles semantically similar to a ticket body
SELECT
id,
title,
LEFT(content, 300) AS preview,
1 - (embedding <=> $1::vector) AS similarity
FROM knowledge_base
WHERE
1 - (embedding <=> $1::vector) > 0.72
AND category = $2
ORDER BY embedding <=> $1::vector
LIMIT 5;And the Python layer that calls it:
import openai
from psycopg2.extras import RealDictCursor
def suggest_articles(ticket_body: str, category: str, conn) -> list[dict]:
# Embed the ticket using the same model used to embed articles
response = openai.embeddings.create(
input=ticket_body,
model='text-embedding-3-small'
)
query_vector = response.data[0].embedding
with conn.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute("""
SELECT id, title, LEFT(content, 300) AS preview,
1 - (embedding <=> %s::vector) AS similarity
FROM knowledge_base
WHERE category = %s
AND 1 - (embedding <=> %s::vector) > 0.72
ORDER BY embedding <=> %s::vector
LIMIT 5
""", [query_vector, category, query_vector, query_vector])
return cur.fetchall()
# Usage:
articles = suggest_articles(
ticket_body='The locking gate on my carabiner is bent and wont close',
category='gear',
conn=db_conn
)
# Returns: 'Carabiner inspection and retirement criteria',
# 'When to retire PPE', 'Hardware inspection checklist'
# No keyword match needed. Semantic proximity does the work.One detail worth noting: the query vector and the article embeddings must be produced by the same model. Switching models later means re-embedding every article in the table. That is the 'schema migration that doesn't exist' — it does exist, it's just a different shape.
The Architecture in One Diagram
PostgreSQL (single instance, pgvector extension)
|
+-- Transactional data (ACID) Vector data (ANN search)
| tickets knowledge_base.embedding
| agents tickets.resolution_embedding
| customers tag_examples.embedding
| orders
| sla_tracking
| assignments
|
+-- Write path Read path (agent UI)
1. SQL transaction 1. Fetch ticket (SQL)
2. Embed ticket body 2. Suggest articles (vector)
3. Store embedding 3. SLA status (SQL)
in same transaction 4. Similar past tickets (vector)
5. Customer order hist (SQL)One database. Two query patterns. No separate system to operate, monitor, back up, or debug. For most teams at most scales, this is the right starting point.
Three More Real Patterns
Auto-tagging incoming tickets with k-nearest examples:
-- A small table of labeled examples per tag
CREATE TABLE tag_examples (
id SERIAL PRIMARY KEY,
tag TEXT NOT NULL,
example TEXT NOT NULL,
embedding VECTOR(1536)
);
-- kNN majority vote across the 9 nearest labeled examples
SELECT tag, COUNT(*) AS votes
FROM (
SELECT tag
FROM tag_examples
ORDER BY embedding <=> $1::vector
LIMIT 9
) candidates
GROUP BY tag
ORDER BY votes DESC
LIMIT 1;No ML training pipeline. No fine-tuned classifier. Just labeled examples and an embedding model. Works well for 10-30 categories. Degrades gracefully: add more examples to improve accuracy, no model retraining required.
Deduplication before creating a new ticket:
SELECT id, subject, customer_id, created_at,
1 - (embedding <=> $1::vector) AS similarity
FROM tickets
WHERE
created_at > NOW() - INTERVAL '24 hours'
AND status != 'resolved'
AND 1 - (embedding <=> $1::vector) > 0.92
ORDER BY embedding <=> $1::vector
LIMIT 3;
-- If results: offer to link to the existing ticket
-- instead of creating a duplicateA threshold of 0.92 means near-identical. 0.80 means related but possibly different problems. Calibrate empirically against your actual data — no vendor can tell you the right number in advance.
Suggesting successful past resolutions while an agent is typing:
-- When a ticket is marked resolved, store the resolution embedding
UPDATE tickets
SET
resolution_embedding = $1::vector,
resolution_text = $2,
status = 'resolved',
resolved_at = NOW()
WHERE id = $3;
-- Find similar resolutions for a new ticket
SELECT
t.resolution_text,
t.resolution_category,
1 - (t.resolution_embedding <=> $1::vector) AS similarity
FROM tickets t
WHERE
t.status = 'resolved'
AND t.resolution_embedding IS NOT NULL
AND 1 - (t.resolution_embedding <=> $1::vector) > 0.80
ORDER BY t.resolution_embedding <=> $1::vector
LIMIT 3;The agent sees the three most similar past successful resolutions while working the ticket. No separate recommendation service. No RAG orchestration layer. Two SQL queries and an embedding call.
Practical Decisions for a Team Starting This
Start with pgvector, not a dedicated vector database. The operational overhead of a separate system — separate backups, monitoring, failure modes, connection management — is real. pgvector gives you 80% of the capability at 20% of the operational cost. When you're consistently searching over 10-20 million vectors with sub-50ms latency requirements, revisit it. Most applications never reach that scale.
Choose your embedding model once and treat it as a schema decision. text-embedding-3-small (1536 dimensions) is a reasonable default for most English-language applications. Open-source alternatives like all-MiniLM-L6-v2 (384 dimensions) run locally at lower cost and latency. Whichever you choose: changing it later means re-embedding every record in every table that uses it. Budget for that.
Set thresholds empirically. The similarity cutoffs in the examples above — 0.72, 0.80, 0.92 — are starting points. What works depends on your data and your embedding model. Run your actual queries against a sample of your actual data and measure precision and recall before hardcoding anything.
Keep transactional data in SQL. If you're tempted to move orders, payments, assignments, or anything with consistency requirements into a vector store 'for simplicity,' resist it. The complexity you eliminate in the store reappears as data integrity bugs in the application layer. Those bugs are harder to find than the schema management you were trying to avoid.
The Honest Summary
Vector search is not competing with relational databases. It handles one specific class of query — finding semantically similar items over unstructured data — extremely well. Relational databases handle a different class — consistent, transactional, structured — extremely well. A real system needs both.
The 'death of SQL' has been announced in every decade since the 1980s. SQL survives because ACID guarantees are requirements, not preferences, for most applications that handle money, inventory, access, or anything where correctness matters. Vector search adds a genuinely useful capability to that stack. It doesn't replace the stack.
The architecture worth building is not 'choose one.' It's one database — PostgreSQL with pgvector for most teams — handling both query patterns with clear separation between the data that needs transactions and the data used for semantic retrieval. That's less dramatic than a death narrative. It's also what works.
The code examples here are simplified for clarity but represent real production patterns. The pgvector GitHub repository and its documentation are more useful for implementation detail than most blog posts, including this one.