Implementing Laravel Multi-Tenancy with PostgreSQL Schemas

During a routine deployment of a new client workspace last Tuesday, we hit a wall where one tenant's request bled data into another's session. Our initial attempt at building a multi-tenant SaaS platform with Laravel 11 focused heavily on row-level security, but we realized that for true compliance and data isolation, we needed to move to a database-per-tenant model using PostgreSQL schemas.

Why PostgreSQL Schemas for SaaS Architecture

When you're scaling a SaaS product, the "one database to rule them all" approach becomes a liability. Using PostgreSQL schemas allows you to maintain a single database instance while keeping data physically separated at the schema level. It's the sweet spot between full database isolation (which gets expensive) and row-level security (which is prone to developer error).

We found that by using schemas, we could run migrations per tenant without impacting the global public schema. However, this introduced a significant challenge: how do we trigger a dynamic database connection switch mid-request?

Implementing Dynamic Database Connection Switching

Laravel's database manager isn't designed to swap schemas on the fly out of the box. We first tried using a simple DB::statement("SET search_path TO tenant_name") in a middleware, but that failed because Laravel’s connection pool cached the metadata. Every subsequent query in the same request cycle was still hitting the public schema.

We eventually landed on a solution that modifies the connection configuration at runtime. Here is how we handle the switch:

PHP
public function setTenant(Tenant $tenant)
{
    config(['database.connections.tenant.schema' => $tenant->schema_name]);
    
    DB::purge('tenant');
    DB::reconnect('tenant');
    
    Schema::connection('tenant')->getConnection()->statement(
        "SET search_path TO {$tenant->schema_name}"
    );
}

This approach works because DB::purge forces Laravel to drop the existing connection instance. When the next query executes, DB::reconnect builds a fresh connection using our updated search_path. It took us roughly 280ms to perform this handshake on our staging environment, which is acceptable for a cold start, though we're still monitoring if this adds overhead during high-concurrency traffic.

Managing Tenant Isolation and Migrations

The beauty of this architecture is that your migrations become simple. You can iterate through your tenants table and run the migration command for each schema:

Bash
foreach (Tenant::all() as $tenant) {
    Artisan::call('migrate', [
        '--database' => 'tenant',
        '--path' => 'database/migrations/tenant',
        '--force' => true
    ]);
}

However, don't forget that your public schema still needs to exist to store the shared tenants lookup table. We've had to be extremely careful about where our models point. Any model dealing with global data must explicitly use the public connection, while tenant-specific models remain connection-agnostic or rely on the tenant default.

Lessons Learned and Trade-offs

We initially tried to handle this via a custom DatabaseManager extension, but it was overkill. The DB::purge method, while slightly "hacky," is significantly easier to maintain. We also had to ensure that our Kubernetes security policies allowed for the increased number of connection handshakes, as our database proxy started flagging the rapid reconnects as potential anomalies.

If I were to do this again, I’d probably look into a dedicated package for multi-tenancy, as managing the connection state manually is fraught with edge cases—especially when dealing with queued jobs. We’re still seeing occasional issues where a background worker picks up a job without the correct schema set, leading to "Table not found" errors.

FAQ

Is PostgreSQL schema isolation secure enough for HIPAA? It provides logical isolation. While it's better than shared tables, most auditors prefer physical database isolation for high-compliance environments.

How does this impact performance? The DB::purge and reconnect calls add a small latency penalty. In our tests, it’s around 20-50ms per request, which is usually negligible compared to the query time.

Can I use this with Eloquent? Yes, but you must ensure your models are configured to use the tenant connection. It's best practice to define a TenantModel base class that sets the connection property to tenant.

This implementation isn't perfect, and we're currently exploring whether we need to move to a full database-per-tenant model to simplify our backup and restore pipelines. For now, schemas provide the balance we need, even if the connection management keeps us on our toes.