What Is a Vector Database? When Postgres With pgvector Is Enough

A traditional SQL query uses exact matching or pattern matching:

SELECT * FROM articles WHERE title ILIKE '%mobile money%';

This finds rows that contain the literal phrase "mobile money." It misses articles titled "M-Pesa for beginners" or "How to send cash from your phone," even though they are about the same topic.

Vector search works differently. Instead of matching text, it compares the mathematical distance between embedding vectors. Two texts about the same concept have vectors that are close together, regardless of the words used. The query becomes: "Find me the 5 vectors closest to this query vector."

This is called a nearest-neighbor search, and it requires a data store optimized for that operation.

At a high level:

1. You generate an embedding vector for each document (done once, at indexing time).
2. You store those vectors in the database.
3. When a query comes in, you embed the query text using the same model.
4. The database compares the query vector against all stored vectors using a distance metric (usually cosine similarity).
5. It returns the top-k most similar documents.

The naive approach compares the query against every stored vector. That works for thousands of documents, but becomes slow at millions. Dedicated vector databases use approximate nearest neighbor (ANN) algorithms, like HNSW or IVFFlat, that trade a tiny bit of accuracy for massive speed improvements.

pgvector supports both exact search and HNSW indexes, so you can start simple and add an index when performance demands it.

If you are already running Supabase (or any Postgres instance), pgvector is the simplest path. Enable the extension and create a table:

-- Enable pgvector
create extension if not exists vector;

-- Create a table with a vector column
create table documents (
  id bigserial primary key,
  content text not null,
  source text,
  embedding vector(1536) -- matches OpenAI text-embedding-3-small
);

-- Create an HNSW index for fast similarity search
create index on documents
  using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

Then create a search function:

create or replace function search_documents(
  query_embedding vector(1536),
  match_count int default 5
)
returns table (
  id bigint,
  content text,
  source text,
  similarity float
)
language sql stable
as $$
  select
    id,
    content,
    source,
    1 - (embedding <=> query_embedding) as similarity
  from documents
  order by embedding <=> query_embedding
  limit match_count;
$$;

Call it from your application code:

const { data } = await supabase.rpc('search_documents', {
  query_embedding: queryVector,
  match_count: 5,
});

That is your entire vector search setup. No extra service to deploy, no additional bill to manage.

Dedicated vector databases like Pinecone, Weaviate, Qdrant, and Milvus are built specifically for high-volume vector operations. Consider one when:

• You have millions of vectors. pgvector handles tens of thousands of vectors well. At hundreds of thousands, HNSW indexes keep it workable. Beyond a million, a dedicated solution may give you better latency.
• You need filtering plus vector search. "Find the 5 most similar documents, but only from the healthcare category, created after January 2026." Dedicated vector databases handle these hybrid queries more efficiently at scale.
• You need multi-tenancy at scale. If you are building a SaaS where each tenant has their own document collection, dedicated solutions offer namespace isolation that is cleaner than Postgres row-level filtering.

For a startup building its first AI feature, a student project, or a SACCO chatbot with a few hundred policy documents, pgvector in your existing Postgres database is the right starting point. You can always migrate later if scale demands it.

Here is how they compare on the factors that matter for most projects:

• Setup complexity: pgvector is one SQL statement (create extension vector). Dedicated databases need a separate service, API keys, and a new SDK in your stack.
• Cost: pgvector uses your existing Postgres instance, so the marginal cost is zero until you need a bigger server. Dedicated databases charge per vector, per query, or per GB.
• Performance at small scale (under 100k vectors): Both are fast enough that you will not notice a difference.
• Performance at large scale (over 1M vectors): Dedicated databases are built for this. pgvector can do it but may need careful tuning of HNSW parameters and more RAM.
• Ecosystem: pgvector lets you join vector search results with your relational data in a single query. Dedicated databases require you to fetch IDs from the vector store, then look up metadata in your main database.

The pragmatic rule: use what you already have. If Postgres is your database, start with pgvector. If you outgrow it, the migration to a dedicated store is straightforward because the embedding generation and application logic stay the same.