N+1 Query Pattern Detection and Resolution

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you identify and fix N+1 query problems in your codebase. Let me first explore your project to understand your tech stack and find the problematic query patterns. Since there's no codebase in the current directory, let me provide you with practical tools and strategies to find and fix N+1 query problems in your application. ## Step 1: Enable Query Logging & Detection Let me create a diagnostic toolkit you can use: Let me provide you with a comprehensive guide directly: ## How to Find N+1 Query Problems ### **Step 1: Analyze Your Logs** Look for these patterns in your PostgreSQL logs: - **Same query repeated many times** with only the ID parameter changing - **Queries clustered by timestamp** - if you see 50-200 nearly identical SELECTs within milliseconds, that's N+1 - Pattern like: `SELECT * FROM posts WHERE user_id = 1`, then `user_id = 2`, then `user_id = 3`... ```bash # Search your logs for duplicate query patterns grep "SELECT" /var/log/postgresql/postgresql.log | \ sed 's/[0-9]\+/?/g' | \ # Replace numbers with ? sort | uniq -c | sort -rn | head -20 ``` ### **Step 2: Add Query Counting (Framework-Specific)** **For Node.js/Sequelize:** ```javascript // Add this middleware to detect N+1 app.use((req, res, next) => { let queryCount = 0; const queries = []; const originalQuery = sequelize.connectionManager.pool.query; sequelize.connectionManager.pool.query = function(...args) { queryCount++; queries.push(args[0].substring(0, 100)); return originalQuery.apply(this, args); }; res.on('finish', () => { if (queryCount > 10) { console.error(`⚠️ ${req.path}: ${queryCount} queries - N+1 likely!`); console.error('Queries:', queries); } }); next(); }); ``` **For Python/SQLAlchemy:** ```python from sqlalchemy import event query_count = 0 @event.listens_for(Engine, "before_cursor_execute") def count_queries(conn, cursor, statement, params, context, executemany): global query_count query_count += 1 if query_count > 10: logger.warning(f"High query count: {query_count}") ``` **For Rails:** ```ruby # Add to Gemfile gem 'bullet' # Automatically detects and alerts on N+1 queries ``` ### **Step 3: Find the Code** Search your codebase for these patterns: ```bash # Find loops with database queries inside grep -r "\.map\|\.forEach\|for.*of\|for.*in" --include="*.js" --include="*.ts" | \ xargs grep -l "find\|query\|select" # Find model files with relationships grep -r "hasMany\|belongsTo\|HasMany\|OneToMany" --include="*.js" --include="*.ts" ``` Common N+1 code patterns: ```javascript // 🚫 BAD - This is N+1! const users = await User.findAll(); for (const user of users) { user.posts = await Post.findAll({ where: { userId: user.id } }); } // 🚫 BAD - Also N+1! users.map(async user => { return { ...user, posts: await Post.find({ userId: user.id }) }; }); ``` ### **Step 4: Fix With Eager Loading** **Sequelize:** ```javascript // ✅ GOOD - Single query with join const users = await User.findAll({ include: [{ model: Post, include: [Comment] // Can nest }] }); ``` **TypeORM:** ```javascript // ✅ GOOD const users = await userRepository.find({ relations: ['posts', 'posts.comments'] }); ``` **Prisma:** ```javascript // ✅ GOOD const users = await prisma.user.findMany({ include: { posts: { include: { comments: true } } } }); ``` **SQLAlchemy:** ```python # ✅ GOOD from sqlalchemy.orm import joinedload users = session.query(User)\ .options(joinedload(User.posts).joinedload(Post.comments))\ .all() ``` ### **Step 5: Use DataLoader for GraphQL** If you're using GraphQL, implement DataLoader pattern: ```javascript const DataLoader = require('dataloader'); const postLoader = new DataLoader(async (userIds) => { const posts = await Post.findAll({ where: { userId: { $in: userIds } } }); // Group by userId const postsByUser = {}; posts.forEach(post => { if (!postsByUser[post.userId]) postsByUser[post.userId] = []; postsByUser[post.userId].push(post); }); return userIds.map(id => postsByUser[id] || []); }); // In resolvers const resolvers = { User: { posts: (user) => postLoader.load(user.id) } }; ``` ### **Step 6: Reduce Connection Pool Size** Your 200+ connections suggest pool exhaustion. Reduce it: ```javascript // For Sequelize const sequelize = new Sequelize(config.database, { pool: { max: 10, // Down from default 25+ min: 2, idle: 10000, acquire: 30000 } }); ``` ### **Immediate Actions:** 1. **Enable query logging** and look for duplicate patterns 2. **Add query counters** to your most-used endpoints 3. **Search for loops** containing database calls in your code 4. **Replace with eager loading** using your ORM's `include`/`relations` feature 5. **Reduce connection pool** to 10-20 max connections What framework/ORM are you using? I can provide more specific fixes for your stack.

