Skip to content

PLANETSCALE 2023-02-09 Tier 3

Read original ↗

PlanetScale — Migrating from Postgres to MySQL

Adnan Kukic's 2023-02-09 PlanetScale post is a practitioner's checklist for the cross-engine migration direction most other literature ignores: PostgreSQL → MySQL. The article is a data-types-and-dialects audit: three sample tables (products, customers, orders) each surface a different incompatibility class — unconstrained string types, spatial data representation, and UUID + JSON — and then the post enumerates operational-dialect gaps (DROP TEMPORARY TABLE, TRUNCATE … CASCADE, stored-procedure language pluggability, identifier case sensitivity). The article's value on the wiki is as the canonical taxonomy of cross-engine SQL compatibility axes a DBA has to audit before flipping engines — a MySQL-direction counterpart to the existing 5.7 → 8.0 upgrade checklist.

Summary

The post frames Postgres → MySQL migration as three data-type incompatibility classes plus five operational-dialect gaps:

Data-type axis

  1. Unconstrained strings: Postgres VARCHAR (no length) vs MySQL VARCHAR(N) (length required). Drop-in path: switch to MySQL TEXT, or keep VARCHAR but supply a max length. Related: SERIAL in MySQL defaults to BIGINT UNSIGNED AUTO_INCREMENT, which differs from Postgres's SERIAL (INTEGER-backed). (See concepts/mysql-varchar-length-requirement.)
  2. Spatial data: Postgres POINT is human-readable; MySQL POINT is Well-Known Binary. A SELECT in MySQL returns 0x00000000010100000027DFC4AE…; you must wrap the column in ST_asText(location) to get back POINT(34.411275716904406,-111.6783709992531). Alternative: split into two DECIMAL latitude / longitude columns and give up MySQL's spatial functions. (See concepts/mysql-spatial-binary-wkb-vs-postgres-point.)
  3. UUIDs and JSON types. Postgres has a native UUID type; MySQL does not — store UUIDs in VARCHAR(36) and generate with MySQL's UUID() function as the analogue of gen_random_uuid(). Postgres has JSONB (indexed binary JSON); MySQL has only JSON — largely equivalent for querying, indexing, and modification, but JSONB storage is more compact. (See concepts/uuid-string-representation-mysql, concepts/postgres-jsonb-vs-mysql-json.)

Operational-dialect axis

  1. DROP TEMPORARY TABLE — MySQL has the TEMPORARY keyword; Postgres does not. In Postgres, a DROP TABLE foo against a name shared between a temp and a regular table can accidentally drop the wrong one; MySQL lets you scope the drop. (See concepts/mysql-temporary-keyword-in-drop-table.)
  2. TRUNCATE TABLE … CASCADE / RESTART IDENTITY — Postgres supports cascade and identity reset; MySQL does not. MySQL's TRUNCATE is also not transaction-safe (no rollback). Postgres's is. (See concepts/postgres-truncate-cascade-restart-identity.)
  3. Stored-procedure language pluggability — Postgres supports procedures in Ruby, Perl, SQL, Python, JavaScript, and other registered languages; MySQL requires standard SQL. (See concepts/postgres-stored-procedure-language-pluggability.)
  4. Identifier case sensitivity — Postgres is case-sensitive for double-quoted identifiers; MySQL is not. Identifiers that differ only in case in a Postgres schema collide in MySQL. (See concepts/mysql-case-insensitive-identifiers.)
  5. Postgres extensions — any Postgres extension the source schema depends on has to be audited one-by-one; MySQL has no equivalent surface.

Other engine-level differences the post calls out upfront (comparison table): MySQL supports unsigned integers, Postgres does not; Postgres supports materialized views and INSTEAD OF triggers, MySQL does not; MySQL supports a richer table-partitioning vocabulary (RANGE, COLUMN, LIST, HASH, KEY, composite HASH/KEY) than Postgres (RANGE, LIST, HASH).

