Azure Database for MySQL triggers for Azure Functions (Public Preview)
Developers can now accelerate development time and focus only on the core business logic of their applications, for developing event-driven applications with Azure Database for MySQL as the backend data store. We are excited to announce that you can now invoke an Azure Function based on changes to an Azure Database for MySQL table. This new capability is made possible through the Azure Database for MySQL triggers for Azure Functions, now available in public preview. Azure Database for MySQL triggers The Azure Database for MySQL trigger uses change tracking functionality to monitor a MySQL table for changes and trigger a function when a row is created or updated enabling customers to build highly-scalable event-driven applications. Similar to the Azure Database for MySQL Input and Output bindings for Azure Functions, a connection string for the MySQL database is stored in the application settings of the Azure Function to trigger the function when a change is detected on the tables. Note: In public preview, Azure Database for MySQL triggers for Azure Functions are available only for dedicated and premium plan of Azure Functions To enable change tracking on an existing Azure Database for MySQL table to use trigger bindings for an Azure Function, it is necessary to alter the table structure, for example, enabling change tracking on an employees data table: ALTER TABLE employees ADD COLUMN az_func_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; Azure Database for MySQL trigger uses the 'az_func_updated_at' and column's data to monitor the table for any changes on which change tracking is enabled. Changes are then processed in the order that they were made, with the oldest changes being processed first. Important: If changes to multiple rows are made at once, then the exact order they're sent to the function is determined on the ascending order of the az_func_updated_at and the primary key columns. If multiple changes are made to a row in-between an iteration, then only the latest changes for that particular rows are considered. The following example demonstrates a C# function that is triggered when changes occur in the employees table. The MySQL trigger uses attributes for the table name and the connection string. using System.Collections.Generic; using Microsoft.Azure.WebJobs; using Microsoft.Azure.WebJobs.Extensions.MySql; using Microsoft.Extensions.Logging; namespace EmployeeSample.Function { public static class EmployeesTrigger { [FunctionName(nameof(EmployeesTrigger))] public static void Run( [MySqlTrigger("Employees", "MySqlConnectionString")] IReadOnlyList<MySqlChange<Employee>> changes, ILogger logger) { foreach (MySqlChange<Employee> change in changes) { Employee employee= change. Item; logger.LogInformation($"Change operation: {change.Operation}"); logger.LogInformation($"EmployeeId: {employee.employeeId}, FirstName: {employee.FirstName}, LastName: {employee.LastName}, Company: {employee. Company}, Department: {employee. Department}, Role: {employee. Role}"); } } } } Join the preview and share your feedback! We are eager for you to try out the new Azure Database for MySQL triggers for Azure Functions and build highly scalable event-driven and serverless applications. For more information refer https://aka.ms/mysqltriggers about using MySQL triggers for all the supported programming frameworks with detailed step-by-step instructions If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!Ignite 2025: Advancing Azure Database for MySQL with Powerful New Capabilities
At Ignite 2025, we’re introducing a wave of powerful new capabilities for Azure Database for MySQL, designed to help organizations modernize, scale, and innovate faster than ever before. From enhanced high availability and seamless serverless integrations to AI-powered insights and greater flexibility for developers, these advancements reflect our commitment to delivering a resilient, intelligent data platform. Join us as we unveil what’s next for MySQL on Azure - and discover how industry leaders are already building the future with confidence. Enhanced Failover Performance with Dedicated SLB for High-Availability Servers We’re excited to announce the General Availability of Dedicated Standard Load Balancer (SLB) for HA-enabled servers in Azure Database for MySQL. This enhancement introduces a dedicated SLB to High Availability configurations for servers created with public access or private link. By managing the MySQL data traffic path, SLB eliminates the need for DNS updates during failover, significantly reducing failover time. Previously, failover relied on DNS changes, which caused delays due to DNS TTL (30 seconds) and client-side DNS caching. What’s new with GA: The FQDN consistently resolves to the SLB IP address before and after failover. Load-balancing rules automatically route traffic to the active node. Removes DNS cache dependency, delivering faster failovers. Note: This feature is not supported for servers using private access with VNet integration. Learn more Build serverless, event-driven apps at scale – now GA with Trigger Bindings for Azure Functions We’re excited to announce the General Availability of Azure Database for MySQL Trigger bindings for Azure Functions, completing the full suite of Input, Output, and Trigger capabilities. This feature lets you build real-time, event-driven applications by automatically invoking Azure Functions when MySQL table rows are created or updated - eliminating custom polling and boilerplate code. With native support across multiple languages, developers can now deliver responsive, serverless solutions that scale effortlessly and accelerate innovation. Learn more Enable AI agents to query Azure Database for MySQL using Azure MCP Server We’re excited to announce that Azure MCP Server now supports Azure Database for MySQL, enabling AI agents to query and manage MySQL data using natural language through the open Model Context Protocol (MCP). Instead of writing SQL, you can simply ask questions like “Show the number of new users signed up in the last week in appdb.users grouped by day.”, all secured with Microsoft Entra authentication for enterprise-grade security. This integration delivers a unified, secure interface for building intelligent, context-aware workflows across Azure services - accelerating insights and automation. Learn more Greater networking flexibility with Custom Port Support Custom port support for Azure Database for MySQL is now generally available, giving organizations the flexibility to configure a custom port (between 25001 and 26000) during new server creation. This enhancement streamlines integration with legacy applications, supports strict network security policies, and helps avoid port conflicts in complex environments. Supported across all network configurations - including public access, private access, and Private Link - custom port provisioning ensures every new MySQL server can be tailored to your needs. The managed experience remains seamless, with all administrative capabilities and integrations working as before. Learn more Streamline migrations and compatibility with Lower Case Table Names support Azure Database for MySQL now supports configuring lower_case_table_names server parameter during initial server creation for MySQL 8.0 and above, ensuring seamless alignment with your organization’s naming conventions. This setting is automatically inherited for restores and replicas, and cannot be modified. Key Benefits: Simplifies migrations by aligning naming conventions and reducing complexity. Enhances compatibility with legacy systems that depend on case-insensitive table names. Minimizes support dependency, enabling faster and smoother onboarding. Learn more Unlock New Capabilities with Private Preview Features at Ignite 2025 We’re excited to announce that you can now explore two powerful capabilities in early access - Reader Endpoint for seamless read scaling and Server Rename for greater flexibility in server management. Scale reads effortlessly with Reader Endpoint (Private Preview) We’re excited to announce that the Reader Endpoint feature for Azure Database for MySQL is now ready for private preview. Reader Endpoint provides a dedicated read-only endpoint for read replicas, enabling automatic connection-based load balancing of read-only traffic across multiple replicas. This simplifies application architecture by offering a single endpoint for read operations, improving scalability and fault tolerance. Azure Database for MySQL supports up to 10 read replicas per primary server. By routing read-only traffic through the reader endpoint, application teams can efficiently manage connections and optimize performance without handling individual replica endpoints. Reader endpoints continuously monitor the health of replicas and automatically exclude any replica that exceeds the configured replication lag threshold or becomes unavailable. To enroll in the preview, please submit your details using this form. Limitations During Private Preview: Only performance-based routing is supported in this preview. Certain settings such as routing method and the option to attach new replicas to the reader endpoint can only be configured at creation time. Only one reader endpoint can be created per replica group. Including the primary server as a fallback for read traffic when no replicas are available is not supported in this preview. Get flexibility in server management with Server Rename (Private Preview) We’re excited to announce the Private Preview of Server Rename for Azure Database for MySQL. This feature lets you update the name of an existing MySQL server without recreating it, migrating data, or disrupting applications - making it easier to adopt clear, consistent naming. It provides a near zero-downtime path to a new hostname of the server. To enroll in the preview, please submit your details using this form. Limitations During Private Preview: Primary server with read replicas: Renaming a primary server that has read replicas keeps replication healthy. However, the SHOW SLAVE STATUS output on the replicas will still display the old primary server's name. This is a display inconsistency only and does not affect replication. Renaming is currently unsupported for servers using Customer Managed Key (CMK) encryption or Microsoft Entra Authentication (Entra Id). Real-World Success: Azure Database for MySQL Powers Resilient Applications at Scale Factorial Factorial, a leading HR software provider, uses Azure Database for MySQL alongside Azure Kubernetes Service to deliver secure, scalable HR solutions for thousands of businesses worldwide. By leveraging Azure Database for MySQL’s reliability and seamless integration with cloud-native technologies, Factorial ensures high availability and rapid innovation for its customers. Learn more YES (Youth Employment Service) South Africa’s largest youth employment initiative, YES, operates at national scale by leveraging Azure Database for MySQL to deliver a resilient, centralized platform for real-time job matching, learning management, and career services - connecting thousands of young people and employers, and helping nearly 45 percent of participants secure permanent roles within six months. Learn more Nasdaq At Ignite 2025, Nasdaq will showcase how it uses Azure Database for MySQL - alongside Azure Database for PostgreSQL and other Azure products - to power a secure, resilient architecture that safeguards confidential data while unlocking new agentic AI capabilities. Learn more These examples demonstrate that Azure Database for MySQL is trusted by industry leaders to build resilient, scalable applications - empowering organizations to innovate and grow with confidence. We Value Your Feedback Azure Database for MySQL is built for scale, resilience, and performance - ready to support your most demanding workloads. With every update, we’re focused on simplifying development, migration, and management so you can build with confidence. Explore the latest features and enhancements to see how Azure Database for MySQL meets your data needs today and in the future. We welcome your feedback and invite you to share your experiences or suggestions at AskAzureDBforMySQL@service.microsoft.com Stay up to date by visiting What's new in Azure Database for MySQL, and follow us on YouTube | LinkedIn | X for ongoing updates. Thank you for choosing Azure Database for MySQL!Preventing and recovering from accidental deletion of an Azure Database for MySQL flexible server
Accidental deletion of critical Azure resources, such as Azure Database for MySQL flexible servers, can disrupt operations. To help avoid such accidental deletions, you can use a couple of options, including Azure Resource Locks and Azure Policy. This post explains how to implement these mechanisms, and how to revive a dropped MySQL flexible server by using the Azure CLI.Custom Port Support in Azure Database for MySQL – Flexible Server is Now Generally Available
We are excited to announce that custom port support for Azure Database for MySQL – Flexible Server is now generally available (GA). This long-requested feature gives you greater flexibility to align MySQL server deployments with your network and security requirements. By default, MySQL uses TCP port 3306; with this GA release, you can configure a custom port (between 25001 and 26000) when creating a new Azure Database for MySQL flexible server. This enables easier integration with legacy applications, helps comply with strict network security policies, and avoids port conflicts in complex environments. What’s new in GA (vs. Public Preview): In the Public Preview (July 2025), custom ports were only supported for VNet-injected (private access) servers, with no support for public access or Private Link connectivity. Now, with GA, you can create custom-port servers in any network configuration – including both publicly accessible servers and those using Private Link (private endpoint) connectivity. In short, all new MySQL flexible servers can be created with a custom port, whether they are configured for public network access or deployed into a private virtual network. Feature Highlights Custom Port Range: Choose a port between 25001 and 26000 during server provisioning. (Only one custom port is supported per server.) This is in addition to the default MySQL port 3306, which remains available for use if needed. Supported Scenarios: Custom ports are fully supported for new server creation, point-in-time restore (including cross-port restores), read replica setup, and High Availability (HA) deployments. You can perform a restore or set up a replica even if the source and target servers use different ports, and you can enable HA on a server configured with a non-default port. Networking Flexibility: Supported on both public access and private access configurations. You can create servers with a custom port in public access mode (accessible via the internet with firewall rules) or in private access mode (injected into a VNet). Azure Private Link is also supported – meaning you can connect via a private endpoint to a MySQL server running on a custom port. This enhancement broadens the feature’s applicability beyond the preview’s limited scope, allowing usage in all network scenarios. Managed Experience: The custom port feature is built into the managed service experience. Aside from specifying a different port number for client connections, there is no change in how you manage or operate the MySQL flexible server – all administrative capabilities and integrations (backup, monitoring, etc.) work as they do with the default port. Current Limitations Be aware of a couple of limitations at GA: Port Immutable After Creation: You cannot change the server’s port after the server is created. If you need to use a different port, you will have to create a new server with that port. As a workaround, you can use Point-in-Time Restore (PITR) to quickly clone your database into a new server with the desired port (since cross-port restores are supported), rather than performing a full manual migration. Geo-Replication/Geo-Restore: Cross-region operations like geo-restore and geo-replication are not yet supported for servers using a custom port. In other words, you cannot perform a geo-restore of a backup from a custom-port server, and you cannot create cross-region read replicas on custom port servers at this time. These capabilities are on the roadmap but remain unsupported in the current release. Why Custom Ports? Many enterprise developers and DBAs have asked for custom port support to accommodate specialized network scenarios. For example, some organizations enforce strict firewall rules or use non-standard ports for databases to meet internal security compliance requirements. Others may have legacy applications or multi-database setups that require MySQL to run on a port other than 3306 to avoid conflicts. The custom port feature addresses these needs by allowing you to select a non-default port during server creation, while Azure continues to handle all the usual PaaS management tasks. In short, you get the flexibility of a custom network configuration without losing the benefits of a fully managed database service. Getting Started Using a custom port is straightforward. At GA, the Azure portal’s create experience is the way to set a custom port (support in CLI/PowerShell/ARM will come later). In the portal, when you create a new Azure Database for MySQL – Flexible Server, you’ll find an option to specify the “Database port.” Provide any value between 25001 and 26000 as the port number for your server. Once the server is deployed, client applications should connect using the <servername>.mysql.database.azure.com hostname and the port you chose, instead of the default 3306. All other connection settings (such as SSL enforcement and credentials) remain the same. Make sure to configure network access rules to allow traffic on your chosen port. For public access servers, this means updating the firewall rules or network security groups to permit the custom port. For private access or Private Link setups, ensure that your networking (NSGs, on-premises firewall rules, etc.) permits traffic on the custom port to reach the database. Learn More Custom port support is now GA and ready for production use, so we encourage you to try it out if your environment can benefit from it. For more details on Azure Database for MySQL – Flexible Server connectivity and custom ports, refer to the official documentation: Networking Overview - Azure Database for MySQL | Microsoft Learn We look forward to seeing how you use this new capability to tailor your MySQL deployments. With custom port support now generally available, Azure Database for MySQL – Flexible Server offers even more flexibility to meet your organizational policies and integration needs, all while delivering a fully managed experience. Happy deploying!September 2025 Recap: Azure Database for MySQL
Join us live on our YouTube channel on October 15, 2025 for an exclusive webinar where we’ll dive deeper into the latest Azure Database for MySQL updates and answer your questions! Watch it live here. Support for In-Place Major Version Upgrade from 8.0 to 8.4 We previously announced that Azure Database for MySQL version 8.4 is now generally available. We’re now happy to share that in-place major version upgrade is supported for MySQL servers from 8.0 to 8.4. If you’re currently on MySQL 5.7, you’ll first need to perform an in-place upgrade from 5.7 to 8.0, and then upgrade from 8.0 to 8.4. Learn more. Near-Zero-Downtime Maintenance - General Availability You can now take advantage of near-zero-downtime maintenance for Azure Database for MySQL with high availability (HA) enabled. This capability is now generally available and is supported by our new HA architecture based on a dedicated Azure Standard Load Balancer design. Thanks to the dedicated Standard Load Balancer design, maintenance is now faster and no longer impacted by client-side DNS caching, which previously caused brief connection drops in some scenarios. This enhancement ensures that your mission-critical workloads remain continuously available, even during infrastructure updates, helping you meet strict uptime requirements and maintain operational continuity. Learn more. We would love your feedback We look forward to your feedback as you explore these enhancements and continue building with Azure Database for MySQL. If you have any suggestions or queries about our service, please let us know by emailing us at AskAzureDBforMySQL@service.microsoft.com. To learn more about what's new with Flexible Server, see What's new in Azure Database for MySQL - Flexible Server. Stay tuned for more updates and announcements by following us on social media: YouTube | LinkedIn | X. Thanks for being part of our community!August 2025 Recap: Azure Database for MySQL
We're excited to share a summary of the Azure Database for MySQL updates for the month of August 2025. Join us live on our YouTube channel on September 11, 2025 for an exclusive webinar where we’ll dive deeper into these updates and answer your questions! Watch it live here. Azure Database for MySQL 8.4 - General Availability We’re excited to announce that Azure Database for MySQL now supports MySQL 8.4 in General Availability (GA). This means you can create new MySQL 8.4 servers on Azure fully supported for production workloads. MySQL 8.4 is a long-term supported release from the MySQL community, bringing the latest features and improvements while emphasizing stability. With Azure’s managed service, you get these new capabilities backed by Azure’s enterprise-grade reliability and support. In short, MySQL 8.4 GA opens the door for you to upgrade your databases and future-proof your MySQL environment on Azure. Learn more. Cross subscription and cross resource-group placement in restore/replica provisioning workflow You can now restore a server or create a read replica in a different subscription and resource group in Azure Database for MySQL – Flexible Server. This enhancement offers greater flexibility for cross-environment restores, resource organization, and subscription-level separation, helping meet governance and operational requirements. Learn more. Ability to delete on-demand backup You can now delete on-demand backups in Azure Database for MySQL – Flexible Server, giving you greater control over backup management and storage costs. This feature allows you to remove on-demand backups that are no longer needed, helping maintain a cleaner backup inventory and optimize resource usage. Learn more. Unlocking Regional Insights with the Location Based Capabilities REST API Managing MySQL Flexible Server deployments across Azure regions often means choosing the right Azure region for your MySQL deployment is critical. The new Location-Based Capability Set – List API helps you: Retrieve real-time, region-specific capabilities. Compare SKUs, storage options, backup retention, and HA configurations. Integrate insights into automation pipelines for smarter deployments. This API empowers architects and developers to make informed decisions, reduce misconfigurations, and accelerate deployment cycles. Learn more. Stay Connected We look forward to your feedback as you explore these enhancements and continue building with Azure Database for MySQL. If you have any suggestions or queries about our service, please let us know by emailing us at AskAzureDBforMySQL@service.microsoft.com. You can also submit product ideas and feedback at Azure Database for MySQL Community forum. To learn more about what's new with Flexible Server, see What's new in Azure Database for MySQL - Flexible Server. Stay tuned for more updates and announcements by following us on social media: YouTube | LinkedIn | X. Take care, and thanks for being part of our community!Azure Database for MySQL 8.4 Now Generally Available
We’re excited to announce that Azure Database for MySQL – Flexible Server now supports MySQL 8.4 in General Availability (GA). This means you can create new MySQL 8.4 servers on Azure fully supported for production workloads. MySQL 8.4 is a long-term supported release from the MySQL community, bringing the latest features and improvements while emphasizing stability. With Azure’s managed service, you get these new capabilities backed by Azure’s enterprise-grade reliability and support. In short, MySQL 8.4 GA opens the door for you to upgrade your databases and future-proof your MySQL environment on Azure. Why Upgrade to MySQL 8.4? Avoid End-of-Support Deadlines: If you’re running MySQL 5.7 or 8.0 on Azure, planning an upgrade is crucial. MySQL 5.7’s community support ended on October 31, 2023, and MySQL 8.0’s end-of-life is April 30, 2026. Azure’s standard support for these versions extends slightly beyond those dates (until March 31, 2026 for 5.7, and May 31, 2026 for 8.0). After those points, servers on 5.7 or 8.0 enter Extended Support, a paid support phase that provides critical fixes for up to three years (through 2029). Running your database in Extended Support means additional costs. Upgrading to MySQL 8.4 now ensures your database stays within standard support for years to come, sparing you the hassle of last-minute upgrades or extended support fees. Benefits of MySQL 8.4: MySQL 8.4 is essentially an evolution of 8.0, so it brings numerous performance enhancements, security patches, and new SQL features that have been introduced since 8.0. Because it’s an LTS release, MySQL 8.4 is designed for stability – making it an ideal target for enterprises. Most applications that work on MySQL 8.0 will be compatible with 8.4 with little to no changes, but with 8.4 you gain improvements in areas like replication, query optimization, and JSON handling (among others) that can boost your application’s efficiency. Moreover, by standardizing on 8.4, you align with the version that will receive updates well into the future. In summary, upgrading means better reliability, availability, and security now, and assured support longevity. Upgrading from MySQL 8.0 (In-Place Upgrade) For current Azure Database for MySQL 8.0 users, moving to 8.4 is straightforward, thanks to our in-place major version upgrade capability. You can upgrade your existing 8.0 server to 8.4 on the same server instance, without dumping and restoring data. Here’s how it works: Upgrade Availability: If you create a new MySQL 8.0 server today (post-GA), the option to upgrade to 8.4 is available immediately in the Azure portal or CLI. For existing 8.0 servers (those created before this GA release), the upgrade capability will become available after your next scheduled maintenance window. The September 2025 platform update is enabling this feature across all regions. Note: Azure will not auto-upgrade your server during that maintenance; it only enables the new version as an option. You remain in control of when to perform the major version upgrade. Performing the Upgrade: Once the feature is enabled for your server, you can initiate the upgrade via the Azure portal, Azure CLI, or PowerShell. The process involves a downtime (the server will be taken offline and restarted on MySQL 8.4), so plan to execute during a maintenance window or low-traffic period. We strongly recommend taking a backup or snapshot before upgrading, as a precaution. For a step-by-step guide and best practices (including how to minimize downtime by using read replicas for the upgrade), refer to the official Azure documentation on https://learn.microsoft.com/azure/mysql/flexible-server/how-to-upgrade. In most cases, upgrading from 8.0 to 8.4 is completed within several minutes. After upgrade, your server retains the same endpoints, configuration, and data – just running on the new MySQL 8.4 engine. Upgrading from MySQL 5.7 (Two-Step Path) Upgrading from MySQL 5.7 to 8.4 requires a two-step approach, since a direct jump is not supported: First upgrade 5.7 to 8.0: Azure MySQL Flexible Server supports in-place major upgrade from 5.7 to 8.0. This moves your server to a supported major version and is a necessary intermediate step (you cannot skip major versions in one go). MySQL 8.0 introduced some changes from 5.7 (for example, stricter SQL modes and a new default authentication plugin), so after upgrading to 8.0, test your application and fix any compatibility issues. Azure’s standard support for 5.7 runs until March 31, 2026, so you should aim to complete this step before then. Then upgrade 8.0 to 8.4: With your server now on 8.0, you can use the in-place upgrade to 8.4 as described above. All Azure 8.0 servers will have the 8.4 upgrade option by the end of the next maintenance cycle (after the feature rollout in September 2025). Plan to perform the 8.0 → 8.4 upgrade at a convenient time, ideally well before MySQL 8.0’s support winds down in 2026. This final step ensures you’re on the latest GA version and out of the legacy support cycle. Some customers may choose to migrate 5.7 to 8.4 by creating a new 8.4 server and importing data (using dump and restore or Azure Database Migration Service). This approach can be useful if you want to reorganize your environment or test in parallel. However, it will likely involve more downtime than the sequential in-place upgrades. Evaluate which method fits your needs – either way, now is the time to start, given that free support for 5.7 ends in less than two years. Support Timeline Summary and Next Steps To recap the support timelines and why upgrading matters: MySQL 5.7: Community EOL: Oct 31, 2023. Azure standard support until: March 31, 2026. After that, servers enter extended support (critical fixes only, with additional charges) until March 31, 2029. Action: Plan to upgrade off 5.7 before Q1 2026 to stay within standard support. MySQL 8.0: Community EOL: Apr 30, 2026. Azure standard support until: May 31, 2026. Extended support then runs to May 31, 2029. Action: Begin upgrading 8.0 instances to 8.4 in the coming months, rather than waiting until the last moment. The upgrade feature is available now (or after one maintenance cycle for older servers). MySQL 8.4: GA start: Sep 2025 (now). This is the recommended target for all MySQL deployments on Azure going forward. It will be fully supported on Azure well beyond 2026, receiving regular updates and improvements as part of the Azure service. Action: Deploy new databases on 8.4 and upgrade existing 5.7/8.0 databases to 8.4 when feasible, to benefit from the latest features and long-term support. Next Steps: Getting started with MySQL 8.4 on Azure is easy. You can create a new Azure Database for MySQL 8.4 server from the Azure Portal or via CLI today. For existing servers, review the https://learn.microsoft.com/azure/mysql/flexible-server/how-to-upgrade to choose your upgrade method (in-place with some downtime vs. replica method for minimal downtime) and schedule a time for the upgrade. By moving to Azure Database for MySQL 8.4, you’re investing in a more stable, performant, and future-proof foundation for your applications. We’re thrilled to see customers embrace MySQL 8.4, and we’re committed to making your upgrade process as smooth as possible. Upgrade with confidence, and leverage the power of MySQL 8.4 in Azure to drive your business forward! For more information or to provide feedback, contact Ask Azure Database For MySQL.Managing bloat in PostgreSQL using pgstattuple on Azure Database for PostgreSQL flexible server
Bloat refers to the unused space within database objects like tables and indexes, caused by accumulated dead tuples that have not been reclaimed by the storage engine. This often results from frequent updates, deletions, or insertions, leading to inefficient storage and performance issues. Addressing bloat is crucial for maintaining optimal database performance, as it can significantly impact storage efficiency, increase I/O operations, reduce cache efficiency, prolong vacuum times, and slow down index scans. In this blog post, I will walk you through how to use the pgstattuple extension in PostgreSQL to analyze and understand the physical storage of your database objects. By leveraging pgstattuple, you can identify and quantify the unused space within your tables and indexes. We will guide you through analyzing bloat, interpreting the results, and addressing the bloat to optimize your database and improve its performance. I will be using the pg_repack extension as an alternative to VACUUM FULL and REINDEX. pg_repack is a PostgreSQL extension that removes bloat from tables and indexes and reorganizes them more efficiently. pg_repack works by creating a new copy of the target table or index, applying any changes that occurred during the process, and then swapping the old and new versions atomically. pg_repack doesn't require any downtime or exclusive access locks on the processed table or index, except for a brief period at the beginning and at the end of the operation. Performing a full table repack requires free disk space about twice as large as the target table(s) and its indexes. For example, if the total size of the tables and indexes to be reorganized is 1GB, an additional 2GB of disk space is required. For pg_repack to run successfully on a table you must have either a PRIMARY KEY or a UNIQUE index on a NOT NULL column. Let us dive in and see how you can make the most of this powerful tool. To get more details on the bloat on an Azure Database for PostgreSQL flexible server, you can follow these steps: 1. Installing pgstattuple Add pgstattuple to the azure.extensions server parameter. You must install the extension on the database in which you want to analyze the bloat. To do so, connect to the database of interest and run the following command: CREATE EXTENSION pgstattuple; 2. Analyze the table/index Once the extension is installed, you can use the pgstattuple function to gather the detailed statistics to analyze the bloat on test table. The function provides information such as the number of live tuples, dead tuples, and the percentage of bloat, free space within the pages. To showcase the pgstattuple extension features, I have used a 4 Vcore SKU with PG version 16, created a test table with an index, loaded it with 24 Gb data and kept on generating bloat by performing some update/delete commands on the table leading to bloat. Creating a test table using the below script. CREATE TABLE test_table ( id bigserial PRIMARY KEY, column1 text, column2 text, column3 text, column4 text, column5 text, column6 text, column7 text, column8 text, column9 text, column10 text); Loading the table with 24 GB data using the below script. INSERT INTO test_table (column1, column2, column3, column4, column5, column6, column7, column8, column9, column10) SELECT md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text), md5(random()::text) FROM generate_series(1, 22000000); Create an index on this table to depict the usage of pgstatindex function to analyze bloat on an index. CREATE INDEX idx_column1 ON test_table (column1); Run functions pgstattuple on the above table and pgstatindex on the above index without bloat. pgstattuple on unbloated table You can observe the dead_tuple_percent is 0 and free_percent is 4.3 referring to the healthy state of a table. pgstatindex on unbloated index You can observe the avg_leaf_density is 89.07% and leaf_fragementation is 8.1 referring to healthy state of the index. Using the command below, I am generating bloat on the table. UPDATE test_table SET column1 = md5(random()::text), column2 = md5(random()::text), column3 = md5(random()::text), column4 = md5(random()::text), column5 = md5(random()::text), column6 = md5(random()::text) WHERE id % 5 = 0; You can analyze a table using the below query: SELECT * FROM pgstattuple('your_table_name'); What it does: It performs a full scan to gather detailed statistics Performance: On large tables the performance is slower and might take some seconds to minutes depending on the table size due to the full table scans Use case: To diagnose bloat or planning vacuuming strategies like (performing VACUUM/VACUUM FULL) To achieve faster estimates you can use, SELECT * FROM pgstattuple_approx(‘your_table_name’); What it does: Uses sampling to estimate statistics about the table. Accuracy: Results are close but not exact Performance: On large tables the performance is faster as it only considers a sample (subset of pages). Use case: quick insights The function provides the following information about the table. Column Description table_len Table length in bytes (tuple_len+dead_tuple_len+frees_space) and the overhead accounts for the padding (for tuple alignment) and page header (for per page table pointers) tuple_count Number of live tuples tuple_len Length of live tuples in bytes tuple_percent Percentage of live tuples dead_tuple_count Number of dead tuples dead_tuple_len Length of dead tuples in bytes dead_tuple_percent Percentage of dead tuples free_space Total free space in bytes within the pages free_percent Percentage of free space within the pages You should mainly concentrate on the below 3 columns to understand the table bloat and the unused space. dead_tuple_percent column tells us the percentage of dead tuples in the table. It is calculated as below. dead_tuple_percent = dead_tuple_len / table_len * 100 This can be reduced by running VACUUM on the table. However, VACUUM does not reclaim the space. Hence, free_space and free_percent would increase after the VACUUM. free_space and free_percent depict the unused/wasted space within the pages. The space can be reclaimed only by performing a VACUUM FULL on the table. If you see high free_percent it depicts the table needs VACUUM FULL (here instead you could use pg_repack) to reclaim the space. If you observe a dead_tuple_percent anything > 20% you would need to run VACUUM on the table. However, if you observe a free_percent > 50% you would need to run VACUUM FULL on the table. The below 3 snips depict the pgstattuple function run on a bloated table, vacuumed table and the output after pg_repack run on the table. Pgstattuple function run on the bloated table. pgstattuple function run on the table after vacuuming the table. Note: Here you also see a difference in tuple_count as I have performed some delete statements which are not captured in the document. Hence you see a tuple count difference. pgstattuple function on after running pg_repack on the table. Summary of Changes with vacuum and pg_repack run on a bloated table: dead_tuple_count: Reduced to 0 after VACUUM dead_tuple_len: Reduced to 0 after VACUUM dead_tuple_percent: Reduced after VACUUM from 21% to 0 free_space: Increased after VACUUM but significantly reduced after pg_repack free_percent: Increased after VACUUM but drastically reduced after pg_repack Similarly, to analyze an index, you can run: SELECT * FROM pgstatindex('your_index_name'); The function provides the following information about the index. Column Description version B-tree version number tree_level Tree level of the root page index_size Total number of pages in index root_block_no Location of root block internal_pages Number of "internal" (upper-level) pages leaf_pages Number of leaf pages empty_pages Number of empty pages deleted_pages Number of deleted pages avg_leaf_density Average density of leaf pages leaf_fragmentation Leaf page fragmentation Low avg_leaf_density implies underutilized pages. It denotes the percentage of good data in index pages. After VACUUM this column value would further go down as cleaning the dead tuples in the indexes reduces the leaf density further pointing us to increase in unused/wasted space. To reclaim the unused/wasted space REINDEX needs to be performed for the index to be performant. High leaf_fragmentation implies Poor data locality within the pages again a REINDEX would help. If you see avg_leaf_density anything < 20% you would need perform REINDEX. The below 3 snips depict the pgstatindex function run on a bloated table index, vacuumed table index and the output after pg_repack run on the table index. pgstatindex ran on bloated table pgstatindex run on index after Vacuuming the table pgstatindex run on index after pg_repack run Summary of Changes with vacuum and pg_repack run on a bloated index: index_size: Remained high after VACUUM but significantly reduced after pg_repack as the unused/wasted space is reclaimed avg_leaf_density: Reduced significantly after VACUUM from 80%-14% depicting a smaller number of good data on the leaf page and increased to 89% after pg_repack leaf_fragmentation: Remained the same after VACUUM and reduced to 0 after pg_repack depicting no page fragmentation happening Note: The index size is a sum of leaf_pages, empty_pages, deleted_pages and internal_pages. pgstattuple acquires a read lock on the object (table/index). So the results do not reflect an instantaneous snapshot; concurrent updates will affect them. For more information on pgstatginindex and pgstathasindex functions refer to PostgreSQL documentation here. For more insights on pgstattuple with respect to TOAST tables, please refer to the relevant documentation here. 3. Analyze bloat on partition tables The pgstattuple extension in PostgreSQL is a powerful tool for analyzing table and index bloat by providing detailed statistics such as dead tuple percentage and free space. However, it’s important to note that this function cannot be executed directly on a partitioned table. Instead, it must be run individually on each partition to gather meaningful insights. To streamline this process, especially when dealing with a large number of partitions, you can use a PL/pgSQL script that iterates through all partitions of a parent table. This script executes pgstattuple on each partition and stores the resulting statistics—such as dead tuple percentage and free space—in a summary table for easy review and analysis. This approach not only simplifies the task of identifying bloat across partitions but also enables proactive monitoring and optimization of storage efficiency in partitioned PostgreSQL environments. -- Create a temporary table to store bloat statistics DROP TABLE IF EXISTS bloat_summary; CREATE TEMP TABLE bloat_summary ( partition_name TEXT, table_len BIGINT, tuple_count BIGINT, tuple_len BIGINT, tuple_percent NUMERIC, dead_tuple_count BIGINT, dead_tuple_len BIGINT, dead_tuple_percent NUMERIC, free_space BIGINT, free_percent NUMERIC ); -- DO block to iterate over all partitions and collect statistics DO $$ DECLARE part RECORD; stats RECORD; BEGIN FOR part IN SELECT inhrelid::regclass AS partition_name FROM pg_inherits WHERE inhparent = 'your_parent_table'::regclass LOOP EXECUTE format('SELECT * FROM pgstattuple(%L)', part.partition_name) INTO stats; INSERT INTO bloat_summary VALUES ( part.partition_name, stats.table_len, stats.tuple_count, stats.tuple_len, stats.tuple_percent, stats.dead_tuple_count, stats.dead_tuple_len, stats.dead_tuple_percent, stats.free_space, stats.free_percent ); END LOOP; END $$; -- Output the summary SELECT * FROM bloat_summary ORDER BY dead_tuple_percent DESC; 4. Addressing the bloat Once you have identified a bloat, you can address it by taking the following steps. Below are common approaches. VACUUM: Clears dead tuples without reclaiming the space. Pg_repack: Performs VACUUM FULL and REINDEX online and efficiently reorganizes the data. Note: Other unused space, like the one left in heap or index pages due to a configured fill factor lower than 100 or because the remaining available space on a page cannot accommodate a row, given its minimum size, is not considered bloat, while it's also unused space. pgstattuple will help you address bloat! This blog post guided you through understanding and managing bloat in PostgreSQL using the pgstattuple extension. By leveraging this tool, you were able to gain detailed insights into the extent of bloat within their tables and indexes. These insights prove valuable in maintaining efficient storage and ensuring optimal server performance.Data integration with Azure Logic Apps and MySQL Flexible Server
Data integration allows applications to move, process, or transform data across multiple systems as part of their micro-service architecture. While you can accomplish data integration in several ways, you can use Logic apps to move data to an Azure Database for MySQL flexible server and automate data integration tasks that you perform in response to API calls.
