Skip to content

CONCEPT Cited by 1 source

Cross-engine SQL data-type equivalence

Cross-engine SQL data-type equivalence is the concept underlying any schema migration between two SQL engines: two engines can both declare a POINT column and still store the data in different wire representations, or both declare a VARCHAR and disagree on whether a length is mandatory. Declaration equivalence is cheap; behavioural equivalence requires an audit.

The PlanetScale Postgres → MySQL audit (sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql) decomposes the equivalence question into three layers:

  1. Syntactic equivalence — does the type name exist on both engines? (POINT, VARCHAR, JSON — yes.)
  2. Declarational equivalence — does the declaration form carry the same constraints? (VARCHAR requires a max length in MySQL; Postgres allows it unconstrained. JSONB does not exist in MySQL. UUID does not exist in MySQL.)
  3. Representational / behavioural equivalence — does the type behave the same at read time? (A MySQL POINT is returned as Well-Known Binary unless wrapped in ST_asText(); Postgres returns human-readable coordinates by default. A MySQL SERIAL quietly becomes BIGINT UNSIGNED AUTO_INCREMENT, which is wider than Postgres's INTEGER-backed SERIAL.)

The third layer is the source of the quiet surprises — a migration that passes the column-type declarational audit can still fail at read time because the representation changed underneath.

Examples from the Postgres → MySQL audit

Postgres type MySQL type Equivalence layer broken
VARCHAR (no length) VARCHAR(N) or TEXT Declarational — length mandatory
POINT POINT Representational — WKB vs text
UUID + gen_random_uuid() VARCHAR(36) + UUID() Declarational — no native type
JSONB JSON Declarational — no binary-JSON type
SERIAL SERIAL → BIGINT UNSIGNED AUTO_INCREMENT Declarational — width differs

Why this matters

  • Application-layer rewrites scale with representational gaps. A WKB-vs-text POINT change has to be patched in every read path. A JSONB-vs-JSON change might not need an application change at all.
  • Storage costs scale with declarational gaps. Storing a UUID as VARCHAR(36) in MySQL costs ~36 bytes per key; Postgres's native UUID is 16 bytes. On a multi-billion-row table this is a real capacity planning line.
  • Migration plans should list each equivalence-layer break separately — they are fixed in different places (schema, ORM, application reads, storage planning).
Last updated · 378 distilled / 1,213 read