Database Query & Migration Optimizer: Eliminating N+1 Bottlenecks with Locking-Aware Migrations (2026)

The Hidden Threat of AI-Generated Database Code

AI coding assistants write functional TypeScript and Python in seconds. The catch: they frequently generate naive ORM iteration loops that fire hundreds of unindexed queries under real traffic, causing CPU spikes, exhausted connection pools, and table locks that take down writes for everyone.

We've seen this exact pattern firsthand, a query that runs in 5ms against a 100-row local database can take 8,500ms and lock write transactions when it hits a 2,000,000-row production table. Same code. Different scale.

The 3 Critical Database Bottlenecks, and How to Fix Them

1. N+1 Query Loops

Fetching related data (users and their recent orders, say) via naive iteration executes 1 + N queries, one for the users, then one more per user. Replace it with framework-idiomatic eager loading:

// Prisma: replace loops with include
const users = await prisma.user.findMany({
  where: { active: true },
  include: { orders: { select: { id: true, total: true, createdAt: true } } }
});

# Django ORM: replace loops with select_related / prefetch_related
users = User.objects.filter(is_active=True).select_related('profile').prefetch_related('orders').only('id', 'email', 'profile__tier')

2. The Composite Index Column-Order Rule

For queries mixing filtering and sorting, WHERE user_id = 42 ORDER BY created_at DESC, column order in the B-Tree index isn't cosmetic, it decides whether the index gets used at all:

• Equality first: columns queried with exact equality (=) go first: (user_id, created_at).
• Range and sort second: columns queried with ranges (>, <, BETWEEN) or used in ORDER BY go last.

3. Keyset (Seek) Pagination vs. OFFSET

High-offset pagination (OFFSET 50000 LIMIT 20) forces the engine to scan and discard 50,020 rows before returning anything. Keyset pagination replaces that scan with an indexed comparison:

-- Slow: O(N) table scan
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 50000;

-- Fast: O(1) B-Tree seek
SELECT * FROM orders 
WHERE (created_at, id) < (:last_seen_created_at, :last_seen_id)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Locking-Impact Classified Migrations Across Dialects

The plain CREATE INDEX (no CONCURRENTLY) takes an exclusive lock on the table for the duration of the build. On a small staging table that finishes in under a second and nobody notices. On a production table with real traffic, it can stall every write for minutes.

Automating Audits with Agent Skills

The Database Query & Migration Optimizer skill reads your schemas and routes and turns the findings above into drop-in patches and safe migration scripts:

"Using the database-query-optimizer skill, audit my Prisma schema and order routes for PostgreSQL. Identify missing indexes and generate migrations classified by their actual locking impact."

Frequently Asked Questions