The digital landscape of the US is ruled by an absolute principle of speed. In a day and age where people’s attention spans have shrunk to fractions of a second, backend performance is literally a matter of life and death for any software operation. Whether you’re building a high frequency trading system on Wall Street or an agile e-commerce portal in Silicon Valley, poor backend performance will cost you lost sales, lower search rankings, and a steady hemorrhaging of your customers to competing platforms.
Most engineers tend to think of lag and inefficiencies in applications as a pure hardware problem. They think that all that’s needed is better cloud compute instances or some extra memory to make a sluggish server node operate faster. But the truth of the matter is that, in 95% of cases, poor backend performance is caused by architectural flaws in the software application itself.
These inefficiencies include bad programming patterns, poor fetching strategies, and inadequate backend resource management. This article presents an in-depth overview of the most common backend development problems that cause applications to crawl and explains how to solve them.
1. Falling Victim to the N+1 Query Problem
The N+1 query problem can be considered the most common performance bottleneck in contemporary web apps. The issue emerges when developers heavily rely on ORMs (such as Hibernate, Sequelize, or Prisma) without really knowing how these frameworks compile abstract pieces of programming code into SQL statements.
Essentially, an N+1 query problem occurs when a backend application has to fetch parent data alongside a list of records from a correlated child table. For example, consider a blogging platform that needs to retrieve names of authors from 50 most recent blog posts.
The problem is that instead of compiling all necessary data at once using an efficient query, a poorly designed backend will execute the first query to get the blog posts and then proceed with 50 separate calls to the database in order to obtain the profile information for each author in a blog post:
// Example of the N+1 Query Problem:
1 Query to fetch 50 blog posts: SELECT * FROM blog_posts LIMIT 50;
50 Queries to fetch author profiles: SELECT * FROM users WHERE id=post.author_id;
Total: 51 queries (database round-trips)!
The solution to this issue is rather straightforward. All you have to do is to use JOIN queries or .include() options in ORMs to retrieve both parent and children datasets in a single request.
2. Ignoring Database Indexes or Over-Indexing
If there was one thing that can turn your web app into a slow-moving monster, it would definitely be improper indexing of tables. Whenever a search call is executed against a non-indexed field in a database table, the SQL engine has no other choice than to conduct a Full Table Scan. That means scanning through each and every record in the database to find a match.
Even though this process might work fast on 100 rows in the database during local tests, on a production machine with millions of records this approach will freeze the application altogether.
That’s why it is extremely important to create indexes in database fields used in WHERE, JOIN and ORDER BY queries. These indexes serve as fast lookup tables similar to those that you might find in an alphabetized appendix at the end of a book. They allow the SQL engine to quickly locate and return a record based on a B-Tree algorithm.
However, just like with indexes, you should avoid the practice of over-indexing. Each INSERT, UPDATE, and DELETE action on a database table will result in automatic updating of the corresponding index. So, if you unnecessarily put an index on rarely accessed table, you’ll create another point of bottleneck in your backend.
3. Not Employing a Proper Caching Strategy
Whenever your backend application executes a query to calculate or aggregate data that is hardly going to change (such as a global product catalogue or a configuration of a national currency system), you are wasting your processing time. By forcing your relational database to go through the whole process again, you are exposing yourself to a performance bottleneck.
An absence of an efficient caching strategy can become a structural problem in a modern web architecture. In order to solve it, one should add an ultra-fast, in-memory caching layer right in front of a database engine (Redis is a popular option here).
User Request ➞ Check Redis Cache ➞ [Cache Hit]: Return Cached Result in Sub-Millisecond
│
└[Cache Miss]
▼
Check Main SQL Database ➞ Fetch Results ➞ Put Data into Redis Cache ➞ Return Result
By storing the results of calculations in the volatile system memory via Redis, a backend application is able to access the data almost instantaneously. This frees up the main SQL engine to only perform critical write queries and other complex operations.
4. Using Blocking, Synchronous Code for Long-Running Operations
Handling I/O operations efficiently is an art that separates the masters from the amateurs in backend engineering. A very common structural flaw of inexperienced web developers is writing synchronous blocking code for long-running background jobs in their application.
Let’s take a simple example of user account creation that involves storing the new profile in a database and generating PDF receipt and optimizing profile image. As soon as a user completes registration form and hits submit button, the application sends back a confirmation message only after all these actions were taken.
Blocking Code:
Request ➞ Save User ➞ Generate PDF ➞ Call Email API ➞ Respond (Slow Operation!)
Asynchronous Task Queue:
Request ➞ Save User ➞ Dispatch Background Tasks ➞ Respond Instantly
│
▼
Background Job Processor Handles PDF and Email Sending
What you really need to do is to move all of the time-consuming tasks to an asynchronous task processor like Celery, BullMQ, or RabbitMQ. The main web application should instantly execute the request, push slow operations to a background job, and return the successful response to the user.
5. Not Implementing Database Connection Pooling
Each time a database engine establishes a connection, it goes through an elaborate sequence of computational and networking steps that involve allocating memory, authentication, cryptography, etc. Without proper management, a large number of connections will inevitably slow your application down dramatically.
If your backend framework always creates a fresh connection for each HTTP request and then closes it once the response was returned, then your database will face an excessive bottleneck of connection creation and closure operations under a high load of users.
That’s when database connection pooling comes in handy. A connection pool is a mechanism that pre-allocates a set of active connections to your database engine and keeps them in the memory of your server ready to be used.
So, whenever a request hits the server and the data needs to be fetched, it instantly takes an active connection from the pool, runs a SQL query, and sends the connection back to the pool for reuse.
Frequently Asked Questions (FAQ)
1. How can I determine if my backend application suffers from the N+1 query problem?
The easiest way is to inspect the database execution logs on a development/staging machine. Whenever you make a page load (say, opening an order dashboard) and see a cascade of similar SQL queries being executed in the log file, you can rest assured your backend experiences the N+1 query problem.
2. What’s the reason behind poor performance of a database when too many indexes are implemented?
While indexes significantly improve database performance in terms of reading data, they also require additional resources to manage write operations. In fact, each insert, update, and delete call will cause the engine to modify data on disk and update indexes in their structure. Therefore, excessive indexes can lead to major performance bottlenecks for write operations.
3. What’s the difference between database replication and caching?
Database caching means keeping frequently requested objects in an extremely fast volatile system memory (with Redis, for example). Database replication implies duplicating the whole database onto several database server instances that allow read/write operations on certain nodes and reads-only on others.
4. How long should I keep data stored in a backend cache layer?
It is entirely dependant on how often this data is updated. For static data, like countries codes, this period can be weeks or even months. However, dynamic data, like e-commerce inventory count, should be refreshed within seconds/minutes to avoid situations where clients purchase products that are out of stock.
5. What happens when a connection pool exhausts its capacity?
Under such conditions, all incoming requests are automatically placed in the execution waiting queue. If the main process takes too much time to free a connection slot, it will raise a database connection timeout that causes internal errors on the user interface.
