majiayu000 Databases Skill
Unified guide for working with MongoDB (document-oriented) and PostgreSQL (relational) databases. Choose the right database for your use case and master both systems.
When to Use This Skill
Use when:
- 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
- Managing database users and permissions
- Analyzing slow queries and performance issues
- Administering production database deployments
Reference Navigation
Database Design
- db-design.md - Activate when user requests: Database/table design for transactional (OLTP), analytics (OLAP), create or extend schema, design fact/dimension tables, analyze/review CSV/JSON/SQL files to create tables, or need advice on data storage structure.
MongoDB References
- mongodb-crud.md - CRUD operations, query operators, atomic updates
- mongodb-aggregation.md - Aggregation pipeline, stages, operators, patterns
- mongodb-indexing.md - Index types, compound indexes, performance optimization
- mongodb-atlas.md - Atlas cloud setup, clusters, monitoring, search
PostgreSQL References
- postgresql-queries.md - SELECT, JOINs, subqueries, CTEs, window functions
- postgresql-psql-cli.md - psql commands, meta-commands, scripting
- postgresql-performance.md - EXPLAIN, query optimization, vacuum, indexes
- postgresql-administration.md - User management, backups, replication, maintenance
Python Utilities
Database utility scripts in scripts/:
- db_migrate.py - Generate and apply migrations for both databases (MongoDB and PostgreSQL)
- db_backup.py - Backup and restore MongoDB and PostgreSQL
- db_performance_check.py - Analyze slow queries and recommend indexes
# Generate migration
python scripts/db_migrate.py --db mongodb --generate "add_user_index"
# Run backup
python scripts/db_backup.py --db postgres --output /backups/
# Check performance
python scripts/db_performance_check.py --db mongodb --threshold 100ms
Best Practices
MongoDB:
- Use embedded documents for 1-to-few relationships
- Reference documents for 1-to-many or many-to-many
- Index frequently queried fields
- Use aggregation pipeline for complex transformations
- Enable authentication and TLS in production
- Use Atlas for managed hosting
PostgreSQL:
- Normalize schema to 3NF, denormalize 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 (pgBouncer) for web apps
Resources
- MongoDB: https://www.mongodb.com/docs/
- PostgreSQL: https://www.postgresql.org/docs/
- MongoDB University: https://learn.mongodb.com/
- PostgreSQL Tutorial: https://www.postgresqltutorial.com/