Overview
Level 2: Build · Module 5: Embeddings and Vector Search · 25 min read
Why this matters
Frontend and full-stack developers often assume that every AI feature requires provisioning a dedicated, expensive cloud vector database like Pinecone or Milvus. In reality, modern applications have widely disparate scale requirements: a client-side offline tool or local desktop app might only need SQLite-vss running in WebAssembly; a standard web application with 200,000 documents is best served by PostgreSQL with the pgvector extension; and an enterprise search engine with 500 million vectors across 100 clusters demands specialized systems like Pinecone, Qdrant, or Weaviate. Choosing the wrong database incurs unnecessary infrastructure complexity, network latency, and monthly cloud bills.
Learning objectives
- Compare pgvector, Pinecone, Qdrant, and SQLite-vss across architecture, operational complexity, and cost.
- Understand relational data co-location: why storing embeddings alongside business rows in PostgreSQL is often the winning architecture.
- Configure pgvector in Prisma and raw SQL.
- Evaluate in-memory client-side vector search using WASM and SQLite-vss.
- Formulate a technical defense for vector database selection in system design interviews.
Architectural Comparison Matrix
| Attribute / Vector Store | PostgreSQL (pgvector) | Pinecone (Managed Cloud) | SQLite-vss / Orama (In-Memory/Local) |
|---|---|---|---|
| Primary Architecture | Relational DB extension (C extension) | Proprietary managed distributed cloud | Embedded C / WASM vector engine |
| Data Co-location | YES (joins embeddings with users, teams) | NO (requires synchronizing two DBs) | YES (runs locally in file or browser memory) |
| Transactions & ACID | Full ACID transactions & foreign keys | Eventually consistent / No ACID joins | Single-file ACID transactions |
| Scaling Ceiling | ~10 million vectors per instance | Billions of vectors with auto-sharding | ~50,000 to 200,000 vectors |
| Cold Starts & Latency | Direct SQL connection (~1ms to 5ms) | Extra HTTPS network roundtrip (~20ms to 60ms) | Zero network roundtrip (<1ms in-memory) |
| Operational Cost | $0 extra (runs on existing RDS/Supabase) | $70/mo+ per pod or consumption fees | $0 (free, runs client-side or on edge) |
| Best Used For | 90% of all SaaS & Next.js applications | Massive multi-billion vector catalogs | Offline-first apps, local tools, browser search |
graph TD
subgraph CoLocated["Co-Located pgvector (Simpler Architecture)"]
User1["Next.js Route"] --> Postgres["Single PostgreSQL Database"]
Postgres --> Tables["User Auth / Billing / Posts"]
Postgres --> Vectors["pgvector: Embedding Chunks"]
Note1["Single SQL query joins documents with user permissions!"]
end
subgraph SplitArch["Dual-Database Architecture (Pinecone / Qdrant)"]
User2["Next.js Route"] --> MainDB["PostgreSQL (User Data)"]
User2 --> CloudVec["Pinecone Cloud (Vector Index)"]
Note2["Must sync IDs across both DBs; two network roundtrips!"]
endThe Superpower of pgvector: Relational Filtering & Joins
The greatest advantage of pgvector is metadata filtering via standard SQL WHERE clauses.
When using a dedicated cloud vector database, searching with user-level access controls requires either:
- Pre-filtering: Restricting the vector search space using metadata tags (can degrade index recall).
- Post-filtering: Retrieving top-100 vectors, then querying your main database to discard rows the user doesn't have permission to see (risks returning 0 results if the top-100 belong to other tenants!).
With pgvector, permissions, multi-tenancy, and vector similarity happen in a single atomic SQL query:
-- Single query: Vector similarity JOINed with team permissions
SELECT
d.id,
d.title,
c.content,
1 - (c.embedding <=> $1) AS similarity_score
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
JOIN organization_members om ON om.org_id = d.org_id
WHERE om.user_id = $2 -- Strict tenant isolation!
AND d.is_archived = false -- Business logic filter!
ORDER BY c.embedding <=> $1
LIMIT 5;Setting Up pgvector in Next.js with Prisma
Prisma natively supports PostgreSQL extensions and raw queries for vectors:
// prisma/schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
extensions = [vector]
}
model DocumentChunk {
id String @id @default(cuid())
documentId String
content String
// Unsupported type allows Prisma to manage the table while letting pgvector handle the column
embedding Unsupported("vector(1536)")
createdAt DateTime @default(now())
@@index([embedding], map: "chunk_embedding_hnsw_idx")
}When to Upgrade to a Dedicated Vector DB (Pinecone / Qdrant)
While pgvector handles millions of rows effortlessly, migrate to a dedicated vector engine when:
- Dataset exceeds 20 million vectors: The memory requirements for holding high-dimensional HNSW graphs in RAM begin competing with Postgres buffer cache for relational tables.
- Multi-Region Global Sharding: You need millisecond vector latency across US, EU, and Asia with automated distributed sharding.
- Specialized Hybrid Search Engines: Systems like Qdrant provide native hybrid vector + sparse BM25 payload scoring out of the box with zero external configuration.
In-Browser Vector Search: SQLite-vss & Orama
For desktop apps (Electron), local tools, or browser-only offline search, you can run vector search directly in JavaScript or WebAssembly:
// Using Orama (in-memory TypeScript vector search)
import { create, insert, search } from "@orama/orama";
const db = await create({
schema: {
title: "string",
embedding: "vector[1536]",
},
});
await insert(db, {
title: "React 19 Hooks Guide",
embedding: [0.021, -0.043, /* ... 1536 floats */],
});
// Lightning-fast search with 0 network latency!
const results = await search(db, {
mode: "vector",
vector: {
value: queryVector,
property: "embedding",
},
similarity: 0.8,
limit: 5,
});Interview Angle: How to Answer in System Design
- Question: "Would you use Pinecone or pgvector for a multi-tenant B2B SaaS documentation assistant?"
- Target Answer:
"For a B2B SaaS application, I strongly recommend pgvector co-located in our existing PostgreSQL database.
The decisive factor is tenant isolation and relational joins. In a multi-tenant enterprise app, search queries must strictly enforce organization boundaries, user role permissions, and soft-delete states. In pgvector, we combine vector distance (
<=>) with relationalJOIN organization_membersin a single atomic SQL query with full ACID consistency.Using Pinecone would introduce dual-system synchronization overhead (keeping IDs, deletes, and permissions in sync across two separate databases) and extra network latency, without offering measurable benefits unless our vector volume exceeds tens of millions of rows."
Quick Check
- What is the primary operational advantage of pgvector over a standalone cloud vector database like Pinecone?
- What is the difference between pre-filtering and post-filtering in vector databases?
- At what scale does migrating from pgvector to a dedicated distributed vector database become justified?