Databases

Expert guidance for 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
• Managing database users and permissions
• Analyzing slow queries and performance issues

Quick Start

MongoDB

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

# Connect
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 } })

PostgreSQL

# Install (Ubuntu/Debian)
sudo apt-get install postgresql postgresql-contrib
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';

Common Use Cases

Schema Design for E-commerce

“Design a MongoDB schema for an e-commerce platform with products, users, orders, and reviews”

“Create a PostgreSQL schema for an e-commerce platform with proper normalization and foreign keys”

Query Optimization

“Optimize this slow MongoDB aggregation pipeline that processes user analytics data”

“Analyze and improve this PostgreSQL query that joins 5 tables and takes 10 seconds”

Database Migration

“Generate a migration to add a new ‘status’ field to all documents in MongoDB users collection”

“Create a PostgreSQL migration to add a composite index on orders(user_id, created_at)“

Performance Analysis

“Analyze MongoDB slow query log and recommend indexes for collections with high read latency”

“Use EXPLAIN ANALYZE to diagnose why this PostgreSQL query is doing sequential scans”

Key Differences

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

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
• Complex queries: Window functions, CTEs, analytical workloads

Quick Reference

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);

Common Operations Comparison

Pro Tips

• Specify database context upfront: “Using MongoDB” or “Using PostgreSQL”
• For complex queries, provide sample data structure
• Mention performance requirements: “Query needs to run under 100ms”
• Include current index strategy when optimizing
• Not activating? Say: “Use databases skill to design a MongoDB schema for…”

Related Skills

• Backend Development - Full backend implementation
• DevOps - Database deployment and management

Key Takeaway

Choose MongoDB for flexible schemas and horizontal scaling, PostgreSQL for ACID transactions and complex relationships—both support JSON, full-text search, and geospatial queries.