Jul 24, 2022

Reducing Database Round-Trips with Boring Queries

Note: This post is the first part of a series on exploring the benefits and drawbacks of engineering for the application and database layer.

If you’ve ever wondered whether you should build this feature using a database query or as part of your application layer, this post is for you.

If we can choose between fetching just the data we need for the specific use case versus fetching all items, we would probably always choose the former, just because it feels more elegant. But in some cases, this may not be the best decision.

Usually, applications grow over time and we keep adding more and more of those queries: Step by step, we perform more requests over the network, and in most cases, individual queries are fast. If we look at the big picture, though, we find that we send hundreds of queries that operate on the same data set.

Imagine you are building a typical SaaS application that provides a role management system where users can create custom roles. You want to display a list of roles in your application and you need a way to count the number of custom roles to calculate whether service quotas are reached.

Now, you might be drawn to create two functions

getCustomRoles(...): CustomRoles[]
countCustomRoles(...): number

Easy, right? But what if we always load all custom roles in places where we count them anyway? This does not necessarily have to be in the same part of the code but assume we require roles and number of roles in the same transaction.

In that case, you could probably refactor countCustomRoles to use the list of roles instead of sending a COUNT-query to the database, right?

If you think this doesn’t feel right, I totally get you. Aren’t we overthinking this a bit? In no way would simple queries like counting be the issue here, right? Also, it’s super fast on my local machine.

Unfortunately, we have little to no control over the network that sits in between our application and database. If we arbitrarily assume that each database query incurs 5ms of round-trip-time (RTT), sending 100 calls back and forth adds a measurable processing delay.

If your application relies on a substantial amount of database requests, strategies like reusing what’s already available in the application layer, even if it means fetching more data ahead of time, can help reduce network delay.

Of course, this approach is not without drawbacks: The more data you fetch, the larger the memory footprint grows. Even when data is kept just for the duration of a transaction, changes that affect cached data must be propagated properly to avoid inconsistencies, so you have to handle cache invalidation.

Apart from performance, caching data in the application layer adds more complexity, so you should critically evaluate whether you’ve reached the point where shaving off milliseconds justifies adding complexity that will slow you down. On the flip side, there usually are many small improvements that go a big way in terms of performance gains.

Continue reading

If you enjoyed this post, you might also like the following content.

Collections containing this post

iconBruno Scheufler
Postgres In-Depth

Here’s to the crazy ones. The misfits. The rebels. The troublemakers. The round pegs in the square holes. The ones who see things differently. They’re not fond of rules. And they have no respect for the status quo. You can quote them, disagree with them, glorify or vilify them. But the only thing you can’t do is ignore them. Because they change things. They push the human race forward. And while some may see them as the crazy ones, We see genius. Because the people who are crazy enough to think they can change the world, Are the ones who do.

1

Well, today we’re introducing three revolutionary products of this class. The first one is a widescreen iPod with touch controls. The second is a revolutionary mobile phone. And the third is a breakthrough Internet communications device.

2

Your time is limited, so don’t waste it living someone else’s life. Don’t be trapped by dogma — which is living with the results of other people’s thinking. Don’t let the noise of others’ opinions drown out your own inner voice. And most important, have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.

3
iconBruno Scheufler
Designing and Scaling Distributed Systems

Here’s to the crazy ones. The misfits. The rebels. The troublemakers. The round pegs in the square holes. The ones who see things differently. They’re not fond of rules. And they have no respect for the status quo. You can quote them, disagree with them, glorify or vilify them. But the only thing you can’t do is ignore them. Because they change things. They push the human race forward. And while some may see them as the crazy ones, We see genius. Because the people who are crazy enough to think they can change the world, Are the ones who do.

1

Well, today we’re introducing three revolutionary products of this class. The first one is a widescreen iPod with touch controls. The second is a revolutionary mobile phone. And the third is a breakthrough Internet communications device.

2

Your time is limited, so don’t waste it living someone else’s life. Don’t be trapped by dogma — which is living with the results of other people’s thinking. Don’t let the noise of others’ opinions drown out your own inner voice. And most important, have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.

3