MySQL vs PostgreSQL — Key Differences for Schema Design

At a Glance

Feature	MySQL	PostgreSQL
Auto-increment primary keys	`AUTO_INCREMENT`	`SERIAL` or `GENERATED ALWAYS AS IDENTITY`
Booleans	`TINYINT(1)`	Native `BOOLEAN` type
JSON support	`JSON` (validated, not indexed)	`JSON` and `JSONB` (binary, fully indexable)
Arrays	Not supported natively	Native array columns
Enums	Built-in `ENUM` type	Custom types or `CHECK` constraints
Full-text search	Full-text indexes on InnoDB	Built-in `tsvector` with GIN indexes
Default engine	InnoDB	N/A (one engine)
Case sensitivity	Table names case-insensitive on Windows/macOS by default	Always case-sensitive (lowercased identifiers)

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.

If you need to query inside JSON frequently, PostgreSQL's JSONB is significantly more capable than MySQL's JSON.

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.