PostgreSQL vs MongoDB: How I Decide What to Use (With Real Examples)

A practical guide to choosing between PostgreSQL and MongoDB based on real project requirements, not just hype or habits.

After using both PostgreSQL and MongoDB in production for years, I've developed a simple framework for choosing between them. No religious wars, no cargo-culting—just practical decision-making based on your actual needs.

The Short Answer

Here's my default decision tree:

Does your data have clear relationships? → PostgreSQL
Is your schema constantly changing? → MongoDB
Need complex queries and aggregations? → PostgreSQL
Storing JSON blobs or logs? → MongoDB
Need ACID transactions? → PostgreSQL
Building a CMS or flexible data model? → MongoDB
Want mature tooling and ecosystem? → PostgreSQL
Need horizontal scaling from day one? → MongoDB

But real projects are nuanced. Let me show you how I actually decide.

When I Choose PostgreSQL

PostgreSQL is my default choice. Here's why and when.

Use Case One: E-commerce Platform

The requirement: Build a product catalog with inventory, orders, and user accounts.

Why PostgreSQL:

-- Products have clear relationships
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INTEGER NOT NULL,
  category_id INTEGER REFERENCES categories(id)
);
 
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);
 
-- Complex queries are easy
SELECT
  p.name,
  SUM(oi.quantity) as total_sold,
  SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10;

What this gives me:

Referential integrity (can't create order for non-existent product)
ACID transactions (inventory decrements happen atomically)
Complex reporting queries
Data consistency guarantees

Real project: An online bookstore I built handles 10,000+ orders/day with zero inventory discrepancies thanks to PostgreSQL's transactions.

Use Case Two: SaaS Application with Multi-Tenancy

The requirement: Multiple organizations, each with users, projects, and tasks.

Why PostgreSQL:

-- Clear hierarchy and relationships
CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  plan VARCHAR(50) NOT NULL
);
 
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  organization_id INTEGER REFERENCES organizations(id)
);
 
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  organization_id INTEGER REFERENCES organizations(id)
);
 
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  project_id INTEGER REFERENCES projects(id),
  assigned_to INTEGER REFERENCES users(id),
  status VARCHAR(50) NOT NULL
);
 
-- Row Level Security for data isolation
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY task_isolation ON tasks
  USING (
    project_id IN (
      SELECT id FROM projects
      WHERE organization_id = current_setting('app.current_org_id')::INTEGER
    )
  );

What this gives me:

Perfect data isolation between tenants
Complex permission queries
Audit trails via triggers
Consistent data model

TypeScript integration:

// With Prisma
const tasks = await prisma.task.findMany({
  where: {
    project: {
      organizationId: user.organizationId,
    },
    assignedTo: userId,
  },
  include: {
    project: {
      include: {
        organization: true,
      },
    },
    assignedUser: true,
  },
});

Real project: A project management SaaS with 500+ organizations, zero data leakage incidents.

Use Case Three: Financial Application

The requirement: Track transactions, balances, and generate reports.

Why PostgreSQL (and nothing else):

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  balance DECIMAL(15, 2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL
);
 
CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  from_account_id INTEGER REFERENCES accounts(id),
  to_account_id INTEGER REFERENCES accounts(id),
  amount DECIMAL(15, 2) NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Critical: ACID transactions prevent data corruption
BEGIN;
  -- Deduct from sender
  UPDATE accounts
  SET balance = balance - 100.00
  WHERE id = 1 AND balance >= 100.00;
 
  -- Add to receiver
  UPDATE accounts
  SET balance = balance + 100.00
  WHERE id = 2;
 
  -- Record transaction
  INSERT INTO transactions (from_account_id, to_account_id, amount, status)
  VALUES (1, 2, 100.00, 'completed');
COMMIT;

What this gives me:

