Finding out where the time actually goes before spending money on hardware

Overview

A retail technology provider had seen response times drift upward over eighteen months with no single change to blame. The proposed remedy was a hardware refresh. AceMQ was engaged to determine whether the hardware was actually the constraint.

Challenge

Gradual degradation rarely has one cause. Query plans had changed as data volumes grew, several heavily updated tables carried significant bloat, and the application opened far more connections than the server had the memory to service efficiently. Separating these effects required measurement rather than inspection.

Environment

PostgreSQL across on-premises and cloud hosts, backing order management and inventory services.

Approach

AceMQ used cumulative statement statistics to rank queries by total time contributed rather than by individual duration, which redirected attention from the slowest query to the ones that actually consumed the cluster. Bloat was measured directly, and connection behavior was profiled at the pooler rather than assumed from application configuration.

Solution

• Ranked query shapes by total execution time contributed, exposing the high-frequency queries that dominated load
• Measured actual table and index bloat and identified the update patterns and blocked vacuum runs behind it
• Reviewed index coverage, redundant indexes, and indexes never chosen by the planner
• Profiled connection counts and pooler behavior against work_mem and shared_buffers sizing
• Evaluated planner statistics targets and autoanalyze coverage on the tables with unstable plans
• Delivered a prioritized findings report separating configuration fixes, schema work, and genuine capacity limits

Outcome

Technologies