Edit

Multitenancy and Azure Database for PostgreSQL

  • Article

Many multitenant solutions on Azure use the open-source relational database management system Azure Database for PostgreSQL. In this article, we review the features of Azure Database for PostgreSQL that are useful when working with multitenant systems. The article also links to guidance and examples for how to use Azure Database for PostgreSQL, in a multitenant solution.

Deployment modes

There are two deployment modes available for Azure Database for PostgreSQL that are suitable for use with multitenant applications:

  • Flexible Server - This is a good choice for most multitenant deployments that don't require the high scalability that's provided by Azure Cosmos DB for PostgreSQL.
  • Azure Cosmos DB for PostgreSQL - An Azure managed database service designed for solutions requiring a high level of scale, which often includes multitenanted applications. This service is part of the Azure Cosmos DB family of products.

Note

Azure Database for PostgreSQL - Single Server is on the retirement path and is scheduled for retirement by March 28, 2025. It is not recommended for new multitenant workloads.

Features of Azure Database for PostgreSQL that support multitenancy

When you're building a multitenant application using Azure Database for PostgreSQL, there are a number of features that you can use to enhance the solution.

Note

Some features are only available in specific deployment modes. These features are indicated in the guidance below.

Row-level security

Row-level security is useful for enforcing tenant-level isolation, when you use shared tables. In PostgreSQL, row-level security is implemented by applying row security policies to tables to restrict access to rows by tenant.

There maybe a slight performance impact when implementing row-level security on a table. Therefore, additional indexes might need to be created on tables with row-level security enabled to ensure performance is not impacted. It is recommended to use performance testing techniques to validate that your workload meets your baseline performance requirements when row-level security is enabled.

More information:

Horizontal scaling with sharding

The Sharding pattern enables you to scale your workload across multiple databases or database servers.

Solutions that need a very high level of scale can use Azure Cosmos DB for PostgreSQL. This deployment mode enables horizontal sharding of tenants across multiple servers (nodes). By using distributed tables in multitenant databases, you can ensure all data for a tenant is stored on the same node, which increases query performance.

Note

From October 2022, Azure Database for PostgreSQL Hyperscale (Citus) has been rebranded as Azure Cosmos DB for PostgreSQL and moved into the Cosmos DB family of products.

More information:

Connection pooling

Postgres uses a process-based model for connections. This model makes it inefficient to maintain large numbers of idle connections. Some multitenant architectures require a large number of active connections, which will negatively impact the performance of the Postgres server.

Connection pooling via PgBouncer is installed by default in Azure Database for PostgreSQL Flexible Server.

More information:

Contributors

This article is maintained by Microsoft. It was originally written by the following contributors.

Principal author:

Other contributors:

  • John Downs | Principal Customer Engineer, FastTrack for Azure
  • Arsen Vladimirskiy | Principal Customer Engineer, FastTrack for Azure
  • Paul Burpo | Principal Customer Engineer, FastTrack for Azure ISVs
  • Assaf Fraenkel | Senior Engineer/Data Architect, Azure FastTrack for ISVs and Start-ups

To see non-public LinkedIn profiles, sign in to LinkedIn.

Next steps

Review storage and data approaches for multitenancy.