Boost Your Postgres Server on Azure with Enhanced Azure Advisor Performance Recommendations!
Are you puzzled about why your server or database performance has suddenly degraded or become slow? This can be quite frustrating, especially when you are trying to maintain optimal performance for your applications. There could be numerous reasons behind this issue. It is essential to identify the root cause to implement the right solution and get your server back to its peak performance. Let us dive deeper into this and explore potential solutions together. In this blog post, I will take you on a journey through the comprehensive Azure Advisor performance recommendations specifically tailored for Azure Database for PostgreSQL flexible server. These insights will not only alert you to potential root causes but also guide you in resolving the server issues you are facing. We have you covered from hardware optimizations and logging enhancements to workload management, autovacuum tuning, bloat reduction, and managing inactive replication slots. Let's dive in and unlock the full potential of your server using the performance recommendations provided here. To ensure you receive proactive notifications regarding Azure Advisor recommendations at the server or subscription level, you have the option to configure alerts to be received via email or text message. Setting up Alerts for Recommendations Stay ahead of the game by setting alerts for Azure Advisor recommendations for your Azure Database for PostgreSQL flexible server! Whenever a recommendation is made for one of your resources, an event is triggered, ensuring you are always in the loop. You can customize these alerts to be recommendation-specific, targeting types of recommendations, or set them up at the Azure subscription level to receive notifications for all recommendations related to the resources within the specified subscription. This way, you will never miss a critical insight and can take proactive steps to maintain optimal server performance, ensuring you are always informed and ready to act! You can set up alerts based on the Impact level, Category, and Recommendation type. On Azure portal, search for Advisor and proceed. Go to Monitoring and Alerts and click on New Advisor Alert. Create the Advisor Alert and set up an Action Group for the recommendation event. When a recommendation alert is triggered, you will receive notification via email or text message according to your Action Group settings. This alert is sent once per recommendation to ensure that you are informed promptly for timely action. You have taken the proactive step of setting up an alert to notify you via email if your server enters any vulnerable state. This ensures that you are immediately informed of any potential issues. For instance, you recently received the following recommendation alert in your email, highlighting a specific area that requires attention. This way, you can promptly address any concerns and maintain the optimal performance of your server. Scenarios in which Azure Advisor recommendation will help keep your Azure Database for PostgreSQL flexible server performant. Let us explore the various scenarios in which Azure Advisor can proactively assist you in identifying and resolving performance issues with your PostgreSQL server. Scenario 1- Inactive Logical Replication slots You have received an alert indicating that your server has an inactive logical replication slot. The outlined section below highlights that this is a high-impact performance. This alert tells me a recommendation named " Review your server for inactive Logical Replication Slots" has been triggered. Th next step is to go to your server on Azure Portal. You will be able to see Recommendations on the Overview page of your server on the portal. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information about the recommendation, including the specific details and the reason for receiving it, which is provided in the "Reason" column. The "Reason" column for this recommendation states: "Found inactive logical replication slot(s). Here are the replication slots that have been inactive for the longest: ['myslot']." Click on the "Recommended Action" to proceed with the necessary steps to resolve your server's vulnerable state. You will be directed to a page that provides the resolution for the server issue encountered. The necessary action is outlined in the "Recommended Action" section. In this scenario, you will need to drop the inactive logical replication slot as mentioned below. You can then check your replication slot for it to be active or inactive. Resolution: Proceed to remove the inactive logical replication slot as shown below to maintain optimal server performance. After dropping the inactive replication slot, you will notice within 24 hours that the recommendation no longer appears on the Overview page under Recommendations in your Azure Portal. Scenario 2 – Checkpoints happening frequently You have now received an alert indicating that your server has a recommendation stating the server has frequent checkpoint warnings. The outlined section below highlights that this is a high-impact performance. This alert tells me a recommendation named "PostgreSqlCheckpointHappeningFrequently" has been triggered. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: 'Checkpoints are occurring frequently over 3 consecutive hours'. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, you will need to update your max_wal_size server parameter. Clicking on the 'Recommended Action' will take you to the Server parameters page of your server as shown below. Search for the max_wal_size parameter and increase it accordingly. After updating the max_wal_size parameter and resolving the frequent checkpoint warnings, the recommendation will disappear from the Overview page under Recommendations in your Azure Portal within 24 hours. Scenario 3 – High Bloat ratio Let us discuss the alert you received regarding a high bloat ratio on your server(s). I will guide you through the troubleshooting process to understand what is happening on the server and how to resolve the issue. You have now received an alert indicating that your server has a recommendation stating that your server is having a high bloat ratio. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: “Here are the Database(s) with the highest BloatRatio: ["testdb"] has a Bloat Ratio(dead/live + dead)[86.08]”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, the page will redirect you to the Troubleshooting guides of your server on the Azure portal. Navigate to the Autovacuum Monitoring tab, where you will find information on Bloat Ratio, Live vs. Dead tuples, Vacuum and Analyze, Autovacuum workers in progress, Autovacuum per table, and Enhanced metrics by server and by database. Navigate through each section to learn more about the server’s bloat and autovacuum information as shown below. The Autovacuum monitoring troubleshooting guide is designed to assess the health of autovacuum or vacuum on Azure Database for PostgreSQL Flexible Server. During autovacuum execution, it removes dead tuples, freeing up disk space and improving query performance. Additionally, it updates the visibility map to track pages without outdated or deleted data, reducing unnecessary I/O operations. Another critical function of autovacuum is to prevent transaction ID wraparound situations, which could result in data loss. By analyzing the results of this troubleshooting guide, you can take proactive steps to optimize autovacuum and maintain the long-term health of your database. As a short-term solution, you can VACUUM ANALYZE the database or specific frequently used tables with high bloat to clean up dead tuples and bring your server to a performant state. Scenario 4 – Autovacuum turned OFF Let us discuss the alert you received regarding autovacuum server parameter on your server(s). I will guide you through the troubleshooting process to understand what is happening on the server and how to resolve the issue. You have now received an alert indicating that your server has a recommendation stating that your server has autovacuum set to OFF. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: “atuovacumm is turned OFF, and the Bloat Ratio on these Database(s) ["postgres","testdb"] and Schema(s) ["pg_toast","public"] is greater than 50%”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, you will need to turn on autovacuum server parameter. Clicking on the 'Recommended Action' will take you to the Server parameters page of your server as shown below. Search for the autovacuum parameter and turn it ON. Scenario 5 – Wraparound Let us walk through a scenario where you receive a Wraparound recommendation alert. We will cover how to troubleshoot and resolve the issue to maintain your server's performance. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: “The server has utilized 99.85% of transactions. Now 2999999 transactions remaining.”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, you will need to perform the mitigation steps provided in the troubleshooting guide. Clicking on the 'Recommended Action' will take you to the troubleshooting guides. Please visit the Autovacuum Blockers section to get into more details on your server behavior pertaining to wrap around. Scroll to the bottom of the page to find the Recommendations to resolve the issue as shown in the screenshot below. Follow through the Recommendations to proactively enable your server to perform effectively. Scenario 6 – logging parameters Too much logging always hinders the server’s performance; hence it is important to understand the need for logging and act accordingly. Let's walk through a scenario where you receive logging parameters recommendation alert (log_duration, log_min_duration_statement, log_error_verbosity, log_statement, log_statement_stats). We will cover how to troubleshoot and resolve the issue to maintain your server's performance. Click on each of the highlighted recommendations above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation will state the value of the parameter set. For example: “log_statement_stats is set to ON”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In these scenarios, you will be directed to the server parameters page on Azure portal to update the server parameter values as provided in the “Recommended Action”. Setting the server parameter values per the recommendation action enables your server resources to be utilized efficiently. Scenario 7 – Long running transaction Let us walk through a scenario where you receive a long running transaction recommendation alert. We will cover how to troubleshoot and resolve the issue to maintain your server's performance. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: “Here are the longest running transactions. PID(s) [311568] Duration (hours): [29]”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, you will need to perform the mitigation steps provided in the troubleshooting guide. Clicking on the 'Recommended Action' will take you to the troubleshooting guides. Please visit the High CPU -> Long running transactions section to find the recommended actions to resolve the issue. Scroll to the bottom of the page to find the Recommendations to resolve the issue as shown in the screenshot below. Follow the recommendation and terminate the session for the Pid’s identified to keep your server performant. Scenario 8 – High CPU Utilization Let us walk through a scenario where you receive a high CPU utilization recommendation alert. We will cover how to troubleshoot and resolve the issue to maintain your server's performance. Click on the highlighted recommendation above. This action will direct you to a detailed page about the recommendation alert you received. On this page, you will find comprehensive information in the Recommendation details section and the reason for receiving it, which is provided in the 'Reason' column. The 'Reason' column for this recommendation states: “90% or higher CPU utilization for more than 2 consecutive hours”. Click on the 'Recommended Action' to proceed with the necessary steps to resolve your server's vulnerable state. The necessary action is outlined in the 'Recommended Action' section. In this scenario, you will need to perform the mitigation steps provided in the troubleshooting guide. Clicking on the 'Recommended Action' will take you to the troubleshooting guides. Please visit the High CPU section to find the reasons for the CPU utilization to be high and take recommended actions to resolve the issue. Review each of the highlighted tabs in the screenshot to identify the issue. Each tab contains recommendations to resolve the specific causes of high CPU utilization. As there might be multiple reasons, make sure to go through all the tabs and follow the recommendations provided accordingly. Conclusion: Imagine having a proactive ally that helps you stay ahead of potential issues with your server; That's exactly what Azure Advisor offers! In many scenarios, Azure Advisor recommendations will empower you to clearly identify and depict your server's vulnerable state. Not only does it highlight these vulnerabilities, but it also provides you with actionable recommendations to ensure your server remains highly performant. With Azure Advisor by your side, you can confidently optimize your server's performance and security, making your operations smoother and more efficient.CFP talk proposal ideas for POSETTE: An Event for Postgres 2025
Some of you have been asking for advice about what to submit to the CFP for POSETTE: An Event for Postgres 2025. So this post aims to give you ideas that might help you submit a talk proposal (or 2, or 3) before the upcoming CFP deadline. If you’re not yet familiar with this conference, POSETTE: An Event for Postgres 2025 is a free & virtual developer event now in its 4th year, organized by the Postgres team at Microsoft. I love the virtual aspect of POSETTE because the conference talks are so accessible—for both speakers and attendees. If you’re a speaker, you don’t need travel budget $$—and you don’t have to leave home. Also, the talk you’ve poured all that energy into is not limited to the people in the room, and has the potential to reach so many more people. If you’re an attendee, well, all you need is an internet connection The CFP for POSETTE: An Event for Postgres will be open until Sunday Feb 9th at 11:59pm PST. So as of the publication date of this blog post, you still have time to submit a CFP proposal (or 2, or 3, or 4)—and to remind your Postgres teammates and friends of the speaking opportunity. If you have a Postgres experience, success story, failure, best practice, “how-to”, collection of tips, lesson about something that's new, or deep dive to share—not just about the core of Postgres, but about anything in the Postgres ecosystem, including extensions, and tooling, and monitoring—maybe you should consider submitting a talk proposal to the CFP for POSETTE. If you’re not sure about whether to give a conference talk, there are a boatload of reasons why you should. And there’s also a podcast episode with Álvaro Herrera, Boriss Mejías, and Pino de Candia that makes the case for why giving conference talks matters. For inspiration, you can also take a look at the playlist of POSETTE 2024 talks. And if you’re looking for even more CFP ideas, you’ve come to the right place! Read on… Ideas for talks you might propose in the POSETTE CFP On the CFP page there is a list of possible talk titles (screenshot below) you might submit—these are good ideas, although the list is by no means exhaustive, and we welcome talk proposals that are not on this list. Figure 1: POSETTE CFP talk topics taken from the CFP page on PosetteConf.com On Telegram the other day, when answering the question “Do you have any ideas of what I should submit?”, I found myself suggesting different TYPES of talks. Not specific ideas and talk titles, but rather I framed the different categories. So I decided to share these different “types” and “classes” of talks with all of you, in the hopes this might gives you a good talk proposal idea. First you need to pick your audience: Before you think about what type of talk to give, remember that the POSETTE team is focused on serving the needs of both the USER community—as well as the Postgres contributor & hacker communities. That means first you need to decide on your audience. Are you giving a talk for PostgreSQL users, or Azure Database for PostgreSQL customers, or the PostgreSQL contributor community? All are good choices. Then you need to decide: what do you want to accomplish with your talk? Do you want to skill up the Postgres hacker community?: If you want to help skill-up the developer/contributor community, maybe pick a part of Postgres that new contributors often ask a lot of questions about, get stuck on, need help with, etc—and give a “tour” of its mechanics, starting with the basics. Do you want to help grow the Postgres community?: If you want to help grow the Postgres community of contributors and developers, you could propose a talk that would motivate tomorrow's developers/contributors to get involved in the project. Imagine you were going to a university to give a talk about "why work on Postgres"… what would you say? And how would you entice people to work on Postgres? What pain points would you challenge them with? What benefits would you share from your own Postgres experience that might inspire these developers to think seriously about Postgres as a career path? You could also shine a light on the different ways people can (and do!) contribute to the Postgres community: from mentoring to translations to organizing conferences to podcasts to speaking at conferences to publishing PostgreSQL Person of the Week. Do you want to share your expertise with Postgres users?: If you want your talk to benefit users, maybe pick an area that you are already expert in (or want an excuse to dig into and learn about?) and create a Beginners Guide for it? Or Advanced Tips for it? Or Surprising Benefits of? Or Things People Might Not Know? Especially if there is a part of Postgres you feel like people sometimes mis-use, or don't take enough advantage of.... Do you want to share your customer experiences with Azure Database for PostgreSQL, or Postgres more generally?: Maybe you have a wild success story you think others will benefit from. Or you want to share a problem you had and how you used Postgres to solve it? People love customer stories. Do you want to shine a light on the broader Postgres ecosystem?: If you want to target users with your talk, don’t limit yourself the Postgres core. There is a rich ecosystem that surrounds Postgres and people need to understand the ecosystem, too. So maybe there are tools or Postgres extensions or forks or startups that you can give a useful talk about? Do you want to help experts in other database technologies learn about Postgres?: If you have expertise in other databases as well as Postgres, maybe you can help people who who are skilled in running workloads on other databases and are looking to skill up on Postgres—by helping them understand what’s similar, and what’s different. As if you’re giving them a dictionary to translate from their familiar database to Postgres, and vice versa. There are so many more possibilities: Often I look at the schedule from previous years to look for inspiration (and to make sure that my talk proposal is not a duplicate of a talk that’s already been given.) And I think about pain points, things people get confused about, or questions that come up a lot. Another thing to keep in mind: how can you help your story to "stick"? Can you make it entertaining? How do you share your story in a way that keeps people watching (versus looking at their phone instead?) Key things to know about POSETTE: An Event for Postgres 2025 CFP deadline: The CFP for POSETTE will close on Sunday, Feb 9th 2025 @ 11:59pm Pacific Time (PST) No travel required: free & virtual developer event Length of talks: 25 minutes/session Language: All talks will be in English Talks will be pre-recorded: All talks will be pre-recorded by the POSETTE team during the weeks of Apr 28th and May 5th (with accepted speakers presenting remotely) When is the event?: Jun 10-12, 2025 Format of the virtual event: All pre-recorded talks will be livestreamed in one of 4 unique livestreams on Jun 10-12, 2025—all with parallel live text chats on Discord. Two of the livestreams will be in Americas-friendly times of day (8:00am-2:00pm PDT) and two of the livestreams will be in EMEA-friendly times of day (8:00am-2:00pm CEST). All talks will be published online after the event is over. More info about the CFP: All the details, including key dates and how to submit on Sessionize, are spelled out on the CFP page for POSETTE 2025 Code-of-conduct: You can find the Code of Conduct for POSETTE online. Please help us to provide a respectful, friendly, and professional experience for everybody involved in this virtual conference. Figure 2: The CFP is open for POSETTE: An Event for Postgres 2025 until Sunday Feb 9th at 11:59pm PST. What Postgres story do you want to share?Introducing support for Graph data in Azure Database for PostgreSQL (Preview)
We are excited to announce the addition of Apache AGE extension in Azure Database for PostgreSQL, a significant advancement that provides graph processing capabilities within the PostgreSQL ecosystem. This new extension brings a powerful toolset for developers looking to leverage a graph database with the robust enterprise features of Azure Database for PostgreSQL.