The article is promotional in places (points at PlanetScale's hosted MySQL and Vitess) but the body is content-dense: every data-type and dialect claim comes with a concrete SQL snippet or a worked row of example data.

Key takeaways

  • Unconstrained VARCHAR is a Postgres luxury. MySQL's storage engine requires a max length on VARCHAR; the migration-safe answer is to move the column to TEXT (no length, unbounded) unless you already know the bound. Getting this wrong truncates data at insert time. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)
  • Spatial columns change representation, not just dialect. SELECT location in MySQL returns raw Well-Known-Binary (0x0000…) rather than a human-readable POINT(…). Every read path that touched Postgres's human-readable POINT output has to be rewritten to wrap the column in ST_asText() — application code, not just the schema. Alternative: break the column into lat DECIMAL, lng DECIMAL and give up MySQL's spatial index / function family. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)
  • UUIDs survive the migration as opaque 36-character strings. MySQL has no native UUID column — store as VARCHAR(36), generate with MySQL's UUID() function. Postgres's gen_random_uuid() maps directly. The cost is ~3× the storage of a native 16-byte UUID and the full concepts/uuid-primary-key-antipattern penalty if used as a clustered primary key in InnoDB. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)
  • JSONB → JSON is mostly a naming change, not a semantic one. MySQL JSON supports indexing (see sources/2026-04-21-planetscale-indexing-json-in-mysql), modification, and subset extraction — the features a team would use JSONB for in Postgres. JSONB is more storage-efficient, but the capability gap is small. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)
  • The quiet risks live in the DDL / DML dialect, not in the column types. DROP TABLE semantics (no TEMPORARY scoping in Postgres), TRUNCATE TABLE (no CASCADE or transaction safety in MySQL), stored-procedure languages (Postgres = pluggable, MySQL = SQL only), and identifier case sensitivity (Postgres = sensitive if quoted, MySQL = insensitive always) are the four dialect axes the post calls out as frequently surprising. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)
  • The framing is explicit: MySQL trades some features for performance and for the Vitess scaling story. The post closes with "While Postgres may have more features out of the box, MySQL is typically more performant for common use cases" and pitches Vitess and PlanetScale as the horizontal-scaling answer — the honest-assessment framing mirrors the Canva story. (Source: sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql.)

Systems extracted

  • systems/mysql — target engine; the audit is entirely in MySQL's terms (TEXT vs VARCHAR(N), UUID() function, JSON type, ST_asText() wrapper, TRUNCATE semantics).
  • systems/postgresql — source engine; unconstrained VARCHAR, UUID + gen_random_uuid(), JSONB, TRUNCATE … CASCADE / RESTART IDENTITY, pluggable-language stored procedures, case-sensitive quoted identifiers.
  • systems/planetscale — suggested hosted-MySQL target; sign-up CTA is the only product-facing element in the post.
  • systems/vitess — called out by name as the horizontal-scaling path for MySQL once migrated.

Concepts extracted

Patterns extracted

  • patterns/cross-engine-database-migration-audit — the blog post is a worked instance of the pattern: before moving schemas between SQL engines, enumerate the differences along three axes — column types, DDL / DML dialect, and operational semantics — and confirm every source column and every DML call maps into a supported target construct.

Operational numbers

  • 3 sample tables (products, customers, orders) chosen to surface different incompatibility classes.
  • 36-character string representation for UUIDs in MySQL (VARCHAR(36)). Contrast with Postgres's 16-byte native UUID.
  • Table-partitioning vocabulary — MySQL: RANGE, COLUMN, LIST, HASH, KEY, composite HASH/KEY (6 modes); Postgres: RANGE, LIST, HASH (3 modes).
  • Trigger coverage — MySQL: AFTER and BEFORE only; Postgres: AFTER, BEFORE, and INSTEAD OF.

Caveats

  • The post is written from the perspective of moving to PlanetScale's hosted MySQL; framing is migration-as-an- opportunity rather than engine-neutral. Its technical content is engine-neutral, but the closing paragraphs pitch PlanetScale and Vitess.
  • The post is from 2023-02-09 and does not cover MySQL's more recent JSON enhancements (e.g. multi-valued indexes on JSON arrays) or PostgreSQL 16-17-18 features.
  • The post does not address data movement (how to actually copy rows between engines), transactional cutover, or dual-writes during overlap — it is a schema / dialect audit, not a migration playbook. For the movement story see sources/2026-02-16-planetscale-zero-downtime-migrations-at-petabyte-scale.
  • Readers running in the MySQL → Postgres direction face a symmetric-but-different audit (materialized views, INSTEAD OF triggers, sequences vs AUTO_INCREMENT, unsigned integers) not covered here.

Source

Last updated · 378 distilled / 1,213 read