Multi-Tenancy on PostgreSQL : An Introduction


Multi-tenant architecture, more commonly referred to as multi-tenancy, is a software architecture where multiple instances of an application run on the same physical server. The same server is then responsible for serving multiple tenants simultaneously.

This type of build allows companies to allocate a single infrastructure to several end users, rather than individually managing the maintenance and updates of multiple environments.

How Multi-Tenancy Works:

The term “tenant” is used to describe the group of users or applications that share access to the same hardware resources. In a multi-tenant architecture, all users share access to the same infrastructure resources that could facilitate collaborative work such as memory, network controller, and access to system resources.

It’s used often in cloud computing, enabling service providers like Amazon Web Services, Microsoft Azure, and Google Cloud to offer a more affordable shared-tenancy option on the public cloud. However, it can also be utilized by software-as-a-service (SaaS) companies or companies with internal software that needs to be distributed to employees in various departments and physical locations.

Multi-tenant architecture works by utilizing virtual machines (VMs). On the same physical server, they’re able to create multiple VMs that all share the same hardware but operate as separate computers in complete independence from one another. This guarantees the user’s security and privacy, especially if the cloud environment is shared with foreign individuals and entities.

Importance :

  • Multi-tenancy has seen a lot of could adoption and is used most with cloud computing.
  • Multi-tenant architectures are found in both public cloud and private cloud environments, allowing each tenant’s data to be separated from each other.
  • Multi-tenancy is also important for the scalability of public and private clouds and has helped make multi-tenancy a standard.
  • The multi-tenant architecture can also aid in providing a better Return On Investment for organizations, as well as quickening the pace of maintenance and updates for tenants.

Key Multi-Tenant Database Consideration:

When it comes to designing the database layer for a multi-tenant system, there are a core set of considerations you should take into account as you decide on the approach that best aligns with your PostgreSQL Server strategy.

  1. Security : Data security is critical in all systems, but in multi-tenant systems you have the extra dimension of there being multiple tenants’ data present in the environment, not just a single, isolated tenant’s data. If you have a multi-tenant software offering, it’s a fundamental requirement that one tenant should not be able to see or interact with any other tenant’s data, and customers often look for assurances before signing up that their data is secure. If they can see other tenants’ data, then that’s a surefire way to lose customers and trust. That will cost you a lot. You could also open yourself up to fines for violating data regulations.
  1. Maintainability : You have a single-tenant system. You have a plan for taking backups of the database on a given schedule. You have some nightly jobs that perform maintenance tasks such as index rebuild/reorganization or data integrity checks to keep the system performing in its prime. You have a strategy for rolling out database updates. Maybe, just maybe, you even have a High Availability/Disaster Recovery solution set up.
  1. Scalability : There’s a clear difference between having 1 tenant with 10 million rows of data and having 1,000 tenants with 10 million rows each. That single month-end report is now going to run 1,000 times over. Knowing how many tenants you’ll have is also an unknown—as a business, you want as many as possible and to be able to add more tenants over time without negatively impacting the performance of the system. 

 Single Tenant vs Multi-Tenant

Single Tenant

A single instance of the software and supporting infrastructure serve a single customer. With single tenancy, each customer has his or her own independent database and instance of the software. Essentially, there is no sharing happening with this option.

Potential benefits of single-tenant include:

  • Security: A single customer and a single server is often contained on secure hardware being used by a limited number of people.
  • Dependability: With an entire environment dedicated to one client, resources are abundant and available anytime.
  • Customization: Control over the entire environment allows for customization and added functionality, if desired.

Multi-Tenant –

Multi-tenant means that a single instance of the software and its supporting infrastructure serves multiple customers. Each customer shares the software application and also shares a single database. Each tenant’s data is isolated and remains invisible to other tenants.

Potential benefits of multi-tenant:

  • Affordable Cost: Having multiple customers on the same instance means that the cost for the environment is shared, and those savings could be transferred to your customers or end-users Integrations: Cloud environments allow for easier integration with other applications through the use of APIs.
  • “Hands-free” Maintenance: The server technically belongs to the IAAS provider, meaning that a certain level of database maintenance is handled by the IAAS provider, instead of you maintaining the environment yourself.

Implementation of Multi-Tenancy on Databases:

 The following are the 4 approaches I will cover in this blog post:

  1. Single database, shared schema
  2. Single database, separate schema
  3. One Database per tenant
  4. Multiple databases, multiple tenants per database, shared schema

1.Single database, shared schema : 

  • One database to hold the data for all tenants
  • Every tenant’s data is stored in the same set of tables
  • Tables that contain tenant-specific data include a column to identify which tenant each row belongs to


❌ Risk of exposing one tenant’s data to another tenant or updating the wrong tenant’s data (e.g., if a developer misses a WHERE clause to filter on the tenant id)

  • Mitigation: Row-Level-Security(RLS) can be used to control access to rows in a table. Create an inline table-valued function to apply a filter on the tenant id and then create a security policy to apply that filter predicate automatically on the target tables. As long as you maintain that security policy with the full set of tables, queries/updates on those tables will then be automatically enforced. Developers don’t need to remember to manually add the filter clause to every SQL statement.

