Application Tuning on Amazon RDS: PostgreSQL Queries Under 10 ms and 50% Lower Cloud Costs for IoT in Manufacturing
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, with its core database hosted on Amazon RDS for PostgreSQL.
As application traffic grew, some classes of queries generated dynamically by the application began to show erratic response times and load peaks.
In the cloud, longer response times and higher CPU and RAM usage translate directly into higher spending to give users the same performance. Tuning is not limited to rewriting queries or adding indexes: it also requires hands-on knowledge of the database engine, in this case PostgreSQL (similar reasoning applies to Oracle).
The queries generated by the application were affected by:
- Continuous re-planning: every execution of a query, even with the same structure and only different parameters, was parsed and re-planned from scratch by PostgreSQL, with a recurring and unnecessary computational cost.
- Unstable response times: the average query duration was around 43 ms, with sharp peaks concentrated in specific time windows during the day, matching the periods of heaviest IoT traffic.
- Targeted change: parameterized queries are prepared once per session with PREPARE, and a generic plan is forced (plan_cache_mode = force_generic_plan).
- Faster queries: the average time dropped from 43 ms to under 10 ms, and load peaks were eliminated in the windows of heaviest IoT traffic.
- Costs cut in half: the RDS instance moved from db.m5.8xlarge to db.m5.4xlarge, with a 50% reduction in compute cost.
- Zero impact on SQL code: no changes to the query logic and no new indexes.
Instead of changing the query logic, the Miriade team worked at the point where the application sends SQL statements to the database, introducing an explicit PREPARE statement immediately before the parameterized queries run.
In practice, each query is "prepared" only once per session (PREPARE … AS …), and the plan caching mode is forced to the generic plan (plan_cache_mode = force_generic_plan). From then on, every subsequent EXECUTE of the same query, even with different parameters, reuses the execution plan already computed instead of being parsed and re-planned from scratch.
The measurements confirm the effect of the change: on a device data read query (get_variable_details), the first execution after PREPARE took about 25 ms, while all subsequent executions, with different parameters, dropped to about 1.1–1.4 ms.
Across the entire application workload, the change cut the average query duration from 43 ms to under 10 ms, and the monitoring dashboards, which had previously shown sharp and recurring load peaks, recorded virtually no load in the same time windows.
The recovered resource headroom made it possible to downsize the production instance from a db.m5.8xlarge to a db.m5.4xlarge, halving the compute cost.
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.
- Application-level query plan caching (PREPARE / plan_cache_mode) - An application-side tuning technique that eliminates repeated query re-planning, with no changes to SQL logic and no new indexes.
See also: for a customer in the HVAC industry, Miriade also managed the PostgreSQL upgrade from version 10 to 14 on Amazon RDS, migrating 10 TB with less than 30 minutes of downtime. Zero-Impact Upgrade on Amazon RDS: PostgreSQL Optimization for IoT in Manufacturing