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/