September 2024 Feature Recap: Azure PostgreSQL Flexible Server

Hello Azure community.

This September we released some cool updates to Azure Database for PostgreSQL flexible server that you might want to check out. We’re excited to give you a sneak peek into it. DiskANN vector indexrecent PostgreSQL 17 Previewand our new fabric mirroring characteristic. These updates are designed to make your database smarter and more efficient. Let’s take a look at this month’s new features and see how these changes can help you.

Main features

DiskANN Vector Index – Preview

We’re excited to announce the preview of DiskANN, the leading vector indexing algorithm in Azure Database for PostgreSQL – Flexible Server! DiskANN, launched by Microsoft Research, allows developers to build highly accurate, performant, and scalable Generative AI applications, outperforming pgVector’s HNSW and IVFFlat in both latency and accuracy. DiskANN implements the following powerful technologies:

• Optimized Storage – Algorithms can be scaled beyond RAM limits without sacrificing search speed.
• vector quantization – Keep quantized vectors in memory. Implementing DiskANN in PostgreSQL balances the interaction between quantized and unquantized vectors to provide both low latency and high accuracy.
• Iterative post-filtering – Improves the accuracy of filtered vector search results without sacrificing speed or precision.
• New Vamana graph structure powerful for index updates – Vamana is more robust to changes than traditional graph indexes by maintaining accuracy despite many insertions, modifications, and deletions without costly index rebuilds.

Learn more about DiskANN. documentation, blog, demo, Participate in public preview

Postgres 17 – Preview

We’re excited to announce the preview release of PostgreSQL 17 on Azure Postgres flexible server, now available for initial testing. This version includes the following important upgrades:

• Memory Insights: The EXPLAIN command has been enhanced for better resource management.
• Vacuum improvements: Minimize transaction interference to ensure smooth operations.
• JSON improvements: Convert JSON data directly to a relational table using the JSON_TABLE function.
• Dynamic logical replication: Changing replication settings does not require a restart.

The Azure Postgres team has made significant contributions to this release, improving system efficiency. We invite you to test this preview and share your feedback as we prepare a major version upgrade soon.

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 fast integration: Seamlessly integrate and analyze your data with just a few clicks.
• Real-time data replication: Mirror changes incrementally in near real-time.
• Advanced query capabilities: Query data directly within OneLake using SQL and support complex queries and views.
• Integration with analytics tools: Access data through Fabric’s SQL analytics endpoint or third-party SQL tools.
• Enhanced data visualization and BI reporting: Leverage BI tools to create dynamic reports and dashboards directly in OneLake.

Check out our blog for more details. Azure Database for PostgreSQL flexible server mirroring on Microsoft Fabric – private preview.

Migration Service – Google Cloud SQL now supports custom FQDN.

Migration Service for Azure Database for PostgreSQL simplifies transferring databases to Azure and supports migration from cloud services, on-premises environments, and VMs. Newly added sources are as follows:

Support for burstable SKUs
Customers can now migrate directly to burstable SKUs without having to provision higher SKUs for migration and then scale down later. This feature reduces the overhead of migrating single server users on the basic SKU to flexible servers.

Custom FQDN/IP
Additionally, you can now use: Custom FQDN or IP addresses for source and destination connectionsSimplifies migration for those using custom DNS settings. These updates increase flexibility and greatly simplify the migration process.

Automatic Migration – From Single Server to Flexible Server

The automatic migration feature provides a highly resilient, self-healing offline migration environment with minimal downtime during the planned migration period. Automatic migration eliminates the overhead of manually migrating servers. After migration, you can take advantage of flexible servers, including better pricing and performance, granular control over database configuration, custom maintenance windows, and more.

We have a nomination process in place for users who would like to voluntarily expedite their migration to flexible servers. If you own a single server workload, you can now nominate yourself for automatic migration (if you haven’t already scheduled it in the service). Submit your server details through this. form.

Python SDK updates

Now, with the Python SDK, you can easily automate tasks like creating and configuring servers directly from within your Python application. Supports identity and access management in Azure for secure operations. The old azure-mgmt-rdbms library is deprecated and replaced by the new azure-mgmt-postgresqlflexibleservers library. To switch to a new library, simply update the import statements in your application.

Please see the updated content for detailed instructions and additional examples. Quick Start Guide.

Automated tasks – generally available

Task automation is now generally available in Azure Database for PostgreSQL. Flexible Server enhances management by automating routine processes such as starting and stopping servers under a consumption-based billing model. Unlike Azure Automation, this solution is lightweight and leverages Azure Logic Apps specifically for resource-related operations. Key features include:

• Schedule server start/stop.
• Automate routine tasks like transferring resource costs or scaling servers.
• Customize the frequency and timing of your tasks.
• Monitor job history, including statuses such as Canceled, Failed, and Successful.
• Edit tasks directly or use the workflow designer for custom management.

Please visit the documentation for detailed instructions. Azure Database for PostgreSQL flexible server management using automated tasks.

learning bytesHow to easily mirror server properties in Azure PostgreSQL

In this section, we’ll look at how to export or copy all the properties of an existing server to set up the target server with the same properties as the old server. Let’s say you have two Azure PostgreSQL flexible server instances and you want to replicate all properties from “server1” to “server2”. You can achieve this using parameter filtering with ‘jq’ in the Azure CLI. Once all parameters are filtered, you can set that property to server2 and then restart the server to reflect those changes.

To copy parameters to a new server, you can use this command that filters the parameters, sets them to the new server, and then restarts the server.

parameters=$(az postgres flexible-server parameter list --resource-group  --server-name  | jq -r '.[] | select(.isReadOnly == false and .source == "user-override") | [.name, .value] | @sh')
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  --server-name  --name "$name" --value "$value"
done <<< "$parameters"
count=$(az postgres flexible-server parameter list --resource-group  --server-name  | jq '[.[] | select(.isConfigPendingRestart == true)] | length')
if [[ $count -ge 1 ]]; then
    az postgres flexible-server restart --resource-group  --name 
fi

There are certain server properties that cannot be mirrored or copied. Follow the following documentation to learn more about setting up resources and servers through the Azure CLI. Quick start instructions

conclusion

This concludes the September 2024 feature summary. We hope you’re as excited as we are about this update. Try it out for yourself and see how it can improve your database management and performance. Your feedback is invaluable to us, so please share your thoughts and experiences. Stay tuned for further updates. We’re always working to make Azure Database for PostgreSQL a better flexible server. Thank you for being part of our community!