databases

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/