ACID guarantees (money never disappears)
Consistency (balances always match transactions)
Durability (data survives crashes)
Isolation (concurrent transfers don't corrupt data)

You cannot build this safely with MongoDB's eventual consistency.

Real project: A payment processing system handling millions of dollars. PostgreSQL's ACID properties are non-negotiable here.

When I Choose MongoDB

MongoDB isn't just "NoSQL hype." There are legitimate use cases where it shines.

Use Case One: Content Management System

The requirement: Blog platform where post structure varies wildly.

Why MongoDB:

// Different post types with different fields
interface BlogPost {
  _id: ObjectId;
  title: string;
  author: string;
  publishedAt: Date;
  tags: string[];
  // Flexible content
  content: {
    type: "article" | "gallery" | "video" | "podcast";
    // Type-specific fields
    body?: string;
    images?: Array<{ url: string; caption: string }>;
    videoUrl?: string;
    audioUrl?: string;
    duration?: number;
  };
  // Flexible metadata
  metadata: Record<string, any>;
}
 
// Easy to query
const posts = await db
  .collection("posts")
  .find({
    "content.type": "article",
    tags: { $in: ["javascript", "typescript"] },
    publishedAt: { $gte: new Date("2025-01-01") },
  })
  .toArray();
 
// Embedded comments (no joins)
interface PostWithComments extends BlogPost {
  comments: Array<{
    author: string;
    text: string;
    createdAt: Date;
    replies: Array<{
      author: string;
      text: string;
      createdAt: Date;
    }>;
  }>;
}

What this gives me:

Schema flexibility (add new post types without migrations)
Embedded data (no joins for comments)
Fast reads (everything in one document)
JSON-native storage

Real project: A multi-author blog platform where authors create custom post formats. MongoDB's flexibility was perfect.

Use Case Two: Analytics and Event Logging

The requirement: Store millions of user events with varying properties.

Why MongoDB:

// Events have wildly different shapes
interface Event {
  _id: ObjectId;
  userId: string;
  eventType: string;
  timestamp: Date;
  // Everything else is flexible
  properties: Record<string, any>;
}
 
// Insert is dead simple
await db.collection("events").insertMany([
  {
    userId: "user_123",
    eventType: "page_view",
    timestamp: new Date(),
    properties: {
      page: "/products",
      referrer: "google.com",
      device: "mobile",
    },
  },
  {
    userId: "user_456",
    eventType: "purchase",
    timestamp: new Date(),
    properties: {
      productId: "prod_789",
      amount: 99.99,
      currency: "USD",
      paymentMethod: "card",
      items: [
        { sku: "ABC123", quantity: 2 },
        { sku: "XYZ789", quantity: 1 },
      ],
    },
  },
]);
 
// Aggregation pipeline for analytics
const dailyRevenue = await db
  .collection("events")
  .aggregate([
    {
      $match: {
        eventType: "purchase",
        timestamp: { $gte: new Date("2025-01-01") },
      },
    },
    {
      $group: {
        _id: {
          $dateToString: { format: "%Y-%m-%d", date: "$timestamp" },
        },
        totalRevenue: { $sum: "$properties.amount" },
        orderCount: { $sum: 1 },
      },
    },
    {
      $sort: { _id: 1 },
    },
  ])
  .toArray();

What this gives me:

No schema changes for new event types
Fast writes (important for high-volume logging)
Flexible querying
Built-in aggregation framework

Real project: An analytics platform ingesting 100K+ events/hour. MongoDB handles the write volume and schema flexibility beautifully.

Use Case Three: Real-Time Chat Application

The requirement: Store messages, handle presence, sync across devices.

Why MongoDB:

interface ChatMessage {
  _id: ObjectId;
  conversationId: string;
  senderId: string;
  timestamp: Date;
  // Flexible message content
  content: {
    type: "text" | "image" | "file" | "location" | "reaction";
    text?: string;
    imageUrl?: string;
    fileUrl?: string;
    fileName?: string;
    latitude?: number;
    longitude?: number;
    emoji?: string;
    reactionTo?: string;
  };
  // Delivery tracking
  delivered: string[]; // User IDs who received
  read: string[]; // User IDs who read
  // Message metadata
  edited?: boolean;
  deletedAt?: Date;
}
 
// Change streams for real-time updates
const changeStream = db.collection("messages").watch([
  {
    $match: {
      "fullDocument.conversationId": conversationId,
    },
  },
]);
 
changeStream.on("change", (change) => {
  // Push to WebSocket clients
  io.to(conversationId).emit("new-message", change.fullDocument);
});

What this gives me:

Change streams for real-time sync
Flexible message types
Embedded delivery tracking
Fast writes for high message volume

Real project: A team chat app with 50K+ active users. MongoDB's change streams made real-time sync simple.

The Hybrid Approach

Sometimes the best answer is both. Here's when I use PostgreSQL and MongoDB together.

Real Example: E-Learning Platform

PostgreSQL for transactional data:

-- User accounts, enrollments, payments
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  subscription_tier VARCHAR(50)
);
 
CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);
 
CREATE TABLE enrollments (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  course_id INTEGER REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW()
);

MongoDB for course content and progress:

interface CourseContent {
  _id: ObjectId;
  courseId: number; // References PostgreSQL
  lessons: Array<{
    id: string;
    title: string;
    type: "video" | "quiz" | "reading" | "assignment";
    content: any; // Flexible based on type
    duration?: number;
  }>;
}
 
interface UserProgress {
  _id: ObjectId;
  userId: number; // References PostgreSQL
  courseId: number; // References PostgreSQL
  completedLessons: string[];
  currentLesson: string;
  quizScores: Record<string, number>;
  notes: Array<{
    lessonId: string;
    text: string;
    timestamp: Date;
  }>;
  lastActivity: Date;
}

Why this works:

PostgreSQL ensures payment and enrollment integrity
MongoDB handles flexible course content
PostgreSQL for complex enrollment queries
MongoDB for fast progress tracking

Implementation:

// Enroll user (PostgreSQL transaction)
async function enrollUser(userId: number, courseId: number) {
  await db.transaction(async (trx) => {
    // Check if already enrolled
    const existing = await trx("enrollments")
      .where({ userId, courseId })
      .first();
 
    if (existing) throw new Error("Already enrolled");
 
    // Create enrollment
    await trx("enrollments").insert({ userId, courseId });
 
    // Initialize progress in MongoDB
    await mongo.collection("progress").insertOne({
      userId,
      courseId,
      completedLessons: [],
      currentLesson: null,
      quizScores: {},
      notes: [],
      lastActivity: new Date(),
    });
  });
}
 
// Track progress (MongoDB only)
async function completeLesson(
  userId: number,
  courseId: number,
  lessonId: string
) {
  await mongo.collection("progress").updateOne(
    { userId, courseId },
    {
      $addToSet: { completedLessons: lessonId },
      $set: { lastActivity: new Date() },
    }
  );
}

My Decision Framework

Here's the systematic process I follow:

Step One: Analyze Your Data Relationships

Ask yourself:

Do entities have clear relationships? (users → orders → items)
Will I need to query across these relationships?
Is referential integrity important?

If yes to most: PostgreSQL

// PostgreSQL shines here
SELECT
  u.name,
  COUNT(DISTINCT o.id) as order_count,
  SUM(oi.quantity * oi.price) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE u.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.id, u.name
HAVING SUM(oi.quantity * oi.price) > 1000
ORDER BY total_spent DESC;

If no: MongoDB might work

// MongoDB works here
interface User {
  _id: ObjectId;
  name: string;
  purchases: Array<{
    orderId: string;
    items: Array<{
      productId: string;
      quantity: number;
      price: number;
    }>;
    total: number;
    date: Date;
  }>;
}

Step Two: Consider Your Query Patterns

PostgreSQL excels at:

Complex joins across tables
Aggregations with grouping
Full-text search (with extensions)
Analytical queries
Data warehousing

MongoDB excels at:

Reading entire documents
Simple queries on indexed fields
Aggregation pipelines
Geospatial queries
Text search on flexible schemas

Example comparison:

// PostgreSQL: Complex join query
const result = await db.query(`
  SELECT 
    c.name as category,
    COUNT(p.id) as product_count,
    AVG(r.rating) as avg_rating
  FROM categories c
  LEFT JOIN products p ON c.id = p.category_id
  LEFT JOIN reviews r ON p.id = r.product_id
  GROUP BY c.id, c.name
  HAVING AVG(r.rating) > 4.0
`);
 
// MongoDB: Aggregation pipeline
const result = await db
  .collection("products")
  .aggregate([
    {
      $lookup: {
        from: "reviews",
        localField: "_id",
        foreignField: "productId",
        as: "reviews",
      },
    },
    {
      $group: {
        _id: "$category",
        productCount: { $sum: 1 },
        avgRating: { $avg: "$reviews.rating" },
      },
    },
    {
      $match: { avgRating: { $gt: 4.0 } },
    },
  ])
  .toArray();

PostgreSQL version is more readable and performant for this use case.

Step Three: Evaluate Schema Stability

How often does your schema change?

Stable schema (rare changes):

-- PostgreSQL: Well-defined schema
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Changes require migrations
ALTER TABLE products ADD COLUMN description TEXT;

Evolving schema (frequent changes):

// MongoDB: Flexible schema
interface Product {
  _id: ObjectId;
  name: string;
  price: number;
  // Easy to add new fields
  description?: string;
  tags?: string[];
  customAttributes?: Record<string, any>;
}

My rule: If you're adding/changing fields weekly, consider MongoDB. If changes are monthly or less, PostgreSQL's migrations are fine.

Step Four: Transaction Requirements

Do you need ACID transactions?

Critical transactions (use PostgreSQL):

Financial operations
Inventory management
Multi-step workflows
Data consistency is critical

Example:

BEGIN;
  -- Deduct inventory
  UPDATE products SET stock = stock - 1 WHERE id = 123 AND stock > 0;
 
  -- Create order
  INSERT INTO orders (user_id, product_id) VALUES (456, 123);
 
  -- If either fails, both rollback
COMMIT;

MongoDB transactions exist but have limitations:

const session = client.startSession();
try {
  await session.withTransaction(async () => {
    await products.updateOne(
      { _id: productId, stock: { $gt: 0 } },
      { $inc: { stock: -1 } },
      { session }
    );
 
    await orders.insertOne({ userId, productId }, { session });
  });
} finally {
  await session.endSession();
}

MongoDB transactions:

Require replica set (more complex setup)
Performance overhead
Less mature than PostgreSQL

For critical transactions, PostgreSQL is the safer choice.

Step Five: Scaling Considerations

Vertical vs Horizontal scaling:

PostgreSQL:

Scales vertically very well (bigger servers)
Read replicas for read scaling
Partitioning for large tables
Can handle TBs of data on a single instance

MongoDB:

Designed for horizontal scaling (sharding)
Automatic data distribution
Better for globally distributed data
Easier to add capacity on demand

My experience:

// PostgreSQL handled this just fine on a single server
// 50M users, 500M transactions, 2TB data
const users = await db.query(
  `
  SELECT * FROM users WHERE id = $1
`,
  [userId]
);
 
// MongoDB sharded across 5 servers
// 100M documents, flexible schema, global distribution
const events = await db
  .collection("events")
  .find({
    userId: userId,
  })
  .toArray();

Reality check: Most applications never need horizontal scaling. PostgreSQL's vertical scaling goes very far.

Performance Considerations

Let me bust some myths with actual benchmarks from my projects.

Myth: MongoDB is Always Faster

Not true. It depends on the query.

Simple read by ID:

// MongoDB: ~1ms
await db.collection("users").findOne({ _id: userId });
 
// PostgreSQL: ~1ms
await db.query("SELECT * FROM users WHERE id = $1", [userId]);

Winner: Tie

Complex aggregation:

// MongoDB: ~150ms
await db
  .collection("orders")
  .aggregate([
    { $match: { createdAt: { $gte: startDate } } },
    {
      $lookup: {
        from: "users",
        localField: "userId",
        foreignField: "_id",
        as: "user",
      },
    },
    { $unwind: "$items" },
    {
      $group: {
        _id: "$items.productId",
        totalSold: { $sum: "$items.quantity" },
      },
    },
  ]);
 
// PostgreSQL: ~50ms
await db.query(
  `
  SELECT product_id, SUM(quantity) as total_sold
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  WHERE o.created_at >= $1
  GROUP BY product_id
`,
  [startDate]
);

Winner: PostgreSQL (optimized for joins and aggregations)

Write performance:

// MongoDB: ~0.5ms per insert (async acknowledgment)
await db.collection("logs").insertOne(logEntry);
 
// PostgreSQL: ~2ms per insert (synchronous)
await db.query("INSERT INTO logs (...) VALUES (...)", [values]);

Winner: MongoDB (for high-volume writes)

Real Performance Tips

PostgreSQL:

-- Add indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
 
-- Use EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
 
-- Optimize with partial indexes
CREATE INDEX idx_active_orders ON orders(user_id)
WHERE status = 'active';

MongoDB:

// Add indexes
await db.collection("users").createIndex({ email: 1 }, { unique: true });
await db.collection("orders").createIndex({ userId: 1, createdAt: -1 });
 
// Compound indexes for common queries
await db.collection("products").createIndex({
  category: 1,
  price: 1,
  rating: -1,
});
 
// Use explain
const explain = await db
  .collection("orders")
  .find({ userId: 123 })
  .explain("executionStats");

Common Mistakes I've Seen

Mistake One: Using MongoDB as a Relational Database

Don't do this:

// Anti-pattern: Mimicking joins in application code
const user = await db.collection("users").findOne({ _id: userId });
const orders = await db
  .collection("orders")
  .find({ userId: user._id })
  .toArray();
const products = await db
  .collection("products")
  .find({
    _id: { $in: orders.flatMap((o) => o.productIds) },
  })
  .toArray();
 
// Now merge in application code... 😱

Do this instead:

// Use PostgreSQL if you need joins
const result = await db.query(
  `
  SELECT u.*, o.*, p.*
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  LEFT JOIN order_products op ON o.id = op.order_id
  LEFT JOIN products p ON op.product_id = p.id
  WHERE u.id = $1
`,
  [userId]
);

Or embed in MongoDB:

// Embed if data is frequently accessed together
interface User {
  _id: ObjectId;
  email: string;
  orders: Array<{
    orderId: string;
    items: Array<{
      productId: string;
      name: string;
      price: number;
    }>;
  }>;
}

Mistake Two: Using PostgreSQL Without Proper Indexes

Don't do this:

-- Slow query without index
SELECT * FROM users WHERE email = 'user@example.com';
-- Scans entire table
 
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Scans all orders

Do this:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
 
-- Now queries are fast
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
-- Index Scan using idx_users_email

Mistake Three: Not Understanding MongoDB's Limitations

MongoDB doesn't have:

// No foreign key constraints
// This insert succeeds even if userId doesn't exist
await db.collection("orders").insertOne({
  userId: 999999, // Non-existent user
  total: 100,
});
 
// No JOIN support (use $lookup, but it's slower)
// No multi-document ACID transactions (without replica set)
// No strong consistency by default

If you need these features, use PostgreSQL.

Migration Stories

From MongoDB to PostgreSQL

A real project: An inventory management system started with MongoDB.

The problem:

// Race condition: Multiple processes updating stock
const product = await db.collection("products").findOne({ _id: productId });
if (product.stock >= quantity) {
  await db
    .collection("products")
    .updateOne({ _id: productId }, { $inc: { stock: -quantity } });
  // Another process might have decremented in between!
}

The solution: Migrated to PostgreSQL

-- Atomic operation with constraint
BEGIN;
UPDATE products
SET stock = stock - 5
WHERE id = 123 AND stock >= 5;
-- Fails if stock insufficient
COMMIT;

Result: Zero inventory discrepancies after migration.

From PostgreSQL to MongoDB

A real project: A CMS with rigid PostgreSQL schema.

The problem:

-- Every new content type required schema changes
ALTER TABLE posts ADD COLUMN video_url TEXT;
ALTER TABLE posts ADD COLUMN video_duration INTEGER;
ALTER TABLE posts ADD COLUMN podcast_url TEXT;
-- Endless migrations...

The solution: Migrated to MongoDB

// Flexible content structure
interface Post {
  _id: ObjectId;
  title: string;
  author: string;
  content: {
    type: string;
    // Any additional fields
    [key: string]: any;
  };
}

Result: New content types without database changes.

My Recommendation Matrix

Here's a quick reference guide:

Choose PostgreSQL for:

E-commerce platforms
Financial applications
SaaS with multi-tenancy
Data warehouses
Applications with complex relationships
When data integrity is critical
When you need mature ecosystem
When team knows SQL

Choose MongoDB for:

Content management systems
Catalogs with flexible schemas
Real-time applications
Event logging and analytics
IoT data collection
When schema evolves frequently
When horizontal scaling is critical
When team prefers JSON/JavaScript

Use Both for:

Large applications with diverse needs
Transactional data (PostgreSQL) + Flexible data (MongoDB)
OLTP (PostgreSQL) + OLAP (MongoDB)
Critical data (PostgreSQL) + Logs/metrics (MongoDB)

Practical Migration Path

If you're stuck with the wrong choice, here's how to migrate:

From MongoDB to PostgreSQL

// 1. Design PostgreSQL schema
// 2. Create migration script
import { MongoClient } from "mongodb";
import { Pool } from "pg";
 
const mongo = new MongoClient(mongoUrl);
const pg = new Pool({ connectionString: pgUrl });
 
// 3. Migrate data in batches
const cursor = mongo.db().collection("users").find();
const batch = [];
 
for await (const doc of cursor) {
  batch.push({
    id: doc._id.toString(),
    email: doc.email,
    name: doc.name,
    created_at: doc.createdAt,
  });
 
  if (batch.length >= 1000) {
    await pg.query(
      "INSERT INTO users (id, email, name, created_at) VALUES ...",
      batch
    );
    batch.length = 0;
  }
}
 
// 4. Verify data integrity
// 5. Switch application code
// 6. Keep MongoDB as backup for rollback

From PostgreSQL to MongoDB

// 1. Design MongoDB schema (embedded vs referenced)
// 2. Create migration script
import { Pool } from "pg";
import { MongoClient } from "mongodb";
 
const pg = new Pool({ connectionString: pgUrl });
const mongo = new MongoClient(mongoUrl);
 
// 3. Migrate with proper embedding
const result = await pg.query(`
  SELECT 
    u.*,
    json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
`);
 
for (const row of result.rows) {
  await mongo
    .db()
    .collection("users")
    .insertOne({
      _id: row.id,
      email: row.email,
      name: row.name,
      orders: row.orders || [],
    });
}

Conclusion: It's About Trade-offs

There's no universal winner. Both databases are excellent tools for different jobs.

PostgreSQL wins when:

Data relationships are complex
Integrity is non-negotiable
Queries involve joins and aggregations
Schema is stable
ACID transactions are critical

MongoDB wins when:

Schema flexibility is crucial
Write volume is very high
Data is hierarchical or document-oriented
Horizontal scaling is needed early
Team prefers JavaScript/JSON

My default: Start with PostgreSQL unless you have a specific reason to use MongoDB. PostgreSQL's flexibility (JSONB columns, extensions) covers most MongoDB use cases while maintaining relational integrity.

The best choice: The one that matches your actual requirements, not the one that's trending on Twitter.

What database are you using? Let me know in the comments which scenarios you'd add to this guide.