MongoDB vs PostgreSQL: NoSQL vs SQL Database Selection Guide

Compare MongoDB and PostgreSQL to choose the right database. Learn when to use NoSQL vs SQL, data modeling differences, performance trade-offs, and migration strategies.

Introduction
Understanding Database Paradigms
MongoDB: Document-Oriented NoSQL Database
PostgreSQL: Advanced Relational Database
Data Modeling Comparison
Query Language and API Comparison
Performance Comparison
Scalability and Architecture
ACID Compliance and Transactions
Use Case Scenarios
Migration Strategies
Best Practices
Conclusion

Introduction

Choosing between MongoDB and PostgreSQL is one of the most fundamental decisions in modern application development. This choice represents more than selecting a database—it’s choosing between two different paradigms: the flexible, document-oriented NoSQL approach of MongoDB versus the structured, relational SQL approach of PostgreSQL.

MongoDB, introduced in 2009, revolutionized database design by offering a schema-less, JSON-like document model that aligns naturally with modern application development. PostgreSQL, with roots dating back to 1986, has evolved into one of the most advanced open-source relational databases, offering ACID compliance, complex queries, and extensive data types.

Both databases are production-ready, widely adopted, and capable of handling enterprise-scale applications. However, they excel in different scenarios. MongoDB shines when you need flexible schemas, rapid development, and horizontal scaling for document-heavy workloads. PostgreSQL excels when you require complex relationships, strong consistency guarantees, and sophisticated querying capabilities.

Understanding their differences, strengths, and trade-offs will help you make an informed decision that aligns with your application’s data structure, query patterns, scalability requirements, and consistency needs. This guide will provide practical examples, performance insights, and real-world use cases to help you choose the right database for your project.

Understanding Database Paradigms

Before diving into MongoDB and PostgreSQL specifics, it’s essential to understand the fundamental differences between NoSQL and SQL database paradigms.

SQL (Relational) Databases

SQL databases, also known as relational database management systems (RDBMS), organize data into tables with predefined schemas. Data is stored in rows and columns, with relationships between tables maintained through foreign keys. This structure enforces data integrity and consistency through constraints, transactions, and ACID properties.

Key characteristics:

Structured schema with tables, rows, and columns
Relationships enforced through foreign keys
ACID transactions for data consistency
SQL query language for complex queries
Normalized data structure to reduce redundancy

NoSQL (Non-Relational) Databases

NoSQL databases come in several forms: document stores (like MongoDB), key-value stores, column-family stores, and graph databases. They prioritize flexibility, scalability, and performance over strict consistency guarantees.

Key characteristics:

Flexible or schema-less data models
Horizontal scalability (sharding)
Optimized for specific access patterns
Often eventual consistency (though MongoDB supports transactions)
Various query languages or APIs

When Each Paradigm Makes Sense

Choose SQL (PostgreSQL) when:

Your data has clear relationships and structure
You need complex queries with joins across multiple tables
ACID compliance is critical
Data integrity and consistency are paramount
You’re building financial, healthcare, or compliance-heavy applications

Choose NoSQL (MongoDB) when:

Your data structure is flexible or changes frequently
You need rapid development and iteration
Horizontal scaling is a primary concern
Your data is document-oriented (JSON-like structures)
You’re building content management, logging, or real-time analytics systems

MongoDB: Document-Oriented NoSQL Database

MongoDB is a document-oriented database that stores data in BSON (Binary JSON) format. Documents are organized into collections, similar to tables in relational databases, but without enforced schemas.

Core Concepts

Documents: The basic unit of data in MongoDB, similar to rows in SQL databases but stored as JSON-like objects:

// Example MongoDB document
{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  name: "John Doe",
  email: "john@example.com",
  age: 30,
  address: {
    street: "123 Main St",
    city: "New York",
    zipCode: "10001"
  },
  hobbies: ["reading", "coding", "hiking"],
  createdAt: ISODate("2024-01-15T10:30:00Z")
}

Collections: Groups of documents, similar to tables but without schema enforcement:

// MongoDB collections are created implicitly
db.users.insertOne({
  name: "Jane Smith",
  email: "jane@example.com",
});

// No schema definition needed - documents can have different structures
db.users.insertOne({
  name: "Bob Johnson",
  email: "bob@example.com",
  age: 25,
  phone: "+1234567890", // Different structure, no problem
});

Key Features

1. Flexible Schema Design

MongoDB’s schema-less nature allows documents in the same collection to have different structures:

// All valid in the same collection
db.products.insertMany([
  {
    name: "Laptop",
    price: 999.99,
    specifications: {
      cpu: "Intel i7",
      ram: "16GB",
      storage: "512GB SSD",
    },
  },
  {
    name: "Book",
    price: 19.99,
    author: "Jane Author",
    pages: 300,
    isbn: "978-0123456789",
  },
]);

2. Rich Query Language

MongoDB provides a powerful query API with support for filtering, projection, aggregation, and more:

// Find documents matching criteria
db.users.find({
  age: { $gte: 18, $lte: 65 },
  city: "New York",
});

// Complex queries with operators
db.orders.find({
  $or: [{ status: "pending" }, { total: { $gt: 100 } }],
  createdAt: { $gte: new Date("2024-01-01") },
});

// Aggregation pipeline for complex data processing
db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $group: {
      _id: "$customerId",
      totalSpent: { $sum: "$total" },
      orderCount: { $sum: 1 },
    },
  },
  { $sort: { totalSpent: -1 } },
  { $limit: 10 },
]);

3. Horizontal Scaling (Sharding)

MongoDB supports automatic sharding to distribute data across multiple servers:

// Enable sharding for a database
sh.enableSharding("ecommerce");

// Shard a collection by a shard key
sh.shardCollection("ecommerce.orders", { customerId: 1 });

// MongoDB automatically distributes documents across shards
// based on the shard key value

4. Indexing for Performance

MongoDB supports various index types for query optimization:

// Create indexes
db.users.createIndex({ email: 1 }); // Single field index
db.users.createIndex({ name: 1, age: -1 }); // Compound index
db.users.createIndex({ location: "2dsphere" }); // Geospatial index
db.users.createIndex({ "$**": "text" }); // Text search index

// Index usage in queries
db.users.find({ email: "john@example.com" }).explain("executionStats");

5. Replication and High Availability

MongoDB replica sets provide automatic failover and data redundancy:

// Replica set configuration (typically done via config file)
// Primary node handles writes
// Secondary nodes replicate data and can handle reads

// Read from secondary for read scaling
db.users.find().readPref("secondary");

MongoDB Advantages