I'll help you identify and fix N+1 query problems in your codebase. Let me start by exploring your project structure to understand what framework and database tools you're using. I can help you tackle this N+1 query problem systematically. Let me create a comprehensive guide with detection strategies and fixes for common frameworks. I'll help you identify and fix N+1 queries. Here's a systematic approach: ## **Immediate Detection Steps** ### 1. **Enable PostgreSQL Query Logging** ```sql ALTER SYSTEM SET log_min_duration_statement = 100; -- Log queries > 100ms SELECT pg_reload_conf(); ``` Then analyze patterns: ```bash # Find repeated queries grep "SELECT" /var/log/postgresql/*.log | sort | uniq -c | sort -rn | head -20 ``` ### 2. **Add Query Counter to Your App** **Python/Django:** ```python # Add middleware to log query counts from django.db import connection class QueryCountMiddleware: def __call__(self, request): queries_before = len(connection.queries) response = self.get_response(request) query_count = len(connection.queries) - queries_before if query_count > 10: logger.warning(f"{request.path}: {query_count} queries!") response['X-DB-Query-Count'] = str(query_count) return response ``` **Node.js/Express:** ```javascript // Track queries per request app.use((req, res, next) => { const queries = []; // Hook into your ORM's query event res.on('finish', () => { if (queries.length > 10) { console.warn(`${req.path}: ${queries.length} queries`); } }); next(); }); ``` ## **Common N+1 Patterns & Fixes** ### **Django** ```python # ❌ PROBLEM (1 + N queries) users = User.objects.all() # 1 query for user in users: print(user.company.name) # N queries # ✅ FIX - Use select_related for ForeignKey users = User.objects.select_related('company').all() # 1 query with JOIN # ✅ FIX - Use prefetch_related for ManyToMany users = User.objects.prefetch_related('posts').all() # 2 queries # ✅ FIX - Use annotate for counts users = User.objects.annotate(post_count=Count('posts')) ``` ### **Rails** ```ruby # ❌ PROBLEM @users = User.all # Later: @users.each { |u| u.company.name } # N+1 # ✅ FIX @users = User.includes(:company, :posts) @posts = Post.includes(comments: :author) # nested ``` ### **Node.js (Sequelize)** ```javascript // ❌ PROBLEM const users = await User.findAll(); for (const user of users) { const company = await user.getCompany(); // N+1 } // ✅ FIX const users = await User.findAll({ include: [ { model: Company, as: 'company' }, { model: Post, as: 'posts' } ] }); ``` ### **TypeORM** ```typescript // ❌ PROBLEM const users = await userRepository.find(); for (const user of users) { await user.company; // Lazy loading causes N+1 } // ✅ FIX const users = await userRepository.find({ relations: ['company', 'posts'] }); ``` ### **Go (GORM)** ```go // ❌ PROBLEM db.Find(&users) for i := range users { db.Model(&users[i]).Association("Company").Find(&users[i].Company) } // ✅ FIX db.Preload("Company").Preload("Posts").Find(&users) ``` ## **Quick Verification** 1. **Before fix:** ```bash curl -I http://your-api/users | grep X-DB-Query-Count # X-DB-Query-Count: 247 ``` 2. **After fix:** ```bash curl -I http://your-api/users | grep X-DB-Query-Count # X-DB-Query-Count: 3 ``` ## **Your Action Plan** 1. **Today (2 hours):** - Enable query logging in PostgreSQL - Add query counter middleware to your app - Identify your top 3 endpoints with most queries 2. **This week:** - Fix highest-traffic endpoint with eager loading - Load test to verify: connections should drop from 200+ to 20-40 - Add automated tests to prevent regression 3. **Add prevention:** ```python # Test example - fails if queries exceed threshold def test_users_endpoint_performance(self): with self.assertNumQueries(3): # Enforce query budget response = self.client.get('/api/users/') ``` **Expected improvement:** 90% reduction in connections (200+ → 20-40) and 50-80% faster response times. What framework/ORM are you using? I can provide more specific debugging steps.

