September 2024 Recap: Azure Database for PostgreSQL Flexible Server

Hello Azure Community,

This September, we've rolled out some cool updates to Azure Database for PostgreSQL Flexible Server that you'll want to check out. We’re excited to bring you a sneak peek at the DiskANN Vector Index, the latest PostgreSQL 17 preview, and our new Fabric Mirroring feature. These updates are all about making your database smarter and more efficient. Let's dive into what's new this month and see how these changes can help you.

 

Feature Highlights

 

• DiskANN Vector Index - Preview
• Postgres 17 - Preview
• Fabric Mirroring - Private Preview
• Migration Service – Now supports Amazon Aurora, Google Cloud SQL, Burstable SKU, Custom FQDN
• Auto Migrations – Single to Flexible server
• Python SDK Update
• Automation Tasks – Generally Available

 

DiskANN Vector Index - Preview

 

 

We’re thrilled to announce the preview of DiskANN, a leading vector indexing algorithm, on Azure Database for PostgreSQL - Flexible Server! Started by Microsoft Research, DiskANN enables developers to build highly accurate, performant, and scalable Generative AI applications, surpassing pgvector’s HNSW and IVFFlat in both latency and accuracy. DiskANN implements the following powerful techniques:

 

• Optimized storage – allows algorithm to scale beyond limits of RAM without sacrificing search speed.
• Vector quantization – keeps quantized vectors in memory. Our implementation of DiskANN on PostgreSQL balances the interactions between quantized and unquantized vectors, delivering both low latency and high accuracy.
• Iterative post filtering - enhances the accuracy of filtered vector search results without compromising on speed or precision.
• New Vamana graph structure robust to index updates – Vamana is more robust to changes than existing graph indices by maintaining accuracy despite many insertions, modifications, and deletions, without the need for expensive index rebuilds.

Explore more about DiskANN: Documentation, Blog, Demo, Join Public Preview

 

Postgres 17 – Preview

 

 

We're thrilled to announce the preview release of PostgreSQL 17 on Azure Postgres Flexible Server, which is now available for early testing. This version features significant upgrades like:

 

• Memory Insights: Enhanced EXPLAIN command for better resource management.
• Vacuum Enhancements: Minimized transaction interference for smoother operations.
• JSON Enhancements: Direct JSON data conversion into relational tables with JSON_TABLE function.
• Dynamic Logical Replication: No restarts needed for replication setup changes.

The Azure Postgres team contributed significantly to this release, enhancing system efficiency. We're preparing for a Major Version Upgrade soon and invite you to test this preview and share your feedback. For more details, check out blog PostgreSQL 17 Preview on Azure Postgres Flexible Server.

Fabric Mirroring - Private Preview

Fabric Mirroring enhances Azure Database for PostgreSQL Flexible Server by seamlessly integrating operational and analytical data, eliminating data silos and enabling powerful analytics with Microsoft Fabric. This feature supports real-time data replication into Fabric OneLake in Parquet format, allowing direct querying and data synchronization without traditional ETL processes. Key benefits include:

 

• Easy and Quick Integration: Seamlessly integrate and analyze data with a few clicks.
• Real-time Data Replication: Incrementally mirror changes almost in real-time.
• Advanced Query Capabilities: Directly query data within OneLake using SQL, supporting complex queries and views.
• Integration with Analytical Tools: Access data through Fabric’s SQL analytics endpoints or third-party SQL tools.
• Enhanced Data Visualization and BI Reporting: Utilize BI tools to create dynamic reports and dashboards directly from OneLake.

 

For further reading, check out blog Mirroring Azure Database for PostgreSQL Flexible Server in Microsoft Fabric - Private Preview.

 

Migration Service – Now supports Amazon Aurora, Google Cloud SQL, Burstable SKU, Custom FQDN

 

 

The Migration service for Azure Database for PostgreSQL streamlines transferring your databases to Azure, supporting migrations from cloud services, on-prem environments, and VMs. Newly added sources include:

• Amazon Aurora PostgreSQL: Now supports offline and online migrations.
• Google Cloud SQL for PostgreSQL: Supports offline and online migrations.