✅ Schema Flexibility: Adapt to changing requirements without migrations ✅ Developer Experience: Natural fit for JavaScript/Node.js applications ✅ Horizontal Scaling: Built-in sharding for massive scale ✅ Rapid Development: No schema migrations needed during development ✅ Rich Data Types: Supports arrays, nested objects, and various data types ✅ JSON-like Structure: Aligns with modern API data formats

MongoDB Limitations

❌ No Joins: Must use application-level joins or denormalize data ❌ Consistency Trade-offs: Eventual consistency by default (though transactions available) ❌ Memory Usage: Entire documents loaded into memory ❌ Query Complexity: Complex relational queries can be challenging ❌ Schema Enforcement: No built-in schema validation (requires application-level or JSON Schema)

PostgreSQL: Advanced Relational Database

PostgreSQL is a powerful, open-source object-relational database system known for its standards compliance, extensibility, and advanced features. It combines the reliability of SQL with modern capabilities like JSON support, full-text search, and advanced indexing.

Core Concepts

Tables: Structured data storage with enforced schemas:

-- Create a table with schema definition
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data (must match schema)
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

Relationships: Foreign keys enforce referential integrity:

-- Create related tables with foreign key relationships
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  price DECIMAL(10, 2) NOT NULL
);

Key Features

1. ACID Compliance

PostgreSQL guarantees ACID properties for all transactions:

-- Transaction example with rollback on error
BEGIN;

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO orders (user_id, total) VALUES (1, 99.99);

-- If any operation fails, entire transaction rolls back
COMMIT;

-- Or explicitly rollback
ROLLBACK;

2. Complex Queries with JOINs

PostgreSQL excels at relational queries:

-- Join multiple tables
SELECT
  u.name,
  u.email,
  o.id AS order_id,
  o.total,
  o.status,
  COUNT(oi.id) AS item_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id, u.name, u.email, o.id, o.total, o.status
HAVING COUNT(oi.id) > 0
ORDER BY o.total DESC;

3. Advanced Data Types

PostgreSQL supports extensive data types beyond standard SQL:

-- JSON/JSONB support
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  metadata JSONB, -- Binary JSON for better performance
  tags TEXT[] -- Array type
);

-- Query JSON data
INSERT INTO products (name, metadata, tags)
VALUES (
  'Laptop',
  '{"specs": {"cpu": "Intel i7", "ram": "16GB"}}'::jsonb,
  ARRAY['electronics', 'computers']
);

SELECT name, metadata->'specs'->>'cpu' AS cpu
FROM products
WHERE metadata @> '{"specs": {"ram": "16GB"}}';

4. Full-Text Search

Built-in full-text search capabilities:

-- Create full-text search index
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  content TEXT,
  tsvector_content tsvector -- Full-text search vector
);

CREATE INDEX idx_articles_search ON articles USING GIN (tsvector_content);

-- Update tsvector
UPDATE articles
SET tsvector_content = to_tsvector('english', title || ' ' || content);

-- Full-text search query
SELECT title, content
FROM articles
WHERE tsvector_content @@ to_tsquery('english', 'database & performance');

5. Extensibility

PostgreSQL supports extensions and custom functions:

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- Trigram similarity

-- Use UUID extension
CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id INTEGER REFERENCES users(id),
  expires_at TIMESTAMP
);

-- Custom functions
CREATE OR REPLACE FUNCTION get_user_order_total(user_id_param INTEGER)
RETURNS DECIMAL AS $$
  SELECT COALESCE(SUM(total), 0)
  FROM orders
  WHERE user_id = user_id_param AND status = 'completed';
$$ LANGUAGE SQL;

6. Advanced Indexing

PostgreSQL offers multiple index types for different use cases:

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- GIN index for JSONB and arrays
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- GiST index for geometric data
CREATE INDEX idx_locations_coords ON locations USING GIST(coordinates);

-- Partial index (index subset of data)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

PostgreSQL Advantages

✅ ACID Compliance: Strong consistency guarantees ✅ Complex Queries: Powerful JOIN operations and subqueries ✅ Data Integrity: Foreign keys, constraints, and triggers ✅ Extensibility: Rich ecosystem of extensions ✅ JSON Support: Can handle document-like data when needed ✅ Mature Ecosystem: Extensive tooling and community support ✅ Standards Compliance: SQL standard compliance

PostgreSQL Limitations

❌ Vertical Scaling: Primarily scales vertically (though supports read replicas) ❌ Schema Rigidity: Schema changes require migrations ❌ Complex Setup: More configuration needed for optimal performance ❌ Join Overhead: Complex joins can be expensive at scale ❌ Development Speed: Schema changes slow down rapid iteration

Data Modeling Comparison

The fundamental difference between MongoDB and PostgreSQL lies in how they model and store data. Understanding these differences is crucial for making the right choice.

MongoDB: Document Model

MongoDB uses a denormalized, document-oriented approach where related data is often embedded within documents:

// Denormalized approach - embed related data
db.orders.insertOne({
  _id: ObjectId("507f1f77bcf86cd799439011"),
  orderNumber: "ORD-001",
  customer: {
    id: ObjectId("507f191e810c19729de860ea"),
    name: "John Doe",
    email: "john@example.com",
  },
  items: [
    {
      productId: ObjectId("507f191e810c19729de860eb"),
      name: "Laptop",
      price: 999.99,
      quantity: 1,
    },
    {
      productId: ObjectId("507f191e810c19729de860ec"),
      name: "Mouse",
      price: 29.99,
      quantity: 2,
    },
  ],
  total: 1059.97,
  status: "pending",
  createdAt: ISODate("2024-01-15T10:30:00Z"),
});

// Or normalized approach with references
db.orders.insertOne({
  _id: ObjectId("507f1f77bcf86cd799439012"),
  orderNumber: "ORD-002",
  customerId: ObjectId("507f191e810c19729de860ea"),
  itemIds: [
    ObjectId("507f191e810c19729de860eb"),
    ObjectId("507f191e810c19729de860ec"),
  ],
  total: 1059.97,
  status: "pending",
});

// Application-level join required
const order = db.orders.findOne({ orderNumber: "ORD-002" });
const customer = db.users.findOne({ _id: order.customerId });
const items = db.products.find({ _id: { $in: order.itemIds } }).toArray();

PostgreSQL: Relational Model

PostgreSQL uses a normalized approach with separate tables and relationships:

-- Normalized schema with relationships
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  price DECIMAL(10, 2) NOT NULL
);

-- Query with automatic joins
SELECT
  o.order_number,
  c.name AS customer_name,
  c.email,
  p.name AS product_name,
  oi.quantity,
  oi.price,
  o.total,
  o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_number = 'ORD-002';

Modeling Decision Factors

Choose MongoDB’s Document Model When:

Data is naturally hierarchical or tree-like
Related data is accessed together frequently
Schema changes are frequent
Read performance is more important than write consistency
Data doesn’t have complex many-to-many relationships

