Skip to content

PostgreSQL vs MongoDB

1. Decision

Choose PostgreSQL when relational constraints, multi-table joins, complex schemas, and strict ACID guarantees across normalized tables are required; choose MongoDB when documents are naturally self-contained, schema flexibility accelerates feature iteration, or native horizontal auto-sharding is needed out of the box.

2. 30-Second Answer

PostgreSQL is a relational database built on ACID compliance, structured schemas, relational joins, and powerful object extensions (JSONB, pgvector). MongoDB is a document-oriented NoSQL database storing JSON-like documents (BSON) inside collections. MongoDB optimizes for rapid developer velocity, dynamic schema updates, and seamless horizontal scaling via native auto-sharding (mongos router + shard keys). PostgreSQL can store JSON documents, but relational normalization is its primary design pattern. MongoDB supports multi-document ACID transactions, but at higher latency costs compared to its native single-document atomic operations.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
PostgreSQL Relational domains, strict data integrity, financial, JSON + SQL hybrid Unpredictable dynamic schemas, >20k write QPS requiring auto-sharding Low (<5ms point read/write) Vertical scale-up; Read replicas; Manual sharding (or Citus extension) Strong ACID by default Medium
MongoDB Content management, user profiles, polymorphic objects, high scale Complex many-to-many joins, heavy cross-collection transactions Low (<3ms document read) Horizontal auto-sharding out-of-the-box (Config Servers + Mongos) Tunable (Read Concern / Write Concern: majority) Medium to High (when sharded)

4. Selection Rules

Requirement Best Choice Rationale / Example
Complex Many-to-Many Relational Models PostgreSQL Foreign key constraints, cascade deletes, declarative multi-table SQL joins
Polymorphic / Evolving Document Schemas MongoDB Catalogs with dynamic attributes per product type without table ALTER locks
Native Horizontal Write Auto-Sharding MongoDB Built-in partition key sharding across cluster without application-layer logic
Hybrid Relational + Document Queries PostgreSQL Join relational tables directly with indexed JSONB fields in single SQL queries
Single Document Embedded Operations MongoDB Atomic updates on nested array/object attributes in a single BSON document
Financial Ledgers / Double-Entry Bookkeeping PostgreSQL Strict isolation levels (Repeatable Read / Serializable) and constraint checks

5. Tradeoffs

  1. Normalized Relational Model vs Denormalized Document Model
  2. Gain (Postgres): No data redundancy; update once, reflected across all queries.
  3. Lose (Postgres): N-table join latency penalty vs fetching single self-contained BSON document in MongoDB.
  4. Schema Enforcement vs Schema Flexibility
  5. Gain (MongoDB): Instant feature deployment without DDL migrations or schema locking.
  6. Lose (MongoDB): Application code must handle missing/legacy fields and type inconsistencies.
  7. Out-of-the-Box Auto-Sharding vs Single-Node ACID Simplicity
  8. Gain (MongoDB): Scale storage and throughput horizontally by adding shard nodes.
  9. Lose (MongoDB): Operational overhead of config servers, mongos query routers, and chunk balancing.
  10. Declarative SQL Joins vs MongoDB Aggregation Pipeline
  11. Gain (Postgres): Highly optimized query planner for multi-table relationships.
  12. Lose (Postgres): Complex data aggregation syntax compared to expressive MongoDB $stage pipelines.
  13. Memory Footprint & Index Overhead
  14. Gain (Postgres): Efficient memory usage for lean scalar columns.
  15. Lose (Postgres): Storing JSONB doubles field name strings unless optimized; MongoDB BSON also carries per-document key string overhead.

6. Interview Counter Questions

  1. Doesn't PostgreSQL's JSONB make MongoDB obsolete for document storage?
  2. How does MongoDB handle multi-document transactions compared to PostgreSQL native transactions?
  3. What is the impact of choosing an improper Shard Key in MongoDB?
  4. How do read and write concerns in MongoDB dictate data durability and consistency?
  5. When should you embed documents vs reference documents in MongoDB?
  6. How does indexing work on PostgreSQL JSONB (GIN) vs MongoDB field indexes?
  7. What happens during a MongoDB primary node failover in a replica set?
  8. How does PostgreSQL scale writes horizontally when single-instance limits are reached?
  9. Why can large document size in MongoDB degrade write and memory performance (16MB limit)?
  10. How do you migrate a monolithic PostgreSQL schema to MongoDB without downtime?

