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
The assessment showed the constraint was query and bloat behavior rather than hardware, and the refresh was deferred. Addressing the top-ranked findings brought response times back toward their earlier baseline without new capacity.
Technologies
PostgreSQLPgBouncer
Prometheus
Linux
Kubernetes
Grafana
