Do more with less: How to reduce your PostgreSQL costs on Azure | Citus Con: An Event for Postgres 2023 | Varun Dhawan

Azure Database for PostgreSQL - Flexible Server is a fully-managed relational database based on PostgreSQL open source on Azure.

One of the greatest benefits of running your application on PostgreSQL in the cloud is being able to scale up and down to meet demand and reduce operational expenditures. And that is especially true when you are experiencing unexpected changes in workload. With Flexible Server in the PostgreSQL managed service on Azure, you can take control of your costs and continuously optimize your cloud spend, while building modern, scalable applications to meet your customers’ needs.

If this sounds exciting, come watch this session where you will learn 10 things you can do today to reduce the cost of Azure Database for PostgreSQL. This talk is for anyone who wants to reduce costs in their PostgreSQL spend in the cloud, whether you are an early-stage startup or a large enterprise with a global footprint.

Transcript

1. An Event for Postgres 2023 Varun Dhawan | Senior PM @ Microsoft Azure @iVarund
2. An Event for Postgres 2023 • Hi! I’m a Product Manager at Open- Source Database team at Microsoft • I bring over 15 years experience working on relational database systems • My previous experiences includes working as DevOps engineer at Target Corp and Database SRE at McKinsey&Company • Some of my fav things – Hiking, Music and Blogging >
3. Tools and Recommendations to reduce cost on Azure Database for PostgreSQL Flexible Server
4. Flexible Server – Overview The 10 ways to reduce your Flexible Server Cost Conclusion
5. Extensions JSONB Full text search Geospatial Rich indexing Rich Data types ACID Constraints Management Automation Extension support Global reach Security Scale up & out High Availability Compliance Intelligent performance Ecosystem integration
6. “For 81% of organizations, managing spend is their top cloud challenge” -- Flexera 2022 State of Cloud Report
7. Usage Total units describing usage of each underlying service Rate* Amount paid per unit of usage $0.138 GB / month 128 GB stored this month $0.214 / hour 720 hours this month How is Azure cost calculated? Azure Cost Total amount to be paid for usage of all services USD 156.22/month USD 17.66/month = Compute Storage * Azure PostgreSQL Flexible Server pricing for West Central US region. Cost of an Azure service can vary between locations based on demand and local General Purpose (2-64 vCores) Max IOPS 3200 X
8. To reduce cost Optimize Rate Optimize Usage Ultimately, there are two levers to reduce costs Use only what you need Pay less for what you use Bring your Azure Postgres bill down.
9. Scale compute Up/Down Azure advisor for right sizing resources Evaluate HA and DR requirements Consolidate databases and servers Starting and Stopping servers Archive old data for cold storage Azure Advisor to shutdown unused database Manage spending via budgets and alerts Save on cost with reserved capacity pricing Place test servers in cheaper geo-regions
10. Save on compute cost with reserved capacity pricing
11. Scenario – Using “Pay as you go” Pricing You are using Flexible Server You are paying for default “Pay as you go” pricing You can save cost by using Azure reservations Recommendation Leverage reserved capacity pricing
12. What? - Reserved capacity pricing allows to save costs by committing to a specific amount of capacity for 1- or 3-year term. - This can be used to cover the costs of Azure PostgreSQL servers created in the same region. Potential savings Up to 58% saving with 3 year reserved Up to 38% saving with 1 year reserved Refer - What are Azure Reservations? Save on compute cost with reserved capacity pricing
13. Get Started with Azure Reservations Steps: 1. Sign into the Azure portal. 2. Select All services > Reservations. 3. Select Add and then in the Purchase reservations pane, select Azure Database for PostgreSQL to purchase a new reservation for your PostgreSQL databases. 4. Fill in the required fields. Existing or new databases that match the attributes you select qualify to get the reserved capacity discount.
14. Place test servers in cheaper geo-regions
15. Scenario – Using same geo-region for all instances You are using Flexible Server You might be overpaying by having all databases instances created in same geo region Recommendation Spread your some of your non-mission critical workloads in geo-regions with less pricing
16. What? - Creating a test server in a cheaper Azure region can be a cost-saving strategy. - For non-mission critical applications consider keeping the app and database in a single region to leverage the advantages of the lower-cost region and lower latency. Potential savings Can save up to 25% or more on the cost in cheaper geo region Refer - Cost impact of Azure regions - Microsoft Learn Place test servers in cheaper geo-regions
17. Get Started with Azure regions Steps: 1. Sign into the Azure portal. 2. Identify a cheaper region with lower cost of computing resources. 3. Create a new Flexible Server instance in the cheaper region with the right configuration for your test environment. 4. Migrate the test data to the new Flexible Server instance. You can use tools such as pg_dump and pg_restore to export and import databases. 5. Monitor the performance of the test server and adjust the resources as needed to ensure optimal performance. 50% 60% 90% 40% 50% 20%
18. Scale compute Up/Down
19. Scenario – Using fixed capacity of resources You are using Flexible Server Your app traffic is unpredictable and is well below peak You might be paying for provisioned capacity you don’t use Recommendation Scale compute Up/Down
20. What? - Scaling up or down the resources based on usage can help you optimize costs. - Increase or decrease the number of vCores or increase the storage size of your database as needed, to ensure you are only paying for the resources you are using. Potential savings Can save up to 49% by right-sizing the capacity Refer - Scale operations in Flexible Server Scale compute Up/Down
21. Get Started with scaling resources Steps: 1. Sign into the Azure portal. 2. Click Compute+storage. 3. You can choose the compute class between burstable, general purpose, and memory optimized tiers. 4. If you want to change the number of vCores, you can click the drop-down of Compute size and click the desired number of vCores/Memory from the list. 5. Click Save.
22. Use Azure advisor recommendations for right sizing resources
23. Scenario – Using Azure advisor recommendations You are using Flexible Server You have not performed any Azure advisor recommendation You might be overpaying for unused capacity Recommendation Use Azure advisor recommendations for right sizing resources
24. What? - Advisor analyzes resource configuration and usage patterns and provides recommendations on how to improve the performance and lower the cost. - Recommendations included compute, storage, networking and databases. Potential savings Up to 30% is cost saving on the cleaning-up unused resourced. Refer - Azure Advisor for PostgreSQL Azure advisor recommendations for right sizing resources
25. Get Started with Azure advisor Steps: 1. Sign into the Azure portal. 2. Recommendations are available from the Overview navigation sidebar in the Azure portal. A preview will appear as a banner notification, and details can be viewed in the Notifications section located just below the resource usage graphs. 3. Recommendations as categorized as: • Performance: Includes CPU usage, memory pressure, connection pooling, disk utilization, and product-specific server parameters. • Reliability: Includes storage limits, and connection limits. • Cost: This includes server right-sizing recommendations.
26. Evaluate HA (high availability) and DR (disaster recovery) requirements
27. Scenario – Configuring HA/DR for all workload types You are using Flexible Server You have configured High Availability for all servers or setup Read-Replica for all the databases You might be overpaying for HA/DR capacity you don’t need Recommendation Evaluate HA (high availability) and DR (disaster recovery) requirements
28. What? - PostgreSQL Flexible Server has built-in node and storage resiliency at no extra cost - Non mission critical workload can leverage this and save on the cost of having a separate HA or DR instance. Potential savings 50% is cost saving on rightsizing resources. Refer - Azure Advisor for PostgreSQL Evaluate HA and DR requirements
29. Get Started - Enable high availability Steps: 1. In the Azure portal, choose Flexible Server and click create. 2. Choose your availability zone. This is useful if you want to collocate your application in the same availability zone as the database to reduce latency. Choose No Preference if you want the flexible server to deploy the primary server on any availability zone. 3. Click the checkbox for Enable high availability. That will open up an option to choose high availability mode. If the region does not support AZs, then only same-zone mode is enabled.
30. Get Started – Disable high availability Steps: 1. In the Azure portal, select your existing PostgreSQL flexible server. 2. On the flexible server page, click High Availability from the left panel to open high availability page. 3. Un-Check on the High availability checkbox to disable the option. Then click Save to save the change.
31. Consolidate databases and servers
32. Scenario – Having too many small instances You have created multiple database instances for your different apps You might be paying more for resources when they are underutilized or idle Recommendation Consolidate databases and servers
33. What? - Consolidating databases in a few larger instances is more cost-effective than many smaller instances. - It is important to measure the impact of consolidation on the performance, to ensure that the server is appropriately sized to meet application requirements. Potential savings Realize Up to 30% is cost saving on the unused resourced. Refer - Azure Advisor for PostgreSQL Consolidate databases and servers
34. Get Started with database consolidation Steps: 1. Identify server that can be consolidated, considering size, region, configuration (CPU, memory, IOPS), performance requirements, workload type. 2. Create a new instance with enough vCPUs, memory, and storage to support the consolidated databases. 3. Migrate the databases to the new instance. Use pg_dump and pg_restore to export and import databases. 4. Monitor the performance of the new instance and adjust the resources.
35. Starting and Stopping servers
36. Scenario – Running instances 24/7 for all workload types You are using Flexible Server You leave instances running during evenings, weekends, and holidays when they are not used. You might be paying for compute capacity even when database is not being used. Recommendation Starting and Stopping servers
37. What? - Starting and stopping servers can be a cost-saving strategy PostgreSQL Flexible Server. - By only running the server when you need it, can significantly reduce the cost. - It is important to ensure that you have a reliable process for starting and stopping the server as needed. Potential savings Start/Stop instances can save up to $1000s in compute saving. Refer - Stop/Start an Azure Database for PostgreSQL - Flexible Server Starting and Stopping servers
38. Get Started with start/stop instance Steps: 1. In the Azure portal, Identify the instance you want to Start and Stop. 2. Start the instance when you need it. You can start the server using the Azure portal, Azure CLI, or Azure REST API. 3. Stop the instance when you do not need it. You can stop the server using the Azure portal, Azure CLI, or Azure REST API. 4. Also, if a server has been in a stopped (or idle) state for several continuous weeks, you can consider dropping the server after the required due diligence.
39. Archive old data for cold storage
40. Scenario – Operating data set includes historical data You have a large database with multiple years of data, however only current year of data is used for transactions and reporting You might be paying for provisioned storage that you don’t use Recommendation Archive old data for cold storage (Azure blob store)
41. What? - Archiving infrequently accessed data to Azure archive store (while still keeping access) can help reduce costs - Export data from PostgreSQL to Azure Archived Storage and store it in a lower-cost storage tier - You can also use Azure Data Factory to automate this process Potential savings Organizations can save up to 95% monthly storage cost, by archiving infrequently accessed to lower-cost storage tier Also, besides cost, this will also benefit the query performance, Refer - Migrate your PostgreSQL database by using dump and restore Archive old data for cold (archive) storage
42. Get Started- archive old data to cold tier storage Steps: 1.Setup Azure Blob Storage account and create a container for your database backups. 2. Use pg_dump to export the old data to a file. 3.Use the Azure CLI or PowerShell to upload the exported file to your Blob Storage container. 4.Set up a retention policy on the Blob Storage container to automatically delete old backups. 5.Modify the backup script to export the old data to Blob Storage instead of local storage. 6.Test the backup and restore process to ensure that the archived data can be restored if needed.
43. Leverage Azure Advisor to find and shutdown unused database
44. Scenario – Having unused/idle instances You are running Flexible Server Your have over provisioned database instances You might be paying for provisioned capacity you don’t use Recommendation Leverage Azure Advisor to find and shutdown unused database
45. What? - Azure Advisor identifies resources that haven't been used at all over the last 7 days and makes a recommendation to shut them down. - Recommendation criteria include CPU and Outbound Network utilization metrics. Potential savings Leveraging Azure advisor recommendations to shutdown unused instanced can save up to $1000s in compute saving. Refer - Reduce service costs by using Azure Advisor Leverage Azure Advisor to find and shutdown unused database
46. Get Started with Azure advisor cost recommendation Steps: 1. In the Azure portal, Open the Advisor service page, navigate to the "Cost" section and select "High-cost resources“ 2. In the list of high-cost resources, filter for the "Azure PostgreSQL servers" category. 3. Identify any Azure PostgreSQL servers that have been inactive for an extended period and are no longer needed. 4. Click on the identified PostgreSQL server from the list and check the Activity log for any activity over the last few days. 5. If there has been no recent activity, In the "Settings" section, select the "Pause" option to shut down the PostgreSQL server. 6. Confirm the shutdown process.
47. Manage spending better by setting up budgets and alerts
48. Scenario – Using Azure budgets to manage cloud spend You are using Flexible Server with database that are always on You have not configured budgets to get alert when consumption reaches a given threshold You might be overspending on Azure services Recommendation Manage spending better by setting up budgets and alerts
49. What? - Azure Budget alerts helps control costs by providing real-time notifications when your spending exceeds thresholds. - This enables you to reduce your spending and avoid unexpected charges. - Besides cost, budgets are critical for visibility and better planning of your Azure cloud spend. Potential savings Organizations can save up to 65% on cost when using Azure budget and alerts. Refer - cost alerts to monitor usage and spending Manage spending better by setting up budgets and alerts
50. Get Started – Create Azure budget Steps: 1. In the Azure portal, Open the Azure Cost Management + Billing service page. 2. Navigate to the "Cost Management" section and select "Budgets.“ 3. Click on the "Add" button to create a new budget. 4. Enter a name for the budget and select the subscription to associate it with. 5. Choose the budget amount and time- period for which it applies. 6. Select the scope of the budget 7. Choose the budget alert threshold and frequency for notifications
51. Get Started – Create Azure budget alerts Steps: 1. Once the budget is created, you can also set up alerts to notify you when the budget threshold is exceeded. 2. In the "Budgets" section of the Cost Management + Billing dashboard, click on the name of the budget you want to set up alerts for. 3. In the budget details page, navigate to the "Alerts" section and click on the "Add" button. 4. Enter the email addresses or phone numbers of the recipients to be notified when the budget threshold is exceeded. 5. Choose the alert threshold and frequency for notifications. 6. Click on the "Create alert rule"
52. 3. Scale compute Up/Down 4. Azure advisor for right sizing resources 5. Evaluate HA and DR requirements 6. Consolidate databases and servers 7. Starting and Stopping servers 8. Archive old data to cold storage 9. Azure Advisor to shutdown unused database 10. Manage spending via budgets and alerts 1. Save on cost with reserved capacity pricing 2. Place test servers in cheaper geo-regions
53. If your specific scenario was not covered in the presentation, please feel free to reach out to us. We’re happy to help.
54. | Resources Azure Database for Postgres homepage http://aka.ms/postgres Azure Postgres documentation http://aka.ms/postgresdocs Azure Postgres on Twitter @AzureDBPostgres Ask Questions AskAzureDBforPostgreSQL@service.microsoft.com Get Started for Free with an Azure Free Account https://aka.ms/try-postgres-free Azure Postgres on LinkedIn https://www.linkedin.com/company/azure-database-for-postgresql/ Azure Postgres Blog https://aka.ms/Azure-Postgres-Blog