Choose PostgreSQL’s Relational Model When:

Data has clear relationships and structure
You need to query across multiple entities frequently
Data integrity and consistency are critical
You need complex analytical queries
Normalization reduces data redundancy significantly

Hybrid Approaches

Both databases can handle the other’s paradigm to some extent:

PostgreSQL with JSON:

-- Store flexible document data in PostgreSQL
CREATE TABLE user_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  preferences JSONB, -- Flexible document data
  metadata JSONB
);

-- Query JSON data with relational queries
SELECT u.name, up.preferences->>'theme' AS theme
FROM users u
JOIN user_profiles up ON u.id = up.user_id
WHERE up.preferences @> '{"notifications": true}';

MongoDB with References:

// Use references when normalization makes sense
db.authors.insertOne({
  _id: ObjectId("507f191e810c19729de860ea"),
  name: "Jane Author",
  bio: "Expert in databases",
});

db.books.insertMany([
  {
    title: "Database Design",
    authorId: ObjectId("507f191e810c19729de860ea"),
    isbn: "978-0123456789",
  },
  {
    title: "Advanced Queries",
    authorId: ObjectId("507f191e810c19729de860ea"),
    isbn: "978-0123456790",
  },
]);

// Application-level join
const author = db.authors.findOne({
  _id: ObjectId("507f191e810c19729de860ea"),
});
const books = db.books.find({ authorId: author._id }).toArray();

Query Language and API Comparison

The way you interact with MongoDB and PostgreSQL differs significantly, reflecting their underlying paradigms.

MongoDB Query API

MongoDB uses a method-based API with JavaScript-like syntax:

// Basic CRUD operations
// Create
db.users.insertOne({
  name: "John Doe",
  email: "john@example.com",
  age: 30,
});

db.users.insertMany([
  { name: "Alice", email: "alice@example.com", age: 25 },
  { name: "Bob", email: "bob@example.com", age: 35 },
]);

// Read
db.users.findOne({ email: "john@example.com" });
db.users.find({ age: { $gte: 30 } });
db.users
  .find({ age: { $gte: 30 } })
  .limit(10)
  .sort({ name: 1 });

// Update
db.users.updateOne({ email: "john@example.com" }, { $set: { age: 31 } });

db.users.updateMany({ age: { $lt: 18 } }, { $set: { status: "minor" } });

// Delete
db.users.deleteOne({ email: "john@example.com" });
db.users.deleteMany({ status: "inactive" });

Aggregation Pipeline:

// Complex data processing with aggregation pipeline
db.orders.aggregate([
  // Stage 1: Match filter
  {
    $match: {
      status: "completed",
      createdAt: { $gte: new Date("2024-01-01") },
    },
  },
  // Stage 2: Unwind array
  {
    $unwind: "$items",
  },
  // Stage 3: Group and calculate
  {
    $group: {
      _id: "$items.productId",
      totalQuantity: { $sum: "$items.quantity" },
      totalRevenue: {
        $sum: { $multiply: ["$items.price", "$items.quantity"] },
      },
      orderCount: { $sum: 1 },
    },
  },
  // Stage 4: Lookup related data
  {
    $lookup: {
      from: "products",
      localField: "_id",
      foreignField: "_id",
      as: "product",
    },
  },
  // Stage 5: Unwind lookup result
  {
    $unwind: "$product",
  },
  // Stage 6: Project final shape
  {
    $project: {
      productName: "$product.name",
      totalQuantity: 1,
      totalRevenue: 1,
      orderCount: 1,
      averageOrderValue: { $divide: ["$totalRevenue", "$orderCount"] },
    },
  },
  // Stage 7: Sort
  {
    $sort: { totalRevenue: -1 },
  },
  // Stage 8: Limit
  {
    $limit: 10,
  },
]);

PostgreSQL SQL Queries

PostgreSQL uses standard SQL with powerful extensions:

-- Basic CRUD operations
-- Create
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

INSERT INTO users (name, email, age)
VALUES
  ('Alice', 'alice@example.com', 25),
  ('Bob', 'bob@example.com', 35);

-- Read
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM users WHERE age >= 30 LIMIT 10 ORDER BY name ASC;

-- Update
UPDATE users SET age = 31 WHERE email = 'john@example.com';
UPDATE users SET status = 'minor' WHERE age < 18;

-- Delete
DELETE FROM users WHERE email = 'john@example.com';
DELETE FROM users WHERE status = 'inactive';

Complex Queries with JOINs:

-- Equivalent aggregation using SQL
SELECT
  p.id AS product_id,
  p.name AS product_name,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.price * oi.quantity) AS total_revenue,
  COUNT(DISTINCT o.id) AS order_count,
  AVG(oi.price * oi.quantity) AS average_order_value
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01'
GROUP BY p.id, p.name
ORDER BY total_revenue DESC
LIMIT 10;

Advanced SQL Features:

-- Window functions
SELECT
  name,
  email,
  age,
  ROW_NUMBER() OVER (PARTITION BY age ORDER BY name) AS age_rank,
  AVG(age) OVER () AS average_age
FROM users;

-- Common Table Expressions (CTEs)
WITH user_orders AS (
  SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
),
top_customers AS (
  SELECT * FROM user_orders
  WHERE total_spent > 1000
  ORDER BY total_spent DESC
  LIMIT 10
)
SELECT * FROM top_customers;

-- Subqueries
SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total > 500
);

Query Performance Considerations

MongoDB:

Indexes are crucial for query performance
Aggregation pipeline can be optimized with indexes
Sharding distributes query load
Document size affects read performance

// Explain query execution plan
db.users.find({ email: "john@example.com" }).explain("executionStats");

// Create compound index for common query patterns
db.orders.createIndex({ customerId: 1, createdAt: -1 });

// Use projection to limit returned fields
db.users.find({ age: { $gte: 30 } }, { name: 1, email: 1 });

PostgreSQL:

Query planner optimizes execution automatically
Indexes improve JOIN and WHERE clause performance
EXPLAIN ANALYZE shows execution plan
Connection pooling important for concurrent queries

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- Create indexes for common query patterns
CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

-- Use SELECT specific columns instead of SELECT *
SELECT name, email FROM users WHERE age >= 30;

Performance Comparison

Performance characteristics differ significantly between MongoDB and PostgreSQL, influenced by data structure, query patterns, and scaling approaches.

Read Performance

MongoDB Read Performance:

// Single document read (very fast with index)
db.users.findOne({ email: "john@example.com" });
// Typically < 1ms with proper index

// Range query with index
db.orders
  .find({
    customerId: ObjectId("507f191e810c19729de860ea"),
    createdAt: { $gte: new Date("2024-01-01") },
  })
  .sort({ createdAt: -1 });
