s0sta
EN NL عربي
Filters
How to search
  • Plain words find names, texts and examples.
  • "two words" finds the exact phrase.
  • --name or -x finds an option.
  • #word looks only inside commands.
  • @grep finds lessons and labs that use grep.
  • Filters: cat:network · in:labs · in:notes · level:rhcsa · ch:8 · root:yes. Put ! before a word to leave it out. OR between words means one of them.
  • ↑ ↓ to move · Enter to open · Esc to close · / to search

Databases & SQLData series

PostgreSQL vs MySQL vs MongoDB

The schema argument is usually a brand argument, and the brand is rarely the reason the query is slow. Three engines, six rows, and the row that decides the rest is the first one.

Quick reference

PostgreSQLMySQLMongoDB
Data modelRelational, extensibleRelational, InnoDBDocument, BSON
JoinsFull SQL joinsFull SQL joinsLimited, via $lookup
TransactionsFull ACID, MVCCACID on InnoDBMulti-doc since 4.0
ReplicationStreaming, logicalBinlog, group replReplica sets
ScalingRead replicasRead replicasSharding built in
Best forComplex queries, GISSimple web appsFlexible documents

How to pick

  • PostgreSQL → complex queries, strong SQL.
  • MySQL → simple read-heavy web apps.
  • MongoDB → flexible documents at scale.

Explained

Data model PostgreSQL and MySQL are relational and give you tables, constraints and SQL. MongoDB stores BSON documents, so a record is a tree you read whole rather than a row you join.

Joins and transactions SQL joins are first class in both relational engines. MongoDB has $lookup, a left outer join in the aggregation pipeline with more limits. PostgreSQL and MySQL on InnoDB give full ACID; MongoDB has multi-document transactions since 4.0 on replica sets, and since 4.2 across shards.

Replication and scaling PostgreSQL streams WAL and offers logical replication; MySQL uses the binlog and group replication; MongoDB uses replica sets with automatic failover. Both relational engines scale reads with replicas and otherwise want a bigger machine, while MongoDB shards on a key from the start.

Picking Start with the shape of the data and the queries. Known schema and ad hoc reporting point at PostgreSQL, which is also the strongest on complex queries and GIS; a simple CRUD app with a big read load fits MySQL; documents that change shape per tenant fit MongoDB.

The honest caveat: every engine has moved. MySQL 8 has window functions, PostgreSQL has JSONB, MongoDB has transactions, so the version in front of you matters more than the logo.

The lesson: pick the model the data has, then the engine that runs it best.

In one line

Pick the model the data has. The engine is a data-shape decision before it is a brand decision.

See a plan

sql
EXPLAIN (FORMAT JSON) SELECT 1;

More notes: Databases & SQL

Open in the notes app: search, filters, copy This note on GitHub