Postgres vs MySQL vs Aurora vs CockroachDB

Postgres is the 2026 default. Five practical options cover 95% of choices.

Postgres

• Richest feature set: JSONB, full-text, geospatial (PostGIS), pgvector, CTEs, window functions, logical replication
• Strongest type system
• Scales to 10 to 100TB on single node
• MIT-style license; no corporate capture
• Extensions make it remarkable: pgvector, TimescaleDB, Citus, pg_cron

MySQL (Oracle) and MariaDB

• Still massive in Rails, PHP, WordPress, older enterprise
• MySQL 8.0+ closed many gaps: CTEs, window functions, roles
• Weaker JSON; fragmented ecosystem (Oracle vs MariaDB vs Percona)
• Pick when team expertise or legacy dictates

Aurora (AWS, Postgres- or MySQL-compatible)

• AWS rewrote the storage layer
• 128TB shared storage; 6-way replication across 3 AZs; fast failover
• Read replicas without IO cost
• Premium price but removes ops work
• Aurora I/O-Optimized plan excellent for write-heavy
• Aurora Serverless v2 autoscales compute

Distributed SQL: CockroachDB, Spanner, YugabyteDB, TiDB

• SQL plus ACID plus horizontal scale plus multi-region
• Spanner pioneered; CockroachDB brought it open source
• YugabyteDB uses Postgres wire protocol
• TiDB MySQL-compatible with TiKV storage
• Pay latency cost of cross-region consensus (Paxos/Raft)
• Use when one-region Postgres/MySQL won't scale or global strong consistency needed

Cloud-specific managed

• CloudSQL, AlloyDB (Google), Azure Database
• Planetscale (MySQL with Vitess); branching-style schema changes
• Supabase, Neon, Turso for startup speed

Decision framework

Step 1: Managed or self-hosted

• Managed is default unless strong DBA expertise or specific requirement
• Self-hosted: full control, lower cost at scale

Step 2: Engine family

• Postgres default; MySQL if team expertise or Rails/PHP
• Oracle/SQL Server only for existing contracts

Step 3: Topology

• Single primary + read replicas for OLTP under 10TB and 50K writes/sec
• Aurora for more headroom; pay for ops simplicity
• Citus or Vitess for sharding existing engines
• CockroachDB or Spanner when single-node can't scale or need multi-region ACID

Step 4: Specific requirements

• Time-series: Postgres + TimescaleDB, or ClickHouse
• Full-text: Postgres tsvector, or Elasticsearch alongside
• Geospatial: Postgres + PostGIS
• Vector/RAG: pgvector (moderate), Pinecone (large scale)
• Analytical: separate warehouse (Snowflake, BigQuery) + CDC

Practical rule: start with managed Postgres; move to CockroachDB only when real global or horizontal scale needs

Trade-offs and hard truths

Postgres scales further than most think

• r6i.16xlarge or db.r6.16xlarge handles 10 to 100K writes/sec and tens of TB
• Run numbers before assuming distributed SQL

MySQL 8.0 closed many gaps

• But JSON weaker, ecosystem bifurcated

Aurora is not Postgres

• Postgres-compatible wire protocol with rewritten storage
• Some extensions not supported; test workload

Aurora cost surprises

• Per-I/O charges balloon for write-heavy
• Storage charges for unused capacity after DELETEs until VACUUM FULL
• I/O-Optimized plan mitigates at higher compute cost

Distributed SQL latency

• Cross-region writes cost 50 to 200ms for consensus
• Design schema (regional vs global tables) to localize writes

ACID across shards isn't free

• 2PC under the hood; contention causes retries
• serialization_failure visible to app

Connection pooling matters everywhere

• PgBouncer, RDS Proxy, built-in CockroachDB proxy

Replication lag is real

• Async replicas lag
• Read-after-write issues; plan session routing or sync replication

Hot partition problem

• Monotonic primary keys cause single-node hotspots
• Use ULIDs, UUIDs, sharding keys

Schema migrations at scale

• CREATE INDEX CONCURRENTLY (Postgres)
• ALGORITHM=INPLACE (MySQL 8)
• gh-ost, pt-online-schema-change for large ALTERs
• Planetscale branching model; CockroachDB online schema changes

Backup and PITR

• Managed includes PITR; test restores quarterly
• Untested backups are theoretical

Team patterns by stage

• Startup MVP: managed Postgres (RDS, Supabase, Neon)
• Series A-B: RDS/Aurora Postgres + Redis + S3
• Series C+: Aurora Postgres + read replicas + CDC to Snowflake
• Multi-region SaaS: CockroachDB, Spanner, or Aurora Global DB
• Massive horizontal: Cassandra/Scylla, DynamoDB, or CockroachDB

When to pick what

• Maxing writes on single Postgres: Aurora, shard via Citus, or migrate to distributed SQL
• Strict cross-region consistency: CockroachDB or Spanner (Aurora Global DB is async)
• Search-as-you-type: Elasticsearch alongside
• ACID across services: saga plus outbox; not distributed transactions
• I/O-heavy cost savings: Aurora I/O-Optimized
• Zero-downtime schema at scale: Planetscale or gh-ost/pg_squeeze

Observability

• pg_stat_statements (Postgres)
• Performance Schema / sys (MySQL)
• Aurora Performance Insights
• CockroachDB DB Console
• Monitor: slow queries, lock contention, replication lag, connection saturation, disk, IO latency

The strategic answer

• Managed Postgres is default for 80% of applications
• Aurora when AWS-heavy and scale/ops matter
• CockroachDB or Spanner only when genuinely needed
• Test backups quarterly; connection pool always; plan replication lag
• Don't reach for NoSQL when Postgres would scale fine
• Licensing and ecosystem matter; Postgres has cleanest governance