# Database Optimization

## 1. Indexing

Create indexes on columns used in WHERE, ORDER BY, or JOIN clauses:

```ts
// backend/src/db/schema.ts
export const tasks = pgTable("tasks", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (table) => ({
  userCreatedIdx: index("idx_tasks_user_id_created").on(table.userId, table.createdAt.desc()),
}));
```

**Rule of thumb:** If a column appears in WHERE or ORDER BY and the table will have >1000 rows, add an index.

Always include indexes in the schema alongside the table definition, so they ship with the same migration.

## 2. Avoid N+1 Queries

The most common performance problem with ORMs and query builders:

```typescript
// Bad: N+1 (1 query for users + N queries for tasks)
const allUsers = await db.select().from(users);
for (const user of allUsers) {
  const userTasks = await db.select().from(tasks).where(eq(tasks.userId, user.id));
}

// Good: single query with a join
const rows = await db
  .select()
  .from(users)
  .leftJoin(tasks, eq(tasks.userId, users.id));

// Or with Drizzle's relational query API (if relations are defined)
const usersWithTasks = await db.query.users.findMany({
  with: { tasks: true },
});
```

## 3. Always Limit Results

Never return unbounded results from the database:

```typescript
// Bad: returns ALL rows
const rows = await db.select().from(tasks);

// Good: capped
const rows = await db.select().from(tasks).limit(50);

// Better: paginated
const rows = await db.select().from(tasks).limit(50).offset(page * 50);
```

## 4. Caching Strategy

For data that changes rarely (dashboard stats, config, categories), cache in the backend process (or a small in-memory LRU) rather than hitting Postgres on every request:

```typescript
let cachedCategories: Category[] | null = null;
let cachedAt = 0;

export async function getCategories() {
  if (cachedCategories && Date.now() - cachedAt < 3600_000) {
    return cachedCategories;
  }
  cachedCategories = await db.select().from(categories);
  cachedAt = Date.now();
  return cachedCategories;
}
```

**When to cache:**
- Data that changes less than once per hour
- Expensive aggregation queries
- Data shared across all users (not user-specific)

**When NOT to cache:**
- User-specific data that changes frequently
- Data that needs to be live across multiple backend instances (use a shared cache like Redis instead of in-process memory)

## 5. Select Only What You Need

```typescript
// Bad: fetches all columns
const rows = await db.select().from(users);

// Good: fetches only needed columns
const rows = await db.select({ id: users.id, email: users.email }).from(users);
```