Blog › Schema Design

March 2026 — 7 min read

MySQL vs PostgreSQL — Key Differences for Schema Design

Both MySQL and PostgreSQL are excellent relational databases, but they have real differences that affect how you design your schema. If you're starting a new project or migrating between them, understanding these differences upfront will save you from surprises later.

At a Glance

Auto-Increment Primary Keys

The most immediately visible difference when writing DDL:

-- MySQL
CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
    -- or, preferred in modern PostgreSQL:
    -- id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

PostgreSQL's SERIAL is syntactic sugar that creates an integer column and a backing sequence object. GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+) is the SQL-standard equivalent and is generally preferred in new schemas.

Boolean Columns

MySQL has no native boolean type. The convention is TINYINT(1), which stores 0 or 1. ORMs like Laravel and Rails treat this as a boolean automatically.

PostgreSQL has a native BOOLEAN type that accepts TRUE/FALSE, 't'/'f', 'yes'/'no', and 1/0. It's cleaner and more explicit.

JSON Support

Both databases support JSON columns, but PostgreSQL's implementation is more powerful:

• MySQL JSON — validates JSON on insert and supports path queries with JSON_EXTRACT() and the -> operator. You cannot create a standard index on a JSON column (only functional indexes on specific paths).
• PostgreSQL JSONB — stores JSON in a decomposed binary format. Supports GIN indexes on the entire document, enabling fast queries like "find all rows where the JSON contains key X". Far more performant for JSON-heavy workloads.

CHECK Constraints

MySQL historically parsed CHECK constraints but silently ignored them. MySQL 8.0.16+ enforces them, but many MySQL installations are still on older versions or have legacy schemas that relied on the old behaviour.

PostgreSQL has always enforced CHECK constraints fully. If you're relying on them for data integrity, test that your MySQL version actually enforces them.

String Case Sensitivity

MySQL's default collation (utf8mb4_unicode_ci) is case-insensitive — WHERE name = 'Alice' matches 'alice', 'ALICE', etc. PostgreSQL is case-sensitive by default.

This is a common source of bugs when migrating: queries that worked in MySQL (case-insensitive match) silently return fewer results in PostgreSQL. Either use LOWER() explicitly, or use PostgreSQL's ILIKE for case-insensitive pattern matching.

Foreign Key Enforcement

Both InnoDB (MySQL) and PostgreSQL enforce foreign keys. However, MySQL's foreign key checks can be disabled with SET FOREIGN_KEY_CHECKS = 0, which is sometimes used during bulk imports. PostgreSQL uses SET session_replication_role = replica for the same purpose — a deliberate extra step that makes disabling constraints more explicit.

Which Should You Choose?

• Choose MySQL if you're working with an existing MySQL stack, using a managed service like PlanetScale, or need maximum compatibility with a particular ORM/framework ecosystem that favours MySQL.
• Choose PostgreSQL if you need advanced data types (arrays, JSONB, ranges, custom types), strong standards compliance, or plan to use JSON heavily as a queryable data store.
• For most new projects, PostgreSQL is the more capable choice. MySQL is the safer choice if you're joining an existing team already using it.

Whichever you choose, the schema design process is the same: model your entities and relationships first, pick appropriate data types, and validate the design visually before writing DDL.