Edit

Guidance for using Azure Database for PostgreSQL in a multitenant solution

Many multitenant solutions on Azure use the open-source relational database management system Azure Database for PostgreSQL. This article describes the features of Azure Database for PostgreSQL that are useful when you work with multitenant systems. The article also includes links to guidance and examples for how to use Azure Database for PostgreSQL in a multitenant solution.

Deployment options

The following options are available for Azure Database for PostgreSQL and are suitable for use with multitenant applications:

  • Azure Database for PostgreSQL is a good choice for most multitenant deployments that don't require the high scalability that Azure Cosmos DB for PostgreSQL provides.

  • Azure Database for PostgreSQL elastic clusters provides horizontal scaling within a managed service. It's suitable for multitenant applications that need to scale from a few tenants to high numbers of tenants.

  • Azure Cosmos DB for PostgreSQL is an Azure-managed database service designed for solutions that require a high level of scale, like multitenant applications. This service is part of the Azure Cosmos DB family of products.

    Important

    Azure Cosmos DB for PostgreSQL is on a retirement path and no longer recommended for new projects.

Azure Database for PostgreSQL features that support multitenancy

When you use Azure Database for PostgreSQL to build a multitenant application, the following features can enhance your solution.

Note

Some features are only available in specific deployment options. The following guidance describes which features are available.

Row-level security

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

Implementing row-level security on a table might affect performance. You might need to create other indexes on tables that have row-level security enabled to ensure that performance isn't affected. When you use row-level security, it's important to use performance testing techniques to validate that your workload meets your baseline performance requirements.

For more information about row-level security, see Access management for Azure Database for PostgreSQL.

Tenant context for row-level security

Row-level security policies require access to the current tenant identifier. Azure Database for PostgreSQL provides two extensions that help manage tenant context:

  • session_variable: Provides session-scoped variables that you can use to store and retrieve the tenant identifier within a session. Use these variables in your row-level security policies.
  • login_hook: Executes a function at login time. Use this extension to automatically set the tenant context when a connection is established.

For more information, see Extensions and extension versions for Azure Database for PostgreSQL.

Horizontal scaling with sharding

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

Solutions that need a high level of scale can use Azure Cosmos DB for PostgreSQL. This deployment mode enables horizontal sharding of tenants across multiple servers or nodes. Use distributed tables in multitenant databases to ensure that all data for a tenant is stored on the same node. This approach improves query performance.

For more information, see the following articles:

Elastic clusters

Elastic clusters are a feature of Azure Database for PostgreSQL. They provide horizontal scaling capabilities within a single managed service. This deployment option uses distributed table functionality for multitenant workloads that require scale-out capabilities.

In multitenant solutions, elastic clusters enable tenant data sharding across multiple nodes. Elastic clusters support two sharding models:

  • Row-based sharding: You can distribute tables by tenant ID to ensure that tenant data colocates on specific nodes. This approach can improve query performance for tenant-specific queries but requires that queries include the distribution column.
  • Schema-based sharding: You can isolate tenants by using a separate schema per tenant. This approach is ideal for ISVs deploying applications that can't undergo query modifications to support row-based sharding. Schema-based sharding is well-suited for workloads that have between 1 and 10,000 tenants.

For more information, see the following articles:

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 many active connections, which negatively affect the performance of the Postgres server.

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

For more information, see the following articles:

Microsoft Entra authentication

Azure Database for PostgreSQL supports connection authentication by using Microsoft Entra ID. This feature enables application workloads in a multitenant environment to authenticate to the database by using a tenant-specific service principal or managed identity. The database access can be scoped to an individual tenant. By combining Microsoft Entra ID authentication with tenant specific row security policies, you can reduce the risk of an application accessing another tenant's data from within a multitenant database.

For more information, see the following articles:

Azure confidential computing

Azure Database for PostgreSQL supports Azure confidential computing through trusted execution environments (TEEs), which provide hardware-based protection for data in use. This feature protects tenant data from unauthorized access by the operating system, hypervisor, or other applications.

For multitenant solutions that handle sensitive data, confidential computing provides hardware-level data protection during processing. Use confidential computing when tenants have strict data protection requirements or regulatory compliance needs or when you need to ensure that the application provider can't access tenant data.

For more information, see Azure confidential computing for Azure Database for PostgreSQL.

Encryption

Data stored in Azure Database for PostgreSQL is encrypted at rest by default by using Microsoft-managed keys, but you can also use customer-managed keys (CMKs) to allow tenants to specify their own encryption keys.

When you use CMKs, you can provide your own encryption keys stored in Azure Key Vault. In multitenant environments, this approach enables you to use different encryption keys for different tenants, even when their data is stored in the same database server. This capability also gives tenants control over their own encryption keys. If a tenant chooses to deactivate their account, deleting the associated key ensures that their data is no longer accessible.

Azure Database for PostgreSQL supports automatic key version updates for CMKs. This feature automatically updates to new key versions after rotation in Key Vault and doesn't require manual key version management. In multitenant environments where regulatory compliance requires regular key rotation, this automation reduces manual operational tasks and maintains data protection without service interruption.

Contributors

Microsoft maintains this article. The following contributors wrote this article.

Principal author:

Other contributors:

  • John Downs | Principal Software Engineer, Azure Patterns & Practices
  • 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 nonpublic LinkedIn profiles, sign in to LinkedIn.

  • Last updated on