DEV Community

Cover image for Turbocharge Your App: Slash DB Calls with Raw SQL Magic!
Chidiebere Ogujeiofor
Chidiebere Ogujeiofor

Posted on • Updated on

Turbocharge Your App: Slash DB Calls with Raw SQL Magic!

Introduction

In my experience building backend applications, one of the key factors in improving performance is reducing the number of I/O operations—such as database calls and external API requests—your app makes. Every I/O operation adds latency and can bottleneck your application's overall performance.

Of all I/O operations, database queries are the most common, and if not optimized, can severely slow down your app. In this series of articles, I will explore common scenarios where an application might make multiple database calls unnecessarily and demonstrate techniques to optimize those operations.

We will use a PostgreSQL database for the examples, but the techniques discussed can be applied to any SQL-based database.

Articles in the series

In this series, I dive into two key scenarios where we often end up making more database queries than necessary—and how to optimize them.

Scenario 1: Multi-Row Updates with Unique Values

When updating multiple rows with different values, we usually fire off individual update queries for each row, which can slow things down. In this article, I break down the issue and show how to tackle it more efficiently.

Scenario 2: Backfilling Data for New Columns

Adding a new column to a table with existing data? Backfilling that column can be a challenge. In this article, I demonstrate a raw SQL approach that simplifies the process.

Sample Data

For our examples, we will use a simplified schema of a ticketing application. The app allows event owners to create and manage events, while users can purchase tickets through an API.

Here are the sample tables we'll work with:

Users Table

Fields Type Constraints
first_name text
last_name text
id VARCHAR(30) PK

events Table

Fields Type Constraints
title text
owner_id text FK -> Users
id VARCHAR(30) PK

tickets Table

Fields Type Constraints
event_id text FK -> events
id VARCHAR(30) PK
title text
amount INT
quantity INT
quantity_sold INT

quantity: represents the number of that tickets that is available at the beginning. This would be like the number of seats in a concert
quantity_sold: is the number of tickets that has been sold.

See you in the next article

Let's explore the first type of problem in the next article.

Alt text of image

Top comments (0)