Burstable SKU support
Customers can now migrate directly into a Burstable SKU without having to provision a higher SKU for migration and then scale down later. This feature reduces overhead for Single server users of Basic SKU to migrate to Flexible server.

Custom FQDN/IP
Additionally, you can now use custom FQDNs or IP addresses for source and target connections, simplifying migrations for those using custom DNS settings.  These updates enhance flexibility and ease the migration process significantly.

 

Auto Migrations – Single to Flexible server

 

The auto migration feature provides a highly resilient and self-healing offline migration experience during a planned migration window, with minimal downtime. Auto migration removes the overhead to manually migrate your server. Post migration, you can take advantage of the benefits of Flexible Server, including better price & performance, granular control over database configuration, and custom maintenance windows.

There is a nomination process in place for users who want to voluntarily fast-track their migration to Flexible server. If you own a Single Server workload, you can now nominate yourself (if not already scheduled by the service) for auto migration. Submit your server details through this form.

 

Python SDK Update

 

 

The Python SDK now allows easy automation of tasks such as server creation and configuration directly within Python applications. It supports Azure’s identity and access management for secure operations. The older azure-mgmt-rdbms library is deprecated, replaced by the new azure-mgmt-postgresqlflexibleservers library. Simply update the import statement in your application to switch to the new library.

For detailed instructions and more examples, refer to the updated Quick-start-guide.

 

Automation Tasks – Generally Available

 

 

 

Task Automation now generally available for Azure Database for PostgreSQL – Flexible Server boosts management by automating routine processes like starting and stopping servers on a consumption-based billing model. Unlike Azure Automation, this solution is lightweight and leverages Azure Logic Apps specifically for resource-related tasks. Key Features include:

• Schedule server START / STOP.
• Automate routine tasks like sending resource costs or server scaling.
• Customize task frequency and timing.
• Monitor task history including statuses like Canceled, Failed, and Succeeded.
• Edit tasks directly or use the workflow designer for tailored management.

 

For more detailed guidance, visit doc Manage Azure Database for PostgreSQL Flexible Server using automation tasks.

Azure Postgres Learning Bytes 🎓

How to Mirror Server Properties in Azure PostgreSQL with Ease

In this section we will explore how to mirror the parameters of an existing server to a target server. Let's say you have two instances of Azure PostgreSQL Flexible Server, server1 and server2. If you want to replicate the parameters from server1 to server2, this can be achieved with combination of queries using 'jq' and azure cli commands. This command uses 'jq' to filter out parameters from server1 and then sets those  to server2. Lastly you will need to restart server2 to reflect the changes. 

To copy the parameters to the server, use this command below:

 

parameters=$(az postgres flexible-server parameter list --resource-group <server1-resource-group> --server-name <server1-name> | jq -r '.[] | select(.isReadOnly == false and .source == "user-override") | [.name, .value] | ')
while IFS= read -r parameter; do
    name=$(echo "$parameter" | awk -F "'" '{print $2}')
    value=$(echo "$parameter" | awk -F "'" '{print $4}')
    az postgres flexible-server parameter set --resource-group <server2-resource-group> --server-name <database2-name> --name "$name" --value "$value"
done <<< "$parameters"
count=$(az postgres flexible-server parameter list --resource-group <server2-resource-group> --server-name <server2-name> | jq '[.[] | select(.isConfigPendingRestart == true)] | length')
if [[ $count -ge 1 ]]; then
    az postgres flexible-server restart --resource-group <server2-resource-group> --name <server2-name>
fi

 

 

There are certain server properties which cannot be mirrored or copied. To explore more about setting up resources and server through Azure CLI you can follow the documentation on - Quickstart Guideline

 

Conclusion

 

That wraps up our September 2024 feature recap! We hope you're as excited as we are about these updates. Give them a try, and see how they can improve your database management and performance. Your feedback is incredibly valuable to us, so please share your thoughts and experiences. Finally, Stay tuned for more exciting announcements and updates next month as we gear up for Microsoft IGNITE 2024!