performance-database-queries.md

---

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
  • Команды разработки
  • Инженерные принципы

Database Query Optimization

Impact: HIGH

Avoid N+1 Queries

// 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

// 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

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