---
title: Database Query Optimization
impact: HIGH
impactDescription: Prevents N+1 queries and ensures fast page loads
tags: performance, database, prisma, postgresql, indexing
---
## Паспорт документа

- Статус документа: living standard
- Актуально на: 28 марта 2026 года
- Владелец: backend/platform-команда
- Пересмотр: при изменении инженерной практики, CI/CD, архитектурных правил или локального workflow
- Область применения: внутренние rule/reference-card документы для инженерной команды
- Связанные документы:
  - [Индекс Agents](../README.md)
  - [Команды разработки](../commands.md)
  - [Инженерные принципы](../../governance/engineering-principles.md)

## Database Query Optimization

**Impact: HIGH**

### Avoid N+1 Queries

```typescript
// Bad: N+1 — one query per item for seller data
const items = await this.prisma.item.findMany({ where: { status: 'PUBLISHED' } });
for (const item of items) {
  item.seller = await this.prisma.seller.findUnique({ where: { id: item.sellerId } });
}

// Good: single query with select
const items = await this.prisma.$replica().item.findMany({
  where: { status: 'PUBLISHED' },
  select: {
    id: true,
    title: true,
    seller: {
      select: { id: true, name: true, logoUrl: true }
    }
  }
});
```

### Always Paginate

```typescript
// Bad: load entire table
const items = await this.prisma.item.findMany();

// Good: paginated with cursor or offset
const items = await this.prisma.$replica().item.findMany({
  where: { status: 'PUBLISHED' },
  select: { id: true, title: true, price: true },
  skip: (page - 1) * limit,
  take: limit,
  orderBy: { createdAt: 'desc' },
});
```

### Indexing Strategy

Add indexes for:
- Columns used in `WHERE` clauses (`status`, `sellerId`, `subjectId`)
- Columns used in `ORDER BY` (`createdAt`, `price`, `rating`)
- Unique constraints (`slug`, `email`, `phoneNumber`)
- Foreign keys (Prisma adds these automatically)
- Composite indexes for common filter combinations

```prisma
model Item {
  id        String   @id @default(uuid())
  slug      String   @unique
  status    ItemStatus
  sellerId  String
  subjectId String
  createdAt DateTime @default(now())

  @@index([status, createdAt])
  @@index([sellerId, status])
  @@index([subjectId, status])
}
```

### Rules
- Never fetch all rows — always paginate
- Never filter in JavaScript what can be filtered in SQL
- Always use `select` to fetch only needed fields
- Add indexes for frequently filtered/sorted columns
- Use `count()` separately for pagination metadata