Presented by:

Yugundhar is working as Lead Database Migration Consultant with Amazon (AWS ProServe) for 4.6 years, he had migrated over 10 enterprise Oracle/SQL server databases to Amazon RDS Postgres/Aurora Postgres. He is specialised in procedural code conversion, data migration and Performance optimisation. Prior to joining Amazon, Yugundhar had 11 years of experience in database technologies Oracle,Cassandra,ETL Tools and Oracle Business Intelligence.

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

Date:
2023 February 23 - 11:30
Duration:
45 min
Room:
Robusta + Arabica
Conference:
PGConf India, 2023
Language:
Track:
Application Developer
Difficulty:
Medium

Happening at the same time:

  1. Understanding and Fixing Autovacuum
  2. Start Time:
    2023 February 23 11:30

    Room:
    Grand Victoria 1

  3. How we are managing $675B+ assets for world’s most sophisticated asset managers and why we are moving from managed PostgreSQL to self-hosted community PostgreSQL
  4. Start Time:
    2023 February 23 11:30

    Room:
    Grand Victoria 2