LLM Best Practices

Postgres vs MySQL

3 min read

View markdown source

Overview

Pick Postgres for a new SaaS or B2B app. It has the richer type system, JSONB, partial and expression indexes, transactional DDL, and a deep extension catalog (pgvector, PostGIS, partman). Reach for MySQL only when the team is already on Aurora MySQL, runs Vitess at horizontal scale, or has operators who know MySQL replication cold. For everything else, the gap has widened in Postgres’s favor since 2020. See postgres for the in-depth rule set.

When Postgres wins

Postgres is the default for app data with mixed access patterns, analytical queries on the same store, or any structured-plus-semi-structured workload.

  • JSONB with GIN indexes outperforms MySQL’s JSON for containment queries and partial-index access.
  • Partial, expression, BRIN, and GiST indexes cover the long tail (case-insensitive lookups, soft-delete predicates, time-series).
  • Transactional DDL: schema changes roll back on failure inside a transaction. MySQL still autocommits most DDL.
  • The extension ecosystem (pgvector, PostGIS, pg_partman, TimescaleDB, Citus) lets one database cover vectors, geo, partitioning, and sharding.
  • Strong defaults for SERIALIZABLE and REPEATABLE READ; MySQL’s REPEATABLE READ is the InnoDB-flavored variant with phantom-read quirks.

When MySQL wins

MySQL is the right pick in three narrow cases. Otherwise the choice is Postgres.

  • Aurora MySQL on AWS for managed read replicas at high QPS where the team already pays for it.
  • Vitess for horizontal sharding past what a single Postgres primary handles; companies running tens of TB on a single logical schema (Slack, Square historically).
  • Pre-existing MySQL operator depth on the team: replication, ProxySQL, Percona toolkit, online schema changes via pt-online-schema-change or gh-ost. The operational muscle memory has real cost to rebuild on Postgres.

For new greenfield work without those constraints, MySQL’s advantages over Postgres are mostly historical.

Trade-offs at a glance

DimensionPostgresMySQL
JSONJSONB with GIN; query and indexJSON column; weaker indexing
ReplicationLogical and physical; mature in 17Async or semi-sync; Aurora and Vitess for scale
DDLTransactional; rolls backMost DDL autocommits
Index typesbtree, GIN, GiST, BRIN, hash, partial, expressionMostly btree, fulltext, hash
Extensionspgvector, PostGIS, partman, CitusLimited; mostly storage engines
LicensePostgreSQL (BSD-style)GPL with Oracle ownership
ShardingCitus or app-level; no nativeVitess is the canonical answer
Window functionsFull specFull spec since 8.0
Default text typetext is unlimited and freeVARCHAR(n) defaults, charset gotchas

Migration cost

Postgres-to-MySQL is rarely worth it; MySQL-to-Postgres is well-trodden.

  • MySQL to Postgres: use pgloader for a one-shot pull; rewrite AUTO_INCREMENT to IDENTITY, TINYINT(1) to boolean, and any ENUM columns to lookup tables or check constraints. Plan one engineer-week per 50 tables of business logic.
  • Driver and ORM changes are usually small: Prisma, SQLAlchemy, Sequelize, and Drizzle all run on both. The pain is SQL dialect (GROUP BY strictness, LIMIT syntax, || vs CONCAT) and any stored procedures.
  • Application code that depends on case-insensitive default collation (MySQL utf8mb4_0900_ai_ci) needs CITEXT or LOWER() indexes on Postgres.

Recommendation

  • New SaaS or B2B app: Postgres. No exceptions worth listing.
  • Hot path needs horizontal sharding past 5 TB on a single logical schema: MySQL with Vitess, or Postgres with Citus if the team prefers Postgres.
  • AWS-only stack with no infra team: Aurora Postgres beats Aurora MySQL on feature surface; both are fine.
  • Existing MySQL fleet with ops depth: stay on MySQL until a project genuinely needs JSONB, pgvector, or PostGIS, then port that workload.
  • Vector search alongside relational data: Postgres with pgvector; see pinecone-vs-pgvector.

Graph View

Backlinks