Shared schema, shared database<\/strong> is the most common pattern for early-stage SaaS products. Every tenant’s data lives in the same tables, distinguished by a tenant_id column on every row. A users table has a tenant_id. A projects table has a tenant_id. An invoices table has a tenant_id.<\/p>\n\n\n\nThe advantages are operational simplicity and cost efficiency. One database to back up, one schema to migrate, one connection pool to manage. The disadvantages are that tenant isolation is entirely enforced at the application layer, meaning one missing filter condition exposes all tenants’ data simultaneously.<\/p>\n\n\n\n
sql<\/p>\n\n\n\n
-- Every query must include tenant context\nSELECT * FROM projects \nWHERE tenant_id = $1 \nAND status = 'active';\n\n-- Missing the tenant_id filter is a silent data leak\nSELECT * FROM projects WHERE status = 'active'; -- WRONG<\/code><\/pre>\n\n\n\nSeparate schemas, shared database<\/strong> gives each tenant their own PostgreSQL schema within the same database instance. The tables are identical in structure across schemas, but the data is physically separated. You switch tenant context by setting the search_path at the connection level.<\/p>\n\n\n\nsql<\/p>\n\n\n\n
-- Switch to tenant context\nSET search_path TO tenant_abc, public;\n\n-- Now queries automatically scope to this tenant's schema\nSELECT * FROM projects WHERE status = 'active';<\/code><\/pre>\n\n\n\nThis model provides stronger isolation than shared schema because a misconfigured query cannot accidentally read another tenant’s data. The tradeoff is schema migration complexity: deploying a new column means running the migration across every tenant schema, which at scale requires careful orchestration.<\/p>\n\n\n\n
Separate databases<\/strong> gives each tenant a completely isolated database instance. This is the strongest isolation model and the most operationally complex. It is typically reserved for enterprise customers with contractual data isolation requirements, or for SaaS products in regulated industries like healthcare or finance.<\/p>\n\n\n\n<\/span>PostgreSQL row-level security for tenant isolation<\/span><\/h2>\n\n\n\nPostgreSQL’s row-level security (RLS) feature allows you to enforce tenant isolation at the database level rather than relying entirely on application-layer filters. This means even a query that forgets the tenant_id WHERE clause will return empty results rather than leaking data.<\/p>\n\n\n\n
sql<\/p>\n\n\n\n
-- Enable RLS on the projects table\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\n-- Create a policy that restricts reads to current tenant\nCREATE POLICY tenant_isolation ON projects\n USING (tenant_id = current_setting('app.current_tenant_id')::uuid);<\/code><\/pre>\n\n\n\nIn your Node.js application, you set the tenant context at the start of each request:<\/p>\n\n\n\n
typescript<\/p>\n\n\n\n
\/\/ Set tenant context before any queries\nawait db.query(`SET LOCAL app.current_tenant_id = '${tenantId}'`);<\/code><\/pre>\n\n\n\nThe advantage of this approach is that the database itself becomes the enforcement layer. Application bugs that forget tenant filtering return empty results rather than returning all tenants’ data. The disadvantage is that it requires careful management of the tenant context throughout connection pooling, because pooled connections may carry stale context from a previous request.<\/p>\n\n\n\n
<\/span>Connection pooling in multi-tenant systems<\/span><\/h2>\n\n\n\nConnection pooling interacts with multi-tenancy in ways that create subtle security risks if not handled correctly. A connection pool maintains persistent connections to the database. If you set a session-level variable like app.current_tenant_id on a connection and then return that connection to the pool, the next request that picks up that connection may inherit the previous tenant’s context.<\/p>\n\n\n\n
The solution is to use transaction-scoped settings rather than session-scoped settings when setting tenant context:<\/p>\n\n\n\n
typescript<\/p>\n\n\n\n
\/\/ Use SET LOCAL (transaction-scoped) not SET (session-scoped)\nawait db.query(\"BEGIN\");\nawait db.query(`SET LOCAL app.current_tenant_id = '${tenantId}'`);\n\/\/ ... your queries\nawait db.query(\"COMMIT\");\n\/\/ Connection returns to pool with clean context<\/code><\/pre>\n\n\n\nA production-ready connection pool configuration for multi-tenant SaaS should also set explicit minimum and maximum connection counts, query execution timeouts to prevent long-running tenant queries from monopolizing pool connections, and health check queries to detect stale connections before they are issued to application code.<\/p>\n\n\n\n