7. Gold Standard Answers

  1. No. While Postgres JSONB with GIN indexing allows powerful JSON queries within SQL, MongoDB provides native horizontal auto-sharding, dynamic field updates without full document rewriting overhead, and developer ergonomics designed natively around JSON/BSON workflows.
  2. MongoDB supports multi-document ACID transactions using two-phase commit internally, but they incur 2x-5x latency overhead, 60-second execution limits, and single-shard lock constraints. Postgres handles multi-row transactions natively at storage engine level with minimal overhead.
  3. An improper shard key (e.g., low cardinality or monotonically increasing timestamp) causes write hotspotting on a single chunk/shard, breaking horizontal distribution and triggering non-stop background chunk migrations.
  4. writeConcern: "majority" ensures writes are committed to disk/journal on a majority of replica set nodes before acknowledging HTTP client. readConcern: "majority" prevents dirty reads of data that might be rolled back after primary election.
  5. Embed when data is bounded, tightly coupled, and queried together (1-to-1 or 1-to-few, e.g., user address). Reference when entities are unboundedly growing (1-to-many, e.g., user posts) or accessed independently, avoiding 16MB document limits.
  6. Postgres JSONB uses GIN (Generalized Inverted Index) to index all keys/values inside a JSON blob in a single index structure. MongoDB creates specific B-tree indexes per path (e.g., db.users.createIndex({"profile.age": 1})) or wildcard indexes.
  7. Raft-like election algorithm: secondary nodes detect primary heartbeat loss within electionTimeoutMillis (~10s), elect a new primary. Writes are rejected during election window (~2-5s), while reads configured with secondaryPreferred continue.
  8. Scale Postgres writes horizontally via manual sharding key partitioning (e.g., by user_id), using extensions like Citus (distributed PostgreSQL), or migrating to NewSQL (CockroachDB / YugabyteDB).
  9. BSON document updates require rewriting the entire contiguous document on disk if payload grows past pre-allocated memory chunk. Storing large blobs inside documents burns WiredTiger RAM cache, degrading overall system throughput.
  10. Use Change Data Capture (CDC via Debezium reading Postgres WAL) to stream updates to Kafka, write a consumer to transform relational tuples into BSON documents in MongoDB, run shadow read validation, then switch traffic.

8. Common Mistakes

  • Using MongoDB like a relational database by creating many referenced collections and performing client-side joins.
  • Storing large binary files (images/PDFs) inside MongoDB BSON documents instead of S3/Object storage.
  • Using PostgreSQL JSONB as a lazy substitute for proper relational modeling when domain fields are known and static.
  • Choosing MongoDB without defining a proper Shard Key upfront, leading to severe production sharding bottlenecks.
  • Setting writeConcern: 1 in MongoDB for financial transactions, risking data loss during primary node failover.

9. Keywords

BSON, JSONB, GIN Index, Shard Key, Mongos Router, WiredTiger, Read Concern, Write Concern, Replica Set, Embedded Document, Referenced Document, 16MB Document Limit, Citus, Auto-Sharding, Debezium CDC.

10. Cheat Sheet

  • Core Distinction: Postgres = Normalized Relational DB (SQL + JSONB); MongoDB = Native Document Store (BSON + Auto-sharding).
  • Transactions: Postgres = Native multi-row ACID; MongoDB = Native single-document atomic (multi-document carries latency penalty).
  • Scaling Writes: Postgres = Vertical scale / Read replicas / Citus; MongoDB = Native horizontal auto-sharding via Shard Keys.
  • Document Modeling: MongoDB = Embed for 1-to-few / atomic access; Reference for 1-to-many / unboundedly growing data.
  • Consistency Control: MongoDB = writeConcern: "majority" + readConcern: "majority" prevents data loss on failover.
  • JSON Querying: Postgres JSONB + GIN index works well for hybrid queries; MongoDB is optimized for pure document-first applications.

11. Related Decisions