Supabase + Vercel Production Architecture: pgvector & RLS at Scale

Last quarter was quite the ride -- we pushed a production system that needed to juggle over 162,000 records using vector similarity search, multi-tenant Row Level Security, and it had to spit out responses in under 200ms. All this magic happened with Supabase and the deployment genius of Vercel. Sounds impressive, right? But, oh boy, getting this contraption to work was a saga of painful debugging, a massive overhaul of how we index stuff, and more than a few moments where I wondered if jumping to a dedicated vector database would've saved a few sleepless nights.

Alright, let's dive into the architecture breakdown -- none of that glossy marketing fluff. This is the gritty, real version -- trade-offs, headaches, and all the clever configurations that actually made the thing tick.

The Problem Space

So, the project was a multi-tenant knowledge base platform. Imagine it like a specialized RAG (Retrieval-Augmented Generation) system. Every tenant (like an org) uploads documents, we roll out embeddings using OpenAI’s text-embedding-3-small model (a whooping 1536 dimensions!), and users get to query their org's knowledge base using good ol’ natural language.

Here's how it looked at launch:

• 162,000+ document chunks with vector embeddings
• 47 tenant organizations, each demanding strict data isolation
• About 800 users going full blast during peak hours
• Target latency: under 200ms for similarity search, with all the authentication jazz included
• Budget constraint: Supabase Pro plan at $25/month + Vercel Pro at $20/month

Data isolation was ironclad. A no-go for sharing between a healthcare company and fintech. RLS wasn't just a good idea; it was a necessity.

Why Supabase + Vercel (and Why Not)

We sniffed around a few stack choices before settling down:

Stack	Pros	Cons	Monthly Cost (est.)
Supabase + Vercel	pgvector-ready, built-in RLS, super developer experience	Connection limits, shared resources on the Pro plan	$45-80
PlanetScale + Pinecone + Vercel	Has its own vector DB, MySQL scaling	Two databases to babysit, no RLS love for vectors	$120-200
Neon + Vercel	Serverless Postgres, the cool branching feature	pgvector support was just getting off the ground, cold starts	$40-70
Railway + self-hosted Postgres	All the control, no connection drama	Ops burden, no managed RLS solutions	$30-60
AWS Aurora + Lambda	Enterprise-ready, supports pgvector	Pricey once you scale, cold start blues, complex IAM	$200+

Why did we hitch our wagon to Supabase?

1. pgvector is king here. It's enabled out of the box, Supabase maintains the dang thing, and client libraries are prepped for vector searches.
2. RLS is baked right into Postgres. No harrowing security experiments in application code.
3. Vercel integrates seamlessly. Environment variables sync up like a dream, and @supabase/ssr manages auth cookies in Next.js components without a hiccup.

Downside? Connection pooling was a nagging gremlin. Let’s chew on that later.

Database Schema Design for pgvector + RLS

Here’s the schema after a few facepalms and retries:

-- Enable the vector extension
create extension if not exists vector;

-- Organizations (tenants)
create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique not null,
  settings jsonb default '{}',
  created_at timestamptz default now()
);

