Agent skill

db-performance-patterns

Patterns for optimizing database queries and preventing connection pool exhaustion. Use when writing batch operations, debugging slow queries, or reviewing code for performance.

View SKILL.md on GitHub Repository
Stars 163
Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/data/db-performance-patterns

SKILL.md

Database Performance Patterns

Patterns and guidelines for preventing connection pool exhaustion and optimizing database queries in Ballee.

When to Use This Skill

  • Writing cron jobs or batch operations
  • Creating services that query multiple entities
  • Debugging slow queries or connection issues
  • Reviewing code for performance issues

Critical Connection Settings

Production Supabase has 60 max connections. These settings prevent pool exhaustion:

sql
-- Applied to both production and staging (2025-12-18)
ALTER DATABASE postgres SET idle_session_timeout = '300000';           -- 5 min
ALTER DATABASE postgres SET idle_in_transaction_session_timeout = '60000'; -- 1 min

Anti-Patterns to Avoid

1. N+1 Query Pattern (FORBIDDEN)

typescript
// BAD: 3 queries per item = 150 queries for 50 items
for (const item of items) {
  const { count: countA } = await supabase.from('table').select('id', { count: 'exact' }).eq('item_id', item.id).eq('status', 'a');
  const { count: countB } = await supabase.from('table').select('id', { count: 'exact' }).eq('item_id', item.id).eq('status', 'b');
  const { count: countC } = await supabase.from('table').select('id', { count: 'exact' }).eq('item_id', item.id).eq('status', 'c');
}

// GOOD: 1 query total, aggregate in memory
const { data: allRecords } = await supabase
  .from('table')
  .select('item_id, status')
  .in('status', ['a', 'b', 'c']);

const countsByItem = new Map();
for (const record of allRecords || []) {
  // Aggregate in memory
}

2. Sequential Independent Queries (FORBIDDEN)

typescript
// BAD: Sequential queries (3x latency)
const production = await supabase.from('productions').select('*').eq('id', id).single();
const roles = await supabase.from('cast_roles').select('*').eq('production_id', id);
const events = await supabase.from('events').select('*').eq('production_id', id);

// GOOD: Parallel queries (1x latency)
const [productionResult, rolesResult, eventsResult] = await Promise.all([
  supabase.from('productions').select('*').eq('id', id).single(),
  supabase.from('cast_roles').select('*').eq('production_id', id),
  supabase.from('events').select('*').eq('production_id', id),
]);

3. Individual Inserts/Updates in Loops (FORBIDDEN)

typescript
// BAD: N inserts = N queries
for (const item of items) {
  await supabase.from('table').insert({ ...item });
}

// GOOD: 1 batch insert
await supabase.from('table').insert(items);

// BAD: N updates
for (const id of ids) {
  await supabase.from('table').update({ status: 'done' }).eq('id', id);
}

// GOOD: 1 batch update
await supabase.from('table').update({ status: 'done' }).in('id', ids);

Required Index Patterns

Always Index Foreign Keys

sql
-- Every FK column should have an index
CREATE INDEX idx_table_foreign_id ON table(foreign_id);

-- Use partial indexes for nullable FKs
CREATE INDEX idx_table_optional_fk ON table(optional_fk) WHERE optional_fk IS NOT NULL;

Index Frequently Filtered Columns

sql
-- Boolean flags queried often
CREATE INDEX idx_feature_flags_is_active ON feature_flags(is_active) WHERE is_active = true;

-- Status columns
CREATE INDEX idx_events_status ON events(status);

-- Composite indexes for common query patterns
CREATE INDEX idx_events_status_date ON events(status, event_date DESC);

Check for Missing Indexes

sql
-- Find tables with high sequential scans (missing indexes)
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

-- Find missing FK indexes
SELECT c.relname, a.attname
FROM pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(con.conkey)
WHERE con.contype = 'f'
AND NOT EXISTS (
  SELECT 1 FROM pg_index i
  WHERE i.indrelid = c.oid AND a.attnum = ANY(i.indkey)
);

Cron Job Best Practices

Structure for Batch Operations

