Events
Apr 8, 3 PM - May 28, 7 AM
Sharpen your AI skills and enter the sweepstakes to win a free Certification exam
Register now!Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Azure Database for PostgreSQL is a relational database service in Azure that's based on the PostgreSQL open-source relational database. It's a fully managed, cloud-based database solution that supports mission-critical workloads with predictable performance, security, high availability, and dynamic scalability. Azure Database for PostgreSQL is built on the community edition of the PostgreSQL database engine. It's compatible with the PostgreSQL server community edition and supports PostgreSQL extension features, such as PostGIS and TimescaleDB.
This article assumes that as an architect, you've reviewed the Azure data options and chose Azure Database for PostgreSQL as the data store for your workload. The guidance in this article provides architectural recommendations that are mapped to the principles of the Well-Architected Framework pillars.
Important
How to use this guide
Each section has a design checklist that presents architectural areas of concern along with design strategies localized to the technology scope.
Also included are recommendations for the technology capabilities that can help materialize those strategies. The recommendations don't represent an exhaustive list of all configurations that are available for Azure Database for PostgreSQL and its dependencies. Instead, they list the key recommendations mapped to the design perspectives. Use the recommendations to build your proof-of-concept or to optimize your existing environments.
Foundational architecture that demonstrates the key recommendations:
Reliable Web App pattern for Java.
Technology scope
This review focuses on the interrelated decisions for the following Azure resources:
- Azure Database for PostgreSQL
The purpose of the Reliability pillar is to provide continued functionality by building enough resilience and the ability to recover fast from failures.
Reliability design principles provide a high-level design strategy applied for individual components, system flows, and the system as a whole.
Familiarize yourself with Azure Database for PostgreSQL product reliability guidance.
For more information, see the following resources:Align your reliability and recovery targets for Azure Database for PostgreSQL with your workload targets. Choose an appropriate Azure Database for PostgreSQL SKU that can support your reliability and recovery targets.
Determine the appropriate high availability and redundancy configurations for your Azure Database for PostgreSQL instances. Determine whether you need zone-redundant or zonal configurations to meet your reliability requirements.
Incorporate the recovery of your Azure Database for PostgreSQL instances into your disaster recovery planning and exercises. Ensure that your entire workload is recoverable according to your recovery targets.
Incorporate your Azure Database for PostgreSQL instances into your observability platform. Enable enhanced metrics to monitor the health of your instance. Include high availability health monitoring in your monitoring solution.
| Recommendation | Benefit |
|---|---|
| Select the appropriate high availability configuration. | When high availability is configured, the Azure Database for PostgreSQL server automatically provisions and manages a standby replica. This setup ensures that the service remains available and doesn't lose data during a zone outage. |
| Configure geo-redundant backups. | Cross-region read replicas can be deployed to protect your databases from region-level failures. Geo-redundant backups are enabled in selected regions and help with disaster recovery if the primary server region is unavailable. Geo-redundancy can also be achieved by using an Azure Backup vault for long-term storage of the recovery points. If a regional outage or disaster occurs, you can use Backup to restore the database server to an Azure-paired region, which minimizes downtime. Azure Backup also provides geo-redundancy for Azure Database for PostgreSQL. This feature increases efficiency and reduces downtime during disasters or regional outages. |
| Test your backup and restore strategy reguarly. | Regularly testing your backup and restore strategy ensures that you can recover your databases and maintain operations if a failure occurs. |
The purpose of the Security pillar is to provide confidentiality, integrity, and availability guarantees to the workload.
The Security design principles provide a high-level design strategy for achieving those goals by applying approaches to the technical design of Azure Database for PostgreSQL.
Review the security baseline. To enhance the security posture of your workload, review the Azure security baseline for Azure Database for PostgreSQL.
Implement strict, conditional, and auditable identity and access management. Use Microsoft Entra ID for authentication and authorization to enhance identity management.
Apply network segmentation and security controls. Use built-in firewalling at the server level alongside virtual network firewalling mechanisms to isolate your instance from other workload components and public networks.
Use native security features to add extra layers of protection. Help prevent malicious activities by configuring row-level security and connection throttling.
| Recommendation | Benefit |
|---|---|
| Implement network security groups and firewalls to control access to your database. | As part of the Zero Trust model for security, use network segmentation to restrict communication paths between components, such as application and database servers, to only what's needed. This feature can be implemented by using network security groups and application security groups. |
| Connect to your databases over Azure Private Link. | Private Link allows you to create private endpoints for Azure Database for PostgreSQL to extend the service inside your virtual network. |
| Use Microsoft Entra ID for authentication and authorization to enhance identity management. | You can use Microsoft Entra authentication to connect to Azure Database for PostgreSQL by using managed identities in Microsoft Entra. |
| Configure row-level security. | Row-level security is a PostgreSQL security feature that allows database administrators to define policies to control how specific rows of data display and operate for one or more roles. Row-level security is an extra filter that you can apply to a PostgreSQL database table. |
| If needed for compliance, use customer managed keys (CMKs) for data encryption, and store your keys in Azure Key Vault. | CMKs give you full control of your encryption key's life cycle, including key rotation, to align with corporate policies. Key Vault enables centralized management and organization of your encryption keys within your own dedicated Key Vault instances. |
| Enable connection throttling for IP addresses that have excessive failed sign-in attempts. | Setting the connection_throttling server parameter to enabled helps protect your databases from malicious sign-in attempts and distributed denial of service (DDoS) attacks by limiting repeated connection attempts from the same IP address. |
| Conduct security audits regularly. | Regularly conducting security audits helps identify and remediate potential vulnerabilities. |
Cost Optimization focuses on detecting spend patterns, prioritizing investments in critical areas, and optimizing in others to meet the organization's budget while meeting business requirements.
The Cost Optimization design principles provide a high-level design strategy for achieving those goals and making tradeoffs as necessary in the technical design related to Azure Database for PostgreSQL and its environment.
Estimate the initial cost. As part of your cost modeling exercise, use the Azure pricing calculator to evaluate the approximate costs associated with Azure Database for PostgreSQL in your workload.
Choose the right service tier for your workload. Determine whether the Burstable, General Purpose, or Memory Optimized pricing tier for compute resources meets your use case needs.
Apply a well-informed scaling strategy. To determine when and how to scale your Azure Database for PostgreSQL resources, continuously observe and analyze your workload capacity and demand.
Take advantage of available discounts. Consider one or three year compute reservations. Reserved instances can save you significant costs for compute resources.
Use your provisioned storage. There's no extra charge for backup storage up to 100% of your total provisioned server storage.
Understand redundancy costs. Using zone-redundant or zonal configurations doubles your instance costs, so carefully consider your redundancy design.
Understand protected instance and backup storage costs. When you back up Azure Databse for PostgreSQL by using Azure Backup, you incur protected instance fees for every 250 GB and backup storage fees based on total data stored and redundancy type.
Deploy to the same region as an app. Deploy to the same region as your applications to minimize transfer costs.
Consolidate databases and servers. When practical, consolidating multiple databases and servers into a single server can help reduce costs.
| Recommendations | Benefits |
|---|---|
| Pick the right tier and SKU. | Choosing the right tier and SKU helps you avoid wasting money on over-provisioned resources. Azure Advisor provides recommendations, include server rightsizing suggestions, to optimize and reduce your overall Azure spending. |
| Scale compute and storage resources when your workload demand changes. | You can scale compute resources vertically (up or down) and horizontally (out or in) as necessary. You can't scale storage down after scaling up. |
| Use the start/stop feature. | Use the start/stop feature to stop the server from running when it isn't needed. |
Operational Excellence primarily focuses on procedures for development practices, observability, and release management.
The Operational Excellence design principles provide a high-level design strategy for achieving those goals for the operational requirements of the workload.
Optimize the recoverability of your databases. Define backup and retention policies to meet your compliance requirements.
Automate operational tasks. Use automation tasks to automatically perform tasks like starting and stopping a server, scaling resources, and other tasks.
Monitor database health and performance. Collect and analyze logs and metrics from your instance to proactively detect potential problems.
| Recommendation | Benefits |
|---|---|
| Use an Azure Backup policy individually or together with the native automated backups to back up databases. | Azure Database for PostgreSQL provides automated backups and point-in-time restore for your database. You can configure the retention period for backups up to 35 days. Use Azure Backup to define how and when backups are created, the retention period for recovery points, and the rules for data protection and recovery. You can retain recovery points in an Azure Backup vault for up to 10 years. You can also manage the backup and restore operations by using Azure Business Continuity Center. |
| Schedule custom maintenance windows to apply service updates. | You can define a custom schedule for each Azure Database for PostgreSQL in your Azure subscription. With a custom schedule, you can specify your maintenance window for the server by choosing the day of the week and the start time of the one-hour maintenance window. |
| Monitor your server to ensure that it's healthy and performing as expected. | Azure Database for PostgreSQL has various metrics that provide insight into the behavior of the resources that support the Azure Database for PostgreSQL instance. You can also enable enhanced metrics to get fine-grained monitoring and alerting on databases. |
Performance Efficiency is about maintaining user experience even when there's an increase in load by managing capacity. The strategy includes scaling resources, identifying and optimizing potential bottlenecks, and optimizing for peak performance.
The Performance Efficiency design principles provide a high-level design strategy for achieving those capacity goals against the expected usage.
Optimize queries. Use native features to find opportunities to optimize queries.
Optimize indexes. Use features such as index tuning to automatically analyze query patterns and receive actionable recommendations for creating or dropping indexes to improve performance.
Offload read-only operations. If your application supports read-only connection strings, you can offload read-only operations to read replicas.
| Recommendation | Benefits |
|---|---|
| Use query store to track query performance over time. | Query store simplifies troubleshooting performance problems by helping you quickly find the longest running and most resource-intensive queries. |
| Use Query Performance Insight to identify the top resource consuming and long-running queries in your workload. | Query Performance Insight helps you identify long-running queries and their changes over time, determine the wait types that affect them, analyze top database queries by frequency, and other benefits. |
| Use index tuning with query store to analyze tracked queries and provide recommendations. | Index tuning analyzes the workload that query store tracks. It produces index recommendations to improve the performance of the analyzed workload or to drop duplicate or unused indexes. Index tuning helps you identify beneficial indexes, duplicate indexes, and unused indexes. |
| Use intelligent tuning to enhance performance automatically and help prevent problems. | Intelligent tuning continuously monitors the Azure Database for PostgreSQL flexible server database's status and dynamically adapts the database to your workload. |
Azure provides an extensive set of built-in policies related to Azure Database for PostgreSQL and its dependencies. Some of the preceding recommendations can be audited through Azure Policy. For example, you can check whether:
For comprehensive governance, review the Azure Policy built-in definitions for Azure Database for PostgreSQL and other policies that might affect the security of the data stores.
Azure Advisor is a personalized cloud consultant that helps you follow best practices to optimize your Azure deployments.
For more information, see Azure Advisor.