Slow API? How to optimize N plus 1 queries in PostgreSQL

What is the N plus 1 query ghost and why your API suffers

Imagine going to a supermarket and, instead of grabbing all your groceries at once in a cart, you ask for a single apple, walk to the cash register, walk back, ask for another apple, and repeat that process a hundred times. That is precisely what happens when a software system suffers from the notorious N plus 1 query problem in databases. In practice, this means that to list ten clients and their respective orders, the system executes one query to fetch the clients and then runs ten separate queries—one for each client—to retrieve the matching orders. As data volume grows, this silent multiplication of requests saturates database connections and turns a fast response into an agonizing wait.

Many development teams only notice the damage when the application hits production and response times skyrocket under real user load. The main culprit behind this behavior is usually the ORM (Object-Relational Mapping), a tool designed to translate database tables into objects understandable by the programming language. While ORMs make everyday coding much easier, they create an illusion of comfort. Developers write a single line of code to read data while ignoring what happens behind the scenes. To better understand how to design efficient databases and prevent similar bottlenecks, be sure to read our article on how to optimize slow queries with composite indexes.

How ORMs hide the problem behind lazy loading

The concept of lazy loading acts like a waiter who only brings dessert if you explicitly ask for it at the end of the meal. In programming, an ORM uses this strategy to avoid loading related data—such as a user's addresses—unless the code explicitly accesses that property. At first glance, this seems like a great optimization idea because it saves memory and time on the initial query. However, in practice, the mechanism turns against the system the moment you iterate over a list of records and try to read the dependent data for each item, triggering dozens or hundreds of individual SQL queries in a chain.

This trap is dangerous because the code looks clean and harmless at first glance. If you test the application on your local machine with just three test records, the page will load instantly, masking the issue. Yet, when the system goes live with thousands of active users, the database gets bombarded with unnecessary calls that exhaust the connection pool. To mitigate this type of architectural failure and maintain system stability under pressure, it is also essential to understand how to speed up PostgreSQL queries using partial indexes for specific scenarios.

Solving the bottleneck with join fetching and eager loading

The primary weapon to combat the N plus 1 problem is join fetching, also known as eager loading. Instead of fetching data in separate steps, this technique instructs the database to bundle the information into a single structured response using join commands. In practice, PostgreSQL can cross-reference the customer table with the order table in a single highly optimized read operation, leveraging indexes to deliver everything at once to the application.

Implementing this approach requires only a minor adjustment in how you instruct the ORM to fetch data. Here is a conceptual example of how to load relationships beforehand:

# Conceptual example using eager loading in Python/SQLAlchemy
users = session.query(User).options(joinedload(User.orders)).all()
for user in users:
    print(user.name, len(user.orders))

With this simple directive, the ORM generates a single SQL statement containing the necessary join, eliminating repetitive queries and reducing response time from seconds down to a few milliseconds.

The role of entity caching to eliminate unnecessary database trips

Even after optimizing queries with joins, there are scenarios where the system repeatedly looks up the same static data or information that changes very little throughout the day, such as product categories, system configurations, or user roles. In these cases, entity caching acts as an additional protection layer. Caching stores these objects directly in the application's RAM, allowing subsequent requests to retrieve the information instantly without touching the database server's disk.

Implementing efficient caching requires careful attention to data invalidation to prevent users from seeing outdated information after an update. Industry tools help manage this retention automatically, but the core principle remains the same: keep close to the application what is queried all the time and rarely modified. This strategy perfectly complements the improvements made to SQL queries and completes the performance optimization cycle. To explore these architectural concepts further in production, see our related technical analysis on Why models hallucinate and how to build RAG guardrails.

Final considerations on performance and best practices in PostgreSQL

Optimizing queries in a relational database like PostgreSQL is an ongoing exercise in vigilance and understanding application behavior. The N plus 1 problem ceases to be a mystery once the team adopts the habit of inspecting the SQL logs generated by the framework during development. Combining the conscious use of join fetching, caching strategies, and solid index modeling ensures that your API remains fast and responsive even as the database grows exponentially over the years.

Investing time in reviewing these queries prevents unnecessary infrastructure costs and delivers a much smoother experience for end users. Adopting these practices from the start of a project turns software stability into a solid and sustainable competitive advantage.