Flexible server performance

Question

I m using Azure PostgreSQL Flexible Server (version 14.x) for a web application with high-concurrency, OLTP workloads. The database is hosted on a Standard_B1ms SKU (2 vCores, 8 GB RAM) and is growing to around 200 GB. We’re experiencing slow query performance and occasional timeouts during peak usage, particularly with complex JOINs and aggregations.

I’ve tried enabling pg_stat_statements for query optimization. But no help.
Are there additional PostgreSQL Flexible Server optimizations for high-concurrency workloads

Thanks in advance

Answer 1

@DataCoder
Thank you for the question and for using Microsoft Q&A platform.

As per my understanding you’re dealing with some challenging performance issues.

There are additional optimizations that you can try for your PostgreSQL Flexible Server to improve performance for high-concurrency workloads. Here are a few suggestions:

1. We can try upgrading to a higher SKU with more vCores and RAM to handle the increased workload. You can also consider using read replicas to offload read traffic from the primary instance.
2. We can try using the PostgreSQL query planner to analyze query performance and identify slow queries. You can then optimize these queries by adding indexes, rewriting queries, or adjusting configuration parameters.
3. Connection pooling can help reduce the overhead of establishing new connections to the database. You can use a connection pooler like PgBouncer to manage connections to your PostgreSQL Flexible Server.
4. We can adjust various configuration parameters in PostgreSQL to optimize performance for your workload. For example, you can adjust the shared_buffers parameter to allocate more memory for caching data, or the max_connections parameter to limit the number of concurrent connections.
5. It's important to monitor the performance of your PostgreSQL Flexible Server to identify bottlenecks and optimize performance. You can use tools like Azure Monitor to monitor performance metrics like CPU usage, memory usage, and disk I/O.

Please refer to the below documents for more help:
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/overview
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-compute

I hope these suggestions help you optimize your PostgreSQL Flexible Server for high-concurrency workloads. Let me know if you have any other questions.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.