How to Set Up a Vector Database with Supabase pgvector

Building AI applications is exciting.

Managing three different databases to do it? Not so much.

If you are focused on building custom AI knowledge bases with RAG, you already know that you need a place to store your vector embeddings.

Most developers rush to spin up a standalone vector database.

Here is the problem with that:

Now you have one database for your users, one database for your app data, and a third database just for your vectors.

Keeping all three in sync is an absolute nightmare.

Luckily, there is a much cleaner way to do this.

You can run your vector database right inside PostgreSQL using Supabase and pgvector.

Here is the exact step-by-step guide to setting it up.

Why Supabase and pgvector

Let’s be honest: dedicated vector databases are great for niche use cases.

Bottom line? For 95% of web applications, they add unnecessary complexity.

When you use Supabase with the pgvector extension, everything lives under one roof.

Here is why this matters:

• Zero Synchronization Lag: You do not need webhook pipelines or cron jobs to mirror user data to a separate vector store.

• Built-in Row-Level Security (RLS): You can secure your vector embeddings using standard Supabase auth policies. If User A shouldn’t see User B’s documents, Postgres handles that at the database level automatically.

• Standard SQL Queries: You can join your vector similarity results directly with your relational tables (like users, subscriptions, or teams) in a single query.

It keeps your stack simple, reliable, and cheap.

Now, let’s get into the setup.

Step 1: Enabling the pgvector Extension

(Word count: ~135 words)

To turn PostgreSQL into a high-performance vector store, you need to enable the vector extension.

If you already have a Supabase project running, this takes about three seconds.

Open your Supabase dashboard, head over to the SQL Editor, and run this command:

-- Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

That’s literally it.

What just happened under the hood?

PostgreSQL now has access to a new data type called vector.

It also gives your database the mathematical operators required to calculate distances between arrays of high-dimensional numbers (like cosine distance, L2 distance, and inner product).

Step 2: Creating Your Document Schema

Next, you need a table to store your text chunks along with their vector embeddings.

Let’s create a standard documents table:

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  embedding VECTOR(1536),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

Let’s break down the key parts of this schema:

• content: This stores the raw text chunk that your embedding was generated from.

• metadata: A JSONB column to store helpful tags, document names, author IDs, or source URLs for filtering.

• embedding VECTOR(1536): This is where the magic happens. The number 1536 represents the exact dimensions output by OpenAI’s standard embedding models (like text-embedding-3-small or text-embedding-ada-002).

(Note: If you use a different model like Cohere or an open-source Hugging Face model, make sure you adjust the dimension number to match).

Step 3: Indexing for Speed (HNSW vs. IVFFlat)

When your table has only 100 rows, vector queries are lightning fast.

The catch?

As your database grows to hundreds of thousands of vectors, running exact nearest-neighbor calculations across every single row will crush your database CPU.

You need an index.

In pgvector, you have two main indexing choices:

1. IVFFlat (Inverted File Flat): Groups vectors into clusters. It builds quickly and uses less memory, but requires regular retraining when new data is added.

2. HNSW (Hierarchical Navigable Small World): Builds a multi-layer graph of vectors. It uses more RAM to build, but delivers significantly faster queries and higher recall.

For production RAG systems, HNSW is almost always the winner.

Here is how to create an HNSW index using Cosine distance:

CREATE INDEX ON documents 
USING hnsw (embedding vector_cosine_ops);

This ensures your database performs Approximate Nearest Neighbor (ANN) lookups in milliseconds.

Step 4: Querying with Cosine Similarity

Now that your vectors are stored and indexed, how do you search them?

You write a custom PostgreSQL stored procedure (Function) to perform the similarity search.

Run this in your SQL Editor:

CREATE OR REPLACE FUNCTION match_documents (
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)
RETURNS TABLE (
  id BIGINT,
  content TEXT,
  similarity FLOAT
)
LANGUAGE sql STABLE
AS $$
  SELECT
    documents.id,
    documents.content,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM documents
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;
$$;

The <=> operator computes the Cosine distance. We subtract it from 1 to convert distance into a similarity score (where 1.0 is a 100% exact match).

Next Steps

You now have a fully functional, production-ready vector database running directly inside your PostgreSQL instance.

You can call this match_documents function directly from your Node.js or Python backend using the standard Supabase client library:

const { data, error } = await supabase.rpc('match_documents', {
  query_embedding: userQueryVector,
  match_threshold: 0.78,
  match_count: 5,
});

From here, you are ready to plug this database directly into your retrieval pipeline, fetch relevant context, and stream grounded answers back to your users.