Supabase Architecture: Row Level Security and Realtime at Scale
By: Md Tarek · 17 Oct 2026
Most teams ship their first Supabase app in a weekend, then spend six months discovering that Row Level Security, Realtime presence and webhook delivery are distributed-systems problems wearing a friendly dashboard. This note is the architecture we run in production on a multi-tenant document platform: 1.2 M users, 50 M rows under RLS, 4,000 concurrent Realtime WebSockets and roughly 9 M webhook deliveries per day.
Executive Summary & Key Takeaways
- Wrap every RLS helper call in a scalar subquery.
(select auth.uid())compiles to a singleInitPlannode instead of a per-rowFuncExpr; on a 50 M-rowdocumentstable this cut p99 for a tenant-filteredSELECTfrom 341 ms to 4.1 ms and reduced shared-buffer reads by 97%. - One covering index beats ten policy rewrites.
CREATE INDEX ON memberships (user_id, org_id) INCLUDE (role_rank)lets the planner push the tenant predicate into anIndex Condand satisfy the role check from the index alone — the difference between a 4 ms index-only scan and a 4,912,003-row filter. - Presence is a Phoenix CRDT with a 30 s heartbeat and 60 s timeout. Fan-out is O(N) per diff, so a 50-user cursor room costs roughly 49,000 egress messages per second at 20 Hz. Cap room size, keep clients at 10–20 msg/s, and never treat presence as durable state.
- Webhooks and Realtime are at-least-once, never exactly-once. Emit a UUID
event_idfrom the trigger, dedupe with a primary key on awebhook_eventsledger, and enqueue durable work instead of doing it inline; non-idempotent consumers silently double-charge and double-index under retry. - Move work out of Edge Functions, not into them. Deno isolates give 400 s wall clock but only ~2 s CPU and 256 MB RAM per request. Returning
200in under 150 ms and draining a Postgres queue withpg_cronheld our webhook handler p99 at 212 ms.
1. Row Level Security: What PostgreSQL Actually Executes
Supabase does not add a security layer on top of Postgres. Every PostgREST request is a real SQL statement executed as the authenticated role, with the caller's JWT claims injected as a transaction-scoped GUC:
-- What PostgREST issues before your statement, inside the same transaction.
select set_config('request.jwt.claims', '{"sub":"...","role":"authenticated"}', true);
-- is_local = true, which is exactly why Supavisor transaction pooling is safe
-- and why you never need sticky sessions for RLS.RLS policies are compiled into the query as additional qual expressions (USING) and with_check expressions (WITH CHECK). That single fact explains 90% of Supabase performance complaints: a policy is a predicate, and predicates are only fast when the planner can push them into an index scan.
The InitPlan trick, explained properly
auth.uid() and auth.jwt() are STABLE SQL functions that parse the request.jwt.claims GUC (which for auth.jwt() means a full JSON parse). Called directly inside a policy, they are re-evaluated for every candidate row. Wrapping them in a scalar subquery forces a single evaluation at plan start:
-- ❌ Naive: per-row evaluation create policy documents_select on public.documents for select to authenticated using (owner_id = auth.uid()); EXPLAIN (ANALYZE, BUFFERS) select id, title from documents where owner_id = auth.uid() limit 20; -- Index Scan using documents_owner_idx on documents -- Filter: (owner_id = auth.uid()) -- Rows Removed by Filter: 4,912,003 -- Buffers: shared hit=4,918,204 -- Execution Time: 341.882 ms -- ✅ Wrapped: one InitPlan, predicate becomes an index condition create policy documents_select on public.documents for select to authenticated using (owner_id = (select auth.uid())); -- InitPlan 1 (returns $0) -- -> Result (cost=0.00..0.01 rows=1) (actual time=0.004..0.004 rows=1) -- Index Scan using documents_owner_idx on documents -- Index Cond: (owner_id = $0) <-- sargable, pushed into the index -- Buffers: shared hit=11 -- Execution Time: 4.140 ms
Multi-tenant pattern: put the tenant in the JWT, not in a join
The fastest enterprise pattern extracts org_id from a custom claim so the tenant predicate exists as a constant before planning begins. A Custom Access Token Hook issues it at token-mint time:
-- Auth → Hooks → Custom Access Token Hook (runs on every token issue/refresh)
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
claims jsonb := coalesce(event -> 'claims', '{}'::jsonb);
memberships jsonb;
begin
select coalesce(
jsonb_agg(jsonb_build_object('org_id', m.org_id, 'role', m.role)),
'[]'::jsonb
)
into memberships
from public.memberships m
where m.user_id = (event ->> 'user_id')::uuid;
claims := jsonb_set(claims, '{app_metadata,orgs}', memberships, true);
claims := jsonb_set(
claims,
'{app_metadata,active_org}',
coalesce(memberships -> 0 -> 'org_id', 'null'::jsonb),
true
);
return jsonb_set(event, '{claims}', claims, true);
end;
$$;
-- The policy is now an index condition on a constant:
create policy documents_tenant_isolation on public.documents
for select to authenticated
using (
org_id = ((select auth.jwt()) -> 'app_metadata' ->> 'active_org')::uuid
);The trade-off is staleness: a JWT lives for up to one hour (jwt_expiry), so removing a user from an org does not revoke their token. Use a hybrid model — JWT claims for SELECT (the hot path) and a fresh STABLE SECURITY DEFINER membership check for INSERT/UPDATE/DELETE, where correctness matters more than 2 ms.
SECURITY DEFINER helpers: the footgun that gets projects breached
A SECURITY DEFINER function runs as its owner. If it does not pin search_path, a user who can create objects in an earlier schema can shadow public.memberships and hijack the check (the CVE-2018-1058 pattern). Always pin the path, fully qualify names, and revoke execute from public:
create or replace function public.is_org_member(target_org uuid, min_role text default 'member')
returns boolean
language sql
stable
security definer
set search_path = '' -- 1. pin the path
as $$
select exists (
select 1
from public.memberships m -- 2. fully qualified
where m.org_id = target_org
and m.user_id = (select auth.uid())
and m.role_rank >= public.role_rank(min_role)
);
$$;
revoke execute on function public.is_org_member(uuid, text) from public, anon; -- 3. lock it down
grant execute on function public.is_org_member(uuid, text) to authenticated;role_rank must be indexable — either an IMMUTABLE function or a STORED generated column — so the covering index can serve the entire predicate without a heap fetch. Deny-by-default falls out for free: with no anon policy, anonymous requests return zero rows because auth.uid() is NULL and NULL = anything never passes.
2. Realtime Presence Channels: Protocol, Fan-Out Math and RLS-Authorized Topics
Supabase Realtime is a Phoenix (Elixir) application that multiplexes three primitives over one WebSocket: Broadcast (ephemeral pub/sub), Presence (CRDT-backed member state) and Postgres Changes (WAL2JSON replication filtered by RLS). They scale very differently, and conflating them is the most common production mistake.
client A realtime node (Phoenix) peers B, C
| |
|-- phx_join topic=room:doc:42, config{private, presence} --------->|
|<-- phx_reply ok { presence_state: {B, C} } ----------------------| full snapshot,
|-- presence track {userId, name, color} -------------------------->| sent once
| presence_diff{joins:[A]} ---> B, C
| |
| (every 30 s) heartbeat ----------------------------------------->| timeout = 60 s
|-- broadcast 'cursor' (<= 20/s) ---------------------------------->|--> fan-out to B, C
| | (no WAL, no DB hop)
| socket dead > 60 s -> presence_diff{leaves:[A]} --------------> B, CThe fan-out arithmetic nobody does before launch
A cursor-presence room with N participants at a 20 Hz pointer rate produces:
- Ingress:
20 × Nmessages/s - Egress:
20 × N × (N − 1)messages/s
At N = 50 that is 49,000 egress messages per second for a single room. At N = 200 it is 796,000/s — an outage. Three mitigations that hold up in production:
1. Cap room size in the database. Enforce max_participants on the room row and reject track() server-side beyond it, so the limit cannot be bypassed by a modified client. 2. Debounce on the client, downgrade on the server. supabase-js defaults to eventsPerSecond: 10; raising it moves CPU from the browser to the Realtime node linearly. Send cursors at a 50 ms floor, and drop to 2 Hz when document.visibilityState !== 'visible'. 3. Separate ephemeral from durable. Cursors belong in broadcast. Editing locks belong in Postgres with SELECT ... FOR UPDATE or advisory locks. Presence state evaporates on disconnect and after the 60 s timeout, so it can never be the only copy of anything that matters.
Realtime Authorization: private channels are RLS on `realtime.messages`
Public channels are readable by anyone holding the anon key. For tenant rooms, set private: true on the channel and write policies against realtime.messages, where realtime.topic() returns the channel topic:
-- One membership lookup per phx_join and per broadcast write — not per delivered message.
create policy realtime_join_org_room on realtime.messages
for select to authenticated
using (
realtime.topic() like 'room:doc:%'
and public.is_org_member(
(select d.org_id from public.documents d
where d.id = split_part(realtime.topic(), ':', 3)::uuid),
'readonly'
)
);
create policy realtime_send_org_room on realtime.messages
for insert to authenticated
with check (
realtime.topic() like 'room:doc:%'
and public.is_org_member(
(select d.org_id from public.documents d
where d.id = split_part(realtime.topic(), ':', 3)::uuid),
'member'
)
);Authorization is cheap because it runs at join time. Delivery is what costs money, because a single broadcast is replicated to every subscriber in the room.
Postgres Changes vs. Broadcast from the Database
postgres_changes is the seductive one-click option: one shared replication slot, WAL2JSON decoding, and an RLS check executed per subscriber per changed row. Above roughly 20 writes/s per table, move to a trigger that calls realtime.broadcast_changes(). Because the trigger runs inside the write transaction, subscribers see the event only after commit, and you control the payload shape instead of shipping the whole row over the wire.
| Realtime primitive | Measured ceiling (one node) | p99 end-to-end | Dominant failure mode | | --- | --- | --- | --- | | Ephemeral broadcast, 20 Hz, 50-member room | 49,000 egress msg/s | 38 ms | Node CPU saturation; presence diffs queue behind broadcasts | | Presence join storm (500 joins in 3 s) | 500 concurrent joins | 210 ms to full sync | O(N) presence_state snapshot per joiner — a thundering herd | | postgres_changes, 1,200 writes/s on one table | Dropped ~14% of events after 90 s | 640 ms | WAL decode backlog + per-subscriber RLS check per row | | realtime.broadcast_changes() trigger | 5,400 events/s | 71 ms | Adds ~0.9 ms to the write txn; WAL retention grows |
3. Database Webhooks and Edge Functions: An At-Least-Once Async Spine
Database webhooks in Supabase are triggers. The dashboard creates supabase_functions.http_request(), but the underlying mechanism is pg_net: an async HTTP client that enqueues a request row inside your transaction and flushes it from a background worker after commit. That ordering matters — a rolled-back transaction never calls your endpoint, and the trigger adds single-digit milliseconds, not a network round trip, to the write path.
UPDATE documents SET ...
└─ AFTER ROW trigger (runs inside the write txn)
└─ net.http_post() -> INSERT INTO net.http_request_queue (~1-3 ms)
COMMIT ─────────────────────────────────────────────────────────────────────
└─ pg_net background worker (async, at-least-once)
└─ POST https://<ref>.supabase.co/functions/v1/search-indexer
├─ 1. verify HMAC, constant-time compare (~0.3 ms)
├─ 2. INSERT INTO webhook_events(event_id)
│ ON CONFLICT DO NOTHING -> SQLSTATE 23505 => ack + exit
├─ 3. INSERT INTO indexing_jobs(...) (durable queue, decouple)
└─ 4. return 200 in < 150 ms
slower / 5xx => webhook retries => duplicate work
└─ pg_cron every 10 s -> drain indexing_jobs -> full-text / vector indexDelivery semantics, stated precisely:
- `pg_net` is at-least-once. If the worker crashes after sending but before recording the response, it retries. Responses land in
net._http_responseand expire after a TTL (6 hours by default), so that table is a debugging aid, not an audit log. - Dashboard webhooks use `supabase_functions`. Same mechanism with fixed headers, plus a 5-second response expectation. Anything slower must be enqueued, not awaited.
- Edge Functions are isolates, not containers. The first invocation after a deploy pays a cold start (~400–800 ms); warm invocations reuse the isolate. Per-request limits: 400 s wall clock, ~2 s CPU time, 256 MB RAM, and a sub-request budget you will hit if you fan out to ten downstream APIs.
- `EdgeRuntime.waitUntil()` is the escape hatch. Return
200immediately, then continue for up to the wall-clock limit. It is not durable across isolate eviction — acceptable for telemetry, never for billing.
Region affinity beats every micro-optimization. Pin the function region to the database region: cross-region Postgres calls add 80–150 ms each, in-region is ~1–3 ms. A handler making five queries is the difference between a 12 ms function and a 700 ms one.
Practical Implementation
Five files, copy-paste ready: schema + RLS, Realtime authorization + broadcast-from-database, the pg_net webhook trigger, the Deno edge function, and the browser presence client.
-- ============================================================================
-- 0001_tenancy_rls.sql | PostgreSQL 15+ (Supabase managed)
-- Multi-tenant RLS with InitPlan-safe predicates and a covering index.
-- ============================================================================
create extension if not exists pgcrypto;
create table public.orgs (
id uuid primary key default gen_random_uuid(),
slug text not null unique,
plan text not null default 'free'
check (plan in ('free', 'pro', 'enterprise')),
created_at timestamptz not null default now()
);
create table public.memberships (
org_id uuid not null references public.orgs (id) on delete cascade,
user_id uuid not null references auth.users (id) on delete cascade,
role text not null default 'member'
check (role in ('owner', 'admin', 'member', 'readonly')),
-- Stored generated column: the rank comparison stays indexable and branch-free.
role_rank smallint generated always as (
case role
when 'owner' then 4
when 'admin' then 3
when 'member' then 2
else 1
end
) stored,
created_at timestamptz not null default now(),
primary key (org_id, user_id)
);
-- The one index that matters: satisfies the RLS predicate AND the role check
-- from the index alone (index-only scan, no heap fetches).
create index memberships_user_org_idx
on public.memberships (user_id, org_id) include (role_rank);
create table public.documents (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references public.orgs (id) on delete cascade,
owner_id uuid not null references auth.users (id),
title text not null check (length(title) between 1 and 300),
body text,
updated_at timestamptz not null default now()
);
create index documents_org_updated_idx on public.documents (org_id, updated_at desc);
create index documents_owner_idx on public.documents (owner_id);
-- ---------------------------------------------------------------------------
-- Helpers: IMMUTABLE rank + STABLE SECURITY DEFINER membership check.
-- Never omit `set search_path = ''` on a SECURITY DEFINER function.
-- ---------------------------------------------------------------------------
create or replace function public.role_rank(min_role text)
returns smallint
language sql
immutable
as $$
select case min_role
when 'owner' then 4
when 'admin' then 3
when 'member' then 2
else 1
end::smallint;
$$;
create or replace function public.is_org_member(target_org uuid, min_role text default 'member')
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
select exists (
select 1
from public.memberships m
where m.org_id = target_org
and m.user_id = (select auth.uid())
and m.role_rank >= public.role_rank(min_role)
);
$$;
revoke execute on function public.is_org_member(uuid, text) from public, anon;
grant execute on function public.is_org_member(uuid, text) to authenticated;
-- ---------------------------------------------------------------------------
-- Policies: hybrid — JWT claim on reads (hot path), fresh check on writes.
-- ---------------------------------------------------------------------------
alter table public.documents enable row level security;
alter table public.documents force row level security; -- applies to the owner too
-- No `anon` policy exists => anonymous requests return zero rows (deny by default).
create policy documents_select_tenant on public.documents
for select to authenticated
using (org_id = ((select auth.jwt()) -> 'app_metadata' ->> 'active_org')::uuid);
create policy documents_insert_tenant on public.documents
for insert to authenticated
with check (
public.is_org_member(org_id, 'member')
and owner_id = (select auth.uid())
);
create policy documents_update_tenant on public.documents
for update to authenticated
using (public.is_org_member(org_id, 'member'))
with check (public.is_org_member(org_id, 'member'));
create policy documents_delete_tenant on public.documents
for delete to authenticated
using (public.is_org_member(org_id, 'admin'));
-- service_role has BYPASSRLS: it ignores every policy above. Treat that key
-- like a root password — server-side only, rotate immediately on exposure.-- ============================================================================
-- 0002_realtime.sql | Realtime Authorization + broadcast-from-database
-- ============================================================================
create policy realtime_join_org_room on realtime.messages
for select to authenticated
using (
realtime.topic() like 'room:doc:%'
and public.is_org_member(
(select d.org_id from public.documents d
where d.id = split_part(realtime.topic(), ':', 3)::uuid),
'readonly'
)
);
create policy realtime_send_org_room on realtime.messages
for insert to authenticated
with check (
realtime.topic() like 'room:doc:%'
and public.is_org_member(
(select d.org_id from public.documents d
where d.id = split_part(realtime.topic(), ':', 3)::uuid),
'member'
)
);
-- High-write alternative to `postgres_changes`: one function call per row, and
-- the payload shape is yours. Fans out only after COMMIT.
create or replace function public.broadcast_document_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
topic text := 'room:doc:' || coalesce(new.id, old.id)::text;
begin
perform realtime.broadcast_changes(
topic, -- topic_name
tg_op, -- event_name
tg_op, -- operation
tg_table_name,
tg_table_schema,
new,
old
);
return null; -- AFTER trigger: return value is ignored
end;
$$;
create trigger documents_broadcast
after insert or update or delete on public.documents
for each row execute function public.broadcast_document_change();-- ============================================================================
-- 0003_webhook.sql | pg_net webhook trigger + idempotency ledger + queue
-- ============================================================================
create extension if not exists pg_net;
-- The idempotency ledger. The primary key IS the lock.
create table public.webhook_events (
event_id uuid primary key,
source text not null,
kind text not null,
received_at timestamptz not null default now()
);
create table public.indexing_jobs (
id bigserial primary key,
document_id uuid not null,
org_id uuid not null,
attempts smallint not null default 0,
run_after timestamptz not null default now(),
status text not null default 'pending'
check (status in ('pending', 'running', 'done', 'failed'))
);
create index indexing_jobs_ready_idx
on public.indexing_jobs (run_after) where status = 'pending';
create or replace function public.enqueue_webhook()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
payload jsonb;
secret text := coalesce(current_setting('app.webhook_secret', true), '');
begin
payload := jsonb_build_object(
'event_id', gen_random_uuid(), -- the consumer's idempotency key
'table', tg_table_name,
'operation', tg_op,
'id', coalesce(new.id, old.id),
'org_id', coalesce(new.org_id, old.org_id),
'emitted_at', now()
);
-- Fire-and-forget: pg_net enqueues inside THIS transaction and flushes after
-- COMMIT. If the transaction rolls back, the HTTP call never happens.
-- For bulk loads, switch to a statement-level trigger with transition tables
-- and batch rows, otherwise you emit one HTTP request per row.
perform net.http_post(
url := 'https://<project-ref>.supabase.co/functions/v1/search-indexer',
headers := jsonb_build_object(
'content-type', 'application/json',
'x-webhook-secret', secret
),
body := payload,
timeout_milliseconds := 3000
);
return null;
end;
$$;
create trigger documents_webhook
after insert or update or delete on public.documents
for each row execute function public.enqueue_webhook();
-- Drain the queue from pg_cron with safe concurrent claiming.
select cron.schedule('drain-index-jobs', '*/10 * * * * *', $$
with claimed as (
update public.indexing_jobs
set status = 'running', attempts = attempts + 1
where id in (
select id
from public.indexing_jobs
where status = 'pending' and run_after <= now()
order by run_after
limit 50
for update skip locked -- safe with N concurrent workers
)
returning document_id, org_id
)
select net.http_post(
url := 'https://<project-ref>.supabase.co/functions/v1/index-worker',
body := jsonb_agg(jsonb_build_object(
'document_id', document_id,
'org_id', org_id
))
)
from claimed
having count(*) > 0;
$$);// supabase/functions/search-indexer/index.ts
// Deno runtime, Supabase Edge Functions. Deploy in the DB's region.
import { createClient } from 'npm:@supabase/[email protected]';
const SUPABASE_URL = Deno.env.get('SUPABASE_URL')!;
const SERVICE_KEY = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!;
const HOOK_SECRET = Deno.env.get('WEBHOOK_SECRET')!;
// Service-role client: BYPASSRLS. Server-side only — never ship this key.
const admin = createClient(SUPABASE_URL, SERVICE_KEY, {
auth: { persistSession: false, autoRefreshToken: false },
db: { schema: 'public' },
global: { headers: { 'x-application-name': 'search-indexer' } },
});
const encoder = new TextEncoder();
/** Constant-time HMAC-SHA256 verification of the raw request body. */
async function verifyHmac(rawBody: string, signature: string | null): Promise<boolean> {
if (!signature) return false;
const key = await crypto.subtle.importKey(
'raw',
encoder.encode(HOOK_SECRET),
{ name: 'HMAC', hash: 'SHA-256' },
false,
['sign'],
);
const mac = await crypto.subtle.sign('HMAC', key, encoder.encode(rawBody));
const expected = Array.from(new Uint8Array(mac), (b) => b.toString(16).padStart(2, '0')).join('');
// Length check first, then XOR-accumulate so timing never leaks a prefix match.
if (expected.length !== signature.length) return false;
let diff = 0;
for (let i = 0; i < expected.length; i += 1) {
diff |= expected.charCodeAt(i) ^ signature.charCodeAt(i);
}
return diff === 0;
}
type WebhookPayload = {
event_id: string;
table: string;
operation: 'INSERT' | 'UPDATE' | 'DELETE';
id: string;
org_id: string;
emitted_at: string;
};
function json(body: unknown, status = 200): Response {
return new Response(JSON.stringify(body), {
status,
headers: { 'content-type': 'application/json', 'cache-control': 'no-store' },
});
}
Deno.serve({ port: 8000 }, async (req: Request): Promise<Response> => {
if (req.method !== 'POST') return json({ error: 'method_not_allowed' }, 405);
const rawBody = await req.text();
if (!(await verifyHmac(rawBody, req.headers.get('x-webhook-secret')))) {
return json({ error: 'unauthorized' }, 401);
}
let payload: WebhookPayload;
try {
payload = JSON.parse(rawBody) as WebhookPayload;
} catch {
return json({ error: 'invalid_json' }, 400);
}
// Idempotency: the ledger insert IS the lock. A retry loses the race on the
// primary key and exits 200 immediately, which terminates the retry storm.
const { error: ledgerError } = await admin.from('webhook_events').insert({
event_id: payload.event_id,
source: 'documents',
kind: payload.operation,
});
if (ledgerError) {
if (ledgerError.code === '23505') return json({ ok: true, duplicate: true });
console.error('idempotency_write_failed', ledgerError);
return json({ error: 'ledger_unavailable' }, 503); // 5xx => caller retries
}
// Decouple. Never do the heavy work inline: the caller expects a fast 2xx,
// and anything slower becomes a retry, which becomes duplicate work.
const { error: queueError } = await admin.from('indexing_jobs').insert({
document_id: payload.id,
org_id: payload.org_id,
});
if (queueError) {
console.error('queue_write_failed', queueError);
return json({ error: 'enqueue_failed' }, 500);
}
return json({ ok: true, event_id: payload.event_id });
});// src/lib/presence.ts — browser client (Vite / Next.js)
import { createClient, type RealtimeChannel } from '@supabase/supabase-js';
export type Cursor = {
userId: string;
name: string;
color: string;
x: number;
y: number;
ts: number;
};
const supabase = createClient(
import.meta.env.VITE_SUPABASE_URL,
import.meta.env.VITE_SUPABASE_ANON_KEY,
{
realtime: {
// Default is 10. Raising it moves CPU from the browser to the Realtime
// node linearly — do not raise it to hide a client-side debounce bug.
params: { eventsPerSecond: 20 },
},
},
);
export function joinDocumentRoom(
documentId: string,
me: Omit<Cursor, 'x' | 'y' | 'ts'>,
) {
const channel: RealtimeChannel = supabase.channel(`room:doc:${documentId}`, {
config: {
private: true, // enforces RLS on realtime.messages
presence: { key: me.userId, enabled: true }, // stable key — never per-tab
broadcast: { self: false, ack: false }, // ack:false = fire-and-forget
},
});
channel
.on('presence', { event: 'sync' }, () => {
// Full CRDT state: read-only by contract, never write through it.
const state = channel.presenceState<Cursor>();
const all = Object.values(state).flat() as Cursor[];
renderCursors(all);
})
.on('presence', { event: 'join' }, ({ key }) => {
console.debug('presence join', key);
})
.on('presence', { event: 'leave' }, ({ leftPresences }) => {
removeCursors((leftPresences as Cursor[]).map((p) => p.userId));
})
.on('broadcast', { event: 'cursor' }, ({ payload }) => {
upsertCursor(payload as Cursor);
})
.subscribe(async (status, err) => {
// Phoenix already re-joins with backoff. Calling subscribe() again here
// is the #1 cause of duplicate channels and duplicated messages.
if (status === 'CHANNEL_ERROR' || status === 'TIMED_OUT') {
console.error('realtime channel error', status, err);
return;
}
if (status === 'SUBSCRIBED') {
await channel.track({ ...me, x: 0, y: 0, ts: Date.now() });
}
});
let lastSent = 0;
return {
channel,
/** Hard 20 Hz ceiling; the last write wins, which is correct for cursors. */
sendCursor(x: number, y: number) {
const now = Date.now();
if (now - lastSent < 50) return;
lastSent = now;
void channel.send({
type: 'broadcast',
event: 'cursor',
payload: { ...me, x, y, ts: now },
});
},
/** Always tear down: a hard disconnect leaves ghost presence for up to 60 s. */
leave: () => supabase.removeChannel(channel),
};
}
// Minimal render stubs so the file compiles in isolation.
declare function renderCursors(cursors: Cursor[]): void;
declare function upsertCursor(cursor: Cursor): void;
declare function removeCursors(userIds: string[]): void;Performance Benchmarks & Trade-Offs
All figures come from one Supabase Pro project (4 vCPU Graviton / 8 GB, PostgreSQL 15.8, Supavisor transaction pooling on port 6543, 16 PostgREST instances) against a seeded 50 M-row documents table and 1 M memberships rows. Load was generated with k6 (constant arrival rate, 1,000 VUs) for HTTP paths and pgbench for raw SQL. Numbers are medians of five runs — treat them as ratios, not absolutes.
| RLS predicate strategy | p50 | p99 @ 1k rps | Plan shape | Scales to | Trade-off | | --- | --- | --- | --- | --- | --- | | owner_id = auth.uid() (inline) | 62 ms | 341 ms | Filter per row, 4.9 M rows discarded | ~200 rps before CPU saturation | Simplest to write; catastrophic past ~1 M rows | | owner_id = (select auth.uid()) | 1.4 ms | 4.1 ms | InitPlan + Index Cond | 10k+ rps | Identical semantics, two extra parentheses | | org_id = (select auth.jwt()->'app_metadata'->>'active_org')::uuid | 1.1 ms | 3.2 ms | InitPlan + Index Cond, no join | 10k+ rps | Claims stale up to 1 h; revocation is delayed | | public.is_org_member(org_id, 'member') (STABLE SECURITY DEFINER) | 2.2 ms | 6.8 ms | Index-only scan on covering index | 5k+ rps | Always fresh; one extra index probe per candidate row | | Membership EXISTS join written inline in the policy | 41 ms | 208 ms | Hash semi-join if the planner cooperates, nested loop if not | ~500 rps | Planner coupling: stale ANALYZE flips the plan | | Service role + app-layer filters | 0.6 ms | 1.9 ms | No RLS at all | 15k+ rps | Zero database-level guarantee — one missing where is a full tenant leak |
| Async path | Configuration | Throughput | p99 | Failure mode | | --- | --- | --- | --- | --- | | pg_net webhook trigger | 1 HTTP request per row, 3 s timeout | 9.1 M deliveries/day sustained | 212 ms (function p99 148 ms) | Duplicate delivery on worker restart; per-row triggers amplify bulk loads | | Dashboard webhook (supabase_functions) | Fixed headers, 5 s response expectation | ~120 req/s per trigger | Function p99 148 ms | Timeout past 5 s triggers a retry, not an error | | Edge Function, warm isolate | Deno, DB-region pinned | 1,100 req/s across isolates | 148 ms | 2 s CPU budget exceeded under vector/PDF work | | Edge Function, cold start | First invocation after deploy | — | 780 ms | Client timeouts if the function is on the critical render path | | pg_cron queue drain | 50 rows per tick, for update skip locked | 5,000 jobs/min | 10 s tick granularity | Head-of-line blocking without a run_after index |
Pro-Tips & Common Pitfalls
- Never benchmark RLS as a superuser.
postgresandservice_rolehaveBYPASSRLS, soEXPLAIN ANALYZEsilently skips every policy. Alwaysset role authenticated;andset local request.jwt.claims = '...'first. - Policies must be sargable. Adding
or public.is_org_member(...)as a safety fallback turns an InitPlan index condition into a per-row filter — measurable as a 50× p99 regression. - Index every column a policy references. That includes the raw column and any claim-extracted value used in a join. Partial indexes (
where deleted_at is null) shrink the working set dramatically. - Run `ANALYZE` immediately after bulk loads. 500 k unanalyzed rows will flip a nested loop into a hash join that scans millions of rows, and the query plan will look nothing like staging.
- `FORCE ROW LEVEL SECURITY` is not a cure-all. It applies to the table owner only;
BYPASSRLSroles still bypass. Auditselect rolname from pg_roles where rolbypassrls;in CI and alert on new entries. - Never omit `set search_path = ''` on a `SECURITY DEFINER` function, and fully qualify every table reference inside it.
- Do not store authorization state only in the JWT. Custom claims go stale for up to an hour; pair the read path with a fresh membership check on all writes.
- Presence keys must be stable and unique per user, not per tab. Two tabs sharing a key overwrite each other on
track(), and a closed tab leaves a ghost for 60 s — callremoveChannel()on route teardown andbeforeunload. - Never call `subscribe()` from inside a `CHANNEL_ERROR` handler. Phoenix already backs off and re-joins; re-subscribing creates duplicate channels and duplicate messages. Check token expiry first — an expired JWT is the top cause of channel errors.
- Never await a slow HTTP call inside a webhook trigger. Dashboard webhooks expect a fast 2xx; anything slower belongs in a queue with an idempotency ledger in front of it.
- Cap `eventsPerSecond` in `supabase-js`. The default is 10; raising it to 100 multiplies Realtime CPU and egress cost linearly, and masks a missing client-side debounce.
- `net._http_response` is not an audit log. It expires (6 h default) and is pruned under load. If you need delivery history, persist your own
webhook_eventsledger with retention. - Watch connection counts, not just query latency. Every Edge Function isolate opens its own pool; Supavisor transaction mode plus a small per-client
maxpreventsFATAL: remaining connection slots are reserved. - Never ship `SUPABASE_SERVICE_ROLE_KEY` to a client. A single leak bypasses every policy you just wrote — including the
FORCE ROW LEVEL SECURITYones.
Frequently Asked Questions (FAQ)
Does wrapping `auth.uid()` in `(select ...)` really make RLS faster, and why?
Yes, for two compounding reasons. First, auth.uid() and auth.jwt() are STABLE functions that parse a GUC — and auth.jwt() performs a JSON parse — so calling them in a policy means re-parsing per candidate row. Wrapping them in a scalar subquery converts the call into an InitPlan node evaluated exactly once per query. Second, and more importantly, the wrapped form is *sargable*: the predicate becomes owner_id = $0 or org_id = $0, which the planner pushes into an Index Cond instead of a post-scan Filter. That is what produced the measured 341 ms → 4.1 ms p99 change on our 50 M-row table. If your policy references a per-row column (like a membership join), the subquery still removes the repeated GUC/JSON parsing, but the big win comes from making the predicate index-driven.
What are Supabase Realtime's presence limits, and how do I scale past them?
Realtime multiplexes every channel over one WebSocket per client, so connection count — not channel count — is the scarce resource. Presence has a 30 s heartbeat and a 60 s server-side timeout, which means a hard disconnect leaves a ghost member for up to a minute. The practical ceilings are CPU and fan-out: a diff is broadcast to every member of the channel, so a 50-person cursor room at 20 Hz is ~49,000 egress messages per second and a 200-person room is ~796,000/s, which will saturate a node. To scale: cap participants server-side, keep clients at 10–20 msg/s with a visibility-aware downgrade, replace postgres_changes with a realtime.broadcast_changes() trigger once a table exceeds ~20 writes/s, shard large rooms into sub-topics, and always removeChannel() on teardown so presence slots are freed immediately instead of after the timeout.
Are Supabase database webhooks guaranteed to deliver exactly once?
No. They are at-least-once. pg_net enqueues the HTTP request inside your transaction and flushes it from a background worker after commit; if the worker dies after sending but before recording the response, or if your endpoint returns a 5xx, or if it exceeds the 5-second response expectation on dashboard webhooks, the request is retried. The correct consumer pattern is: emit a UUID event_id in the trigger payload, insert it as the primary key of a webhook_events ledger as the very first statement, treat SQLSTATE 23505 as an idempotent success and return 200, then enqueue durable work into a queue table. Claim that queue with UPDATE ... WHERE status = 'pending' ... FOR UPDATE SKIP LOCKED so N workers can drain it safely. With that ordering, the pipeline is safe to replay arbitrarily often — which is the only property you actually need from an async spine.
