Tenant Data Isolation in PostgreSQL: Building Multi-Tenant SaaS the Right Way

Every multi-tenant SaaS product eventually confronts the same design question: how should the database separate data between tenants? The answer shapes your performance profile, your compliance posture, your operational complexity, and your ability to serve enterprise customers who demand strict data isolation.

In PostgreSQL there are three main approaches: a separate database per tenant, a separate schema per tenant within a shared database, and a shared schema with row-level security. Each has real tradeoffs that look different at different points in a product's life. This is the analysis we use when designing database architecture for SaaS products built in Lebanon and the MENA region.

The three approaches and when each is appropriate

Database per tenant creates one PostgreSQL database for each customer organization. The customer's data is completely isolated at the database level. A bug in the application that returns the wrong data will never cross tenant boundaries at the database layer because there is no physical way for a query against tenant A's database to return tenant B's rows.

This approach is appropriate when you have enterprise customers with strict compliance requirements (SOC 2, GDPR, regulated industries), when tenants have significantly different performance profiles and you need to manage resources per tenant independently, or when tenant databases need different backup schedules, retention policies, or geographic regions.

The operational cost is high. Connection pooling must be per-database. Schema migrations require a separate migration execution per database. Monitoring requires aggregating metrics across potentially hundreds of databases. For a SaaS product targeting SMBs in Lebanon where compliance requirements are moderate, this overhead is rarely justified.

Schema per tenant creates one PostgreSQL schema for each customer within the same database. All tenants share the same database server and connection pool, but each tenant's tables are in their own namespace. A query against tenant_a.orders cannot accidentally return rows from tenant_b.orders.

Schema isolation is a middle ground. Migrations still require per-schema execution but can be parallelized across all schemas within a single database connection. PgBouncer pools connections at the database level, which is shared. The PostgreSQL search path must be set correctly for each connection to point at the right schema.

The limitation of schema per tenant is that it does not scale well past roughly 100 to 500 tenants in a single database. PostgreSQL catalog operations that scan all schemas slow down proportionally as the number of schemas grows. Introspection queries, pg_tables, and some migration tools become noticeably slow at 1,000+ schemas.

Row-level security (RLS) is the PostgreSQL native approach where all tenants share the same tables. Each row contains a tenant identifier (typically org_id or tenant_id), and PostgreSQL policies enforce that a database session can only access rows belonging to its tenant.

-- Create the policy
CREATE POLICY tenant_isolation ON orders
    USING (org_id = current_setting('app.current_org_id')::uuid);

-- Enable it
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Application sets the tenant context before querying
SET app.current_org_id = '550e8400-e29b-41d4-a716-446655440000';
SELECT * FROM orders; -- Only sees this tenant's orders

RLS is operationally the simplest approach. One schema, one migration per table, one connection pool. It scales to thousands of tenants with no degradation in operational complexity.

The risks of RLS are real and specific. The isolation is enforced by application-level session variable setting. A bug that fails to set app.current_org_id before a query will access all rows across all tenants, which is a data leak. Testing must explicitly verify that the tenant context is always set correctly. A superuser connection bypasses all RLS policies by default, so migration scripts and database administrators with superuser access must operate carefully.

How to choose between the three approaches

For most early-stage SaaS products targeting the Lebanese and MENA markets, the choice is between RLS and schema isolation. Database per tenant is typically appropriate only when compliance requirements explicitly demand physical data separation.

Start with RLS if: your initial customer base is fewer than 1,000 tenants, your team has strong application-level testing discipline, your compliance requirements do not mandate physical isolation, and you value operational simplicity over maximum isolation.

Start with schema per tenant if: you anticipate enterprise customers who will ask about data isolation in sales conversations, your team is already comfortable with schema-level PostgreSQL operations, or you have compliance requirements in specific regulated verticals.

The critical thing to avoid is deferring the decision entirely, using ad-hoc WHERE org_id = ? filters in application code without a systematic enforcement mechanism. This is how SaaS products end up with inconsistent filtering across hundreds of endpoints, which at some point produces a tenant data leak that is extremely difficult to audit.

Implementing RLS correctly in Go with sqlc

If you are using sqlc to generate type-safe Go code from SQL queries, implementing RLS requires a consistent pattern for setting the tenant context before executing any query.

type TenantDB struct {
    pool *pgxpool.Pool
}

func (db *TenantDB) WithTenant(ctx context.Context, orgID uuid.UUID) (*pgxpool.Conn, error) {
    conn, err := db.pool.Acquire(ctx)
    if err != nil {
        return nil, err
    }
    _, err = conn.Exec(ctx, "SET LOCAL app.current_org_id = $1", orgID.String())
    if err != nil {
        conn.Release()
        return nil, err
    }
    return conn, nil
}

The SET LOCAL variant sets the session variable only for the current transaction, which is important when using connection pools where connections are reused across requests. Without LOCAL, a connection that set the org ID for tenant A could be returned to the pool and reused by a request from tenant B before the variable is overwritten.

Every request handler must call WithTenant before executing any database queries. This is enforced structurally: the sqlc-generated queries accept a *pgxpool.Conn argument, and the only way to obtain a properly configured connection is through WithTenant. Making the correct path the only path is the most reliable form of tenant isolation enforcement.

Migration strategy when switching approaches

Starting with RLS and needing to migrate to schema-per-tenant later is a real scenario as products grow and acquire enterprise customers.

The migration path is: create new schemas for enterprise tenants, migrate their data from the shared tables to the tenant-specific schema, update the application routing layer to direct queries for those tenants to the tenant schema instead of the shared table, and verify the data before decommissioning the rows from the shared table.

This migration can be done incrementally without downtime. Enterprise customers who need schema isolation can be migrated one at a time. The bulk of SMB customers continue on the shared-table RLS model. The application routing layer needs to know which isolation model applies to each tenant, which can be stored as a configuration field on the organization record.

Key lessons from production

The isolation model is a day-one architectural decision. Retrofitting it later is expensive. Pick deliberately.

RLS with SET LOCAL and proper connection pooling is safe and scales well. The risk is in inconsistent tenant context setting, which must be addressed structurally, not through code review alone.

Schema per tenant has a practical upper limit around a few hundred schemas before PostgreSQL catalog operations become noticeably slow.

Enterprise sales conversations will ask about data isolation. Know your answer before you need it.

Test multi-tenancy explicitly. Unit tests that do not set the tenant context and do not verify isolation are not testing the most important property of the system.