-- Organization memberships
create table public.org_members (
  id uuid primary key default gen_random_uuid(),
  org_id uuid references public.organizations(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  role text check (role in ('admin', 'member', 'viewer')) default 'member',
  unique(org_id, user_id)
);

-- Document sources
create table public.documents (
  id uuid primary key default gen_random_uuid(),
  org_id uuid references public.organizations(id) on delete cascade not null,
  title text not null,
  source_url text,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

-- The main vector table
create table public.document_chunks (
  id uuid primary key default gen_random_uuid(),
  document_id uuid references public.documents(id) on delete cascade not null,
  org_id uuid references public.organizations(id) on delete cascade not null,
  content text not null,
  embedding vector(1536) not null,
  chunk_index integer not null,
  token_count integer,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

Look, org_id is right there on document_chunks. Initially, we only kept document_id on chunks and joined documents to fetch the org. That JOIN, tangled with RLS checks, tacked on 80-120ms to each vector search. By denormalizing org_id on the chunks table, we shaved off nearly 40% of query time.

Yes, it feels dirty to carry redundant data. But we keep it synchronized with a trigger:

create or replace function sync_chunk_org_id()
returns trigger as $$
begin
  new.org_id := (select org_id from public.documents where id = new.document_id);
  return new;
end;
$$ language plpgsql;

create trigger set_chunk_org_id
  before insert or update on public.document_chunks
  for each row execute function sync_chunk_org_id();

Indexing Strategy: IVFFlat vs HNSW at 162K Records

Let’s shake up the bag with indexing. pgvector pitches two index types for ANN search: IVFFlat and HNSW.

Factor	IVFFlat	HNSW
Build time (162K records)	~45 seconds	~8 minutes
Index size (1536 dims)	~920 MB	~1.4 GB
Query latency (top-10)	15-35ms	8-18ms
Recall @ 10	0.92-0.95	0.97-0.99
Insert performance	Fast (no rebuild needed*)	Slower per insert
Memory usage	Moderate	High

*IVFFlat doesn’t need a rebuild for inserts, exactly, but recall degradation is sneaky as data distributions shift. REINDEX periodically, or it’ll bite you.

We rolled with IVFFlat to begin with because of speedy builds:

-- IVFFlat index (our initial dabble)
create index on public.document_chunks 
  using ivfflat (embedding vector_cosine_ops)
  with (lists = 400);

The lists parameter isn’t just digital fluff. The pgvector docs suggest sqrt(n) for up to 1M rows. With 162K records, ~402 was our magic number. We settled for 400.

Two weeks in, we ditched it for HNSW:

-- HNSW index (our current darling)
create index document_chunks_embedding_idx on public.document_chunks 
  using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 128);

Two motivators behind the switch:

1. Recall quality. Users grumbled about missing documents in the results. IVFFlat's recall of 0.92-0.95 is decent, but if a user knows a document’s there, “decent” doesn’t cut it.
2. No constant reindexing. IVFFlat nagged us with its periodic reindexing needs. HNSW auto-adjusts as new documents join the party.

Setting up HNSW consumed about 8 minutes on the Supabase Pro plan, rocketing CPU to 95%. Did it during a lull. Bumping ef_construction to 128 (instead of the default 64) improved recall though it did bulk up build time. Worth the wait.

For search queries, we pumped ef_search:

-- Pumping the search magic
set local hnsw.ef_search = 80;

Row Level Security That Doesn't Kill Performance

Most tutorials around RLS with pgvector fall apart on proof. They paint a rosy picture about RLS, but the reality of it playing with vector index scans isn’t all sunshine.

Our initial RLS policy for document_chunks:

alter table public.document_chunks enable row level security;

-- Policy for reading chunks
create policy "Users can read chunks from their org"
  on public.document_chunks
  for select
  using (
    org_id in (
      select org_id from public.org_members
      where user_id = auth.uid()
    )
  );

Looks lean, right? But drag this through a lacking index setup, and it’s a slog. The auth.uid() is reeled with every row eval, and subquery on org_members is a heavy lift.

Critical optimization #1: Index the org_members thoughtfully.

create index org_members_user_org_idx 
  on public.org_members(user_id, org_id);

Critical optimization #2: A security definer function for a precomputed org collection.

create or replace function get_user_org_ids()
returns setof uuid
language sql
stable
security definer
set search_path = public
as $$
  select org_id from org_members where user_id = auth.uid();
$$;

-- Updated policy
create policy "Users can read chunks from their org"
  on public.document_chunks
  for select
  using (org_id in (select get_user_org_ids()));

The stable keyword coaches Postgres to cache the result for the statement span rather than repeat calculations.

Critical optimization #3: A composite partial index on document_chunks featuring org_id.

create index chunks_org_id_idx on public.document_chunks(org_id);

With these tweaks, the RLS delays tumbled from ~85ms to ~12ms per query. Huge. That’s the threshold between a snappy app and one that tries your patience.

The RLS + Vector Search Query Pattern

Here’s our battle-tested search function:

create or replace function search_documents(
  query_embedding vector(1536),
  match_count int default 10,
  similarity_threshold float default 0.7
)
returns table (
  id uuid,
  content text,
  similarity float,
  document_id uuid,
  metadata jsonb
)
language plpgsql
security invoker  -- Important: relies on caller's RLS context
set search_path = public
as $$
begin
  return query
  select
    dc.id,
    dc.content,
    1 - (dc.embedding <=> query_embedding) as similarity,
    dc.document_id,
    dc.metadata
  from document_chunks dc
  where 1 - (dc.embedding <=> query_embedding) > similarity_threshold
  order by dc.embedding <=> query_embedding
  limit match_count;
end;
$$;

Notice security invoker. This keeps RLS intact by running with the caller’s permissions. Flip to security definer and RLS takes a backseat -- not the plan.

Vercel Edge Functions and Connection Pooling

Here’s where Vercel's serverless approach rubs Supabase's Postgres the wrong way: serverless spins up a crowd of short-lived connections, and Postgres grimaces when hundreds of connections pop open and shut repeatedly.

Supabase offers two connection flavors:

• Direct connection (port 5432): Standard issue Postgres. The Pro plan caps it at ~60 connections.
• Supavisor pooler (port 6543): Handles pool connections, supporting hundreds with aplomb.

For Vercel, we default to the pooler:

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function createClient() {
  const cookieStore = await cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // Server component, can't set cookies
          }
        },
      },
    }
  )
}