Check `postgresql.queries.count` grouped by normalized query text to find queries executing hundreds or thousands of times per minute. Look for simple SELECT statements with nearly identical structure but different parameter values (e.g., 'SELECT * FROM orders WHERE user_id = ?'). If you see one query fetching a parent list followed by many identical child queries, that's your N+1 smoking gun. The `n-plus-one-query-problem` insight describes exactly this pattern - one query for the list, then N queries for related data.

Use `queries_duration_max` to see individual query duration, but multiply by `postgresql.queries.count` to calculate total time impact. A 5ms query running 10,000 times per minute consumes 50 seconds of database time per minute - far worse than a single 1-second query. Focus on queries with high frequency × duration, not just slow queries. These are your primary optimization targets.

Monitor `postgresql.backends` during busy periods - if you're hitting 200+ connections, check `connections_by_process` to see which application processes are holding connections. The `n-plus-one-query-connection-exhaustion` insight documents cases where N+1 patterns during high traffic (like Black Friday) caused 50,000 simultaneous connections. If connections spike in correlation with traffic, your N+1 queries are creating connection pool pressure.

Turn on SQL query logging in your ORM (e.g., SQLAlchemy's `echo=True`, Django's `DEBUG=True` with SQL logging, or ActiveRecord's query log). Reproduce a slow API endpoint and watch the log output - you'll see the parent query followed by a loop of child queries. Match the query patterns you found in step 1 to specific ORM calls in your codebase. Look for loops that reference related objects without explicit eager loading.

Not all N+1 problems appear as application loops - sometimes they're hidden in SQL as correlated subqueries in SELECT lists. The `correlated-subqueries-anti-pattern` insight explains how subqueries that reference the outer query execute once per row. If you see subqueries in your slow queries, rewrite them as JOINs or LATERAL JOINs. Ensure proper indexes exist on join columns.

Fix identified N+1 patterns by using your ORM's eager loading: SQLAlchemy's `joinedload()`, Django's `select_related()` for foreign keys or `prefetch_related()` for many-to-many. Critically, limit the blast radius per the `n-plus-one-query-connection-exhaustion` insight: add pagination with explicit limits (50-100 rows), use `attributes` clauses to fetch only needed columns, and optimize `include` clauses to minimal nested data. Overly aggressive eager loading can itself cause connection exhaustion.

After deploying eager loading fixes, compare `postgresql.queries.count` for the specific query patterns you identified - it should drop from thousands to tens or hundreds. Check that `postgresql.backends` stays well below connection pool limits during peak traffic. Measure API response times - eliminating N+1 should cut hundreds of milliseconds per request. If connections or query counts are still high, review your eager loading strategy for missing relationships or overfetching.