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.
Top comments (0)