// Fast with compound index on (customerId, createdAt)

// Aggregation pipeline performance
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$customerId", total: { $sum: "$total" } } },
]);
// Performance depends on indexes and data volume

PostgreSQL Read Performance:

-- Single row read (very fast with index)
SELECT * FROM users WHERE email = 'john@example.com';
-- Typically < 1ms with B-tree index

-- JOIN query performance
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
WHERE u.email = 'john@example.com';
-- Performance depends on JOIN algorithm and indexes

-- Complex analytical query
SELECT
  customer_id,
  SUM(total) AS total_spent,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;
-- Can be optimized with indexes and materialized views

Write Performance

MongoDB Write Performance:

// Single document insert
db.users.insertOne({
  name: "John Doe",
  email: "john@example.com",
});
// Very fast, typically < 1ms

// Bulk insert
db.users.insertMany(
  [
    { name: "User1", email: "user1@example.com" },
    { name: "User2", email: "user2@example.com" },
    // ... thousands more
  ],
  { ordered: false },
);
// Can achieve 10,000+ inserts/second

// Update operations
db.users.updateMany(
  { status: "inactive" },
  { $set: { lastLogin: new Date() } },
);
// Performance depends on matching documents and indexes

PostgreSQL Write Performance:

-- Single row insert
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
-- Fast, typically < 1ms

-- Bulk insert
INSERT INTO users (name, email)
VALUES
  ('User1', 'user1@example.com'),
  ('User2', 'user2@example.com');
  -- ... more rows
-- Can achieve high throughput with COPY command

-- COPY for bulk loading (fastest method)
COPY users (name, email) FROM '/path/to/file.csv' WITH CSV;
-- Can achieve 100,000+ rows/second

-- Update with transaction
BEGIN;
UPDATE users SET last_login = NOW() WHERE status = 'inactive';
COMMIT;
-- ACID guarantees add overhead but ensure consistency

Performance Benchmarks Considerations

⚠️ Important: Performance varies significantly based on:

Hardware configuration (CPU, RAM, storage type)
Data volume and document/row size
Index strategy
Query patterns
Network latency
Concurrent load

General Performance Characteristics:

Operation	MongoDB	PostgreSQL	Notes
Single document/row read	Very Fast	Very Fast	Both excel with proper indexes
Complex JOINs	N/A (app-level)	Fast	PostgreSQL advantage
Aggregation/Analytics	Fast	Very Fast	PostgreSQL often faster for complex queries
Bulk inserts	Very Fast	Fast	MongoDB slightly faster
Updates	Fast	Fast	Similar performance
Transactions	Fast (v4.0+)	Very Fast	PostgreSQL has longer history
Horizontal scaling	Excellent	Limited	MongoDB advantage

Optimization Strategies

MongoDB Optimization:

// 1. Create appropriate indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.orders.createIndex({ customerId: 1, createdAt: -1 });

// 2. Use projection to limit returned data
db.users.find({ age: { $gte: 30 } }, { name: 1, email: 1 });

