Checkout the slides

Objective: Depicting & Trouble-shooting challenges for Procedural Code of PostgreSQL and performance bench-marking. Rearchitecting techniques applied, specially in heterogenous migration from various source databases as Oracle/SQLServer. Following are scenarios which improved on performance responses to application, by enlightening what PostgreSQL do best at query Optimiser plan decisions.

PL/pgSQL Conversion Scenarios:

1. Low response/performance when function having too many loops of executing SQL: When procedural code converted from SQL server, the slowness is observed in PL/pgSQL code when looping and executing complex SQLs over 10000+ times. This code is refactored/re-designed in project to execute SQLs in batch mode using Array of inputs/batch mode for improvement.
2. PL/pgSQL inline function calling performance slow (SELECT clause having another user function call): We had observed very low performance on hitting in such inline SQL function calls which outer query executing 1000+ iterations. We did code refactoring breaking logic into SQL queries with help of Analytical functions in batch mode for performance improvement.
3. Postgres PL/pgSQL code has limited support on FULL JOIN functionality: SQL is not supporting OR condition with full outer joins” , converting SQL with this functionality become blocker. We fixed the issue by driving deep on data algorithms and data combinations of output, redesigning logic with operators and analytical/group functions.
4. Postgres index performance improves in API query cases using covering index on sub-second SLAs
5. Function code causing CPU spikes, we observed CPU got spiked in API calls with high concurrency of 100 users where IF condition branching pattern is the root cause. Further we fixed refactoring logic.

In above cases we will walk through Postgres Explain Plan details. Thank you