patrickandClaude Sonnet 5
4a19335a1e
Replace Next.js/Supabase/Vercel stack with a self-hostable setup: Vite+React frontend, Fastify+Drizzle+PostgreSQL backend with own JWT auth, deploy via git pull + rsync + systemd (checkout/service separated, updates pulled from Gitea). Adds a reference auth route (register/login/me) as the canonical pattern for new routes. Updates all skills/rules/agents/docs to match. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01BMdBJfD9TT6XUU2NqGG1PN
96 lines
2.7 KiB
Markdown
96 lines
2.7 KiB
Markdown
# 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);
|
|
```
|