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 Feature MongoDB PostgreSQL Data Model Document (JSON/BSON) Relational (Tables/Rows) Schema Flexible, dynamic Strict, predefined Query Language MongoDB Query Language SQL Joins $lookup (limited) Native, optimized Transactions Multi-document (4.0+) Native ACID Scaling Horizontal (sharding) Vertical (primary), Horizontal (extensions) Indexes Single, compound, text, geo, etc B-tree, hash, GiST, GIN, etc 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/