database-performance

1# Database Performance Patterns
2 
3## When to Use This Skill
4 
5Use this skill when:
6- Designing data access layers
7- Optimizing slow database queries
8- Choosing between EF Core and Dapper
9- Avoiding common performance pitfalls
10 
11---
12 
13## Core Principles
14 
151. **Separate read and write models** - Don't use the same types for both
162. **Think in batches** - Avoid N+1 queries
173. **Only retrieve what you need** - No SELECT *
184. **Apply row limits** - Always have a configurable Take/Limit
195. **Do joins in SQL** - Never in application code
206. **AsNoTracking for reads** - EF Core change tracking is expensive
21 
22---
23 
24## Read/Write Model Separation (CQRS Pattern)
25 
26**Read and write models are fundamentally different - they have different shapes, columns, and purposes.** Don't create a single "User" entity and reuse it everywhere.
27 
28- **Read models** are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin)
29- **Write models** are normalized, validation-focused, and accept strongly-typed commands (CreateUserCommand, UpdateUserCommand)
30 
31### Architecture
32 
33```
34src/
35  MyApp.Data/
36    Users/
37      # Read side - multiple optimized projections
38      IUserReadStore.cs
39      PostgresUserReadStore.cs
40 
41      # Write side - command handlers
42      IUserWriteStore.cs
43      PostgresUserWriteStore.cs
44 
45      # Read DTOs - lightweight, denormalized
46      UserProfile.cs
47      UserSummary.cs
48 
49      # Write commands - validation-focused
50…