---
name: db-performance-patterns
description: "Patterns for optimizing database queries and preventing connection pool exhaustion. Use when writing batch operations, debugging slow queries, or reviewing code for performance."
---

# 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


---
Source attribution: JaveedIshaq
Original project: https://github.com/JaveedIshaq/ai-workflow-orchestrator/blob/6b54ecbdcacca21d769e634f21ec2674f28b6cc9/skills/ballee/db-performance-patterns/SKILL.md
License: MIT
License information: https://opensource.org/license/mit
Original upstream instructions; downloaded from Dave AI.

## Upstream license notice

MIT License

Copyright (c) 2025 Antoine Schaller

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.



# Dave AI resource metadata

Creator / publisher: JaveedIshaq
Original project: https://github.com/JaveedIshaq/ai-workflow-orchestrator/blob/6b54ecbdcacca21d769e634f21ec2674f28b6cc9/skills/ballee/db-performance-patterns/SKILL.md
Attribution basis: Recovered original package and original project license at a pinned revision

Fields of work: Development & Engineering, Product Management
Roles: Backend Developer, Data Engineer, Database Administrator, ML / AI Engineer, Product Manager
Specialties: Database
Tools: Supabase
Country applicability: Country not specified
Classification basis: Source catalog labels
Instruction SHA-256: aeaec458c0fcdf897728d55aa8d4a69e088051b52fb5922a38d994504df7cbe4
Metadata reviewed: 2026-10-27
This metadata supersedes earlier Dave AI catalog attribution. Original instruction and supporting files are preserved.