typescript
export async function GET(request: Request) {
  // 1. Single query to get all entities
  const { data: entities } = await client.from('entities').select('id, status').eq('status', 'pending');

  // 2. Single query to get related data for all entities
  const entityIds = entities.map(e => e.id);
  const { data: relatedData } = await client.from('related').select('*').in('entity_id', entityIds);

  // 3. Group related data by entity in memory
  const relatedByEntity = new Map();
  for (const item of relatedData || []) {
    // Group in memory
  }

  // 4. Process and prepare batch operations
  const toInsert = [];
  const toUpdate = [];

  for (const entity of entities) {
    const related = relatedByEntity.get(entity.id);
    // Process and add to batch arrays
  }

  // 5. Single batch insert
  if (toInsert.length > 0) {
    await client.from('results').insert(toInsert);
  }

  // 6. Single batch update (if needed)
  if (toUpdate.length > 0) {
    await client.from('entities').update({ status: 'processed' }).in('id', toUpdate);
  }
}

Connection Pool Monitoring

Check Current Connections

sql
SELECT state, count(*) FROM pg_stat_activity WHERE datname = 'postgres' GROUP BY state;

Monitor Slow Queries

sql
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Supabase Dashboard Monitoring

  1. Database Reports → Query Performance
  2. Database Reports → Connection Pooler
  3. Set alerts for:
    • Connection count > 50 (of 60 max)
    • Query time > 100ms average

Quick Reference: Query Reduction

Pattern Before After Reduction
Status counts 3 queries 1 query + memory 66%
Per-item metrics N×3 queries 1 query + memory 99%
Batch reports N×3 queries 3 queries 99%
Sequential inserts N queries 1 query 99%

Fix Templates (Copy-Paste Ready)

Template 1: N+1 → Batch Fetch + Map Lookup

Before (N+1):

typescript
const results = [];
for (const item of items) {
  const { data } = await supabase
    .from('related_table')
    .select('*')
    .eq('item_id', item.id)
    .single();
  results.push({ ...item, related: data });
}

After (1 query):

typescript
// 1. Collect all IDs
const itemIds = items.map(item => item.id);

// 2. Batch fetch all related data
const { data: allRelated } = await supabase
  .from('related_table')
  .select('*')
  .in('item_id', itemIds);

// 3. Create lookup Map for O(1) access
const relatedMap = new Map(
  (allRelated ?? []).map(r => [r.item_id, r])
);

// 4. Use Map in loop (no queries)
const results = items.map(item => ({
  ...item,
  related: relatedMap.get(item.id),
}));

Template 2: Multiple Counts → Single Query + Aggregation

Before (3 queries):

typescript
const { count: acceptedCount } = await supabase
  .from('assignments')
  .select('id', { count: 'exact', head: true })
  .eq('status', 'accepted');

const { count: pendingCount } = await supabase
  .from('assignments')
  .select('id', { count: 'exact', head: true })
  .eq('status', 'pending');

const { count: declinedCount } = await supabase
  .from('assignments')
  .select('id', { count: 'exact', head: true })
  .eq('status', 'declined');

After (1 query):

typescript
// 1. Single query fetching all statuses
const { data: assignments } = await supabase
  .from('assignments')
  .select('status')
  .in('status', ['accepted', 'pending', 'declined']);

// 2. Aggregate in memory
const counts = { accepted: 0, pending: 0, declined: 0 };
for (const a of assignments ?? []) {
  if (a.status in counts) {
    counts[a.status as keyof typeof counts]++;
  }
}

const { accepted: acceptedCount, pending: pendingCount, declined: declinedCount } = counts;

Template 3: Sequential Queries → Promise.all

Before (sequential - 3x latency):

typescript
const { data: production } = await supabase
  .from('productions')
  .select('*')
  .eq('id', productionId)
  .single();

const { data: roles } = await supabase
  .from('cast_roles')
  .select('*')
  .eq('production_id', productionId);

const { data: events } = await supabase
  .from('events')
  .select('*')
  .eq('production_id', productionId);

After (parallel - 1x latency):

typescript
const [productionResult, rolesResult, eventsResult] = await Promise.all([
  supabase.from('productions').select('*').eq('id', productionId).single(),
  supabase.from('cast_roles').select('*').eq('production_id', productionId),
  supabase.from('events').select('*').eq('production_id', productionId),
]);

const production = productionResult.data;
const roles = rolesResult.data;
const events = eventsResult.data;

