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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.