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

Database Selection Guide

Choose MongoDB When:

• •Schema flexibility: frequent structure changes, heterogeneous data
• •Document-centric: natural JSON/BSON data model
• •Horizontal scaling: need to shard across multiple servers
• •High write throughput: IoT, logging, real-time analytics
• •Nested/hierarchical data: embedded documents preferred
• •Rapid prototyping: schema evolution without migrations

Best for: Content management, catalogs, IoT time series, real-time analytics, mobile apps, user profiles

Choose PostgreSQL When:

• •Strong consistency: ACID transactions critical
• •Complex relationships: many-to-many joins, referential integrity
• •SQL requirement: team expertise, reporting tools, BI systems
• •Data integrity: strict schema validation, constraints
• •Mature ecosystem: extensive tooling, extensions
• •Complex queries: window functions, CTEs, analytical workloads

Best for: Financial systems, e-commerce transactions, ERP, CRM, data warehousing, analytics

Both Support:

• •JSON/JSONB storage and querying
• •Full-text search capabilities
• •Geospatial queries and indexing
• •Replication and high availability
• •ACID transactions (MongoDB 4.0+)
• •Strong security features

Quick Start

MongoDB Setup

# Atlas (Cloud) - Recommended
# 1. Sign up at mongodb.com/atlas
# 2. Create M0 free cluster
# 3. Get connection string

# Connection
mongodb+srv://user:pass@cluster.mongodb.net/db

# Shell
mongosh "mongodb+srv://cluster.mongodb.net/mydb"

# Basic operations
db.users.insertOne({ name: "Alice", age: 30 })
db.users.find({ age: { $gte: 18 } })
db.users.updateOne({ name: "Alice" }, { $set: { age: 31 } })
db.users.deleteOne({ name: "Alice" })

PostgreSQL Setup

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql

# Connect
psql -U postgres -d mydb

# Basic operations
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users WHERE age >= 18;
UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE name = 'Alice';

Common Operations

Create/Insert

// MongoDB
db.users.insertOne({ name: "Bob", email: "bob@example.com" })
db.users.insertMany([{ name: "Alice" }, { name: "Charlie" }])

-- PostgreSQL
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Alice', NULL), ('Charlie', NULL);

Read/Query

// MongoDB
db.users.find({ age: { $gte: 18 } })
db.users.findOne({ email: "bob@example.com" })

-- PostgreSQL
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE email = 'bob@example.com' LIMIT 1;

Update

// MongoDB
db.users.updateOne({ name: "Bob" }, { $set: { age: 25 } })
db.users.updateMany({ status: "pending" }, { $set: { status: "active" } })

-- PostgreSQL
UPDATE users SET age = 25 WHERE name = 'Bob';
UPDATE users SET status = 'active' WHERE status = 'pending';

Delete

// MongoDB
db.users.deleteOne({ name: "Bob" })
db.users.deleteMany({ status: "deleted" })

-- PostgreSQL
DELETE FROM users WHERE name = 'Bob';
DELETE FROM users WHERE status = 'deleted';

Indexing

// MongoDB
db.users.createIndex({ email: 1 })
db.users.createIndex({ status: 1, createdAt: -1 })

-- PostgreSQL
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

Reference Navigation

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
• •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

Key Differences Summary

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/