Chapter 46

MySQL vs PostgreSQL

MySQL vs PostgreSQL: In-depth Comparison

MySQL and PostgreSQL are the two dominant open-source relational databases today. This chapter provides a thorough comparison across architecture, features, performance, and ecosystem, with concrete selection guidance. Based on MySQL 8.4 LTS and PostgreSQL 16.

Bottom line: Both are excellent production databases. 90% of use cases work fine with either. Real differences: complex SQL/JSON/GIS/AI → PostgreSQL wins; web apps/cloud ecosystem/ultra-high concurrent reads → MySQL is more broadly supported.

1. Background

	MySQL	PostgreSQL
Founded	1995, Sun → Oracle acquisition	1986, UC Berkeley, truly open-source
License	GPL v2 (Oracle commercial separate)	PostgreSQL License (extremely permissive, BSD-like)
Positioning	"Web database" — simple, fast, ubiquitous	"World's most advanced open-source RDBMS" — feature-complete, SQL-standard
Notable users	Facebook, ByteDance, YouTube, Alibaba	Apple, Instagram, Reddit, Heroku

2. Core Architecture Differences

2.1 MVCC Implementation

	MySQL InnoDB	PostgreSQL
Old version storage	Undo Log segments (separate)	Dead tuples in-heap (same file)
Read old version	Rebuild from Undo Log chain	Read dead tuple directly
Write amplification	Lower	Higher (UPDATE = new row + dead row)
Cleanup	Purge thread (automatic, low-impact)	VACUUM required (maintenance overhead)
Table bloat	No (Undo is separate)	Yes in high-write scenarios (need VACUUM FULL)

2.2 Connection Model

MySQL: Thread-per-connection (or thread pool). Lower per-connection overhead.
PostgreSQL: Process-per-connection (fork). Higher overhead — always use PgBouncer connection pooler in production.

3. Feature Comparison

Feature	MySQL 8.4	PostgreSQL 16	Winner
SQL standard compliance	Moderate	High (SQL:2016)	PG
JSON support	JSON column + functions	JSONB + GIN index (far superior)	PG
Full-text search	Basic FTS, ngram for CJK	tsvector/tsquery, more powerful	PG
Geospatial (GIS)	Basic Geometry	PostGIS extension (industry standard)	PG
Parallel queries	Limited	Full parallel Seq/Index/Agg/Join scan	PG
Online DDL	Instant DDL, most ops lock-free	Many ops require table rewrite	MySQL
Vector search (AI)	9.x experimental	pgvector (mature, AI standard)	PG
Replication	Mature (GTID, Group Replication)	Logical + physical replication	Tie
Extensions ecosystem	Limited	Rich (PostGIS, TimescaleDB, Citus, pgvector...)	PG

4. Performance

OLTP point reads: Similar; MySQL slightly better under extreme concurrent simple queries (thread vs process model)
High-concurrency writes: MySQL slightly better; PG VACUUM can cause write stalls
Complex analytics: PostgreSQL clearly wins with full parallel queries and better query planner
JSON operations: PostgreSQL JSONB + GIN index overwhelmingly better

5. Ecosystem

Area	MySQL	PostgreSQL
Cloud (AWS)	RDS MySQL, Aurora MySQL	RDS PostgreSQL, Aurora PostgreSQL, Supabase, Neon
AI/Vector	HeatWave ML (commercial)	pgvector (free, de facto AI standard)
Time-series	Basic	TimescaleDB (PostgreSQL extension)
ORM support	Full coverage	Full coverage

6. Selection Guide

Choose MySQL when:

Traditional web app (LAMP/LNMP stack)
Team has deep MySQL experience
Ultra-high concurrent reads with read replicas
China-based business with existing MySQL toolchain
Using AWS Aurora MySQL or Alibaba Cloud RDS

Choose PostgreSQL when:

Rich JSON/document storage needs (replacing MongoDB)
Geospatial data (PostGIS is unmatched)
Complex analytical queries (mixed OLTP/OLAP)
AI applications (pgvector for Embedding storage)
International SaaS (Supabase/Neon ecosystem, global teams)
Strict SQL standard compliance required

Final take: For a new project with AI features planned → choose PostgreSQL (pgvector + more complete feature set). For scaling an existing MySQL system, Chinese internet use cases, or high-concurrency read workloads → MySQL is still the safe choice. Both are excellent; the right choice depends on your specific constraints, not which is "objectively better."

Rate this chapter

4.5 / 5 (3 ratings)