MongoDB vs PostgreSQL

Think of MongoDB and PostgreSQL as the two chefs in a kitchen, each with a different cooking style.

• MongoDB is like a creative chef who cooks freely, mixing ingredients without following a strict recipe. It’s great for flexible and fast cooking.
• PostgreSQL is like a careful baker who follows exact recipes. Everything must be measured and structured to get perfect results.

Both are great, but which one we choose depends on what kind of dish (data) we need!

A Simple Example: Organising a Music Festival

Imagine we’re organising a music festival with thousands of attendees, artists, and food stalls.

MongoDB

It is like the flexible volunteer team that handles dynamic tasks:

• A volunteer managing attendee registrations (storing names, ticket types, and social media handles in any format).
• A volunteer tracking food truck menus (with varying items, prices, and dietary tags).
• A volunteer logging real-time crowd movements (unstructured sensor data).

PostgreSQL

It is like the festival control center with strict spreadsheets:

• A spreadsheet for ticket sales (with fixed columns: Ticket ID, Buyer Email, Purchase Date).
• A spreadsheet linking artists to stages (Artist ID, Stage ID, Performance Time).
• A spreadsheet tracking payments (with ACID compliance to ensure no double charges).

MongoDB

MongoDB is a NoSQL database built for flexibility, scalability, and unstructured data.

How MongoDB Works in the Festival Example

1. Storing Attendee Data

• Each attendee’s document can have different fields:

{
   "_id":"attendee123",
   "name":"Alice",
   "tier":"VIP",
   "socials":{
      "twitter":"@alice",
      "instagram":"@alice_in_wonderland"
   }
}

• Another attendee might skip the socials field but add dietary_preferences: ["vegan"].

2. Handling Food Truck Menus

• Food trucks update menus dynamically. MongoDB’s flexible schema allows changes without downtime

{
   "truck_id":"taco_mania",
   "menu":[
      {
         "item":"Vegan Taco",
         "price":8.99,
         "tags":[
            "vegan",
            "gluten-free"
         ]
      },
      {
         "item":"Spicy Chicken Taco",
         "price":9.99
      }
   ]
}

3. Scaling for Crowd Data

• MongoDB uses sharding to split crowd movement data across servers, handling millions of updates per second.

MongoDB Architecture

1. Documents

• Data stored as JSON-like BSON documents.

2. Collections

• Groups of documents (e.g., attendees, food_trucks).

3. Replica Sets

• Automatic data replication across servers for backup.

4. Sharding

• Horizontal scaling by splitting data into chunks.

PostgreSQL

PostgreSQL is a relational SQL database built for structured data, complex queries, and ACID compliance.

How PostgreSQL Works in the Festival Example

1. Managing Ticket Sales

• A strict tickets table ensures every sale is tracked accurately:

CREATE TABLE tickets 
( ticket_id SERIAL PRIMARY KEY, 
buyer_email VARCHAR(255) UNIQUE NOT NULL, 
purchase_date TIMESTAMP DEFAULT NOW() );

2. Linking Artists to Stages

• A performances table with foreign keys:

CREATE TABLE performances 
( performance_id SERIAL PRIMARY KEY, 
artist_id INT REFERENCES artists(artist_id), 
stage_id INT REFERENCES stages(stage_id), 
start_time TIMESTAMP NOT NULL );

3. Processing Payments

• Transactions are ACID-compliant. If a payment fails, PostgreSQL rolls back changes to avoid inconsistencies.

PostgreSQL Architecture

1. Tables

• Structured data with rows and columns.

2. Schemas

• Predefined rules for data types (e.g., VARCHAR, INT).

3. Indexes

• B-tree indexes speed up queries (e.g., finding a ticket by buyer_email).

4. MVCC (Multi-Version Concurrency Control)

• Allows multiple users to read/write simultaneously without conflicts.

How It Works in Real Life

1. Building a Social Media App (MongoDB)

• Use MongoDB to store user profiles with varying data (e.g: posts, comments, reactions).
• Example: A new feature adding “stories” doesn’t require altering existing documents.

2. Building a Banking App (PostgreSQL)

• Use PostgreSQL to manage accounts, transfers, and balances with ACID compliance.
• Example: Transferring ₹100 between accounts atomically (deduct from one, add to another).

3. Scaling Your App

• MongoDB: Add more servers to handle a viral social media post.
• PostgreSQL: Upgrade server hardware to handle more transactions per second.

Why Choose One or Both?

• Choose MongoDB if:
• Our data is unstructured (e.g: IoT sensors, user-generated content).
• We need rapid development with changing requirements.
• Choose PostgreSQL if:
• Our data has strict relationships (e.g: invoices, inventory).
• We need complex joins or transactions (e.g: financial systems).
• Use both together:
• Store user profiles in MongoDB (flexibility) and orders in PostgreSQL (ACID compliance).

Just like a festival needs both creative volunteers and precise planners, modern apps often leverage MongoDB’s flexibility and PostgreSQL’s reliability to handle diverse data challenges!

References

• MongoDB Documentation
• PostgreSQL Tutorial
• AWS: When to Use NoSQL vs. SQL