Mastering Database Sharding and Performance in Multi-Tenant PostgreSQL

Designing a Multi-Tenant SaaS Architecture

When building client-facing portals that host data for multiple independent companies, security and isolation are paramount. We must ensure that under no circumstances can Client A access or view the database entries of Client B.

Rather than running separate database instances for each tenant, we implement a Shared Database, Shared Schema architecture, enforced securely via PostgreSQL Row Level Security (RLS).

Enforcing Row Level Security (RLS) in Supabase

In Supabase, we declare RLS policies that automatically filter queries based on the authenticated user's metadata and ID.

Here is the exact DDL SQL code to configure secure multi-tenancy:

`sql -- 1. Create our tenant profiles table create table public.profiles ( id uuid references auth.users on delete cascade primary key, company_name text not null, website_url text, project_status text default 'onboarding' not null, has_core_plan boolean default false not null, has_seo_plan boolean default false not null, created_at timestamp with time zone default timezone('utc'::text, now()) not null );

-- 2. Enable Row Level Security alter table public.profiles enable row level security;

-- 3. Create RLS Policies -- Allow clients to view only their own profile create policy "Users can view own profile" on public.profiles for select using (auth.uid() = id);

-- Allow clients to update only their own profile fields create policy "Users can update own profile" on public.profiles for update using (auth.uid() = id);

-- Allow the system service role (admin) complete override access create policy "Service role complete access" on public.profiles for all using (true); `

High-Performance Query Indexing

As the database grows to thousands of client accounts, scanning tables becomes expensive. To optimize query times for automated active site checks, purging scripts, and dashboard history renders, we compile targeted indices:

`sql -- Optimize active client scans and compliance lookups CREATE INDEX IF NOT EXISTS idx_profiles_live_status ON public.profiles(project_status) WHERE project_status = 'live';

-- Optimize daily GDPR inactive customer purging CREATE INDEX IF NOT EXISTS idx_profiles_deletion_schedule ON public.profiles(deletion_scheduled_at) WHERE deletion_processed_at IS NULL; `

With these performance indices and RLS policies locked in, PostgreSQL queries execute in microseconds, securing a completely bulletproof and ultra-fast tenant boundary.