PostgreSQL Architecture Basics for Multi-Tenant SaaS

Choose a tenant model deliberately

A common shared-database model stores many organizations in the same tables and includes a tenant or organization identifier on tenant-owned records. This can be efficient and simple to operate, but every query and authorization path must preserve the boundary. Separate databases can provide stronger operational isolation at the cost of more provisioning and maintenance complexity.

Do not choose a model because it sounds more enterprise. Start from the required isolation, expected customer count, backup needs, regulatory constraints, and operational capacity.

Model membership separately from identity

A person may belong to more than one workspace. Keeping user identity separate from organization membership allows one account to have a different role in each tenant. Roles and permissions should describe what the membership can do rather than duplicating a user record for every workspace.

users → memberships → organizations
memberships → role / status / joined_at

Enforce integrity in the database too

• Use foreign keys for relationships the application relies on.
• Use unique constraints for business rules such as one membership per user and organization.
• Index tenant identifiers together with frequently filtered columns.
• Store audit events for sensitive changes when the product needs traceability.
• Treat application authorization and database constraints as complementary layers.

Plan for change rather than prediction

Schema design should make current rules explicit without attempting to predict every future feature. Use migrations, test them on realistic data, and keep destructive changes reversible where practical. A simple, well-constrained model is usually easier to evolve than a highly abstract schema built for hypothetical requirements.