❌ No tenant isolation


✔️ One database schema to maintain and a simple schema update rollout process—it only needs to be applied once

✔️ Manage the High Availability/Disaster Recovery/maintenance operation/monitoring strategy for just one database

✔️ Limited development/application code complexity—single schema, single database to connect to

✔️ Adding new tenants is easy—no processes needed around database/schema provisioning or connection determination

❌ Any query or data modification must includes a predicate to restrict the operation to a specific tenant id

  • Mitigation: Can use RLS policy

❌ Must remember to update the RLS policy as new tables are added over time

❌ Can’t easily restore a single tenant’s data


❌ Limited to scaling-up hardware, rather than scaling out

❌ Risk of “noisy neighbors”—tenants can impact the performance of the system for all others due to a lack of isolation and all competing for the same resources

❌ One-size-fits-all performance tuning and stability—tenants’ data volumes and usage can vary dramatically, impacting things such as execution plans making it more difficult to optimize performance across every tenant

❌ As the number of tenants and data per tenant grows, maintenance activities take longer, potentially impacting all tenants.

2.Single Database, Separate Schema : 

  • One database to hold the data for all tenants
  • Separate tables for each tenant, each set under a tenant-specific schema


✔️ Tenant data has some more isolation (but still within the same database)

✔️ No RLS needed; reduced risk of missing a WHERE clause to limit to specific tenant’s data

  • Either queries need to be dynamic to define the qualified table name or connect using a tenant-specific user account with the default schema set to that tenant’s schema (preferred)

❌ Still a risk of querying the incorrect schema (e.g., specifying the schema for an object when it should have instead come from the user account’s default schema—usual best practice is include schema prefixes, which can feel unnatural)

❌ Still limited data isolation


✔️ 1 database to manage High Availability/Disaster Recovery/maintenance operation/monitoring strategy for

✔️ Extra scope and control over some tenant-specific maintenance activities

❌ Schema updates more involved, needing to be rolled out to n tenants

  • Mitigation: Automate the process

❌ Query complexity

❌ Can’t easily restore a single tenant’s data (although it’s a slightly better process than approach 1 due to isolation of tenant data)

❌ Adding new tenants is more involved as new schemas/user accounts need to be created

  • Mitigation: Automate the process

As the number of tenants grows, there will be a lot of database objects being created to manage and maintain


✔️ Data is partitioned into smaller tables, with smaller indexes

✔️ Optimizations could be made at an individual tenant’s schema level

  • Caution: This could cause schema drift (e.g., custom indexes, which introduces a maintenance / source control process cost). Strong processes would be needed to track these changes to the schema.

❌ Limited to scaling-up hardware, rather than scaling out

❌ Risk of “noisy neighbors”—tenants can impact the performance of the system for all others due to limited level of isolation and all competing for the same resources

3.One Database Per Tenant

Each tenant has their own database


✔️ Highest level of tenant isolation, supporting options for shared server and/or isolated servers

❌ Potentially more servers to patch and keep secure


✔️ Maintenance jobs can be managed and customized per tenant

✔️ Can easily restore/relocate/clear down a tenant’s data

✔️ No added query complexity

❌ Adding new tenants is more involved, as new schemas need to be created

  • Mitigation: Automate the process

❌ As the number of tenants grows, there will be more databases being created to manage and maintain

❌ Some added complexity to maintain a registry of tenant-db mappings/application code to determine which connection to use


✔️ Scale-out and scale-up are both options—tenants can be spread over multiple servers

✔️ Choose to balance between cost (higher tenant density/fewer servers) and performance (lower tenant density/more servers)

✔️ Control over “noisy neighbor” risks

4.Multiple Databases, Multiple Tenants Per Database, Shared Schema

  • Hybrid of approach #1 and approach #3
  • A pool of databases exist
  • Tenants share a database and schema with other tenants, but are spread over multiple databases


✔️ Some tenant isolation possible in general over approach #1

❌ Tenants still share a database and schema with others (same RLS mitigation applies as approach #1)


✔️ Choose to balance between overhead of more databases to maintain (lower tenant density) versus fewer (higher tenant density)

✔️ Possible to relocate a tenant’s data (although harder than approach #3)

❌ More maintenance overhead than approach #1


✔️ Scale-out and scale-up are both options—tenants can be spread over multiple servers

✔️ Choose to balance between cost (higher tenant density/fewer servers) and performance (lower tenant density/more servers)


You might have now gained a brief idea about multi-tenancy, and how it can be implemented on a PostgreSQL database. While there is no one-size fits all approach, choosing the right architecture and automation tools will ensure that your PostgreSQL databases as well as DBAs remain massively performant, or at the very least, optimally utilized.

How would you implement multi-tenancy in your company? Comment below and let us know your thoughts.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>