# Multi-tenant SaaS partitioning models for PostgreSQL
<a name="partitioning-models"></a>

The best method for accomplishing multi-tenancy depends on the requirements for your SaaS application. The following sections demonstrate partitioning models for successfully implementing multi-tenancy in PostgreSQL. 

**Note**  
The models discussed in this section are applicable to both Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible. References to *PostgreSQL* in this section apply to both services.

There are three high-level models that you can use in PostgreSQL for SaaS partitioning: silo, bridge, and pool. The following image summarizes the trade-offs between the silo and pool models. The bridge model is a hybrid of the silo and pool models.


****  

| **Partitioning model** | **Advantages** | **Disadvantages** | 
| --- | --- | --- | 
| Silo | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | 
| Pool | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | 
| Bridge | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/partitioning-models.html) | 

The following sections discuss each model in more detail.

**Topics**
+ [

# PostgreSQL silo model
](silo.md)
+ [

# PostgreSQL pool model
](pool.md)
+ [

# PostgreSQL bridge model
](bridge.md)
+ [

# Decision matrix
](matrix.md)

# PostgreSQL silo model
<a name="silo"></a>

The silo model is implemented by provisioning a PostgreSQL instance for each tenant in an application. The silo model excels at tenant performance and security isolation, and completely eliminates the *noisy neighbor* phenomenon. The noisy neighbor phenomenon occurs when one tenant’s usage of a system affects the performance of another tenant. The silo model lets you tailor performance specifically to each tenant and potentially limit outages to a specific tenant’s silo. However, what generally drives adoption of a silo model is strict security and regulatory constraints. These constraints can be motivated by SaaS customers. For example, SaaS customers might demand that their data be isolated due to internal constraints, and SaaS providers might offer such a service for an additional fee. 

 ![\[SaaS PostgreSQL silo model\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/images/saas-postgresql-silo.png) 

Although the silo model might be necessary in certain cases, it has many drawbacks. It is often difficult to use the silo model in a cost-effective manner, because managing resource consumption across multiple PostgreSQL instances can be complicated. Furthermore, the distributed nature of database workloads in this model makes it more difficult to maintain a centralized view of tenant activity. Managing so many independently operated workloads increases operational and administrative overhead. The silo model also makes tenant onboarding more complicated and time-consuming, because you have to provision tenant-specific resources. Furthermore, the entire SaaS system can be harder to scale, because the ever-increasing number of tenant-specific PostgreSQL instances will demand more operational time to administer. One last consideration is that an application or a data access layer will have to maintain a mapping of tenants to their associated PostgreSQL instances, which adds to the complexity of implementing this model.

# PostgreSQL pool model
<a name="pool"></a>

The pool model is implemented by provisioning a single PostgreSQL instance (Amazon RDS or Aurora) and using [row-level security (RLS)](https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/) to maintain tenant data isolation. RLS policies restrict which rows in a table are returned by `SELECT` queries or which rows are affected by `INSERT`, `UPDATE`, and `DELETE` commands. The pool model centralizes all tenant data in a single PostgreSQL schema, so it is significantly more cost-effective and requires less operational overhead to maintain. Monitoring this solution is also significantly simpler due to its centralization. However, monitoring tenant-specific impacts in the pool model usually requires some additional instrumentation in the application. This is because PostgreSQL by default isn’t aware of which tenant is consuming resources. Tenant onboarding is simplified because no new infrastructure is required. This agility makes it easier to accomplish rapid and automated tenant onboarding workflows.

 ![\[SaaS PostgreSQL pool model\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/images/saas-postgresql-pool.png) 

Although the pool model is generally more cost-effective and simpler to administer, it does have some disadvantages. The noisy neighbor phenomenon cannot be completely eliminated in a pool model. However, it can be mitigated by ensuring that appropriate resources are available on the PostgreSQL instance and by using strategies to reduce the load in PostgreSQL, such as offloading queries to read replicas or to Amazon ElastiCache. Effective monitoring also plays a role in responding to tenant performance isolation concerns, because application instrumentation can log and monitor tenant-specific activity. Lastly, some SaaS customers might not find the logical separation provided by RLS to be sufficient and might ask for additional isolation measures.

# PostgreSQL bridge model
<a name="bridge"></a>

The PostgreSQL bridge model is a combination of the pooled and siloed approaches. Like the pooled model, you provision a single PostgreSQL instance for each tenant. To maintain tenant data isolation, you use PostgreSQL logical constructs. In the following diagram, PostgreSQL databases are used to logically separate data.

**Note**  
A PostgreSQL database doesn’t refer to a separate Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance. Instead, it refers to a logical construct of the PostgreSQL database management system to separate data.

 ![\[SaaS PostgreSQL bridge model with separate databases\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/images/saas-postgresql-bridge-dbs.png) 

You can also implement the bridge model by using a single PostgreSQL database, with tenant-specific schemas in each database, as illustrated in the following diagram.

 ![\[SaaS PostgreSQL bridge model with separate schemas\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/saas-multitenant-managed-postgresql/images/saas-postgresql-bridge-schemas.png) 

The bridge model suffers from the same noisy neighbor and tenant performance isolation concerns as the pool model. It also incurs some additional operational and provisioning overhead by requiring either separate databases or schemas to be provisioned on a per-tenant basis. It requires effective monitoring to respond quickly to tenant performance concerns. It also requires application instrumentation to monitor tenant-specific usage. Overall, the bridge model can be viewed as an alternative to RLS that slightly augments the tenant onboarding effort by requiring new PostgreSQL databases or schemas. As with the silo model, an application or a data access layer will have to maintain a mapping of tenants to their associated PostgreSQL databases or schemas.

# Decision matrix
<a name="matrix"></a>

To decide which multi-tenant SaaS partitioning model you should use with PostgreSQL, consult the following decision matrix. The matrix analyzes these four partitioning options:
+ Silo – A separate PostgreSQL instance or cluster for each tenant.
+ Bridge with separate databases – A separate database for each tenant in a single PostgreSQL instance or cluster.
+ Bridge with separate schemas – A separate schema for each tenant in a single PostgreSQL database, in a single PostgreSQL instance or cluster.
+ Pool – Shared tables for tenants in a single instance and schema.


****  

| **** | **Silo** | **Bridge with separate databases** | **Bridge with separate schemas** | **Pool** | 
| --- | --- | --- | --- | --- | 
| Use case | Isolation of data with full control of resource usage is a key requirement, or you have very large and very performance-sensitive tenants. | Isolation of data is a key requirement, and limited or no cross-reference of tenants’ data is required. | Moderate number of tenants with a moderate amount of data. This is the preferred model if you have to cross-reference tenants’ data. | Large number of tenants with less data per tenant. | 
| New tenant onboarding agility | Very slow. (A new instance or cluster is required for each tenant.) | Moderately slow. (Requires creating a new database for each tenant to store schema objects.) | Moderately slow. (Requires creating a new schema for each tenant to store objects.) | Fastest option. (Minimal setup is required.) | 
| Database connection pool configuration effort and efficiency | Significant effort required. (One connection pool per tenant.) Less efficient. (No database connection sharing between tenants.)  | Significant effort required. (One connection pool configuration per tenant unless you use [Amazon RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.html).)  Less efficient. (No database connection sharing between tenants and total number of connections. Usage across all tenants is limited based on the DB instance class.) | Less effort required. (One connection pool configuration for all tenants.)  Moderately efficient. (Connection reuse through the `SET ROLE` or `SET SCHEMA` command in session pool mode only. `SET` commands also cause session pinning when using Amazon RDS Proxy, but the client connection pools can be eliminated and direct connections can be made for each request for efficiency.) | Least effort required. Most efficient. (One connection pool for all tenants and efficient connection reuse across all tenants. Database connection limits are based on the DB instance class.) | 
| Database maintenance ([vacuum management](https://www.postgresql.org/docs/current/routine-vacuuming.html)) and resource usage | Simpler management. | Medium complexity. (Might lead to high resource consumption, because a vacuum worker has to be started for each database after vacuum\$1naptime, which leads to high autovacuum launcher CPU usage. There might also be additional overhead associated with vacuuming the PostgreSQL system catalog tables for each database.) | Large PostgreSQL system catalog tables. (Total pg\$1catalog size in tens of GBs, depending on number of tenants and relations. Likely to require modifications to vacuuming-related parameters to control table bloat.) | Tables might be large, depending on the number of tenants and data per tenant. (Likely to require modifications to vacuuming-related parameters to control table bloat.) | 
| Extensions management effort | Significant effort (for each database in separate instances). | Significant effort (at each database level). | Minimal effort (one time in the common database). | Minimal effort (one time in the common database). | 
| Change deployment effort | Significant effort. (Connect to each separate instance and roll out changes.) | Significant effort. (Connect to each database and schema, and roll out changes.) | Moderate effort. (Connect to common database and roll out changes for each schema.) | Minimal effort. (Connect to common database and roll out changes.) | 
| Change deployment – scope of impact | Minimal. (Single tenant affected.) | Minimal. (Single tenant affected.) | Minimal. (Single tenant affected.) | Very large. (All tenants affected.) | 
| Query performance management and effort | Manageable query performance. | Manageable query performance. | Manageable query performance. | Significant effort likely required to maintain query performance. (Over time, queries might run more slowly due to the increased size of tables. You can use table partitioning and database sharding to maintain performance.) | 
| Cross-tenant resource impact | No impact. (No resource sharing among tenants.) | Moderate impact. (Tenants share common resources such as instance CPU and memory.) | Moderate impact. (Tenants share common resources such as instance CPU and memory.) | Heavy impact. (Tenants affect one another in terms of resources, lock conflicts, and so on.) | 
| Tenant-level tuning (for example, creation of additional indexes per tenant or DB parameter tweaking for a particular tenant) | Possible. | Somewhat possible. (Schema-level changes can be made for each tenant, but database parameters are global across all tenants.) | Somewhat possible. (Schema-level changes can be made for each tenant, but database parameters are global across all tenants.) | Not possible. (Tables are shared by all tenants.) | 
| Rebalance effort for performance-sensitive tenants | Minimal. (No need to rebalance. Scale server and I/O resources to handle this scenario.) | Moderate. (Use logical replication or pg\$1dump to export the database, but downtime might be lengthy depending on data size. You can use the transportable database feature in Amazon RDS for PostgreSQL to copy databases between instances faster.) | Moderate but likely involves lengthy downtime. (Use logical replication or pg\$1dump to export the schema, but downtime might be lengthy depending on data size.) | Significant, because all tenants share the same tables. (Sharding the database requires copying everything to another instance and an additional step to clean up tenant data.)  Most likely requires a change in application logic. | 
| Database downtime for major version upgrades | Standard downtime. (Depends on PostgreSQL system catalog size.) | Longer downtime likely. (Depending on system catalog size, the time will vary. PostgreSQL system catalog tables are also duplicated across databases) | Longer downtime likely. (Depending on PostgreSQL system catalog size, the time will vary.) | Standard downtime. (Depends on PostgreSQL system catalog size.) | 
| Administration overhead (for example, for database log analysis or backup job monitoring) | Significant effort | Minimal effort. | Minimal effort. | Minimal effort. | 
| Tenant-level availability | Highest. (Each tenant fails and recovers independently.) | Higher scope of impact. (All tenants fail and recover together in case of hardware or resource issues.) | Higher scope of impact. (All tenants fail and recover together in case of hardware or resource issues.) | Higher scope of impact. (All tenants fail and recover together in case of hardware or resource issues.) | 
| Tenant-level backup and recovery effort | Least effort. (Each tenant can be backed up and restored independently.) | Moderate effort. (Use logical export and import for each tenant. Some coding and automation are required.) | Moderate effort. (Use logical export and import for each tenant. Some coding and automation are required.) | Significant effort. (All tenants share the same tables.) | 
| Tenant-level point-in-time recovery effort | Minimal effort. (Use point-in time recovery by using snapshots, or use backtracking in Amazon Aurora.) | Moderate effort. (Use snapshot restore, followed by export/import. However, this will be a slow operation.) | Moderate effort. (Use snapshot restore, followed by export/import. However, this will be a slow operation.) | Significant effort and complexity. | 
| Uniform schema name | Same schema name for each tenant. | Same schema name for each tenant. | Different schema for each tenant. | Common schema. | 
| Per-tenant customization (for example, additional table columns for a specific tenant) | Possible. | Possible. | Possible. | Complicated (because all tenants share the same tables). | 
| Catalog management efficiency at object-relational mapping (ORM) layer (for example, Ruby) | Efficient (because the client connection is specific for a tenant). | Efficient (because the client connection is specific to a database). | Moderately efficient. (Depending on the ORM used, user/role security model, and search\$1path configuration, the client sometimes caches the metadata for all tenants, leading to high DB connection memory usage.) | Efficient (because all tenants share the same tables). | 
| Consolidated tenant reporting effort | Significant effort. (You have to use foreign data wrappers [FDWs] to consolidate data in all tenants or extract, transform, and load [ETL] to another reporting database.) | Significant effort. (You have to use FDWs to consolidate data in all tenants or ETL to another reporting database.) | Moderate effort. (You can aggregate data in all schemas by using unions.) | Minimal effort. (All tenant data is in the same tables, so reporting is simple.) | 
| Tenant-specific read-only instance for reporting (for example, based on subscription) | Least effort. (Create a read replica.) | Moderate effort. (You can use logical replication or AWS Database Migration Service [AWS DMS] to configure.) | Moderate effort. (You can use logical replication or AWS DMS to configure.) | Complicated (because all tenants share the same tables). | 
| Data isolation | Best. | Better. (You can manage database-level permissions by using PostgreSQL roles.) | Better. (You can manage schema-level permissions by using PostgreSQL roles.) | Worse. (Because all tenants share the same tables, you have to implement features such as row-level security [RLS] for tenant isolation.) | 
| Tenant-specific storage encryption key | Possible. (Each PostgreSQL cluster can have its own AWS Key Management Service [AWS KMS] key for storage encryption.) | Not possible. (All tenants share the same KMS key for storage encryption.) | Not possible. (All tenants share the same KMS key for storage encryption.) | Not possible. (All tenants share the same KMS key for storage encryption.) | 
| Using AWS Identity and Access Management (IAM) for database authentication for each tenant | Possible. | Possible. | Possible (by having separate PostgreSQL users for each schema). | Not possible (because tables are shared by all tenants). | 
| Infrastructure cost | Highest (because nothing is shared). | Moderate. | Moderate. | Lowest. | 
| Data duplication and storage usage | Highest aggregate across all tenants. (PostgreSQL system catalog tables and the application’s static and common data are duplicated across all tenants.) | Highest aggregate across all tenants. (PostgreSQL system catalog tables and the application’s static and common data are duplicated across all tenants.) | Moderate. (The application’s static and common data can be in a common schema and accessed by other tenants.) | Minimal. (No duplication of data. The application’s static and common data can be in the same schema.) | 
| Tenant-centric monitoring (quickly find out which tenant is causing issues) | Least effort. (Because each tenant is monitored separately, it’s easy to check the activity of a specific tenant.) | Moderate effort. (Because all tenants share the same physical resource, you have to apply additional filtering to check the activity of a specific tenant.) | Moderate effort. (Because all tenants share the same physical resource, you have to apply additional filtering to check the activity of a specific tenant.) | Significant effort. (Because all tenants share all resources, including tables, you have to use bind variable capture to check which tenant a specific SQL query belongs to.) | 
| Centralized management and health/activity monitoring | Significant effort (to set up central monitoring and a central command center). | Moderate effort (because all tenants share the same instance). | Moderate effort (because all tenants share the same instance). | Minimal effort (because all tenants share the same resources, including the schema). | 
| Chances of object identifier (OID) and transaction ID (XID) wraparound | Minimal.  | High. (Because OID,XID is a single PostgreSQL clusterwide counter and there can be issues vacuuming effectively across physical databases). | Moderate. (Because OID,XID is a single PostgreSQL clusterwide counter). | High. (For example, a single table can reach the TOAST OID limit of 4 billion, depending on the number of out-of-line columns.) |