Understanding Postgres ↔ MySQL
Two SQLs that look the same — until they don't.
The auto-increment difference, the JSON divergence, the index types each side uniquely has, and the migration steps that actually move data.
The 80 % overlap.
Both speak ANSI SQL. SELECT, INSERT, UPDATE, DELETE, JOIN, indexes, transactions, views, stored procedures — all transferable. A simple application using portable SQL will run on either with config-only changes. The trouble is the other 20 %: the dialect features that crept in over decades and now make the migration messy.
Auto-increment.
MySQL: id INT AUTO_INCREMENT PRIMARY KEY. Postgres: id SERIAL PRIMARY KEY (or BIGSERIAL for 64-bit). Modern Postgres prefersid BIGINT GENERATED ALWAYS AS IDENTITY for SQL-standard compliance. The semantics are similar; the syntax converts mechanically. UUID primary keys are common too — Postgres has native UUID type, MySQL stores them as CHAR(36) or BINARY(16).
JSON support.
Both have JSON columns now, but Postgres's JSONB is binary-stored with rich indexing (GIN indexes for containment queries). MySQL's JSON type is text-stored with limited indexing — functional indexes on JSON paths help but don't match Postgres's GIN flexibility. Queries that rely on JSON containment (data @> '{ "active": true }') port poorly; you rewrite them asJSON_EXTRACT calls.
Specific types that differ.
Postgres has arrays (INT[]), enums (CREATE TYPE), ranges (INT4RANGE), inet/cidr addresses, geometric types, full-text tsvector. MySQL has SET (a bit-flag-ish type Postgres lacks), spatial via MyISAM-derived extensions, and the legacy TEXT family with explicit byte limits. Application code that uses Postgres-specific types is the migration's hard part — a column of INT[] becomes a junction table on the MySQL side.
A worked migration.
Source: MySQL 8 with a 50 GB users table and 200 GB orders table. Steps: runpgloader to convert schema + bulk-copy data into Postgres; rewrite any stored procedures (MySQL uses BEGIN-END blocks, Postgres uses PL/pgSQL); rewrite queries using JSON functions; switch the application's connection string; verify with a sample of read-write traffic against both. Total elapsed time for the cutover on a quiet weekend: 4-8 hours. The application changes typically take longer than the data copy.
50GB + 200GB migration
pgloader + queries rewrite
Schema + data + procs + queries.
pgloader copy ; rewrite procs ; rewrite JSON queries
= 4-8h weekend cutover
Which to pick now.
New project, no constraints: Postgres. The feature lead is real and growing — partial indexes, partial-update JSON, parallel query, ROW-LEVEL SECURITY, native partitioning, and the extension ecosystem (PostGIS, pg_trgm, TimescaleDB) all favour Postgres. MySQL still wins on managed-service ubiquity and a slightly faster simple read path on commodity hardware. For everything else, Postgres is the safer default in 2026.