PostgreSQL is an open-source object-relational database system known for robustness, advanced features, and strong standards compliance. On Azure, Azure Database for PostgreSQL provides a managed option for complex, high-scale applications, combining PostgreSQL capabilities with automatic performance tuning. section 10.3.3
Azure Database for PostgreSQL brings several practical trade-offs:
- Performance: Its advanced features suit complex, high-scale workloads, although complex queries may need manual tuning and can be slower than on some database systems. section 10.3.3
- Cost: Pay-as-you-go pricing and selectable compute and storage resources help control spending, while higher tiers, backups, storage, and network traffic add cost. section 10.3.3
- Recovery and security: Automated backups, point-in-time recovery, firewall rules, SSL connections, and advanced threat protection are available, but active geo-replication is not. section 10.3.3
Creating a managed PostgreSQL database
For the Banning Books ordering system, create the Azure PostgreSQL server first, then create its database: az postgres server create --name BanningBooksPostgreSQLServer --resource-group BanningRG --location australiaeast --admin-user adminuser --admin-password <your_password> --sku-name B_Gen5_2 --version 11 az postgres db create --resource-group BanningRG --server-name BanningBooksPostgreSQLServer --name BanningBooksDatabase section 10.3.3
Table 10.5 Pros and cons of using Azure Database for PostgreSQL for the Banning Books requirements
| Requirement |
Pros |
Cons |
| Performance |
PostgreSQL is known for its robustness and advanced features, making it suitable for complex, high-scale applications. Azure Database for PostgreSQL also offers automatic performance tuning. |
While PostgreSQL is generally performant, it may require manual tuning for optimal performance. In addition, complex queries can be slower in PostgreSQL compared to some other database systems. |
| Costs |
PAYG model allows for cost control. The cost can be optimized by choosing the right combination of compute and storage resources. |
Costs can increase with higher performance tiers. In addition, while the base cost may be lower, additional costs may be incurred for storage, backup storage, and network traffic. |
| Disaster recovery |
Supports automated backups and point-in-time recovery, which aligns with the need for a robust disaster recovery plan. In addition, Azure Database for PostgreSQL supports geo-redundant backups. |
While it supports geo-redundant backups, active geo-replication isn’t supported, which could be a limitation for a robust disaster recovery strategy. |
| Security |
Supports firewall rules and SSL connections to ensure data privacy and compliance with regulations. In addition, Azure Database for PostgreSQL provides advanced threat protection. |
Requires proper configuration to ensure security. While it provides advanced threat protection, it may not have all the security features provided by Azure SQL DB. |
Compare PostgreSQL’s performance, cost, disaster recovery, and security advantages with its operational limitations for the Banning Books requirements. section 10.3.3