For our reach-out-and-touch search API route:

// app/api/search/route.ts
import { createClient } from '@/lib/supabase/server'
import { NextRequest, NextResponse } from 'next/server'
import OpenAI from 'openai'

const openai = new OpenAI()

export async function POST(request: NextRequest) {
  const supabase = await createClient()
  const { query } = await request.json()

  // Generate embedding
  const embeddingResponse = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  })
  const embedding = embeddingResponse.data[0].embedding

  // Search with RLS applied automatically
  const { data, error } = await supabase.rpc('search_documents', {
    query_embedding: JSON.stringify(embedding),
    match_count: 10,
    similarity_threshold: 0.72,
  })

  if (error) {
    console.error('Search error:', error)
    return NextResponse.json({ error: 'Search failed' }, { status: 500 })
  }

  return NextResponse.json({ results: data })
}

Edge Runtime Considerations

At first, we tinkered with Vercel’s Edge Runtime for zippier responses. Don’t bother. While @supabase/ssr works on Edge, the OpenAI SDK’s streaming and bulkier vector search responses felt constrained by the V8 isolate environment. Surprisingly, our p95 latency jumped by 40ms. Not worth it, so we shifted back to Node.js runtime.

Query Optimization and Benchmarks

Here's the dirt from our production logs (Supabase Dashboard + custom logging), drawn over a seven-day stretch with 162,437 records:

Metric	Before Optimization	After Optimization
p50 search latency (DB only)	145ms	38ms
p95 search latency (DB only)	312ms	87ms
p99 search latency (DB only)	580ms	142ms
RLS overhead per query	85ms	12ms
Total API latency (incl. embedding)	520ms	285ms
Connection errors/day	12-15	0-1

Post-optimization, these numbers reflect HNSW indexing, the RLS hacks, connection pooling, and one nifty trick -- pre-filtering by org_id before the vector sweep:

-- Pre-filter by `org_id` before trawling the vector haystack
create or replace function search_documents_v2(
  query_embedding vector(1536),
  target_org_id uuid,
  match_count int default 10,
  similarity_threshold float default 0.7
)
returns table (
  id uuid,
  content text,
  similarity float,
  document_id uuid,
  metadata jsonb
)
language plpgsql
security invoker
set search_path = public
as $$
begin
  return query
  select
    dc.id,
    dc.content,
    1 - (dc.embedding <=> query_embedding) as similarity,
    dc.document_id,
    dc.metadata
  from document_chunks dc
  where dc.org_id = target_org_id
    and 1 - (dc.embedding <=> query_embedding) > similarity_threshold
  order by dc.embedding <=> query_embedding
  limit match_count;
end;
$$;

By explicitly funneling org_id and leveraging the org_id index before the vector crunch, we slashed the search space tenfold! For a tenant with 5,000 chunks in the ocean of 162,000, that’s a 97% cutback on vectors to crunch.

RLS still runs the check (since the function is security invoker), but it’s sifting through far fewer contenders.

Monitoring and Observability in Production

Supabase’s dashboard is fine, but let's go above and beyond:

1. pg_stat_statements -- already set up on Supabase. We poke it weekly for slowpokes.
2. Custom logging in API routes measuring embedding logic vs. DB query time.
3. Vercel Analytics relaying full-circle API latency.
4. Supabase Log Explorer set with alerts for queries daring to breach the 200ms line.

