Skip to main content
Unlisted page
This page is unlisted. Search engines will not index it, and only users having a direct link can access it.

N+1 Requests to DB Queries or API Endpoints

N+1 requests to DB queries or API endpoints​

Context: Some requests/queries can spawn multiple more (sometimes an unpredictable number) based on the response data, leading to awkward and arbitrary slow app performance. This can have a knock-on-effect of creating the Socket Exhaustion issue mentioned previously.

In the simplest forms, it can generally fall into 3 types:

  1. Fetch Keys, And ForEach Row
  2. Scattered Fetch
  3. Fetch Item, Check For More

Case 1: Fetch Keys, And ForEach Row​

# Query for a user's shopping cart
cart = db.query("SELECT user_id, item_ids FROM carts WHERE user_id = ?", user_id)
item_ids = cart['item_ids']  # e.g., [101, 102, 103, 104, 105]

items = {}
for item_id in item_ids:
    # Bad: 1 cart query + 5 individual item queries
    item = db.query("SELECT * FROM items WHERE id = ?", item_id)
    items[item_id] = item

return items

This is the easiest to solve

# Query for a user's shopping cart
cart = db.query("SELECT user_id, item_ids FROM carts WHERE user_id = ?", user_id)
item_ids = cart['item_ids']  # e.g., [101, 102, 103, 104, 105]

# Good: Just 2 queries total
items = {}
all_items = db.query("SELECT id, name, price FROM items WHERE id IN (?)", item_ids)
for item in all_items:
    items[item['id']] = item

return items
Memory Sawtooth

Both the bad and good code samples are prone to the Memory Sawtooth issue, discussed in the next section.

Case 2: Scattered Fetch​

When a data store is fetched many times individually due to the code that initiates the request being scattered over many files. This is very common in templates, for example:

<!-- Main blog listing page -->
{% extends "base.html" %}

{% block content %}
  <div class="blog-posts">
    {% for post in posts %}
      {% include "partials/post_card.html" with post=post %}
    {% endfor %}
  </div>
{% endblock %}
Total for 10 blog posts

41 queries

The queries are scattered across three template files that include each other, making the N+1 problem difficult to spot during code review, but also hard to fix when the data architecture of the app might not be able to fetch all data in the controller and feed it to the template.

This is not limited to templates. As an example, this issue can also exist for translation layers, especially when tied into a distributed translation tool that can be updated through a web app instead of a hard-coded deploy.

Case 3: Fetch Item, Check For More​

In principle this can be thought of like a linked list: you have to fetch a node before you know if there are more nodes to fetch. In practice it can more commonly be found in trees or graph structures.

Regardless if the data is intentionally structured as a tree or not, they can pop up in all sorts of places: File system navigation, menus, permissions systems, customer referral chains, discount pricing rules, workflow engines, URL redirection, cache invalidation cascades, content recommendations, payment and invoice tracking, feature flags, webhooks indirectly (or non-obviously) chained together, data sync tools, etc...

Cyclical Graphs

If you have any logic in the shape of "look up the next item based on this item", you must take care to avoid infinite lookup loops. This can be designed out from the logic, but as a further safeguard should be protected against by the lookup code by keeping track of visited nodes. If your data points to a node your code has already visited, you very need to avoid a secondary lookup.

In this example the infinite lookup cycle is clear, Alice looks up Bob, Bob looks up Charlie, Charlie looks up Alice, Alice looks up Bob... and we go until the program crashes. That, or a little array that tracks what IDs we visited stops us.

def get_comment_thread(comment_id):
    """
    Build a comment chain by walking up parent references.
    For a comment nested 8 levels deep, this creates 8 queries.
    """
    thread = []
    current_id = comment_id
    
    while current_id is not None:
        # Each iteration = 1 database query
        comment = db.query(
            """SELECT id, author_id, content, parent_comment_id, created_at 
               FROM comments WHERE id = ?""",
            current_id
        )
        
        # Need author name too - another query!
        author = db.query(
            "SELECT name FROM users WHERE id = ?",
            comment['author_id']
        )
        
        thread.insert(0, {  # Insert at beginning to maintain order
            'author': author['name'],
            'content': comment['content'],
            'created_at': comment['created_at']
        })
        
        current_id = comment.get('parent_comment_id')  # Walk up the chain
    
    return thread

thread = get_comment_thread(comment_id=9547)
# Result: Parent comment → Reply → Reply to reply → ... → Target comment
# Queries: 16 (8 for comments + 8 for authors)

How the app behaves when you have this issue​

