lidge-jun/codexclaw / plugins/codexclaw/skills/dev-backend
cxc-dev-backend
FlaggedUse for APIs, servers and app databases. Triggers: REST, GraphQL, migration, query optimization, middleware, caching, queues, 백엔드, API 작업, 마이그레이션, 쿼리 최적화.
references/stacks/database.md
View on GitHub# Database — PostgreSQL & MongoDB
Stack-specific database rules.
Synthesized from dev-backend + senior-backend + mrgoonie databases skill.
---
## Database Selection Guide
### Choose PostgreSQL When:
- ACID transactions critical (financial, e-commerce)
- Complex relationships (JOINs, referential integrity)
- SQL expertise on team
- Mature tooling needed (extensions, pg_stat, pgBouncer)
- Analytical workloads (window functions, CTEs)
### Choose MongoDB When:
- Schema flexibility needed (frequent structure changes)
- Document-centric data (natural JSON/BSON)
- Horizontal scaling (sharding)
- High write throughput (IoT, logging)
- Rapid prototyping
### Both Support:
JSON/JSONB, full-text search, geospatial, replication, ACID transactions.
---
## Query Optimization
### EXPLAIN Everything
```sql
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;
-- Look for: Seq Scan (bad) vs Index Scan (good)
-- Check: actual time, rows, loops
```
### Never SELECT *
```sql
-- ❌ BAD
SELECT * FROM users;
-- ✅ GOOD — only needed columns
SELECT id, name, email FROM users WHERE role = 'admin' LIMIT 20;
```
---
## N+1 Prevention
```
❌ BAD (N+1):
users = fetchUsers() -- 1 query
for user in users:
orders = fetchOrders(user.id) -- N queries
✅ GOOD (batch):
users = fetchUsers() -- 1 query
userIds = users.map(u => u.id)
orders = fetchOrdersByUserIds(userIds) -- 1 query
ordersMap = groupBy(orders, 'userId')
-- Total: 2 queries regardless of N
```
ORM solutions:
- **SQLAlchemy**: `joinedload()`, `selectinload()`
- **Prisma**: `include: { orders: true }`
- **TypeORM**: `relations: ['orders']`
- **Mongoose**: `.populate('orders')`
---
## Index Strategy
| Type | Use Case | Example |
| ---------- | ------------------- | ----------------------------------------------------------------------- |
| Single | Equality lookups | `CREATE INDEX idx_users_email ON users(email)` |
| Composite | Multi-column WHERE | `CREATE INDEX idx_orders_user_status ON orders(user_id, status)` |
| Partial | Filtered subsets | `CREATE INDEX idx_active ON orders(created_at) WHERE status = 'active'` |
| Covering | Avoid table lookups | `CREATE INDEX idx_email_name ON users(email) INCLUDE (name)` |
| Expression | Computed values | `CREATE INDEX idx_lower_email ON users(LOWER(email))` |
**Rule of thumb:** If a WHERE clause column appears in slow queries → index it.
---
## Transactions
- Use the framework's transaction API — never manual `BEGIN`/`COMMIT`
- Keep transactions SHORT — no network calls inside
- Deadlock prevention: always acquire locks in consistent order
- Use `SERIALIZABLE` isolation only when required
---
## Migrations
- One file per schema change, timestamped
- Always include rollback (reverse migration)
- **Never modify an applied migration**
- Test on copy of production data before deploying
- Use tools: Alembic (Python), Prisma Migrate (Node), Flyway (Java)
---
## Connection Pooling
| Tool | Language | Purpose |
| ---------- | ----------- | ------------------------ |
| pgBouncer | Any | External pool proxy |
| pg.Pool | Node.js | Built-in connection pool |
| SQLAlchemy | Python | Built-in `pool_size` |
| HikariCP | Java/Kotlin | High-performance pool |
Settings:
- `pool_size`: number of DB connections
- `max_overflow`: extra connections when pool is full
- `pool_timeout`: wait time for connection
- `pool_recycle`: recycle connections to prevent stale
---
## PostgreSQL Specifics
### Performance Maintenance
```sql
-- Regular maintenance
VACUUM ANALYZE table_name;
-- Check table bloat
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- Check missing indexes
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND seq_scan > 100;
```
### Useful Extensions
| Extension | Purpose |
| ------------------ | -------------------------- |
| pg_stat_statements | Query performance tracking |
| pgvector | AI vector search |
| PostGIS | Geospatial data |
| uuid-ossp | UUID generation |
| pg_trgm | Fuzzy text search |
---
## MongoDB Specifics
### Aggregation Pipeline
```javascript
db.orders.aggregate([
{ $match: { status: "completed" } },
{ $group: { _id: "$userId", total: { $sum: "$amount" } } },
{ $sort: { total: -1 } },
{ $limit: 10 }
]);
```
### Schema Design Patterns
- **Embedded**: 1-to-few relationships (address in user doc)
- **Referenced**: 1-to-many or many-to-many (separate collection + $lookup)
- **Bucket**: time-series grouping (hourly/daily buckets)
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |