MongoDB vs PostgreSQL: Document-Oriented vs Relational -- How to Choose

Goal

This article aims to help developers deeply understand the core differences between MongoDB (document database) and PostgreSQL (relational database), including data models, query languages, performance characteristics, and use cases, to provide a decision basis for project database selection.

Background

Database selection is one of the most critical decisions in architecture design. Choosing the wrong database leads to extremely high migration costs later. MongoDB and PostgreSQL represent two different camps -- NoSQL and relational databases -- with fundamentally different design philosophies.

Two Schools of Databases

1. Relational Databases (RDBMS): Centered on tables and relationships, emphasizing data consistency and integrity
2. Document Databases: Centered on documents and collections, emphasizing flexibility and development efficiency

Neither database is absolutely superior; the key lies in what your business scenario demands more.

1. Data Model Comparison

PostgreSQL's Relational Model

-- Create users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create orders table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  product_name VARCHAR(100),
  amount DECIMAL(10, 2),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query users and their orders
SELECT u.username, o.product_name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.username = 'john';

MongoDB's Document Model

// User document
{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  username: "john",
  email: "john@example.com",
  created_at: ISODate("2024-07-05"),
  // Embedded order data
  orders: [
    {
      product_name: "iPhone 15",
      amount: 999.99,
      status: "completed",
      created_at: ISODate("2024-07-01")
    },
    {
      product_name: "AirPods Pro",
      amount: 249.99,
      status: "pending",
      created_at: ISODate("2024-07-05")
    }
  ]
}

// Query user and their orders
db.users.findOne(
  { username: "john" },
  { username: 1, orders: 1 }
);

Core Differences in Data Models

| Feature | PostgreSQL | MongoDB | |---------|-----------|---------| | Data Structure | Fixed Schema | Flexible Schema | | Relationship Expression | Foreign Keys + JOINs | Embedding or References | | Data Integrity | Strong Constraints | Application-Layer Enforcement | | Scaling Approach | Primarily Vertical | Primarily Horizontal | | Transaction Support | Full ACID | Multi-document transactions since 4.0+ |

2. Query Language Comparison

PostgreSQL's SQL

SQL is a declarative language with extremely rich expressiveness:

-- Complex aggregation query
SELECT 
  u.username,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_spent,
  AVG(o.amount) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.username
HAVING COUNT(o.id) > 5
ORDER BY total_spent DESC
LIMIT 10;

-- Window functions
SELECT 
  username,
  amount,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
FROM orders;

-- CTE (Common Table Expression)
WITH user_stats AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
)
SELECT u.username, s.order_count
FROM users u
JOIN user_stats s ON u.id = s.user_id;

MongoDB's Aggregation Pipeline

MongoDB uses the Aggregation Pipeline for complex queries:

// Complex aggregation query
db.orders.aggregate([
  { $match: { created_at: { $gte: ISODate("2024-01-01") } } },
  { $group: {
      _id: "$user_id",
      order_count: { $sum: 1 },
      total_spent: { $sum: "$amount" },
      avg_order_value: { $avg: "$amount" }
    }
  },
  { $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user"
    }
  },
  { $unwind: "$user" },
  { $project: {
      username: "$user.username",
      order_count: 1,
      total_spent: 1,
      avg_order_value: 1
    }
  },
  { $sort: { total_spent: -1 } },
  { $limit: 10 }
]);

// Document update
db.users.updateOne(
  { username: "john" },
  { 
    $push: { 
      orders: { 
        product_name: "MacBook Pro",
        amount: 1999.99,
        status: "pending",
        created_at: new Date()
      }
    }
  }
);

Query Language Comparison

| Operation | SQL (PostgreSQL) | Aggregation (MongoDB) | |-----------|------------------|----------------------| | Simple Query | SELECT * FROM users WHERE id = 1 | db.users.findOne({_id: 1}) | | JOIN | JOIN orders ON ... | $lookup | | Aggregation | GROUP BY + aggregate functions | $group + aggregate operators | | Subquery | Supported | $expr + $function | | Full-text Search | tsvector + tsquery | $text + $search |

3. Performance Characteristics

Read Performance

// MongoDB - document-level locking, embedded data read in one go
db.users.findOne({ username: "john" });
// Returns user data + all orders, no JOIN needed

// PostgreSQL - requires JOIN operation
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.username = 'john';
// Requires two index lookups + JOIN operation

Analysis:

• MongoDB is faster when reading nested documents (no JOIN needed)
• PostgreSQL can also be fast with index optimization when reading related data
• For simple queries, the difference is minimal

Write Performance