Any one of these issues is a good indicator you can be suspicious of this defect.

  • Content loads piece by piece, "popping" or "teleporting" in as the page loads.
  • Linear slowdown with item count. If your page has 10 items and takes 1 second, 100 items takes 10 seconds, and 1000 takes 100 seconds.
  • It feels fast for simple cases but slow for complex ones
  • Can't recreate locally (using small data or data that is not configured with enough reference look-ups to create the issue)
  • Can't recreate locally (system you run lookups against is on the same machine, introduces no network delay, or a mocked response)
  • Everything worked fine and suddenly it breaks despite no code changes (the data pattern changed based on users adopting a new use-case you hadn't tested for).
  • New users don't have the issue but long-time users or power users have it the most
Sockets & Sawtooths

This issue can easily generate Socket Exhaustion (previous section) Memory Sawtooths (next section). You often only need to improve one to resore the service, but do make space to fix both in your near-term plans.

If you have detailed production monitoring, you may also notice CPU and Network usage for these requests is not a single block but scattered through the timeline like fine recurring slivers inside a larger logical block.

Not just databases​

Do not be tricked into thinking this n+1 issue is limited to databases, it could be orchestrated over many attached resources like redis, a database, and microservice REST API working together to create this problem.

Imagine this hybrid example which uses a DB query and API calls:

# Single DB call creates N API calls
def sync_user_permissions():
    teams = db.query("SELECT * FROM teams WHERE sync_enabled = true")  # 1 DB query
    
    for team in teams:
        members = team.member_ids  # Already in memory
        for member_id in members:
            # N API calls to external auth provider
            response = requests.post("https://auth.example.com/api/v1/permissions", 
                                    json={"user_id": member_id, "team_id": team.id})

It can be harder for teams to identify these as many development teams commonly think of N+1 issues as something purely related to database queries, but the request pattern is still here from the application's perspective regardless of the backing technology.

How to fix it​

In general there are only 2 solutions:

  1. Optimise the query
  2. Introduce a key-cache

There is a third option of optimising the data, but restructing data in the event of a trauma recovery operation is ill-advised and better suited to normal development work. Similarly, introducing a value-cache at the last minute can introduce unexpected stale data problems that generate new defects.

Optimise Queries​

Refactoring the query to select all items in one shot sounds ideal but it often contains some risks with changing the returned query data, as well as not always being possible without adding new fields, which further implies data migrations and other heavier work not suitable to a trauma recovery situation.

WHERE id IN (?)​

In many cases, simply using IN in SQL or the equivilant in your ORM of choice solves this:

items = db.query("SELECT * FROM items WHERE id IN (?)", item_ids)
# Often this means refactoring some code to move the `id == ?` out of the for-loop
Use a data-loader pattern to delay the actual request:​

If you do not need the data immediately but refactoring the code is too big of a challenge, you can replace your queries with an abstraction which simply keeps track of which IDs you wish to query.

Imagine this use-case and pretend we did not want to refactor the code too heavily, and also knew we didn't use the results inside the loop (that's asking for a lot, but let's go with it):

# This is a bad implementation with many queries
def show_posts_bad():
    # Get all posts
    posts = db.query("SELECT id, title, author_id FROM posts LIMIT 10")
    
    for post in posts:
        # Query database for EACH author - 10 separate queries!
        author = db.query("SELECT name FROM users WHERE id = ?", post.author_id)
        print(f"{post.title} by {author.name}")

Here we can add a DataLoader pattern to collect IDs and let python query them in batches for us.

# There are better ways to implement this, for example purposes only
class SimpleDataLoader:
    def __init__(self):
        self.queue = []  # Collects IDs
        self.cache = {}  # Stores results

    def load(self, user_id):
        """Add ID to queue - doesn't query yet"""
        self.queue.append(user_id)
        return user_id  # Just return the ID for now

    def execute(self):
        """THIS IS WHERE BATCHING HAPPENS - all IDs queried at once"""
        if not self.queue:
            return

        # Remove duplicates
        unique_ids = list(set(self.queue))

        # ONE query for ALL authors
        print(f"→ Batching query for user IDs: {unique_ids}")
        users = db.query("SELECT id, name FROM users WHERE id IN (?)", unique_ids)

        # Store in cache
        for user in users:
            self.cache[user.id] = user

        self.queue = []  # Clear queue

    def get(self, user_id):
        """Retrieve result from cache"""
        return self.cache.get(user_id)


# The updated implementation
def show_posts_good():
    # Get all posts
    posts = db.query("SELECT id, title, author_id FROM posts LIMIT 10")

    # Create loader
    loader = SimpleDataLoader()

    # STEP 1: Queue up all the IDs (doesn't query database yet)
    for post in posts:
        loader.load(post.author_id)  # Just adds to queue

    # STEP 2: Execute batch query (ONE database call for all authors)
    loader.execute()

    # STEP 3: Get results from cache
    for post in posts:
        author = loader.get(post.author_id)
        print(f"{post.title} by {author.name}")

This system builds a list of what we need to query, runs just one query, and later deals with the data.

Key-caching​

Not a value-cache, but a key-cache. Value-caches that are implemented without appropreate space to think and plan about the entire lifecycle of a value are bound to generate problems with cache-misses, wrong data, stale data, or invalidation issues.

A key-cache is much simpler and lower in risk, adding a pre-request to warm-up data so you can avoid request round-trips outside of process memory.

The implementation requires a pattern similar to the DataCollector pattern from earlier, but instead of delaying lookup it checks if the required id is already pre-fetched. At the end of the execution, it will write a cache value containing all the IDs it needed to look up, and store it under a predictable key.

So if we look up some unpredictable page like /page/CY+=TTz1 in our app, we can use that page ID as the key and store it in redis:

When everything works perfectly, all the data your request needs are already pre-fetched.

Here is an example implementation to give you the general idea of how to get started. You should not directly use this code, but adapt and create something around your needs and quality requirements. There are many ways to implement this pattern.

class DataCollector:
    """Tracks what data gets accessed during request execution"""

    def __init__(self, prefetched_data=None):
        self.prefetched_data = prefetched_data or {}
        self.accessed_keys = {
            'authors': set(),
            'categories': set(),
            'comment_counts': set()
        }

    def get_author(self, author_id):
        """Get author, tracking the access"""
        self.accessed_keys['authors'].add(author_id)

        # Return prefetched data if available
        if 'authors' in self.prefetched_data:
            return self.prefetched_data['authors'].get(author_id)

        # Otherwise query (will be optimized next time)
        return db.query("SELECT * FROM users WHERE id = ?", author_id)

    def get_category(self, category_id):
        """Get category, tracking the access"""
        self.accessed_keys['categories'].add(category_id)

        if 'categories' in self.prefetched_data:
            return self.prefetched_data['categories'].get(category_id)

        return db.query("SELECT * FROM categories WHERE id = ?", category_id)

    def get_comment_count(self, post_id):
        """Get comment count, tracking the access"""
        self.accessed_keys['comment_counts'].add(post_id)

        if 'comment_counts' in self.prefetched_data:
            return self.prefetched_data['comment_counts'].get(post_id)

        result = db.query("SELECT COUNT(*) FROM comments WHERE post_id = ?", post_id)
        return result['count']

    def get_accessed_keys(self):
        """Return all keys that were accessed"""
        return {
            k: list(v) for k, v in self.accessed_keys.items() if v
        }
caching random keys

In one sitaution, the key-cache was regularly broken by a software team that wanted to show random (as in math.random()) products on their main page. While the key lookups should have been predictable, this code effectly ensured there would always be a complete cache-miss on the pre-fetch data, leading to slow performance via indiviudally looking up each record.

Do not try to cache random data.

Details

There is no serious business case for generating a random list of products on page. There are other proven practices to show rotating content which improve product conversion in ecommerce for example. The products shown on your page should be focused on generating sales, not random items. There are better ways to rotate content on a landing page relevant to your customer, and those options should be used and pre-calculated instead. No serious brand would leave their revenue in the hands of random().

User-specific & locale-specific data

Be cautious about caching of user-specific data on non-user-specific keys. This can lead to a situation where one user receives a pre-fetch for another user's data, which (ignoring the obvious security issues for a moment) can lead to a pre-fetch with mostly cache-misses. You can run into the same issue for other combinations such as locale for example.

The solution is to either build a combined key (page ID & user ID) which can increase the memory footprint of cache, or partition into two instances of the key-cache system (one for the page data, one for user data). Picking which one requires understanding how data varies on delivered pages. If each page is mostly the same with some user information decorating it, a dual system may be best (or if there are very few user-records, perhaps exclude them from the cache and take the performance hit). Alternatively, if the user determines what is put on the page, a combined key is likely to work better. You have to decide.

Warm-up

If you understand what keys are likely to be used by your software (for example, if you use page IDs or user IDs), a background process can warm-up the keys for you in the background. This can be a simple cron job with some loops in the most basic form, and grow into a complex solution as needed.

Services like gmail for example will begin to pull all your user-data to their edge server (not the browser) once you type in your email to log-in yet before you started typing your password. This improves the user experience and prevents the experience of slowness, despite the bytes taking the same time to move from the data store on to the server you will be using.

This key cache can be as effective as query optimisation in some cases, and scale better. I've personally seen it bring 60 second load times down to 80ms or less. It did not require without re-writing large parts of logic or flow, database schemas could remain unchanged, no need to "project" cache views of data through events or other heavy tasks that would otherwise require it.