From aai-dev-database
Provides patterns for optimizing database query performance: EXPLAIN ANALYZE, indexing strategies (composite, partial, covering), avoiding N+1 queries with Prisma, efficient pagination. For slow SQL queries.
How this skill is triggered — by the user, by Claude, or both
Slash command
/aai-dev-database:query-optimizationThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Patterns for improving database query performance.
Patterns for improving database query performance.
-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.role = 'ADMIN'
GROUP BY u.id;
-- Key metrics to watch:
-- - Seq Scan vs Index Scan
-- - Actual rows vs Estimated rows
-- - Sort operations
-- - Nested loops vs Hash joins
Seq Scan -- Full table scan (often bad)
Index Scan -- Using index (good)
Index Only Scan -- All data from index (best)
Bitmap Scan -- Multiple index matches
Hash Join -- Good for large joins
Nested Loop -- Good for small inner table
Sort -- May use disk if large
-- Index: Foreign keys (always)
CREATE INDEX idx_posts_author_id ON posts(author_id);
-- Index: Frequently filtered columns
CREATE INDEX idx_users_role ON users(role);
-- Index: Columns in ORDER BY
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
-- Index: Columns in JOIN conditions
CREATE INDEX idx_comments_post_id ON comments(post_id);
-- Order matters! Most selective first
CREATE INDEX idx_posts_status_date
ON posts(published, published_at DESC);
-- Covers queries like:
SELECT * FROM posts WHERE published = true ORDER BY published_at DESC;
SELECT * FROM posts WHERE published = true AND published_at > '2024-01-01';
-- Does NOT help:
SELECT * FROM posts WHERE published_at > '2024-01-01'; -- Needs leading column
-- Only index active records
CREATE INDEX idx_active_users_email
ON users(email)
WHERE deleted_at IS NULL;
-- Only index specific values
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';
-- Include all needed columns to avoid table lookup
CREATE INDEX idx_users_covering
ON users(email) INCLUDE (name, role);
-- Query can be satisfied entirely from index:
SELECT email, name, role FROM users WHERE email = '[email protected]';
// Bad: N+1 queries
const users = await prisma.user.findMany();
for (const user of users) {
user.posts = await prisma.post.findMany({
where: { authorId: user.id },
});
}
// Good: Eager loading
const users = await prisma.user.findMany({
include: { posts: true },
});
// Good: Separate batch query
const users = await prisma.user.findMany();
const posts = await prisma.post.findMany({
where: { authorId: { in: users.map(u => u.id) } },
});
// Offset pagination (slow for large offsets)
const users = await prisma.user.findMany({
skip: 10000,
take: 20,
orderBy: { createdAt: 'desc' },
});
// Cursor pagination (consistent performance)
const users = await prisma.user.findMany({
take: 20,
cursor: { id: lastUserId },
orderBy: { createdAt: 'desc' },
});
// Keyset pagination (fastest for sorted data)
const users = await prisma.user.findMany({
where: {
createdAt: { lt: lastCreatedAt },
},
take: 20,
orderBy: { createdAt: 'desc' },
});
// Bad: Load everything
const users = await prisma.user.findMany();
// Good: Only needed fields
const users = await prisma.user.findMany({
select: {
id: true,
name: true,
email: true,
},
});
// Bad: Individual inserts
for (const item of items) {
await prisma.item.create({ data: item });
}
// Good: Batch insert
await prisma.item.createMany({
data: items,
skipDuplicates: true,
});
// Good: Batch update
await prisma.item.updateMany({
where: { status: 'pending' },
data: { status: 'processed' },
});
-- Bad
SELECT * FROM users WHERE id = 1;
-- Good
SELECT id, name, email FROM users WHERE id = 1;
-- IN (loads all values into memory)
SELECT * FROM users
WHERE id IN (SELECT author_id FROM posts WHERE published = true);
-- EXISTS (stops at first match)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.author_id = u.id AND p.published = true);
-- Bad (may not use index)
SELECT * FROM users WHERE email = '[email protected]' OR name = 'John';
-- Better (union uses indexes)
SELECT * FROM users WHERE email = '[email protected]'
UNION
SELECT * FROM users WHERE name = 'John';
-- INNER JOIN: Only matching rows (most selective)
SELECT u.*, p.title
FROM users u
INNER JOIN posts p ON p.author_id = u.id;
-- LEFT JOIN: All from left, matching from right
SELECT u.*, p.title
FROM users u
LEFT JOIN posts p ON p.author_id = u.id;
-- Avoid: Cartesian products
SELECT * FROM users, posts; -- Bad!
async function getPopularPosts(): Promise<Post[]> {
const cacheKey = 'popular-posts';
const cached = await redis.get(cacheKey);
if (cached) {
return JSON.parse(cached);
}
const posts = await prisma.post.findMany({
where: { published: true },
orderBy: { viewCount: 'desc' },
take: 10,
});
await redis.setex(cacheKey, 300, JSON.stringify(posts)); // 5 min TTL
return posts;
}
-- Create materialized view for expensive aggregations
CREATE MATERIALIZED VIEW post_stats AS
SELECT
author_id,
COUNT(*) as post_count,
SUM(view_count) as total_views,
MAX(published_at) as last_published
FROM posts
WHERE published = true
GROUP BY author_id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW post_stats;
-- Or refresh concurrently (no lock)
REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;
-- PostgreSQL: Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1s
-- View slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
// Prisma with connection pool
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// Pool settings
connectionLimit = 10
}
// Or external pooler (PgBouncer)
DATABASE_URL="postgres://user:pass@pgbouncer:6432/db?pgbouncer=true"
Used by:
database-developer agentProvides behavioral guidelines to reduce common LLM coding mistakes, focusing on simplicity, surgical changes, assumption surfacing, and verifiable success criteria.
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Creates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.
npx claudepluginhub bradtaylorsf/alphaagent-team