How can you optimize a multi-tenant schema design using Row Level Security instead of schema-per-tenant?

The core optimization is making the tenant filter cheap for Postgres to apply on every query, since every read and write in a shared-schema design passes through an RLS policy keyed on tenant_id. That starts with a composite index leading with tenant_id on every frequently queried table, so the policy's filter can use an index scan rather than degrading toward a sequential scan as row counts grow across many tenants sharing the same table.

create policy "tenant_isolation" on orders
  for select using (tenant_id = (select current_tenant_id()));

create index orders_tenant_id_idx on orders (tenant_id, created_at);

Wrapping the tenant-lookup function as (select current_tenant_id()), the same technique used for auth.uid(), avoids re-evaluating it per row. Beyond indexing, partitioning very large tables by tenant_id (or a hash of it) can further help once individual tenants grow large enough that even an indexed scan touches a meaningful fraction of the table, since partition pruning lets Postgres skip entire partitions that don't match the current tenant.

The overall goal is treating tenant_id as a first-class part of every index and query plan, not an afterthought bolted onto an existing single-tenant schema, since RLS makes it implicit in every query whether or not the developer remembers to filter by it explicitly.