108 lines
3.7 KiB
Markdown
108 lines
3.7 KiB
Markdown
---
|
|
name: ck-databases
|
|
description: Design schemas and write queries for MongoDB and PostgreSQL. Use for database design, SQL/NoSQL queries, aggregation pipelines, indexes, migrations, replication, performance optimization, psql CLI operations.
|
|
---
|
|
|
|
# ck-databases
|
|
|
|
Unified guide for working with MongoDB (document-oriented) and PostgreSQL (relational) databases.
|
|
|
|
## When to Use
|
|
|
|
- Designing database schemas and data models
|
|
- Writing queries (SQL or MongoDB query language)
|
|
- Building aggregation pipelines or complex joins
|
|
- Optimizing indexes and query performance
|
|
- Implementing database migrations
|
|
- Setting up replication, sharding, or clustering
|
|
- Configuring backups and disaster recovery
|
|
- Analyzing slow queries and performance issues
|
|
- Administering production database deployments
|
|
|
|
## Don't Use When
|
|
|
|
- ORM/query-builder API work only — refer to the specific library's docs via `ck-docs-seeker`
|
|
- Infrastructure setup for managed database services — use `ck-devops`
|
|
|
|
## Database Selection Guide
|
|
|
|
| Need | Choose |
|
|
|------|--------|
|
|
| ACID transactions, complex joins | PostgreSQL |
|
|
| Flexible document schema | MongoDB |
|
|
| Caching, sessions, queues | Redis |
|
|
| Analytics/OLAP workloads | PostgreSQL with proper indexing |
|
|
| 1-to-few relationships | MongoDB embedded documents |
|
|
| 1-to-many or many-to-many | MongoDB references or PostgreSQL FK |
|
|
|
|
## MongoDB Best Practices
|
|
|
|
- Use embedded documents for 1-to-few relationships
|
|
- Reference documents for 1-to-many or many-to-many
|
|
- Index frequently queried and sorted fields
|
|
- Use aggregation pipeline for complex transformations
|
|
- Enable authentication and TLS in production
|
|
- Use Atlas for managed hosting
|
|
|
|
**Common patterns:**
|
|
- CRUD operations with query operators and atomic updates
|
|
- Aggregation pipeline stages: `$match`, `$group`, `$lookup`, `$project`, `$sort`
|
|
- Index types: single field, compound, text, geospatial, TTL
|
|
- Atlas search for full-text capabilities
|
|
|
|
## PostgreSQL Best Practices
|
|
|
|
- Normalize schema to 3NF; denormalize selectively for performance
|
|
- Use foreign keys for referential integrity
|
|
- Index foreign keys and frequently filtered columns
|
|
- Use `EXPLAIN ANALYZE` to optimize queries
|
|
- Regular `VACUUM` and `ANALYZE` maintenance
|
|
- Connection pooling via pgBouncer for web apps
|
|
|
|
**Key features:**
|
|
- `SELECT`, JOINs, subqueries, CTEs, window functions
|
|
- `psql` meta-commands for administration
|
|
- `EXPLAIN`, query optimization, vacuum, index maintenance
|
|
- User management, backups, replication, point-in-time recovery
|
|
|
|
## Python Utility Scripts
|
|
|
|
Available in the skill's `scripts/` directory:
|
|
|
|
```bash
|
|
# Generate and apply migrations
|
|
python scripts/db_migrate.py --db mongodb --generate "add_user_index"
|
|
python scripts/db_migrate.py --db postgres --apply
|
|
|
|
# Backup and restore
|
|
python scripts/db_backup.py --db postgres --output /backups/
|
|
|
|
# Performance analysis
|
|
python scripts/db_performance_check.py --db mongodb --threshold 100ms
|
|
```
|
|
|
|
## Performance Optimization Checklist
|
|
|
|
- [ ] Indexes on frequently queried fields
|
|
- [ ] Compound indexes ordered by selectivity
|
|
- [ ] `EXPLAIN ANALYZE` run on slow queries
|
|
- [ ] Connection pooling configured
|
|
- [ ] Query result caching with Redis where appropriate
|
|
- [ ] Pagination instead of full table scans
|
|
- [ ] Read replicas for read-heavy workloads
|
|
- [ ] Partitioning for very large tables (PostgreSQL)
|
|
|
|
## Security Checklist
|
|
|
|
- [ ] Authentication enabled (no anonymous access)
|
|
- [ ] TLS/SSL for connections in production
|
|
- [ ] Parameterized queries everywhere (never string concatenation)
|
|
- [ ] Least-privilege database users
|
|
- [ ] Regular backups tested for restore
|
|
- [ ] Audit logging enabled for sensitive operations
|
|
|
|
## Resources
|
|
|
|
- MongoDB: https://www.mongodb.com/docs/
|
|
- PostgreSQL: https://www.postgresql.org/docs/
|