Heads up: Supabase Pro includes a paltry 500MB database size, with overage charged at $0.125/GB. Just our HNSW index ballooned to 1.4GB! All in all, our database hit 2.8GB. Translate to an extra ~$0.29/GB/month. It’s extra dough, but mind your budget, because as of 2025, the plan's pricing hasn't wavered -- yet.

What We'd Do Differently

If we kicked this off again:

1. Kick off with HNSW. The IVFFlat-to-HNSW transition was technically a downer and a time thief.
2. Start with org_id denormalization. We learned the hard way after a week wrangling slow RLS.
3. Get on with Supabase's pgvector 0.7.0+. These newer versions put out cooler features like quantized vectors (halfvec). Migrating soon.
4. Try Supabase Branching for schema shifts. We sweated migrations manually, and my nerves are still recovering.
5. Sort out connection pooling from the onset rather than tackling random FATAL: too many connections errors in the dead of night.

For teams crafting similar structures, we've got your back. Check our offerings for headless CMS development and Next.js development. Curious about potential costs? Swing by our pricing page.

FAQ

Can Supabase handle more than 162,000 vector records?

Oh, definitely. Supabase’s Postgres is up for millions of vectors -- pgvector itself isn’t daunted by row limits. Expect bottlenecks with memory (HNSW likes to hog RAM), storage fees, and a tick-tocking query time. At more than a million records, the need for Supabase's Team or Enterprise plan’s amped compute kicks in. Reports from some teams high-five performance even up to 5M vectors on dedicated instances.

Does RLS seriously bog down pgvector queries?

If botched, it does. Ouch-worthy RLS policies with heavy subqueries might slap on 50-100ms per query. But with tricks outlined here -- index-tuned memberships, deft security definer functions, and shrewd foreign key denormalization -- the RLS toll plummets to a paltry 10-15ms. Worthy trade-off for solid data fortification.

IVFFlat or HNSW for pgvector?

For 2025 builds, HNSW is generally the golden ticket. Its recall trumps (0.97-0.99 vs. 0.92-0.95), no pesky reindexing needed, and shines in query speed. The pangs? Longer builds and beefier memory munchies. For prototypes or write-hefty loads, IVFFlat’s simplicity could still be worthwhile.

Handling connection pooling between Vercel and Supabase?

Supabase’s Supavisor pooler (port 6543) is your ally when connecting from serverless realms like Vercel. Point your DATABASE_URL to the pooler, not the straight line connection. If you're playing with Prisma, you’ll also need directUrl deciphered for migrations. Thankfully, the Supabase JavaScript client's got your back via the API layer.

Best embedding model with Supabase pgvector?

OpenAI’s text-embedding-3-small (1536 dimensions) strikes a fantastic balance of quality and cost at $0.02 per million tokens (early 2025). Craving better results? text-embedding-3-large (3072 dimensions) doubles index mass while slowing queries a tad. Fires up results with Cohere’s embed-v3, and open-source options like nomic-embed-text if you go self-host.

Can Supabase Edge Functions moonlight in place of Vercel for API?

Sure thing. Supabase Edge Functions run closer to your database (means lower latency). But if you’re already nuzzling up to Vercel with Next.js, deploying Edge Functions means wielding dual pipelines. We favored Vercel API routes for streamlining. Latency diff was barely 20ms -- not enough to juggle added complexities.

What’s the price tag for this architecture in production?

Monthly run-down: Supabase Pro at $25 + ~$3 storage overage + Vercel Pro at $20 + OpenAI embeddings $15-30 depending on volume = roughly $65-80/month total. That’s for 162K records, 47 tenants, and 800 concurrent folks. Ridiculously neat price point for the functionality. Main cost wild card? OpenAI embeddings when whisking in fresh documents.

Is Supabase pgvector a champ, or is a vector database like Pinecone the ticket?

For a lean 500K vectors with moderate query loads, Supabase pgvector holds its ground strongly. One database for vectors, relational fun, auth, RLS keeps things straightforward -- zero syncing, less headache soup. Vector champs like Pinecone or Weaviate shine past millions of vectors, needing pointed features like namespacing, or when query appetites outrun a single Postgres’ oomph. For much of our Next.js development practice, Supabase suffices splendidly.