Reducing Latencies: Cutting Database Load by 72%
optimization
database
postgresql
TL;DR
Optimizing database performance by adding indexes, extending materialized view refresh rates, and caching frequently accessed queries resulted in a 72% increase in efficiency and reduced costs. These changes collectively decreased database load and improved user experience.
Introduction
As NodeFlair grows and weāre seeing a 30% month-over-month increase in user sessions, weāre running into performance issues that are affecting the user experience.
Join me as I dive into the problem and show you how I fixed it with just three simple tweaks.
š Investigation
Database connections remains the same | ![]() |
Freeable memory (RAM) remains the same | ![]() |
DB Load is increasing! | ![]() |
When weāre using the db.t4g.medium model
with its 2 vCPUs, weāve got to keep the database load under control. If it goes over that limit, performance takes a hit. So, letās dive deep and pinpoint which queries are pushing the load up.
Hereās how I reduced the DB Load
One thing I love about Amazon RDS is that it includes free Performance Insights, which allows us to delve into the top SQL queries contributing to the database load.
After experimenting with it a bit, I now have a good idea of some areas where I can optimize.
1ļøā£ Adding Database Indexes
We had a query relying on a few columns for joins and orderingācolumns that werenāt indexed. Adding those indexes cut the database load by 10% (from 2.2 to 2.0). Itās a small tweak, but hey, itās a solid win right out of the gate!
2ļøā£ Reducing Frequecy of Materialized View Refresh
NodeFlairās salary data comes from a bunch of sources, and leveraging a materialized view was a game changer for speed. It did wonders by pre-computing the data.
But hereās the kicker: as our data points grew to millions, the refresh process started taking up to 6 minutes. Thatās bonkers, especially since weāre refreshing every 10 minutes!
So, I had to askādo we really need this refresh frenzy every 10 minutes? Sure, almost-real-time data sounds great, but the reality is, the data doesnāt shift that much every 10 minutes. Stretching the refresh to 60 minutes doesnāt really hurt the user experience.
Hereās the bonus: by slowing down the refresh rate, weāre also cutting down on query time and database load when not refreshing.
This is because refreshing involves:
- Locking tables, which blocks other queries and creates wait times.
- Heavy I/O operations, which lead to disk contention and slower performance.
Overall, this tweak dropped our database load from 2.0 to 0.7!
Itās a solid fix and we can definitely call it quit, but since weāre all about optimization, why not dig deeper and find more ways to scale better for the future?
3ļøā£ Caching Frequently Queries
We put the final touches on our performance boost by caching frequently accessed but rarely updated values. No need to hit the database every time.
With Ruby on Rails, weāve got the power of low-level caching and Redis on our side. Fetching from Redis is lightning-fast compared to hitting Postgres. This tweak not only streamlined the process but also enhanced the UX significantly.
The result? We shaved off time from 0.7 to 0.4 seconds.
Wrapping Up: Efficiency Boosts at Lower Cost!
In just one day, we cut the average database load from 2.2 to 0.4 ā talk about a 72% boost!
Hereās what that means:
Weāve slashed database latencies, giving users a smoother experience. Latencies for landing pages now reliably sit around 600ms, with dips to 400ms during quieter times (check out how we nailed it under 300ms by using using Cloudflare CF-IPCountry).
Even better, our database usage dropped by 8% instead of the feared 16% increase, despite a 30% jump in web sessions! Thatās because weāre now using t4g DB instances, which only charge us when we go over the baseline (2vCPU in our case). Say goodbye to wasteful charges!