Manifacturing
All Case Studies
7 min

Zero-Impact Upgrade on Amazon RDS: PostgreSQL Optimization for IoT in Manufacturing

-30%
Cloud costs
RDS instance optimization with a sharp drop in IOPS and CPU
<30
Minutes
Downtime to migrate 10 TB
-20%
ETL time
Optimized query execution
Context

The customer is a leading international manufacturer that develops and produces advanced air conditioning, refrigeration, and heating solutions. The company relies on a large cloud infrastructure on AWS to manage, in real time, the data flows from thousands of IoT devices deployed worldwide. After an initial setup supported by Miriade, the internal team ran the environment on its own. However, competing business priorities and growing complexity kept postponing the upgrade of the core database until it reached a critical stage: the imminent end of life (EOL) of the version in use.

Problems

Less than six months before the end of official support for PostgreSQL 10, the customer faced a risky choice: accept a forced, automatic upgrade by AWS, with an almost certain risk of severe performance degradation and service interruptions, or orchestrate an urgent, tightly controlled migration. The technical challenges for IT were considerable and included:

  • volume and critical resources: a monolithic database of about 10 TB, hosted on a large RDS instance (32 vCPUs, 128 GB of RAM);
  • uninterrupted IoT traffic, with hundreds of connected devices and complex patterns of bulk reads and targeted writes;
  • minimal downtime: strictly less than one business day, with no room for error;
  • no option at all to rewrite the application, change the query logic, or create new indexes to adapt them to the new engine.
Solution

To neutralize the risks, Miriade designed a "Zero-Impact" migration and tuning strategy. The core of the work was an advanced staging environment, engineered to faithfully simulate the load on the production system. By capturing real sessions from the live environment, the team replayed the workload on the new database, upgraded to PostgreSQL 14, inside a protected perimeter. This approach produced very precise benchmarks and made it possible to optimize execution plans in advance in the relational engine.

The results were substantial: the optimization sharply cut the execution times of critical queries (for example, from 2 minutes 29 seconds to 36 seconds, or from 20 to 2 seconds) while leaving the source code untouched. This stabilized the application load, reduced resource usage (CPU down from 80% to 58%, IOPS from 25,000 to 15,000), and removed the need to increase the instance size on AWS.

Technologies

To ensure a smooth version upgrade and build load tests that remain valid in the future, Miriade used the following tools and methods.

  • Amazon RDS for PostgreSQL - Business-critical upgrade (from version 10 to 14) of the managed relational database that underpins the customer's entire global infrastructure.
  • Apache JMeter - Open source tool used to orchestrate and automate large-scale load tests, making them repeatable for future upgrade roadmaps.
  • Database Performance Tuning - Advanced techniques for analyzing database behavior, essential to optimize IOPS consumption and read/write performance on IoT data with no application refactoring.