Template 4: Loop Inserts → Batch Insert

Before (N inserts):

typescript
for (const item of items) {
  await supabase.from('notifications').insert({
    user_id: item.userId,
    message: item.message,
    type: 'reminder',
  });
}

After (1 insert):

typescript
const toInsert = items.map(item => ({
  user_id: item.userId,
  message: item.message,
  type: 'reminder',
}));

if (toInsert.length > 0) {
  await supabase.from('notifications').insert(toInsert);
}

Template 5: Loop Updates → Batch Update

Before (N updates):

typescript
for (const id of completedIds) {
  await supabase
    .from('tasks')
    .update({ status: 'done', completed_at: new Date().toISOString() })
    .eq('id', id);
}

After (1 update):

typescript
if (completedIds.length > 0) {
  await supabase
    .from('tasks')
    .update({ status: 'done', completed_at: new Date().toISOString() })
    .in('id', completedIds);
}

Template 6: Cron Job Batch Pattern

Complete pattern for cron jobs:

typescript
export async function GET(request: Request) {
  const client = getSupabaseRouteHandlerClient({ admin: true });

  // 1. Fetch all eligible entities in ONE query
  const { data: entities } = await client
    .from('entities')
    .select('id, user_id, status')
    .eq('status', 'pending')
    .lt('created_at', oneHourAgo);

  if (!entities?.length) {
    return NextResponse.json({ processed: 0 });
  }

  // 2. Collect IDs for batch queries
  const entityIds = entities.map(e => e.id);
  const userIds = [...new Set(entities.map(e => e.user_id))];

  // 3. Batch fetch ALL related data in PARALLEL
  const [relatedResult, usersResult, existingResult] = await Promise.all([
    client.from('related').select('*').in('entity_id', entityIds),
    client.from('profiles').select('id, email').in('id', userIds),
    client.from('processed').select('entity_id').in('entity_id', entityIds),
  ]);

  // 4. Create lookup Maps
  const relatedByEntity = new Map<string, typeof relatedResult.data>();
  for (const r of relatedResult.data ?? []) {
    if (!relatedByEntity.has(r.entity_id)) {
      relatedByEntity.set(r.entity_id, []);
    }
    relatedByEntity.get(r.entity_id)!.push(r);
  }

  const usersMap = new Map((usersResult.data ?? []).map(u => [u.id, u]));
  const alreadyProcessed = new Set((existingResult.data ?? []).map(e => e.entity_id));

  // 5. Process and build batch operations (NO QUERIES IN LOOP)
  const toInsert = [];
  const toUpdate = [];

  for (const entity of entities) {
    if (alreadyProcessed.has(entity.id)) continue;

    const related = relatedByEntity.get(entity.id) ?? [];
    const user = usersMap.get(entity.user_id);

    // Process logic here...
    toInsert.push({ entity_id: entity.id, processed_at: new Date().toISOString() });
    toUpdate.push(entity.id);
  }

  // 6. Batch insert
  if (toInsert.length > 0) {
    await client.from('processed').insert(toInsert);
  }

  // 7. Batch update
  if (toUpdate.length > 0) {
    await client.from('entities').update({ status: 'done' }).in('id', toUpdate);
  }

  return NextResponse.json({ processed: toInsert.length });
}

Template 7: Grouped Updates by Value

Before (N updates with different values):

typescript
for (const item of items) {
  await supabase
    .from('table')
    .update({ reminder_count: item.count })
    .eq('id', item.id);
}

After (grouped by value):

typescript
// Group IDs by their target value
const byCount = new Map<number, string[]>();
for (const item of items) {
  if (!byCount.has(item.count)) {
    byCount.set(item.count, []);
  }
  byCount.get(item.count)!.push(item.id);
}

// One update per unique value
for (const [count, ids] of byCount) {
  await supabase.from('table').update({ reminder_count: count }).in('id', ids);
}

Related Files

  • Migration: supabase/migrations/20251218200640_add_performance_indexes.sql
  • Optimized service: app/admin/_lib/services/reporting.service.ts
  • Optimized crons: app/api/cron/feedback-*/route.ts

Related Skills

  • db-anti-patterns - Detection rules for finding issues
  • /db-perf command - Automated scanning and fixing

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results