Hi Team,
Greetings !!
We have recently done the migration from Oracle Database Version 12C to Azure PostgreSQL PaaS instance version 11.4 and most of the application functionality testing has been over and tested successfully
However, there is 1 process at application level which is taking approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins.
After investigating further we identified that process which is executed from application end contains 500 to 600 no of short SQL queries into the database. We tried to run the few queries individually on database and they are taking less than sec in Postgres Database to execute, and we noticed that in Oracle taking half of the time as is taking in PostgreSQL. for ex . in oracle same select statement is taking 300 millisecond and in PostgreSQL it is taking approx. 600 millisecond which over increases the execution of the process.
Oracle Database are hosted on ON- Prem DC with dedicated application server on OnPrem and same for PostgreSQL.
We are using below specifications for PostgreSQL
PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage on general purpose tier ) = 8 Core and 40 Gb of Memory
PostgreSQL version - 11.4
We have tried running maintenance Jobs like vaccum, analyze, creating indexes, increasing compute but no sucess
I am happy to share my server parameter for PostgreSQL for more information.
Please let us know if this is expected behavior in PostgreSQL or is there any way i can decrease the time for the SQL queries and make it a comparison with Oracle
Regards,
Mukesh Kumar