PostgreSQL vs MySQL vs MongoDB vs Cassandra: How to Choose a Database

"Which database would you use?" is asked in nearly every system design interview, and "MongoDB because it scales" is the answer that most reliably loses points. The useful way to compare databases is not by brand but by data model, consistency guarantees, query flexibility and how they scale writes. This guide compares the four databases you are most likely to name in an interview or a design review, with the same data modeled in each so the differences are concrete.

The Short Answer: A Comparison Table

The Same Data Modeled Four Ways

Consider a food delivery platform like Swiggy or Zomato. A customer places an order with several items from one restaurant. Queries we need: fetch an order by ID, list a customer's recent orders, and (for analytics) total revenue per restaurant per day.

Relational (PostgreSQL or MySQL)

CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  phone       TEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL
);

CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customers(id),
  restaurant_id BIGINT NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('PLACED','PREPARING','OUT','DELIVERED','CANCELLED')),
  total_paise   BIGINT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_customer_recent ON orders (customer_id, created_at DESC);

CREATE TABLE order_items (
  order_id    BIGINT NOT NULL REFERENCES orders(id),
  line_no     INT NOT NULL,
  item_id     BIGINT NOT NULL,
  qty         INT NOT NULL CHECK (qty > 0),
  price_paise BIGINT NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- Revenue per restaurant per day: one query, no new table needed
SELECT restaurant_id, date_trunc('day', created_at) AS day, SUM(total_paise)
FROM orders WHERE status = 'DELIVERED'
GROUP BY 1, 2;

Normalized, enforced by constraints and foreign keys, and the analytics question is answered with a query nobody planned for. That last property, ad hoc queryability, is the underrated superpower of relational databases. Money is stored as integer paise, never floating point.

Document (MongoDB)

{
  "_id": ObjectId("6713f0c2a1b2c3d4e5f60718"),
  "customerId": 91827,
  "restaurant": { "id": 552, "name": "Meghana Foods", "city": "Bengaluru" },
  "status": "DELIVERED",
  "items": [
    { "itemId": 11, "name": "Chicken Biryani", "qty": 2, "pricePaise": 32000 },
    { "itemId": 47, "name": "Raita", "qty": 1, "pricePaise": 4000 }
  ],
  "totalPaise": 68000,
  "createdAt": ISODate("2026-10-09T13:05:00Z")
}

// index for "my recent orders"
db.orders.createIndex({ customerId: 1, createdAt: -1 })

The order and its items are one document: one read, one atomic write, no join. Restaurant name is denormalized into the order, which is actually correct here because an order should remember the name at order time. Revenue per day works through the aggregation pipeline. The cost appears when data is shared: if a customer's profile is embedded in many documents, updating it means updating all of them.

Wide-column (Cassandra)

-- Table per query. Query 1: order by ID
CREATE TABLE orders_by_id (
  order_id      uuid PRIMARY KEY,
  customer_id   bigint,
  restaurant_id bigint,
  status        text,
  items         list<frozen<map<text, text>>>,
  total_paise   bigint,
  created_at    timestamp
);

-- Query 2: a customer's recent orders, newest first
CREATE TABLE orders_by_customer (
  customer_id bigint,
  created_at  timestamp,
  order_id    uuid,
  status      text,
  total_paise bigint,
  PRIMARY KEY ((customer_id), created_at, order_id)
) WITH CLUSTERING ORDER BY (created_at DESC, order_id ASC);

SELECT * FROM orders_by_customer WHERE customer_id = 91827 LIMIT 20;

The partition key (customer_id) decides which nodes store the rows; the clustering columns sort rows inside the partition so "latest 20" is a sequential read. The application writes each order to both tables (often in a logged batch). Revenue per restaurant per day? There is no efficient way to ask that here without a third table designed for it, or exporting data to Spark or a warehouse. That is the Cassandra trade in one sentence: blazing reads and writes for queries you designed for; nothing for the ones you did not.

PostgreSQL vs MySQL: The Real Differences

Both are excellent, mature, open source, ACID relational databases. For most CRUD applications either works. The differences that matter in practice:

My practical advice: pick PostgreSQL for a new product when you value features, complex queries, geo (PostGIS) or want vectors next to your data (pgvector). Pick MySQL when your team already operates it well, when you foresee Vitess-style horizontal sharding, or when you are joining an ecosystem built on it. Operational expertise in your team outweighs feature lists. For how the B-tree and clustered index differences affect performance, see Database Indexing Explained.

When MongoDB Is the Right Choice (and When It Is Not)

Good fits

• Product catalogs with heterogeneous attributes. A phone has RAM and screen size; a kurta has fabric and sleeve length. In SQL you end up with EAV tables or JSONB; in MongoDB it is natural.
• Content and CMS data: articles with nested blocks, comments, tags.
• Aggregates read as a unit: user preferences, game state, a form submission, an IoT device's config.
• Fast iteration in early-stage products where the schema genuinely changes weekly (though PostgreSQL JSONB gives you much of this too).

Poor fits

• Heavily relational data with many-to-many relationships queried from multiple directions (social graphs, permissions).
• Financial ledgers and inventory where cross-entity invariants must hold. Multi-document transactions work, but you are using the feature the database is least optimized for.
• Unbounded arrays inside documents, e.g. all messages of a chat inside one document. Documents cap at 16 MB and large documents are rewritten on update.

When Cassandra Is the Right Choice (and When It Is Not)

Cassandra combines Dynamo-style distribution (consistent hashing, leaderless replication, gossip) with a Bigtable-style storage model (LSM trees). If you want the internals, Design a Distributed Key-Value Store builds the same ideas from scratch.

Write path, and why it is fast

1. Append to the commit log (sequential disk write) for durability.
2. Write into the memtable (in memory, sorted).
3. Acknowledge. Later, flush the memtable to an immutable SSTable on disk.
4. Compaction merges SSTables in the background, dropping overwritten values and expired tombstones.

No read-before-write, no random I/O on the write path. That is why Cassandra-like systems shine for chat messages, activity feeds, sensor readings and audit events.

Good fits

• Write-heavy, append-mostly data at scale (hundreds of thousands of writes per second across a cluster).
• Access by a known key, often with a time range: "messages in channel X after T", "readings for device D today".
• Multi-datacenter active-active deployments with tunable consistency.

Poor fits and sharp edges

• Ad hoc queries: filtering on non-key columns needs ALLOW FILTERING (a cluster scan) or secondary indexes with limitations.
• Hot or huge partitions: a celebrity's partition or an unbounded time series in one partition. Commonly cited guidance is to keep partitions well under ~100 MB; bucket by time ((channel_id, day)) to bound them.
• Delete-heavy workloads: deletes write tombstones; reading through many tombstones is slow until compaction.
• Read-modify-write and uniqueness: lightweight transactions (IF NOT EXISTS) use Paxos and are several round trips; do not build your core flow on them.
• Small data: below a few hundred GB, the operational overhead of a Cassandra cluster rarely pays off.

Consistency and Transactions Compared

Relational databases give you multi-row ACID transactions on a single primary. The classic transfer:

BEGIN;
UPDATE wallets SET balance_paise = balance_paise - 50000
  WHERE user_id = 1 AND balance_paise >= 50000;
-- application checks 1 row updated, else ROLLBACK
UPDATE wallets SET balance_paise = balance_paise + 50000 WHERE user_id = 2;
INSERT INTO ledger (from_user, to_user, amount_paise) VALUES (1, 2, 50000);
COMMIT;

In MongoDB you would either model this with a session transaction across documents, or redesign around single-document atomic updates plus a ledger. In Cassandra you would not do this at all; money movement belongs in a relational store, as discussed in Design a Digital Wallet.

Tunable consistency in Cassandra

With replication factor N, a write at consistency level W and a read at level R are guaranteed to overlap on at least one replica when R + W > N. With N = 3, QUORUM writes (2) and QUORUM reads (2) give 2 + 2 > 3, so reads see the latest acknowledged write (barring concurrent-write edge cases resolved by last-write-wins timestamps). ONE/ONE is faster and more available but may return stale data. Use LOCAL_QUORUM in multi-DC setups to avoid cross-region latency on each request.

MongoDB read and write concerns

Since MongoDB 5.0 the default write concern is majority, meaning a write is acknowledged after a majority of replica set members have it, so it survives a primary failover. Reads default to the local read concern from the primary; reading from secondaries can return stale data. For read-your-writes across secondaries, use causally consistent sessions.

How this maps to CAP and PACELC framing is covered in CAP Theorem Explained. Briefly: a single-primary relational setup and MongoDB replica sets favor consistency during partitions (the minority side cannot accept writes), while Cassandra lets you choose per query.

How Each Database Scales

• Vertical first. A single modern PostgreSQL or MySQL server with lots of RAM and NVMe can handle a surprisingly large workload. Many products never outgrow one primary plus replicas.
• Read replicas scale reads but introduce replication lag; read-your-own-writes needs routing to the primary for recently-written data.
• Sharding relational databases means choosing a shard key (tenant ID, user ID), handling cross-shard queries and resharding. Tools: Vitess (MySQL), Citus (PostgreSQL). Details in Database Sharding Explained.
• MongoDB shards natively by a shard key (hashed or ranged); a poor shard key (monotonic createdAt, low-cardinality country) creates hot shards.
• Cassandra scales by adding nodes to the ring; data and load rebalance via virtual nodes. Throughput grows roughly linearly with node count when partition keys spread load evenly.

Decision Flowchart for Choosing a Database

Most real systems end up polyglot: PostgreSQL for orders and payments, Cassandra or a similar store for chat or event history, Redis for sessions and hot reads (Redis Deep Dive), and a search index. The skill is assigning each data set to the right store and keeping them in sync, usually via change data capture or an outbox pattern.

What Real Companies Chose, and Why

These are drawn from companies' own public engineering posts and talks. Architectures evolve, so treat them as illustrations of reasoning rather than current snapshots.

The pattern: everyone starts relational, and moves specific, well-understood, high-volume data sets to specialized stores when a concrete limit is hit. Nobody migrates core money flows to an eventually consistent store.

How to Answer "Which Database?" in an Interview

1. Name the data and the access pattern. "Messages: written once, read by channel in time order, 500K writes/s at peak."
2. State the consistency need. "Losing ordering within a channel is unacceptable; slight staleness across devices is fine."
3. Pick and justify. "Cassandra with partition key (channel_id, bucket) and clustering by message_id descending. Linear write scaling, efficient time-range reads."
4. Name the trade-off you accept. "No ad hoc queries; search goes to Elasticsearch via CDC. Hot channels need bucketing."
5. Contrast with an alternative. "PostgreSQL would need app-level sharding at this write rate, which is doable but more operational work."

Follow-ups to prepare: How do you pick a shard key? What happens on a node failure? How do you handle a hot partition? How would you migrate from MongoDB to PostgreSQL with zero downtime (dual writes or CDC backfill, shadow reads, cutover)? If SQL depth is also tested in your loop, practice with SQL interview questions.

Common Mistakes When Choosing a Database

• "NoSQL because scale." Scale is a number. Estimate it first; a single PostgreSQL primary handles thousands of writes per second.
• Choosing Cassandra and then needing many query patterns. Each new query is a new table and a backfill.
• MongoDB with relational habits: normalizing everything into collections and emulating joins with many round trips.
• Ignoring operations: a database your team cannot back up, upgrade and debug at 3 AM is the wrong database.
• Using a cache as the source of truth without persistence guarantees.
• Floating point money in any database. Store integer minor units (paise) or exact decimals.
• Forgetting the analytics path. OLTP databases are not your data warehouse; plan CDC into a columnar store for heavy reporting.

Frequently Asked Questions

Pranjal Jain

Ex-Microsoft SDE · IIT Kanpur · Founder of Prepflix. Helps engineers crack startup and product company interviews across India.