An afternoon on Supabase: pgvector, a 44× query plan, and two RLS lessons

I run a small daily AI-briefing pipeline on my personal site — a scheduled agent researches one AI topic a day, writes it up, and publishes it. The pipeline had no real memory: retrieval meant grepping files. I wanted a proper memory layer — semantic search over everything the pipeline has ever written — and I wanted it on Postgres.

There was a second reason. I lead a customer-facing technical team for a living, and the only way I trust myself to talk about a platform is to run it, break it, and fix it myself. Reading docs produces opinions; running the thing produces judgment. So: one afternoon, local Supabase stack, real data, and a promise to write down whatever surprised me.

It surprised me twice.

What I built

The whole thing is small on purpose:

• Local stack via the Supabase CLI on OrbStack. supabase init && supabase start — about ten minutes including image pulls, and I had Postgres, auth, storage, a REST API, and a dashboard on localhost.
• One migration: a briefings table with a vector(384) column, a match_briefings() SQL function for cosine search, and a row-level-security policy for anonymous reads.
• Ingest in Python: 18 real briefings from my pipeline, embedded locally with all-MiniLM-L6-v2 — no API keys, embeddings computed on my laptop.
• An Edge Function (Deno/TypeScript) that takes a query embedding over HTTP and calls match_briefings via RPC.

The schema is the only part worth showing:

create extension if not exists vector;

create table briefings (
  id           bigint generated always as identity primary key,
  title        text not null,
  content      text not null,
  source_url   text,
  published_at timestamptz default now(),
  embedding    vector(384)
);

create function match_briefings(query_embedding vector(384), match_count int default 5)
returns table (id bigint, title text, source_url text, similarity float)
language sql stable as $$
  select b.id, b.title, b.source_url,
         1 - (b.embedding <=> query_embedding) as similarity
  from briefings b
  where b.embedding is not null
  order by b.embedding <=> query_embedding
  limit match_count;
$$;

First search, real data, and it just worked: querying "MCP tool design" returned my four MCP-related briefings at the top, correctly ranked. Good. Now for the part I actually came for.

The query plan story

I ran the first search with EXPLAIN ANALYZE on. The plan:

Limit -> Sort (top-N heapsort) -> Seq Scan on briefings (rows=18)
Execution Time: 0.070 ms

A sequential scan — and that is the correct plan. At 18 rows, walking the whole table costs nothing; an index would be pure overhead. If your reflex is "seq scan = add an index," the planner is smarter than your reflex. This matters in real support conversations: an index recommendation without row counts is a guess wearing a lab coat.

But 18 rows proves nothing about scale, so I made the problem real: a demo table with 20,000 random 384-dimension vectors, no index, nearest-neighbor query.

Before:

Limit  (cost=4590.50..4590.51 rows=5 ...) (actual time=3.472..3.472 rows=5)
  ->  Sort  (top-N heapsort, Memory: 25kB)
        Sort Key: ((vec_demo.embedding <=> ...))
        ->  Seq Scan on vec_demo  (rows=20000) (actual time=0.011..2.977)
Execution Time: 3.497 ms

Exactly what it looks like: Postgres computes the distance for all 20,000 rows, then keeps the top 5. There is no index that helps an ORDER BY distance — until you install one designed for it.

create index on vec_demo using hnsw (embedding vector_cosine_ops);
-- Time: 625.067 ms

After:

Limit  (cost=582.38..591.21 rows=5 ...) (actual time=0.063..0.066 rows=5)
  ->  Index Scan using vec_demo_hnsw on vec_demo
        Order By: (embedding <=> ...)
Execution Time: 0.079 ms

3.497 ms → 0.079 ms. A 44× speedup, from one index.

Three honest footnotes, because the headline number hides them:

• 1. The index wasn't free. 625 ms to build on 20k rows. On a production table with millions of rows, index builds are a scheduled event, not a casual one.
• 2. HNSW is approximate. You trade a sliver of recall for that 44×. For "find related briefings," ideal. For "find every matching transaction," read the fine print.
• 3. At 3.5 ms, nobody was suffering yet. The interesting question is never "is it slow today" — it's "what does the curve look like." This query's cost is linear in table size. At 200,000 rows the seq scan is ~35 ms; at 2 million it's a product incident. The plan tells you the future if you read it.

What surprised me: RLS has two gates

Here's the part I didn't get from the docs.

My migration had a clean RLS policy:

alter table briefings enable row level security;
create policy "public read" on briefings for select to anon using (true);

Policy says: anonymous users can read everything. So I called my Edge Function (which runs as anon) and got:

{ "error": "permission denied for table briefings" }

Permission denied — with a policy that literally says using (true). The diagnosis was one command:

\dp briefings
  anon=Dxtm/postgres        <- no "r"

The anon role had never been granted SELECT on the table. And that's the lesson: Postgres has two gates, and RLS is the second one. GRANT decides whether you may touch the table at all. Policies decide which rows you see once you're in. Writing a policy without a grant is opening the inner door while the outer door stays locked.

grant select on briefings to anon, authenticated;

Fixed — and baked back into the migration so db reset stays reproducible.

Then I ran the experiment in reverse: grants in place, but the policy flipped to using (false). Now the same request returns:

{ "results": [] }

No error. HTTP 200. Just… nothing. And this is the operationally interesting part — the two gates fail with different fingerprints:

That last row deserves a sentence. The dashboard proves your data exists. It proves nothing about whether any API path can reach it — the dashboard walks in through the staff entrance. If I had a euro for every debugging session that starts with "but it's right there in the admin panel," I could fund the index builds.

If this were a customer proof-of-value

My day job is running technical evaluations and competitive migrations — I led a displacement program in the mobile-measurement world that switched 300+ accounts on a playbook I wrote — so I can't help scoping this like one. If a team were evaluating Supabase against their incumbent backend, I'd insist on three things before anyone writes code:

• 1. Written success criteria — p95 latency on their real query patterns, auth-flow parity, cost at their scale. Agreed before the PoV starts, so the result is a decision, not a debate.
• 2. One bounded workload — not the whole app. This memory layer is exactly the right size.
• 3. A parallel run with a parity gate — old system and new system side by side, and you don't switch until the numbers match. The 44× story above is the kind of evidence that belongs in that report.

PoVs die of scope drift. The criteria document is the vaccine.

What I'd explore next

• Wire the write path into the live pipeline, so every new briefing embeds on publish.
• Measure HNSW recall properly and play with hnsw.ef_search instead of trusting defaults.
• A text-in query path on the Edge Function (right now the client supplies the embedding; an embeddings API would make it end-to-end).
• Per-user policies when this memory becomes multi-tenant — now that I know where both gates are.

The whole project — migration, scripts, Edge Function, and the raw EXPLAIN archives from this post — runs locally with supabase start. One afternoon, two genuine surprises, and a query planner that turned out to have better judgment than my reflexes. That's a good trade.