Why are application performances often limited by the database?

When an application starts slowing down, the first instinct is often to look for a problem in the application code. However, the real culprit is often the database.

An application can have perfectly optimized code, but a few poorly designed SQL queries can quickly become a bottleneck.

We will look at the most common problems and the best practices to solve them.

The database is not magic

A database stores data, but above all, it has to scan, filter, and organize it before it can return results.

When we write a query and it works, we tend to move on to something else.

However, a database has to process the data before returning a result. Depending on the size of the tables, a query may need to read thousands or even millions of rows.

The question is not only: "Does my query work?"

The real question is: "How will the database execute this query?"

N+1 queries

The N+1 problem is probably one of the most common mistakes in applications using an ORM.

Let's take a simple example. A page displays a list of users with their orders. First, we retrieve the users:

SELECT *
FROM users;

Then, for each user:

SELECT *
FROM orders
WHERE user_id = 1;

SELECT *
FROM orders
WHERE user_id = 2;

SELECT *
FROM orders
WHERE user_id = 3;

If the page displays 100 users, we execute:

• 1 query to retrieve the users;
• 100 queries to retrieve their orders.

A total of 101 queries.

The problem is that each query has a cost: connection, SQL parsing, data access, network transfer...

The solution is often to load the required data with a single query:

SELECT *
FROM users
JOIN orders ON orders.user_id = users.id;

or to use the eager loading mechanisms provided by ORMs, or implement them yourself.

Missing indexes

An index allows the database to quickly find information. Without an index, a search may require scanning the entire table, which can have a significant impact on performance (and on the required resources, which have a cost...) when there is a lot of data.

Imagine a table containing 50 million users:

SELECT *
FROM users
WHERE email = 'user@example.com';

Without an index on email, the database may have to check every row, meaning 50 million rows!

With an index:

CREATE INDEX idx_users_email
ON users(email);

The search becomes much more efficient.

However, indexes are not free:

• they take disk space;
• they slow down some write operations;
• they must be maintained when data changes.

Adding indexes everywhere is therefore not a solution, and in extreme cases can even become more harmful than having no index at all.

Unnecessary queries

Another common source of problems is retrieving more data than necessary.

For example:

SELECT *
FROM products;

This query retrieves all columns.

But if the application only displays the name and price:

SELECT name, price
FROM products;

It is better to request only the required information.

The problem becomes even more important with:

• columns containing large text fields;
• images stored in the database;
• complex relationships.

Every unnecessary piece of data consumes memory, CPU time, and bandwidth.

Pagination with OFFSET

Pagination seems simple:

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

We request the 20 elements after ignoring the first 100,000.

The problem is that the database often has to process those 100,000 rows before it can return the result. The further we go through the pages, the more performance decreases.

An alternative is to use cursor pagination:

SELECT *
FROM posts
WHERE id < 500000
ORDER BY id DESC
LIMIT 20;

The database can directly use the index to find the desired position.

This approach is particularly suitable for large tables.

ORMs sometimes hide complexity

ORMs have greatly simplified development.

Instead of writing:

SELECT *
FROM users
WHERE id = 42;

we often use:

$user = User::find(42);

This is convenient, but it can also hide what is actually happening.

A single line of code can trigger:

• multiple SQL queries;
• complex joins;
• automatic relationship loading;
• unnecessary queries.

A developer may therefore feel like they are writing simple code while the database is receiving a significant workload.

Understanding the SQL generated by your ORM remains essential, but it requires practice and experience.

Analyzing SQL execution plans

When a query is slow, you should not guess (Personally, I often guess wrong...). You need to observe.

Most databases provide a tool to analyze query execution.

For example with PostgreSQL:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user@example.com';

The result shows:

• whether an index is used;
• how many rows are scanned;
• which operations take the most time;
• whether the database performs an expensive sort.

A query that looks simple can sometimes reveal a significant problem.

Performance does not only come from code

Optimizing an application does not only mean writing fewer lines of code or using a faster language (even though this can sometimes help).

A large part of performance depends on:

• data structure;
• executed queries;
• available indexes;
• amount of data being processed.

A slow application does not necessarily need a more powerful server. Sometimes it simply needs a better-designed SQL query.

Some best practices

Some rules help avoid many problems:

• analyze slow queries using the tools provided by the database;
• monitor queries generated by ORMs;
• avoid N+1 queries;
• add indexes adapted to the queries that are actually used;
• only retrieve the required columns;
• prefer cursor pagination for large amounts of data;
• test performance with realistic data volumes.

And the elephant in the room is the use of caching!

Conclusion

Databases are often the heart of applications, containing the application's central data, but they can also quickly become their main performance bottleneck.

With the right practices, a large part of these problems can be avoided.

Now, get to work 🙂