Update of datawarehouse to PostgreSQL 16 with data model restructuring

About project:

Client overview

A leading financial technology company faced critical performance issues with their database system after upgrading from PostgreSQL 11 to 16. The system, handling millions of daily transactions and complex financial data analytics, experienced severe query slowdowns that threatened their operational efficiency and service delivery capabilities.

Tech Stack:

Postgresql 11

Tech stack after migration:

Flat tables on PostgreSQL 16

Time to deliver project:

4-6 weeks

Problem

  • After upgrading PostgreSQL from version 11 to 16, the client experienced a significant decline in query performance, with some queries slowing down by a factor of ten.

Inspection

  • The performance issues were traced to models stored as partitioned tables by date. Each table was further partitioned by a specific feature and then combined using a view. Upon investigation, we discovered that starting from PostgreSQL 12, the handling and optimization of queries to partitioned tables had changed, leading to the observed degradation in performance.

Recommendation

  • It is essential to perform thorough testing of database version upgrades in a test environment that closely mirrors the production setup. This practice can help identify potential performance issues before they impact live operations.

Resolution

We restructured the storage model by converting the partitioned tables into flat tables when migrating to PostgreSQL 16. This change resulted in a remarkable performance improvement, restoring and even enhancing query execution speed.

Similar projects

Do you want
the same one?

Leave a request and our manager will contact you to discuss your project and give an assessment of a similar project.

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Our website use cookies
Read our Privacy Policy.
Order an audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order Black box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order White box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number