pgvector

Core concepts

1. Vector search inside Postgres: If you're already on Postgres (or Supabase, which ships pgvector by default), this is the lowest-friction way to add vector search without a new database to manage. pgvector GitHub
2. Index types: IVFFlat and HNSW trade off build time, query speed, and recall differently — worth understanding both before picking one at scale.
3. When to graduate to a dedicated vector DB: Once query volume or index size genuinely outgrows what Postgres can serve efficiently, not before.

What Is It?

pgvector adds vector types and indexes to PostgreSQL.

Supabase exposes it with helpful defaults; IVFFlat and HNSW trade build time vs recall.

Keeps transactional data and embeddings co-located.

Why Should I Care?

One database simplifies ops for early RAG products.

Joins between metadata and vectors stay SQL-familiar.

Real World Analogy

pgvector is adding a compass to the database you already trust — no second warehouse until your map gets crowded.

Visual Mental Model

Postgres rows + embedding column
  IVFFlat / HNSW indexes

Beginner Explanation

Run vectors inside Postgres via pgvector — great when you already use Supabase/Neon/RDS. Know when to graduate to dedicated vector SaaS.

Smallest Possible Example

DIM = 1536
print("pgvector column dims:", DIM)

Common Mistakes

❌ No index on embedding column at scale.

Sequential scans melt latency at millions of rows.

❌ Wrong index type for update-heavy tables.

HNSW vs IVFFlat have different maintenance costs.

❌ Storing giant embeddings without dimension planning.

Disk and memory balloon; pick embedding model early.

Real Project Example

Supabase AI templates showcase pgvector RAG.

Teams on RDS Postgres add pgvector before buying separate search infra.

Build This Mini Project

Enable pgvector locally, insert ten rows with fake 3D vectors, and write a SQL query for nearest neighbor.

Interview Question

When stay on pgvector vs dedicated vector DB?

Stay when data fits Postgres ops, need strong transactional joins, and QPS/recall needs are modest; graduate when search scale, multi-region, or specialized filtering outgrows Postgres tuning.

Key Takeaways

• Vectors inside Postgres simplify early RAG.
• Pick and tune indexes deliberately.
• Co-locate metadata with SQL.
• Plan graduation criteria to SaaS vector DB.

Resources

• pgvector GitHub
• Supabase: Vector/Embeddings

YouTube learning

Core concepts from video walkthroughs:

• OpenAI embeddings on Supabase (pgvector)
• pgvector for Python developers
• Postgres vector search fundamentals

The 60-second story

You already have a warehouse. Postgres is where the orders, users, and invoices live. pgvector is a new aisle inside that warehouse for “shelve this by meaning,” not a second warehouse across town. Supabase turns the aisle on for a lot of people by default. If your app is already a SQL app, this is the lowest-friction way to try retrieval without a new login, a new backup story, and a new way to say “delete this user’s data.”

Indexes are how the aisle stays walkable when it grows. IVFFlat and HNSW are two ways to build shortcuts. They trade build time, speed, and how often the shortcut misses a book that was actually close. You do not need a theology degree. You need to know “recall” means “did we find the real neighbors,” and that a fast index can be slightly wrong on purpose. Measure that on your data before you write a blog post about it.

Graduation is the humble part. A dedicated vector database is a second warehouse. You move when Postgres is actually sweating: query volume, index size, features you cannot get in-process. You do not move because a conference talk made a second warehouse sound professional. Two backups, two failure modes, and a join you can no longer do in one query are the rent.

Map the warehouse.

• Postgres is the building you already run.
• pgvector is a new aisle.
• IVFFlat and HNSW are shortcut maps with different miss rates.
• Recall is “did the shortcut find the right books.”
• A dedicated vector DB is a second building.
• A join between an order row and a chunk is why one building is nice.

select id, content
from docs
order by embedding <-> :query
limit 5;

The order row and the chunk can live in one transaction. That is the feature people forget while they are excited about neighbors.

pgvector exists because most apps needed “a bit of RAG” next to the data they already trust. The index types exist because a sequential scan of every embedding is a walk down every aisle. Leaving Postgres is a scale decision, not a personality.

Mental model: a new aisle, not a new city. Stay until the aisle is honestly too crowded. Then move, with a reason you can say out loud.

You have the page if you can name the two index styles as a tradeoff, and if “we might need Pinecone later” is a measurement, not a mood.