Supabase development, treated as Postgres engineering rather than a Firebase clone.

A policy written as using (user_id = auth.uid()) re-evaluates that function for every candidate row; the planner has no licence to hoist it. Write using (user_id = (select auth.uid())) and it becomes an InitPlan, evaluated once for the statement. At five thousand rows the difference is invisible. At five million it’s a gateway timeout, and it arrives all at once rather than gradually. Add TO authenticated so anonymous requests get refused before evaluation, and split policies by command: one FOR ALL policy whose WITH CHECK was copied from its USING clause is how a user writes a row into a tenant they can’t read.

Postgres folds RLS into your query rather than layering it above. using (tenant_id = (select auth.jwt() ->> 'tenant_id')) is a predicate the planner has to satisfy on every read, and an unindexed tenant_id hands each authenticated user a sequential scan. A policy on org_members that queries org_members fails outright with infinite recursion detected, so move that lookup into a SECURITY DEFINER function, declared STABLE with SET search_path = ''. And on the record: below Series A, absent a contractual isolation requirement, shared schema with a tenant column beats database-per-tenant. Per-tenant databases buy you migrations that run N times and a connection budget that scales with customer count.

The service key maps to a Postgres role with BYPASSRLS. One server action instantiated with it and every policy on every table stops applying for that request. That’s the authorisation layer switched off rather than bent. It usually happens because a developer hit a policy that was wrong and reached for the key that silenced the error. Use the anon key with the user’s JWT for anything user-scoped, including on the server, where the session comes off the cookie. Keep service_role to paths no user request can reach, never behind a NEXT_PUBLIC_ prefix, and list every file that holds it.

A small instance tops out in the low hundreds of connections, each one a backend process holding memory, while serverless scales to hundreds of concurrent invocations by design. Supavisor transaction mode on port 6543 returns the connection after every statement, which is what your functions should use. It costs you prepared statements, so postgres-js needs prepare set to false, and it costs you session state, so SET, LISTEN and NOTIFY, and advisory locks held across statements don’t survive. Session mode on 5432 suits a long-lived container and migration tooling. Decide per runtime rather than once per project.

Edge Functions run Deno on their own deploy clock, which is what you want for third-party webhooks whose URLs shouldn’t move every release and for jobs that belong near the database. Route handlers are right when the code shares your application’s types, session and validation. Triggers are excellent for invariants like updated_at, an audit row or a denormalised counter, and poor as business logic: a trigger reaching out over pg_net has no retries and no way to test the transaction that fired it. Realtime is not a queue either. Postgres Changes is an at-most-once broadcast with no replay after a reconnect, so use it to say something changed, then refetch. Work that has to happen exactly once belongs in a table read with FOR UPDATE SKIP LOCKED.

IVFFlat builds fast but clusters rows into lists, so it wants representative data present at build time, and its recall drifts as you insert until somebody reindexes. HNSW builds slower and costs more memory, holds recall as the table grows, and can be built on an empty table; m and ef_construction are fixed at build, while ef_search trades recall against latency per query. Filtering is the trap nobody warns you about. An approximate index answers nearest neighbours overall, then Postgres applies your tenant predicate to that result, so a narrow filter returns fewer rows than you asked for. Use partial indexes per tenant, or plain exact search when the candidate set is small. An indexed vector column also stops at 2,000 dimensions.