Slow perfomance of Azure Postgresql DB

Question

We configured Azure database for PostgreSQL server with performance configuration like: General Purpose, 4 vCore(s), 100 GB
We currently have data in database of only around 1 GB and we have not scaled our application cause it is not required as of now.
The performance of PostgreSQL is very low. It is taking around 15 to 30 second in simple query to execute from PG Admin as well.
Can you please help us to get it resolved?
Can we edit performance configuration if so then how?

Answer 1

HI @Shafin Anwarsha Thank you for posting your question on Microsoft Q&A.

This may be due for many reasons.

Please check the following to help you identify the cause of the slow performance

1. Please navigate to your Azure Database for PostgreSQL and use the Metrics blade to check if you are maxing out any of your resource limits.
   If your workload is maxing out any of these server metrics please consider scaling up resources to accommodate the workload and review top queries consumers and tune them.
2. Overall Slowness
   Disabling pg_stat_statements, this can be done by visiting the server parameters blade on the Azure Portal and set "pg_stat_statements.track" to NONE.
   Some customers workload have seen up to a 50 percent performance improvement with disabling this. The tradeoff you make when you disable pg_stat_statements is the inability to troubleshoot performance issues.
   Network latency: Check the network latency between the client and the database service instance. You can check the network latency by running simple query as
   SELECT 1
   Consider disabling the following server parameters to reduce the overhead on the database side. Those server parameters are helpful to understand your workload but proven to cause performance degradation when enabled.
   • log_duration = OFF
   • log_min_duration_statement = -1
   • log_statement_stats = OFF
     Checkpoint: Periodic flush of the cached data to the disk to ensure the durability. This is an IO intense job and cause some performance impact. For larger databases, please consider changing the following two server parameters:
     bgwriter_delay = 20
     checkpoint_completion_target = 0.2
     Autovacuum:
     PostgreSQL uses multiversion concurrency control (MVCC) to allow greater database concurrency. Every update results in an insert and delete, and every delete results in rows being soft-marked for deletion. Soft-marking identifies dead tuples that will be purged later. To carry out these tasks, PostgreSQL runs a vacuum job. More information can be found here.
     "autovacuum_*" server parameters needs to be tuned to be more effective and less invasive.

Check the query performance insight to identify what your longest running queries are, how they change over time, and what waits are affecting them.

Please try the above and let us know the result.

Regards,
Oury