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

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

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

Read/Query

Update

Delete

Indexing

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

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/

databases

Databases Skill

Database Selection Guide