Designing Multi-Tenant SaaS Databases with PostgreSQL
A practical guide to shared-table, schema-per-tenant and database-per-tenant models, tenant isolation, row-level security, migrations and operational trade-offs.
For many early SaaS products, shared tables with a tenant identifier can be cost-effective and operationally simple. Strong access controls, tenant-aware queries, row-level security and tested isolation are essential. Separate schemas or databases may be justified by regulatory, scale or customer-specific needs.
Shared tables with a tenant key
Each tenant-owned row includes a tenant_id, and indexes and constraints account for that key. This model keeps migrations and connection management relatively simple. The main risk is cross-tenant access caused by a missing filter, incorrect join or overly broad service permission.
Schema per tenant
Each tenant receives a separate PostgreSQL schema. This can provide clearer namespace separation and support tenant-specific structures, but increases migration, connection, monitoring and operational complexity as tenant count grows.
Database per tenant
Each tenant has a separate database. This can improve isolation and make customer-specific backup, restore or residency arrangements easier. It also increases provisioning, patching, migration, observability and cost-management work.
Use layered tenant isolation
Derive tenant identity from trusted authentication context rather than accepting an arbitrary client-supplied identifier. Apply server-side authorization, tenant-scoped queries, composite constraints and PostgreSQL row-level security where appropriate. Test both allowed and denied access paths.
Plan migrations and operations
Automate schema migrations, test them on representative data, keep them reversible where practical and monitor long-running changes. Backups should be tested with restore drills. Decide how tenant deletion, export, retention and incident investigation will work before the system becomes large.
Frequently asked questions
Is one shared PostgreSQL database safe for multiple tenants?
It can be, when tenant identity is enforced consistently at the application and database layers, access paths are tested and operational controls are in place. The model itself does not guarantee isolation.
What is PostgreSQL row-level security?
Row-level security lets policies restrict which rows a database role can read, insert, update or delete. It is a useful additional control, but it must be configured and tested carefully.
When should a SaaS product use a database per tenant?
It may be appropriate where customer isolation, independent restore, data residency or custom operational requirements justify the extra cost and management complexity.