// 3. Use aggregation pipeline efficiently
db.orders.aggregate([
  { $match: { status: "completed" } }, // Early filtering
  { $group: { _id: "$customerId", total: { $sum: "$total" } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
]);

// 4. Enable read preferences for replica sets
db.users.find().readPref("secondaryPreferred");

// 5. Use bulk operations
const bulkOps = users.map((user) => ({
  updateOne: {
    filter: { email: user.email },
    update: { $set: user },
    upsert: true,
  },
}));
db.users.bulkWrite(bulkOps);

PostgreSQL Optimization:

-- 1. Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

-- 2. Use EXPLAIN ANALYZE to optimize queries
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- 3. Use materialized views for expensive queries
CREATE MATERIALIZED VIEW customer_totals AS
SELECT
  customer_id,
  SUM(total) AS total_spent,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

CREATE UNIQUE INDEX ON customer_totals(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;

-- 4. Use connection pooling (application level)
-- PgBouncer or application-level pooling

-- 5. Optimize JOINs with proper indexes
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

Scalability and Architecture

Scalability approaches differ fundamentally between MongoDB and PostgreSQL, reflecting their design philosophies.

MongoDB: Horizontal Scaling

MongoDB is designed for horizontal scaling through sharding:

// Sharding configuration
// 1. Enable sharding on database
sh.enableSharding("ecommerce");

// 2. Create sharded collection with shard key
sh.shardCollection("ecommerce.orders", { customerId: 1 });

// 3. MongoDB automatically distributes data
// Documents with customerId hash to different shards

// Query routing
// MongoDB automatically routes queries to correct shards
db.orders.find({ customerId: ObjectId("507f191e810c19729de860ea") });
// Routed to specific shard based on shard key

// Scatter-gather queries
db.orders.find({ status: "pending" });
// Queries all shards and merges results

Sharding Architecture:

┌─────────────┐
│   MongoDB   │
│   Router    │ (mongos)
│  (mongos)   │
└──────┬──────┘
       │
   ┌───┴───┬────────┬────────┐
   │       │        │        │
┌──▼──┐ ┌──▼──┐ ┌──▼──┐ ┌──▼──┐
│Shard│ │Shard│ │Shard│ │Shard│
│  1  │ │  2  │ │  3  │ │  N  │
└─────┘ └─────┘ └─────┘ └─────┘

Replica Sets for High Availability:

// Replica set provides:
// - Automatic failover
// - Data redundancy
// - Read scaling

// Write to primary
db.users.insertOne({ name: "John", email: "john@example.com" });

// Read from secondary
db.users.find().readPref("secondary");

PostgreSQL: Vertical and Read Replica Scaling

PostgreSQL primarily scales vertically, with read replicas for read scaling:

-- Vertical scaling: Increase server resources
-- More CPU, RAM, faster storage

-- Read replicas for read scaling
-- Primary handles writes
-- Replicas handle reads

-- Connection string examples
-- Primary: postgresql://primary.example.com/db
-- Replica: postgresql://replica1.example.com/db

-- Application-level read/write splitting
-- Write queries → Primary
-- Read queries → Replicas

PostgreSQL Scaling Architecture:

┌──────────────┐
│  PostgreSQL  │
│   Primary    │ (Writes)
└──────┬───────┘
       │
   ┌───┴────┬────────┬────────┐
   │        │        │        │
┌──▼──┐ ┌──▼──┐ ┌──▼──┐ ┌──▼──┐
│Rep  │ │Rep  │ │Rep  │ │Rep  │
│  1  │ │  2  │ │  3  │ │  N  │ (Reads)
└─────┘ └─────┘ └─────┘ └─────┘

PostgreSQL Sharding Solutions:

While PostgreSQL doesn’t natively support sharding like MongoDB, several solutions exist:

-- Option 1: PostgreSQL extensions
-- pg_shard, Citus (now Citus Data)

-- Option 2: Application-level sharding
-- Route queries based on shard key in application

-- Option 3: Partitioning (PostgreSQL 10+)
CREATE TABLE orders (
  id SERIAL,
  customer_id INTEGER,
  total DECIMAL(10, 2),
  created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Scaling Comparison

Aspect	MongoDB	PostgreSQL
Horizontal Scaling	✅ Native sharding	❌ Requires extensions/solutions
Vertical Scaling	✅ Supported	✅ Primary method
Read Scaling	✅ Replica sets	✅ Read replicas
Write Scaling	✅ Sharding distributes writes	⚠️ Limited (single primary)
Auto-sharding	✅ Automatic	❌ Manual configuration
Shard Management	✅ Built-in	⚠️ External tools needed

When Each Scaling Approach Works Best

Choose MongoDB for:

Applications requiring horizontal write scaling
Very large datasets (terabytes+)
High write throughput requirements
Global distribution needs

Choose PostgreSQL for:

Applications that fit on single server or read replicas
Complex queries that benefit from single-server optimization
Strong consistency requirements across all data
When vertical scaling is sufficient

ACID Compliance and Transactions

Transaction support and ACID guarantees differ significantly between MongoDB and PostgreSQL, affecting data consistency and reliability.

PostgreSQL: Full ACID Compliance

PostgreSQL provides full ACID compliance by default:

-- ACID Transaction example
BEGIN;

-- Atomicity: All or nothing
INSERT INTO accounts (id, balance) VALUES (1, 1000.00);
INSERT INTO accounts (id, balance) VALUES (2, 500.00);

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If any operation fails, entire transaction rolls back
COMMIT;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Nested transactions (savepoints)
BEGIN;
  INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
  SAVEPOINT sp1;
    INSERT INTO orders (user_id, total) VALUES (1, 99.99);
    -- If this fails, rollback to savepoint
    ROLLBACK TO SAVEPOINT sp1;
COMMIT;

ACID Properties in PostgreSQL:

✅ Atomicity: All operations in a transaction succeed or fail together ✅ Consistency: Database constraints ensure data validity ✅ Isolation: Concurrent transactions don’t interfere ✅ Durability: Committed transactions survive crashes

MongoDB: ACID Transactions (v4.0+)

MongoDB added multi-document ACID transactions in version 4.0:

// Multi-document transaction
const session = client.startSession();

try {
  session.startTransaction();

  // Atomicity: All operations succeed or fail together
  await db.accounts.updateOne(
    { _id: ObjectId("507f191e810c19729de860ea") },
    { $inc: { balance: -100 } },
    { session },
  );

  await db.accounts.updateOne(
    { _id: ObjectId("507f191e810c19729de860eb") },
    { $inc: { balance: 100 } },
    { session },
  );

  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

MongoDB Transaction Limitations:

⚠️ Important considerations:

Transactions available since MongoDB 4.0 (2018)
Multi-document transactions have performance overhead
Sharded collections: transactions limited to single shard (v4.2+) or cross-shard (v4.4+)
Default write concern: w:1 (acknowledged) - can be configured for stronger guarantees

// Configure write concern for durability
db.accounts.insertOne(
  { _id: ObjectId("507f191e810c19729de860ea"), balance: 1000 },
  { writeConcern: { w: "majority", wtimeout: 5000 } },
);

// Read concern for consistency
db.accounts.find({}).readConcern("majority");

Consistency Models

PostgreSQL: Strong Consistency

-- Strong consistency by default
-- All reads see committed data
-- Isolation levels control visibility

-- Read Committed (default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Always sees committed data

-- Serializable (strongest)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Prevents phantom reads and serialization anomalies

MongoDB: Configurable Consistency

// Read preference affects consistency
// Primary (strong consistency)
db.accounts.find({}).readPref("primary");

// Secondary (eventual consistency)
db.accounts.find({}).readPref("secondary");

// Read concern levels
// "local" - Fastest, may see uncommitted data
db.accounts.find({}).readConcern("local");

// "majority" - Only see majority-committed data
db.accounts.find({}).readConcern("majority");

// "linearizable" - Strongest, linearizable reads
db.accounts.find({}).readConcern("linearizable");

Transaction Use Cases

When PostgreSQL’s ACID is Critical:

-- Financial transactions
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO transactions (from_account, to_account, amount)
  VALUES (1, 2, 100);
COMMIT;

-- Inventory management
BEGIN;
  UPDATE products SET stock = stock - 1 WHERE id = 123 AND stock > 0;
  INSERT INTO orders (product_id, quantity) VALUES (123, 1);
COMMIT;

When MongoDB Transactions are Sufficient:

// Multi-document updates with transactions
const session = client.startSession();
try {
  session.startTransaction();

  await db.orders.updateOne(
    { _id: orderId },
    { $set: { status: "completed" } },
    { session },
  );

  await db.inventory.updateOne(
    { productId: productId },
    { $inc: { stock: -quantity } },
    { session },
  );

  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
}

Consistency Trade-offs

Aspect	PostgreSQL	MongoDB
Default Consistency	Strong	Configurable (eventual by default)
ACID Transactions	✅ Full support	✅ Multi-document (v4.0+)
Cross-shard Transactions	N/A	⚠️ Limited (v4.4+)
Isolation Levels	✅ Multiple levels	⚠️ Limited
Read Consistency	✅ Strong by default	⚠️ Configurable
Write Durability	✅ Guaranteed	⚠️ Configurable (write concern)

Use Case Scenarios

Understanding when to use MongoDB vs PostgreSQL requires analyzing your specific application requirements. Here are common scenarios and recommendations.

Choose MongoDB When:

1. Content Management Systems

// Flexible content structure
db.articles.insertOne({
  title: "Getting Started with MongoDB",
  content: "...",
  author: {
    name: "John Doe",
    email: "john@example.com",
  },
  tags: ["database", "nosql", "mongodb"],
  metadata: {
    views: 0,
    likes: 0,
    published: true,
    publishDate: ISODate("2024-01-15"),
  },
  // Flexible schema - can add fields without migration
  seo: {
    metaDescription: "...",
    keywords: ["..."],
  },
});

// Easy to evolve schema
db.articles.insertOne({
  title: "New Article",
  content: "...",
  // Different structure - no problem
  author: "Jane Smith", // String instead of object
  category: "Technology", // New field
});

2. Real-time Analytics and Logging

// High-volume write operations
db.logs.insertMany([
  {
    timestamp: ISODate(),
    level: "info",
    message: "User logged in",
    userId: ObjectId("..."),
    metadata: { ip: "192.168.1.1", userAgent: "..." },
  },
  // ... thousands of log entries
]);

// Efficient aggregation for analytics
db.logs.aggregate([
  {
    $match: {
      timestamp: { $gte: new Date("2024-01-01") },
      level: "error",
    },
  },
  {
    $group: {
      _id: { $dateToString: { format: "%Y-%m-%d", date: "$timestamp" } },
      errorCount: { $sum: 1 },
    },
  },
]);

3. User Profiles and Preferences

// Flexible user data
db.users.insertOne({
  _id: ObjectId("..."),
  email: "user@example.com",
  preferences: {
    theme: "dark",
    notifications: {
      email: true,
      push: false,
      sms: true,
    },
    language: "en",
  },
  // Can vary per user
  socialProfiles: {
    twitter: "@username",
    github: "username",
  },
});

4. E-commerce Product Catalogs

// Products with varying attributes
db.products.insertOne({
  name: "Laptop",
  category: "Electronics",
  price: 999.99,
  attributes: {
    brand: "Dell",
    model: "XPS 15",
    specs: {
      cpu: "Intel i7",
      ram: "16GB",
      storage: "512GB SSD",
    },
  },
});

db.products.insertOne({
  name: "Book",
  category: "Books",
  price: 19.99,
  attributes: {
    author: "Jane Author",
    isbn: "978-0123456789",
    pages: 300,
    // Different structure for different product types
  },
});

Choose PostgreSQL When:

1. Financial Applications

-- Strong ACID guarantees critical
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

INSERT INTO transactions (
  from_account_id,
  to_account_id,
  amount,
  transaction_type,
  timestamp
) VALUES (1, 2, 100, 'transfer', NOW());

COMMIT;

-- Complex financial reporting
SELECT
  DATE_TRUNC('month', created_at) AS month,
  transaction_type,
  SUM(amount) AS total_amount,
  COUNT(*) AS transaction_count
FROM transactions
WHERE created_at >= '2024-01-01'
GROUP BY month, transaction_type
ORDER BY month, transaction_type;

2. E-commerce with Complex Relationships

-- Normalized schema with relationships
SELECT
  o.order_number,
  c.name AS customer_name,
  c.email,
  p.name AS product_name,
  oi.quantity,
  oi.price,
  o.total,
  o.status,
  a.street,
  a.city,
  a.zip_code
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
LEFT JOIN addresses a ON o.shipping_address_id = a.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

3. Reporting and Business Intelligence

-- Complex analytical queries
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS order_count
  FROM orders
  WHERE status = 'completed'
  GROUP BY month
),
customer_lifetime_value AS (
  SELECT
    customer_id,
    SUM(total) AS lifetime_value,
    COUNT(*) AS total_orders,
    MIN(created_at) AS first_order_date,
    MAX(created_at) AS last_order_date
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT
  c.name,
  c.email,
  clv.lifetime_value,
  clv.total_orders,
  clv.first_order_date,
  clv.last_order_date,
  CASE
    WHEN clv.lifetime_value > 1000 THEN 'VIP'
    WHEN clv.lifetime_value > 500 THEN 'Premium'
    ELSE 'Standard'
  END AS customer_tier
FROM customers c
INNER JOIN customer_lifetime_value clv ON c.id = clv.customer_id
ORDER BY clv.lifetime_value DESC;

4. Multi-tenant SaaS Applications

-- Row-level security for multi-tenancy
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- Set tenant context
SET app.tenant_id = 123;

-- Queries automatically filtered by tenant
SELECT * FROM orders; -- Only returns orders for tenant 123

5. Healthcare and Compliance Applications

-- Audit trails with triggers
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(100),
  record_id INTEGER,
  action VARCHAR(20),
  old_data JSONB,
  new_data JSONB,
  changed_by INTEGER,
  changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    row_to_json(OLD)::jsonb,
    row_to_json(NEW)::jsonb
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER patients_audit
  AFTER INSERT OR UPDATE OR DELETE ON patients
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Hybrid Approaches

Sometimes, using both databases makes sense:

Example: E-commerce Platform

// MongoDB for product catalog (flexible attributes)
// PostgreSQL for orders, payments, inventory (ACID critical)

// Application architecture
// Product service → MongoDB
const product = await mongoDb.products.findOne({ _id: productId });

// Order service → PostgreSQL
const order = await postgres.query(
  `
  INSERT INTO orders (customer_id, total, status)
  VALUES ($1, $2, 'pending')
  RETURNING *
`,
  [customerId, total],
);

Migration Strategies

Migrating between MongoDB and PostgreSQL (or vice versa) requires careful planning and consideration of data structure, relationships, and application code changes.

Migrating from PostgreSQL to MongoDB

Step 1: Analyze Schema and Relationships

-- Identify tables and relationships
SELECT
  table_name,
  column_name,
  data_type,
  is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

-- Identify foreign key relationships
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Step 2: Design Document Structure

// Option 1: Denormalized (embed related data)
// PostgreSQL: users table + orders table + order_items table
// MongoDB: Single orders collection with embedded data

{
  _id: ObjectId("..."),
  orderNumber: "ORD-001",
  customer: {
    id: 1,
    name: "John Doe",
    email: "john@example.com"
  },
  items: [
    {
      productId: 123,
      name: "Laptop",
      price: 999.99,
      quantity: 1
    }
  ],
  total: 999.99,
  status: "pending"
}

// Option 2: Normalized (use references)
// Keep separate collections, use ObjectId references
{
  _id: ObjectId("..."),
  orderNumber: "ORD-001",
  customerId: ObjectId("..."),
  itemIds: [ObjectId("..."), ObjectId("...")],
  total: 999.99
}

Step 3: Data Migration Script

// Migration script example
const { Client } = require("pg");
const { MongoClient } = require("mongodb");

async function migratePostgresToMongo() {
  const pgClient = new Client({
    /* PostgreSQL config */
  });
  const mongoClient = new MongoClient("mongodb://localhost:27017");

  await pgClient.connect();
  await mongoClient.connect();

  const db = mongoClient.db("migrated_db");

  // Migrate users
  const usersResult = await pgClient.query("SELECT * FROM users");
  const users = usersResult.rows.map((user) => ({
    _id: new ObjectId(),
    name: user.name,
    email: user.email,
    age: user.age,
    createdAt: user.created_at,
  }));
  await db.collection("users").insertMany(users);

  // Migrate orders with embedded customer data
  const ordersResult = await pgClient.query(`
    SELECT
      o.*,
      c.name AS customer_name,
      c.email AS customer_email
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
  `);

  for (const order of ordersResult.rows) {
    const orderItems = await pgClient.query(
      "SELECT * FROM order_items WHERE order_id = $1",
      [order.id],
    );

    await db.collection("orders").insertOne({
      _id: new ObjectId(),
      orderNumber: order.order_number,
      customer: {
        id: order.customer_id,
        name: order.customer_name,
        email: order.customer_email,
      },
      items: orderItems.rows.map((item) => ({
        productId: item.product_id,
        quantity: item.quantity,
        price: item.price,
      })),
      total: parseFloat(order.total),
      status: order.status,
      createdAt: order.created_at,
    });
  }

  await pgClient.end();
  await mongoClient.close();
}

Migrating from MongoDB to PostgreSQL

Step 1: Analyze Document Structure

// Analyze MongoDB collections and document structures
db.orders
  .find()
  .limit(10)
  .forEach((doc) => {
    printjson(doc);
  });

// Identify common fields vs variable fields
// Plan normalized schema

Step 2: Design Relational Schema

-- Create normalized tables
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10, 2)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE,
  customer_id INTEGER REFERENCES customers(id),
  total DECIMAL(10, 2),
  status VARCHAR(50),
  created_at TIMESTAMP
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  price DECIMAL(10, 2)
);

Step 3: Data Migration Script

// Migration script
const { MongoClient } = require("mongodb");
const { Client } = require("pg");

async function migrateMongoToPostgres() {
  const mongoClient = new MongoClient("mongodb://localhost:27017");
  const pgClient = new Client({
    /* PostgreSQL config */
  });

  await mongoClient.connect();
  await pgClient.connect();

  const db = mongoClient.db("source_db");

  // Migrate customers (extract unique customers from orders)
  const customersMap = new Map();
  const orders = await db.collection("orders").find({}).toArray();

  for (const order of orders) {
    if (order.customer && !customersMap.has(order.customer.email)) {
      customersMap.set(order.customer.email, order.customer);
    }
  }

  // Insert customers
  for (const [email, customer] of customersMap) {
    const result = await pgClient.query(
      "INSERT INTO customers (name, email, created_at) VALUES ($1, $2, $3) RETURNING id",
      [customer.name, customer.email, customer.createdAt || new Date()],
    );
    customer.pgId = result.rows[0].id;
  }

  // Migrate orders
  for (const order of orders) {
    const customer = customersMap.get(order.customer.email);

    const orderResult = await pgClient.query(
      `INSERT INTO orders (order_number, customer_id, total, status, created_at)
       VALUES ($1, $2, $3, $4, $5) RETURNING id`,
      [
        order.orderNumber,
        customer.pgId,
        order.total,
        order.status,
        order.createdAt,
      ],
    );

    const orderId = orderResult.rows[0].id;

    // Migrate order items
    for (const item of order.items) {
      // First ensure product exists
      let productResult = await pgClient.query(
        "SELECT id FROM products WHERE id = $1",
        [item.productId],
      );

      if (productResult.rows.length === 0) {
        productResult = await pgClient.query(
          "INSERT INTO products (id, name, price) VALUES ($1, $2, $3) RETURNING id",
          [item.productId, item.name, item.price],
        );
      }

      await pgClient.query(
        `INSERT INTO order_items (order_id, product_id, quantity, price)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productId, item.quantity, item.price],
      );
    }
  }

  await mongoClient.close();
  await pgClient.end();
}

Migration Best Practices

✅ Planning Phase:

Analyze data volume and relationships
Design target schema carefully
Plan for downtime or dual-write strategy
Test migration on subset of data first

✅ Execution Phase:

Use transactions where possible
Implement data validation
Monitor performance and errors
Keep source database until verification complete

✅ Post-Migration:

Verify data integrity
Update application code
Monitor performance
Plan rollback strategy if needed

⚠️ Common Challenges:

Data type conversions (dates, numbers, JSON)
Handling nested structures
Maintaining referential integrity
Performance during migration
Application code changes required

Best Practices

Following best practices ensures optimal performance, maintainability, and reliability with either database.

MongoDB Best Practices

1. Schema Design

// ✅ DO: Design schema based on query patterns
// Embed data that's accessed together
db.orders.insertOne({
  orderNumber: "ORD-001",
  customer: {
    name: "John Doe",
    email: "john@example.com"
  },
  items: [
    { productId: 123, name: "Laptop", price: 999.99 }
  ]
});

// ❌ DON'T: Embed data that changes frequently
// If customer email changes, you'd need to update all orders
// Better to reference:
db.orders.insertOne({
  orderNumber: "ORD-001",
  customerId: ObjectId("..."), // Reference instead
  items: [...]
});

2. Indexing Strategy

// ✅ DO: Create indexes for query patterns
db.users.createIndex({ email: 1 }, { unique: true });
db.orders.createIndex({ customerId: 1, createdAt: -1 });

// ❌ DON'T: Create too many indexes (slows writes)
// Limit to 5-10 indexes per collection

// ✅ DO: Use compound indexes for multi-field queries
db.orders.createIndex({ status: 1, createdAt: -1 });

// ✅ DO: Use partial indexes for filtered queries
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true } },
);

3. Query Optimization

// ✅ DO: Use projection to limit returned fields
db.users.find({ age: { $gte: 30 } }, { name: 1, email: 1 });

// ❌ DON'T: Use SELECT * equivalent
db.users.find({ age: { $gte: 30 } }); // Returns all fields

// ✅ DO: Use aggregation pipeline efficiently
db.orders.aggregate([
  { $match: { status: "completed" } }, // Early filtering
  { $group: { _id: "$customerId", total: { $sum: "$total" } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
]);

// ✅ DO: Use explain() to analyze queries
db.users.find({ email: "john@example.com" }).explain("executionStats");

4. Write Operations

// ✅ DO: Use bulk operations for multiple writes
const bulkOps = users.map((user) => ({
  updateOne: {
    filter: { email: user.email },
    update: { $set: user },
    upsert: true,
  },
}));
db.users.bulkWrite(bulkOps, { ordered: false });

// ✅ DO: Configure write concern for durability
db.accounts.insertOne(
  { balance: 1000 },
  { writeConcern: { w: "majority", wtimeout: 5000 } },
);

// ❌ DON'T: Use unacknowledged writes in production
db.users.insertOne({ name: "John" }, { writeConcern: { w: 0 } });

5. Transactions

// ✅ DO: Keep transactions short
const session = client.startSession();
try {
  session.startTransaction();
  // Minimal operations
  await db.accounts.updateOne(
    { _id: id1 },
    { $inc: { balance: -100 } },
    { session },
  );
  await db.accounts.updateOne(
    { _id: id2 },
    { $inc: { balance: 100 } },
    { session },
  );
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

// ❌ DON'T: Long-running transactions
// Keep transaction duration under 1 second when possible

PostgreSQL Best Practices

1. Schema Design

-- ✅ DO: Normalize appropriately
-- Balance normalization with query performance
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- ✅ DO: Use appropriate data types
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL, -- Not FLOAT for money
  created_at TIMESTAMP DEFAULT NOW() -- Not VARCHAR
);

-- ✅ DO: Add constraints for data integrity
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
  status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'cancelled'))
);

2. Indexing Strategy

-- ✅ DO: Create indexes for foreign keys
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- ✅ DO: Create indexes for WHERE clauses
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ DO: Use composite indexes for multi-column queries
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

-- ✅ DO: Use partial indexes for filtered queries
CREATE INDEX idx_active_users_email
ON users(email) WHERE active = true;

-- ❌ DON'T: Over-index (slows writes)
-- Monitor index usage with pg_stat_user_indexes
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

3. Query Optimization

-- ✅ DO: Use EXPLAIN ANALYZE to optimize queries
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- ✅ DO: Use specific columns instead of SELECT *
SELECT name, email FROM users WHERE age >= 30;

-- ✅ DO: Use JOINs efficiently
-- Ensure JOIN columns are indexed
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
WHERE u.email = 'john@example.com';

-- ✅ DO: Use LIMIT for large result sets
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

-- ❌ DON'T: Use functions in WHERE clauses (prevents index usage)
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';
-- Better: SELECT * FROM users WHERE email = 'john@example.com';

4. Connection Management

-- ✅ DO: Use connection pooling
-- Application level: pg-pool, PgBouncer, etc.

-- ✅ DO: Set appropriate connection limits
-- postgresql.conf: max_connections = 100

-- ✅ DO: Use prepared statements
PREPARE get_user AS
SELECT * FROM users WHERE id = $1;

EXECUTE get_user(123);

-- ✅ DO: Use transactions appropriately
BEGIN;
  -- Multiple operations
COMMIT;

5. Maintenance

-- ✅ DO: Regular VACUUM and ANALYZE
VACUUM ANALYZE users;

-- ✅ DO: Monitor table bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- ✅ DO: Update statistics regularly
ANALYZE;

-- ✅ DO: Monitor slow queries
-- Enable log_min_duration_statement in postgresql.conf

General Best Practices (Both Databases)

1. Security

// MongoDB: Use authentication and authorization
// Enable authentication in mongod.conf
// Create users with least privilege

use admin;
db.createUser({
  user: "appuser",
  pwd: "securepassword",
  roles: [{ role: "readWrite", db: "mydb" }]
});

-- PostgreSQL: Use roles and permissions
CREATE ROLE appuser WITH LOGIN PASSWORD 'securepassword';
GRANT CONNECT ON DATABASE mydb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

2. Backup and Recovery

# MongoDB backup
mongodump --db mydb --out /backup/mongodb

# MongoDB restore
mongorestore --db mydb /backup/mongodb/mydb

# PostgreSQL backup
pg_dump -U postgres mydb > /backup/postgres/mydb.sql

# PostgreSQL restore
psql -U postgres mydb < /backup/postgres/mydb.sql

3. Monitoring

// MongoDB: Monitor server status
db.serverStatus();
db.stats();
db.collection.stats();

// Check replication lag
rs.printSlaveReplicationInfo();

-- PostgreSQL: Monitor database activity
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;

-- Check replication lag (if using replicas)
SELECT * FROM pg_stat_replication;

4. Performance Tuning

💡 MongoDB Performance Tips:

Monitor query performance with explain()
Use appropriate read preferences
Configure write concern based on needs
Monitor replica set lag
Use connection pooling

💡 PostgreSQL Performance Tips:

Tune shared_buffers and work_mem
Use EXPLAIN ANALYZE regularly
Monitor slow query log
Use connection pooling (PgBouncer)
Consider read replicas for read scaling

Conclusion

Choosing between MongoDB and PostgreSQL is a fundamental architectural decision that depends on your specific requirements, data structure, query patterns, and scalability needs.

Choose MongoDB when:

Your data structure is flexible and changes frequently
You need horizontal scaling for writes
Your data is naturally document-oriented
Rapid development and iteration are priorities
You’re building content management, logging, or real-time analytics systems

Choose PostgreSQL when:

Your data has clear relationships and structure
ACID compliance and data integrity are critical
You need complex queries with JOINs
You’re building financial, healthcare, or compliance-heavy applications
Vertical scaling or read replicas meet your needs

Both databases are production-ready, widely adopted, and capable of handling enterprise-scale applications. MongoDB excels at flexibility and horizontal scaling, while PostgreSQL excels at data integrity and complex relational queries.

Many successful applications use both databases in a polyglot persistence approach, leveraging each database’s strengths for different parts of the system. For example, using MongoDB for flexible product catalogs and PostgreSQL for orders and payments where ACID compliance is critical.

The key is understanding your application’s specific requirements and choosing the database that best aligns with your data model, query patterns, consistency needs, and scalability requirements. Consider starting with one database and migrating if requirements change—both databases offer migration paths and can coexist in modern architectures.

For more database comparisons, check out our guide on PostgreSQL vs MySQL vs SQLite or explore our backend development articles for more insights into database selection and optimization.

Table of Contents

Introduction

Understanding Database Paradigms

SQL (Relational) Databases

NoSQL (Non-Relational) Databases

When Each Paradigm Makes Sense

MongoDB: Document-Oriented NoSQL Database

Core Concepts

Key Features

MongoDB Advantages

MongoDB Limitations

PostgreSQL: Advanced Relational Database

Core Concepts

Key Features

PostgreSQL Advantages

PostgreSQL Limitations

Data Modeling Comparison

MongoDB: Document Model

PostgreSQL: Relational Model

Modeling Decision Factors

Hybrid Approaches

Query Language and API Comparison

MongoDB Query API

PostgreSQL SQL Queries

Query Performance Considerations

Performance Comparison

Read Performance

Write Performance

Performance Benchmarks Considerations

Optimization Strategies

Scalability and Architecture

MongoDB: Horizontal Scaling

PostgreSQL: Vertical and Read Replica Scaling

Scaling Comparison

When Each Scaling Approach Works Best

ACID Compliance and Transactions

PostgreSQL: Full ACID Compliance

MongoDB: ACID Transactions (v4.0+)

Consistency Models

Transaction Use Cases

Consistency Trade-offs

Use Case Scenarios

Choose MongoDB When:

Choose PostgreSQL When:

Hybrid Approaches

Migration Strategies

Migrating from PostgreSQL to MongoDB

Migrating from MongoDB to PostgreSQL

Migration Best Practices

Best Practices

MongoDB Best Practices

PostgreSQL Best Practices

General Best Practices (Both Databases)

Conclusion