What are the key differences between SQL and NoSQL databases? When would you choose MongoDB over PostgreSQL?

SQL vs NoSQL Comparison

Aspect	SQL (PostgreSQL)	NoSQL (MongoDB)
Data model	Fixed schema (tables/rows)	Flexible schema (documents)
Relations	Foreign keys, JOINs	Embedding, $lookup
Scaling	Vertical (scale up)	Horizontal (scale out)
Transactions	ACID, mature	ACID (4.0+, multi-document)
Schema changes	ALTER TABLE (migration)	No migration needed
Query	SQL (declarative)	MongoDB Query Language
Best for	Relational data, complex queries	Flexible data, rapid iteration

When to Choose MongoDB

// Flexible schema — iterate fast
const jobApplication = {
  company: "Google",
  position: "SWE",
  customFields: {         // No migration needed to add fields
    referral: "John",
    source: "LinkedIn",
  },
  interviewRounds: [       // Embed arrays directly
    { round: 1, type: "phone", feedback: "..." },
    { round: 2, type: "onsite", feedback: "..." },
  ],
};

When to Choose PostgreSQL

-- Complex relational queries
SELECT u.name, COUNT(a.id) as applications, AVG(a.salary) as avg_salary
FROM users u
JOIN applications a ON u.id = a.user_id
JOIN companies c ON a.company_id = c.id
WHERE c.industry = 'fintech'
GROUP BY u.id
HAVING COUNT(a.id) > 5
ORDER BY avg_salary DESC;

Hybrid Approach (Common in Production)

MongoDB  → User-generated content, logs, analytics, flexible data
PostgreSQL → Financial records, user accounts, complex reporting
Redis    → Caching, sessions, rate limiting

MongoDB Strengths for Interview OS

1. Flexible question data: Tags, sketchData (JSON), optional fields — no migrations
2. Embedded documents: Interview rounds inside applications
3. Horizontal scaling: Sharding for high write throughput
4. JSON native: Direct storage of JS objects, no ORM mapping friction

PostgreSQL Strengths

1. Complex JOINs: Reporting across users, applications, companies
2. Data integrity: Foreign keys, constraints, triggers
3. Full-text search: Built-in tsvector
4. Analytics: Window functions, CTEs, materialized views