How Midwest Tape achieved a 10x performance boost with PostgreSQL tuning on AWS RDS

Midwest Tape, a top US-based media distributor serving the public library sector, offers a digital content service, called Hoopla, to library patrons. Libraries using Hoopla use available APIs to download Hoopla content metadata to their library ILS systems. Midwest Tape uses a dedicated PostgreSQL replica to provide that metadata to libraries and due to the high utilization of those APIs by its library customers, was having serious performance issues on that database instance. Midwest Tape used DBtune for AWS RDS PostgreSQL performance tuning to resolve database query bottlenecks and achieved a 10x performance improvement using it. Quick query responses and highest availability are key to Hoopla's business success.

Within 4 hours and with zero downtime, the DBtune AI agent analyzed Hoopla's production workload and automatically tuned the database instance to achieve a 10.8x performance improvement in query response time and reduce PostgreSQL high CPU risk under peak load.

The challenge: PostgreSQL high CPU and slow query response times

Midwest Tape was managing a complex environment of AWS RDS for PostgreSQL database clusters running production workloads. Their critical "Partner" database replica, which serves Hoopla library customers, was struggling with a baseline average query runtime (AQR) of 75.9 ms, significantly impacting the speed to download Hoopla metadata. These performance issues were a risk to library users and highlighted the need for a different approach to database tuning beyond manual adjustments.

Solution: Sophisticated AWS RDS PostgreSQL performance tuning with automated analysis

The database team deployed DBtune to analyze the workload running on a large 48 vCPU production server on AWS RDS for PostgreSQL. By monitoring performance data in real time, the DBtune AI agent, under the database administrator's supervision, identified the root causes of performance degradation. It then went beyond recommendations and automatically implemented precise, effective configuration changes, optimizing the PostgreSQL database for Midwest Tape's specific production replica partner workload. The goal for the PostgreSQL tuning process was clear: make the database faster and more efficient, targeting AQR as the optimization metric.

The tuning session focused on maximizing performance with minimal operational overhead. Over a span of 4 hours of total tuning time, DBtune made 16 sets of parameter adjustments to the database configuration. Crucially, these changes were deployed using server reload-only tuning, meaning no downtimes were required, allowing the critical production system to remain fully operational throughout the process.

Results: 10x faster query response times after PostgreSQL performance tuning

The tuning session led to immediate and compelling results.

Before DBtune, the baseline AQR for the database stood at 75.9 ms — which was an immediate threat to customer satisfaction. After the four-hour tuning session, DBtune's automated adjustments drove the AQR down to an impressive 7 ms.

This represents a 10.8x improvement in performance, directly enhancing the speed and responsiveness of the Hoopla services relying on this database. Furthermore, the measurement stability of the observed configurations was confirmed to be highly consistent and trustworthy, ensuring the sustained reliability of the changes made by DBtune.

"Seeing the immediate positive impact from the tuning session was a game-changer. We see a large improvement in query performance, around 10x," said Josh Lorenzen, Senior Database Developer at Midwest Tape. The performance improvements are being driven by the speed-up of two important queries that make up the majority of the workload. Looking a little closer at the parameter changes, DBtune identified and tuned key server parameters that seem to have had a large impact, including random_page_cost and max_wal_size, to better balance I/O, WAL behavior, and CPU usage during peak demand. Josh added, "DBtune quickly identified and delivered these optimizations giving us improved stability and efficiency on one of our most critical production instances."

Going forward: Proactive PostgreSQL performance tuning across production and staging

Midwest Tape has expanded its DBtune coverage across both its entire production database fleet and staging environment. This comprehensive deployment across both production and staging environments highlights the value of proactive database health management for unlocking new levels of performance and reliability across the software lifecycle.

"DBtune is a strategic part of our engineering team — knowing our database performance is expertly and automatically managed by DBtune frees the team to focus on core product innovation," said Victor Del Corral, IT Director of Midwest Tape.

Start optimizing today

Ready to experience predictable high performance for your PostgreSQL fleet? Start your free trial of DBtune today!

About Hoopla Digital

With a mission to serve public libraries and their patrons, Hoopla is the only all-in-one app that provides online and mobile access to millions of eBooks, audiobooks, comics and manga, music, movies, TV, and more with BingePass. Available in 115+ world languages, content on Hoopla is available for free with a valid library card. Hoopla is the pioneer of the pay per use model that allows library patrons to borrow content immediately, no waits or holds required. Headquartered in Holland, Ohio, Hoopla is a service of Midwest Tape, a trusted partner to public libraries for over 35 years. For more information, or to download the Hoopla app, visit hoopladigital.com.

Frequently Asked Questions (FAQ) about PostgreSQL performance tuning

Q: Who is Midwest Tape, and what is Hoopla?

A: Midwest Tape is a US-based company and the developer of Hoopla, a popular digital media service. Hoopla provides over a million daily users with access to digital content through their local library.

Q: What was the main performance issue Midwest Tape was facing?

A: The primary challenge was severe and prolonged PostgreSQL high CPU usage. This performance bottleneck posed a direct risk to Hoopla library customers.

Q: What AWS RDS for PostgreSQL environment was tuned?

A: A production database replica running on Amazon RDS db.r6g.12xlarge with PostgreSQL version 14.17. This instance has 48 vCPUs and 412 GB of memory.

Q: What was the measurable result of the DBtune tuning session?

A: DBtune delivered a 10.8x performance speedup in query runtime by focusing on the most important queries and automating AWS RDS PostgreSQL performance tuning. The average query runtime (AQR) was dramatically reduced from a baseline of 75.9 ms to a best-found AQR of 7 ms. This improvement was primarily achieved by optimizing the two most important queries in the workload.

Q: How long did the tuning process take, and was there any downtime?

A: The successful tuning was achieved over about 4 hours of total tuning time. Importantly, the process was entirely automatic and no downtime was required for the production system.

Q: How does DBtune optimize PostgreSQL query performance automatically?

A: DBtune uses an intelligent engine to analyze the database's specific workload and performance metrics. It identifies bottlenecks and then automatically implements precise, data-driven configuration changes to tune the database for resource efficiency and stability.