Azure PostgreSQL Flexible Deep Dive

Key Concepts

Postgress SQL, Open-source database, ACID properties, Azure Database for Postgress SQL Flexible Server, Single Server (retired), Cosmos DB for Postgress SQL, Situs extension, Sharding, Primary instance, Virtual machine isolation, Server parameters, Burstable SKUs, Confidential compute, Premium SSD, Write Ahead Log (WAL), Availability Zones, Authentication (Postgress SQL native, Microsoft Entra ID), Networking (Public IP, V-Net integration), Encryption (Service-managed key, Customer-managed key), High Availability (HA) replica, Synchronous replication, Physical streaming replication, PG Bouncer, Read replicas, Asynchronous replication, Cascading/Chained replication, Virtual endpoints (Writer, Reader), Maintenance window (System-managed, Custom-managed), Major version upgrades, Minor version upgrades, Automatic backups, Point-in-time restore, Azure Backup, PG dump, Pricing (VM skew, Storage, IOPS, Throughput, Backup, Cross-region traffic), Elastic cluster, Vector database capabilities.

Postgress SQL Overview

• What is Postgress SQL? It's an open-source relational database management system (RDBMS) with a strong community-driven ecosystem.
• Why use Postgress SQL?
  • Open-source: No licensing costs.
  • Developer-friendly: Taught in schools, easy to use.
  • Rich ecosystem: Integrates well with open-source stacks.
  • Migration target: Commonly used for migrating from Oracle databases due to PLPGSQL similarity.
• ACID Compliance: Ensures atomicity, consistency, isolation, and durability of transactions. Uses a Write Ahead Log (WAL) for transaction logging.
• Feature Set: Includes tables, views, functions, and stored procedures, similar to Microsoft SQL Server.
• Comparison to Microsoft SQL Server: Postgress SQL is portrayed as a dependable "Accord" compared to the feature-rich "Ferrari" that is Microsoft SQL Server.

Azure Database for Postgress SQL Flexible Server

• Managed Offering: Shifts management responsibility to Microsoft.
• Flexible Server: The current Azure managed database offering for Postgress SQL, replacing the retired Single Server offering.
• Cosmos DB for Postgress SQL: A branding exercise that adds the Situs extension for sharding.
• Situs Extension: Enables horizontal sharding of the database across multiple instances for increased capacity and performance. This extension is being integrated into Postgress SQL Flexible Server.

Core Building Blocks

• Primary Instance: A single instance for both reads and writes, housed in its own virtual machine for isolation.
• Virtual Machine: Each instance runs in its own VM, providing isolation from other tenants.
• Server Parameters: Offers extensive control over database settings (over 500 parameters).
• Virtual Machine Skew:
  • Burstable (B-series): Cost-effective for dev environments, provides a base CPU allocation with the ability to burst into accrued CPU credits. Can be stopped and started to reduce compute costs.
  • Confidential Compute: Uses Intel TDX and AMD SEV-SNP for VM-level encryption (available in select regions).
  • General Purpose/Memory Optimized: Other options for different workload needs.
• Storage:
  • Premium SSD: Used for data storage.
  • Premium SSD v2: Allows independent scaling of capacity, IOPS, and throughput.
  • Data Disk: Stores database objects (tables, views) and the Write Ahead Log (WAL).
  • Auto-grow: Option to automatically increase disk size as needed.
• Availability Zone: Option to select a specific availability zone for the instance.
• Postgress SQL Version: Specifies the major and minor versions of Postgress SQL.

Authentication

• Options:
  • Postgress SQL native authentication only.
  • Microsoft Entra ID authentication only.
  • Both.
• Entra ID Integration: Allows using managed identities for applications to authenticate to the database without storing secrets.

Networking

• Options:
  • Public IP: Allows access via public IP addresses with firewall rules and optional private endpoints.
  • V-Net Integration: Deploys the instance into a virtual network, requiring a dedicated subnet (at least /28). No public IP address.

Encryption

• Options:
  • Service-managed key: Encryption managed by Microsoft.
  • Customer-managed key: Encryption using a key stored in Azure Key Vault.
• Versionless Key: Allows automatic key rotation in Azure Key Vault without manual intervention in Postgress SQL.

High Availability (HA)

• HA Replica: A synchronous replica for automatic failover within the same region.
• Configuration:
  • Same VM skew and data disk size as the primary.
  • Synchronous replication for zero data loss.
  • Can be in the same or different availability zone (different zone is recommended).
  • Burstable SKUs are not supported with HA.
• Failover: Automatic failover to the HA replica in case of primary instance failure.
• Primary DNS Name: Applications connect to a primary DNS name that automatically points to the active primary instance.
• Physical Streaming Replication: Uses Postgress SQL's built-in physical streaming replication for byte-by-byte copying of the Write Ahead Log (WAL) from the primary to the standby.
• PG Bouncer: Optional connection pooler that reduces the number of idle connections and optimizes resource usage. Runs on port 6432.

Read Replicas

• Purpose: Read-only replicas for offloading read workloads and for disaster recovery (DR).
• Replication: Asynchronous replication from the primary instance.
• Configuration:
  • Same VM skew as the primary.
  • Can be in the same or different regions.
• DR Failover: Manual failover to a read replica in case of a disaster.
• Number of Replicas: Up to five read replicas per primary instance.
• Cascading/Chained Replication: Read replicas can have their own read replicas (up to one level deep), allowing for a total of 30 read replicas.

Virtual Endpoints

• Writer: A virtual endpoint that always points to the current primary instance.
• Reader: A virtual endpoint that can be manually configured to point to one of the read replicas.

Maintenance

• Minor Version Upgrades: Automatic upgrades to the latest minor version, occurring four times a year as part of a monthly maintenance event.
• Major Version Upgrades: Manual upgrades that require downtime.
• Maintenance Window: A 1-hour window for maintenance events, configurable as system-managed or custom-managed.
• Notification: Five days' notice is provided for scheduled maintenance events (excluding critical updates).
• System Managed vs. Custom Managed: System managed windows always occur before custom managed windows.

Backups

• Automatic Backups:
  • Enabled by default, with a retention period of 7 days (configurable up to 35 days).
  • Uses managed disk snapshots (daily full snapshot, followed by incremental snapshots).
  • Five-minute Recovery Point Objective (RPO) due to frequent Write Ahead Log (WAL) capture.
  • Point-in-time restore to a new server.
• Azure Backup:
  • Optional service for long-term retention (up to 10 years).
  • Uses PG dump for logical backups, enabling cross-version restores and database-level operations.

Pricing

• Factors:
  • VM skew (compute costs).
  • Storage (disk size).
  • IOPS and throughput (for Premium SSD).
  • Backup storage.
  • HA replicas and read replicas (additional instances).
  • Cross-region traffic.
• Stopping and Starting: Compute costs can be reduced by stopping the VM, but storage costs remain.

Situs Extension and Elastic Cluster

• Situs Extension: Enables horizontal sharding of the database across multiple instances for increased capacity and performance.
• Elastic Cluster: A deployment mode that uses the Situs extension, allowing for scaling out the database by adding more instances.

Conclusion

Azure Database for Postgress SQL Flexible Server offers a flexible and scalable managed database solution with extensive control over configuration, performance, and availability. It provides options for HA, DR, backups, and security, making it suitable for a wide range of workloads. The integration of the Situs extension enables horizontal scaling for even greater capacity and performance. The speaker highlights the importance of understanding the various configuration options and their implications for cost, performance, and availability. The future of Postgress SQL includes exciting AI capabilities, particularly in the realm of vector databases.