V
varun-dhawan
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.
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:
For further reading, check out blog Mirroring Azure Database for PostgreSQL Flexible Server in Microsoft Fabric - Private Preview.
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:
For more detailed guidance, visit doc Manage Azure Database for PostgreSQL Flexible Server using automation tasks.
Learning Bytes
In this section we will explore how to export or copy all the properties of an existing server to set up a target server with same properties as the previous server. Suppose you have two instances of Azure PostgreSQL Flexible Server and want to replicate all the properties from "server1" to "server2". You can achieve this by using parameter filtering with ‘jq’ on Azure CLI. Once all the parameters are filtered you can set those properties to server2 and then restart the server to reflect those changes.
To copy the parameters to the new server we can use this command that filters out the parameters and sets those to the new server and then restarts the server:
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
Continue reading...
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 Google Cloud SQL, 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:
Explore more about DiskANN: Documentation, Blog, Demo, Join Public Preview
- 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:
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.
- 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.
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 Google Cloud SQL, 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:
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.
- 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.
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.
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.
Learning Bytes
How to Mirror Server Properties in Azure PostgreSQL with Ease
In this section we will explore how to export or copy all the properties of an existing server to set up a target server with same properties as the previous server. Suppose you have two instances of Azure PostgreSQL Flexible Server and want to replicate all the properties from "server1" to "server2". You can achieve this by using parameter filtering with ‘jq’ on Azure CLI. Once all the parameters are filtered you can set those properties to server2 and then restart the server to reflect those changes.
To copy the parameters to the new server we can use this command that filters out the parameters and sets those to the new server and then restarts the server:
Code:
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] | @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 <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>
fiThere 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. Stay tuned for more updates—we're always working to make Azure Database for PostgreSQL Flexible Server even better for you. Thanks for being part of our community!
Continue reading...