Let’s know something about PostgreSQL
PostgreSQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.
PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Windows, Linux, FreeBSD, and OpenBSD.
Features:
Here are the key features of PostgreSQL:
- Helps developers to build applications.
- It allows administrators to build fault-tolerant environment by protecting data integrity.
- Compatible with various platforms using all major languages and middleware.
- It offers a most sophisticated locking mechanism.
- Mature Server-Side Programming Functionality.
- Compliant with the ANSI SQL standard.
- Full support for client-server network architecture.
- Log-based and trigger-based replication SSL.
- Standby server and high availability.
- Object-oriented and ANSI-SQL2008 compatible.
- Support for JSON allows linking with other data stores like NoSQL which act as a federated hub for polyglot databases.
Multi-version concurrency control (MVCC)
- PostgreSQL manages concurrency through multi-version concurrency control (MVCC), which gives each transaction a “snapshot” of the database, allowing changes to be made without affecting other transactions. This largely eliminates the need for read locks, and ensures the database maintains ACID principles. PostgreSQL offers three levels of transaction isolation: Read Committed, Repeatable Read and Serializable. Because PostgreSQL is immune to dirty reads, requesting a Read Uncommitted transaction isolation level provides read committed instead. PostgreSQL supports full serializability via the serializable snapshot isolation (SSI) method.
About Azure Flexible Server:
Azure Database for PostgreSQL – Flexible Server is a fully managed database service designed to provide more granular control and flexibility over database management functions and configuration settings. In general, the service provides more flexibility and server configuration customizations based on the user requirements. The flexible server architecture allows users to collocate database engine with the client-tier for lower latency, choose high availability within a single availability zone and across multiple availability zones.
Flexible servers also provide better cost optimization controls with additional ability to stop/start your server and burstable compute tier that is ideal for workloads that do not need full compute capacity continuously.
Supported PostgreSQL Versions:
The service currently supports community version of PostgreSQL 11, 12, and 13.
High availability
The flexible server deployment model is designed to support high availability within single availability zone and across multiple availability zones. The architecture separates compute and storage. The database engine runs on a container inside a Linux virtual machine, while data files reside on Azure storage. The storage maintains three locally redundant synchronous copies of the database files ensuring data durability.
During planned or unplanned failover events, if the server goes down, the service maintains high availability of the servers using following automated procedure:
- A new compute Linux VM is provisioned.
- The storage with data files is mapped to the new Virtual Machine
- PostgreSQL database engine is brought online on the new Virtual Machine.
Picture below shows transition for VM and storage failure.
If zone redundant high availability is configured, the service provisions and maintains a warm standby server across availability zone within the same Azure region. The data changes on the source server is synchronously replicated to the standby server to ensure zero data loss. With zone redundant high availability, once the planned or unplanned failover event is triggered, the standby server comes online immediately and is available to process incoming transactions. This allows the service resiliency from availability zone failure within an Azure region that supports multiple availability zones as shown in the picture above.
Read More: How to Keep Your Cloud Database Instances Secure
Features:
Automatic backups
The flexible server service automatically creates server backups and stores them on zone redundant storage (ZRS) within the region. Backups can be used to restore your server to any point-in-time within the backup retention period. The default backup retention period is seven days. The retention can be optionally configured up to 35 days. All backups are encrypted using AES 256-bit encryption.
Performance and Scale
The flexible server service is available in three compute tiers: Burstable, General Purpose, and Memory Optimized.
The Burstable tier is best suited for low-cost development and low concurrency workloads that do not need full compute capacity continuously.
The General Purpose and Memory Optimized are better suited for production workloads requiring high concurrency, scale, and predictable performance.
Stop/Start server
One of the best solutions, we like personally and which we felt missing in Single server. The flexible server service allows you to stop and start server on-demand to lower your TCO. The compute tier billing is stopped immediately when the server is stopped. This can allow you to have significant cost savings during development, testing and for time-bound predictable production workloads. The server remains in the stopped state for seven days unless restarted sooner.
Enterprise grade security
The flexible server service uses the FIPS 140-2 validated cryptographic module for storage encryption of data at-rest. Data, including backups, and temporary files created while running queries are encrypted. The service uses the AES 256-bit cipher included in Azure storage encryption, and the keys can be system managed (default). The service encrypts data in-motion with transport layer security (SSL/TLS) enforced by default. The service enforces and supports TLS versions 1.2 only.
Flexible servers allow full private access to the servers using Azure virtual network (VNet integration). Servers in Azure virtual network can only be reached and connected through private IP addresses. With VNet integration, public access is denied, and servers cannot be reached using public endpoints.
Monitoring and alerting
The flexible server service is equipped with built-in performance monitoring and alerting features. All Azure metrics have a one-minute frequency, and each metric provides 30 days of history. You can configure alerts on the metrics. The service exposes host server metrics to monitor resources utilization and allows configuring slow query logs. Using these tools, you can quickly optimize your workloads, and configure your server for best performance.
Built-in PgBouncer
Another best feature, we do like and we were lacking in yearly version and this was one of the best for some enterprise use cases and automation. The flexible server comes with a built-in PgBouncer, a connection pooler. You can optionally enable it and connect your applications to your database server via PgBouncer using the same host name and the port 6432.
Difference between Azure PostgreSQL Single Server VS Flexible Server
Here will see what is major change between Single server vs Flexible Sever,
| Feature / Capability | Single Server | Flexible Server |
| Underlying O/S | Windows | Linux |
| AZ selection for application colocation | No | Yes |
| Built-in connection pooler | No | Yes (PgBouncer) |
| Connectivity | ||
| Username in connection string | <user_name>@server_name. For example, pgUserr@mypgServer | Just username. For example, pgUser |
| Connection port | 5432 | 5432 (DB), 6432 (PgBouncer) |
| Max. connections | 1982 | 5000 |
| Compute & Storage | ||
| Compute tiers | Basic, General Purpose, Memory Optimized | Burstable, General Purpose, Memory Optimized |
| Burstable SKUs | No | Yes |
| Ability to scale across compute tiers | Cannot scale Basic tier | Yes. Can scale across tiers |
| Stop/Start | No | Yes (for all compute SKUs). Only compute is stopped/started |
| Min storage size | 5 GB (Basic), 100 GB (GP, MO) | 32 GB |
| Storage auto-grow | Yes (1 GB increments) | No |
| Max IOPS | Basic – Variable. GP/MO: up to 20K | Up to 20K |
| Networking/Security | ||
| Supported networking | Virtual network, private link, public access | Private access (VNET injection in a delegated subnet), public access) |
| Private link support | Yes | No |
| Private DNS Zone support | No | Yes |
| Ability to move between private and public access | No | No |
| TLS support | TLS 1.2 | TLS 1.2, 1.3 enforced |
| Can turn off SSL | Yes | No |
| High Availability | ||
| Zone-redundant HA | No | Yes (a synchronous standby is established on another zone within a region) |
| HA Configuration | Built-in with storage pinned to a zone. Compute can float across regions. | Physically separate compute & storage provisioned across two zones |
| Cost | 1x | 2x (compute + storage) |
| Availability with non-HA configuration | Automatic restart, compute relocation | Automatic restart, compute relocation) |
| Protect from zone failure | Compute – Yes. Storage – No | Compute & storage – Yes |
| Protect from region failure | No | No |
| Application performance impact | No (not replicating) | Yes (Due to sync replication. Depends on the workload) |
| Automatic failover | Yes (spins another server) | Yes |
| Backup and Recovery | ||
| Ability to restore on a different zone | N/A | Yes |
| Ability to restore to a different VNET | No | Yes |
| Ability to restore a deleted server | Limited via API | Limited via support ticket |
| Metrics | ||
| Errors | Failed connections | Failed connections |
| Latency | Max lag across replicas, Replica lag | N/A |
| Security | ||
| Azure Active Directory Support (AAD) | Yes | No |
| Customer managed encryption key (BYOK) | Yes | No |
| Secure Sockets Layer support (SSL) | Yes | Yes |