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.