// MongoDB - batch write optimization
db.orders.insertMany([
  { user_id: 1, product: "A", amount: 100 },
  { user_id: 1, product: "B", amount: 200 },
  // ... more documents
]);

// PostgreSQL - batch insert
INSERT INTO orders (user_id, product_name, amount)
VALUES 
  (1, 'A', 100),
  (1, 'B', 200);

Analysis:

• MongoDB's batch write performance is generally better
• PostgreSQL's single-row write performance is also good
• Transaction overhead affects both

Index Strategies

// MongoDB indexes
db.users.createIndex({ username: 1 });
db.users.createIndex({ "orders.created_at": -1 });

// Compound index
db.orders.createIndex({ user_id: 1, created_at: -1 });

-- PostgreSQL indexes
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- GIN index (for full-text search)
CREATE INDEX idx_products_search ON products 
  USING GIN(to_tsvector('english', name || ' ' || description));

4. Use Cases

When to Choose MongoDB

1. Content Management Systems (CMS)
   • Different content types have different fields
   • Document structure changes frequently
   • Rapid iteration required

// CMS documents - flexible field structure
// Article
{ type: "article", title: "...", content: "...", tags: [...] }

// Video
{ type: "video", title: "...", url: "...", duration: 120 }

// Product
{ type: "product", name: "...", price: 99.99, specs: {...} }

2. Real-time Analytics and Logs

   • High write throughput
   • Variable query patterns
   • Rapid aggregation needed

3. Mobile/Game Backends

   • Flexible data structures
   • Fast read/write
   • Horizontal scaling requirements

When to Choose PostgreSQL

1. Financial/E-commerce Systems
   • Strong data consistency
   • Complex transactions
   • Data integrity

-- Transfer transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

2. Complex Reports and Analytics

   • Window functions
   • Complex JOINs
   • Data warehouse scenarios

3. Geographic Information Systems

   • PostGIS extension
   • Spatial queries
   • Map applications

Hybrid Strategy

Many modern applications use both databases:

+-----------------------------------------------------------+
|                      Application Layer                     |
+-----------------------------------------------------------+
|                                                             |
|  +----------------+              +----------------+         |
|  |    MongoDB     |              |  PostgreSQL    |         |
|  |                |              |                |         |
|  | - User Content |              | - User Accounts|         |
|  | - Log Data     |              | - Order/Payment|         |
|  | - Cache Data   |              | - Inventory    |         |
|  +----------------+              +----------------+         |
|                                                             |
+-----------------------------------------------------------+

5. Cloud Service Comparison

MongoDB Atlas

// Connect to Atlas
const { MongoClient } = require('mongodb');
const client = new MongoClient(uri);

async function connect() {
  await client.connect();
  const database = client.db('myapp');
  const users = database.collection('users');
}

Advantages:

• Global distribution
• Automatic backups
• Free tier available

PostgreSQL Cloud Services

// Connect using Prisma
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function getUsers() {
  const users = await prisma.user.findMany({
    include: { orders: true }
  });
}

Options:

• AWS RDS / Aurora
• Google Cloud SQL
• Azure Database
• Supabase (open-source Firebase alternative)

6. Migration Considerations

From MongoDB to PostgreSQL

// MongoDB document
{
  _id: ObjectId("..."),
  name: "John",
  address: {
    street: "123 Main St",
    city: "New York"
  },
  tags: ["admin", "user"]
}

// PostgreSQL table structure
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  street VARCHAR(100),
  city VARCHAR(50)
);

CREATE TABLE user_tags (
  user_id INTEGER REFERENCES users(id),
  tag VARCHAR(50)
);

From PostgreSQL to MongoDB

Considerations:

• Data flattening
• Relationships to embedding
• Index rebuilding
• Query rewriting

Summary

| Consideration | Choose MongoDB | Choose PostgreSQL | |--------------|----------------|-------------------| | Data Structure | Frequently changes | Relatively stable | | Consistency Needs | Eventual consistency OK | Strong consistency | | Scaling Needs | Horizontal scaling | Vertical scaling | | Query Complexity | Mostly simple queries | Complex queries | | Team Skills | NoSQL experience | SQL experience | | Cost Budget | Compute-intensive | Storage-intensive |

Recommendations:

1. Understand the Business First: Database selection should start from business needs, not technical preference
2. Consider Team Capabilities: Which database is the team more familiar with?
3. Plan for Growth: Choose a solution that supports the next 3-5 years of development
4. Don't Fear Hybrid Use: Multiple databases in one system is perfectly fine

There is no silver bullet -- only the best choice for your scenario. If you are torn between the two, start with PostgreSQL -- it is more versatile and migration costs are relatively low. When you encounter clear performance or flexibility bottlenecks, consider introducing MongoDB.