MySQL vs PostgreSQL — Key Differences for Schema Design

At a Glance

PostgreSQL and MySQL share the same SQL fundamentals but diverge in schema-critical ways that matter at design time. PostgreSQL has led MySQL in developer adoption since 2022, reaching 55.6% vs 40.5% in the 2025 Stack Overflow Developer Survey (89,000+ respondents). That shift reflects real differences in capability, especially around JSON, type system depth, and standards compliance.

How Does Auto-Increment Work in MySQL vs PostgreSQL?

The auto-increment syntax is the first thing you'll notice when porting DDL between the two databases. MySQL uses AUTO_INCREMENT as a column attribute, added inline. PostgreSQL historically used SERIAL as a shorthand type. Since PostgreSQL 10 (released 2017), the preferred approach is GENERATED ALWAYS AS IDENTITY, which is the SQL-standard equivalent. Both do the same job. The syntax just doesn't transfer directly.

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

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

-- PostgreSQL (legacy shorthand, still works)
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

One practical difference: SERIAL creates a backing sequence object that's loosely coupled to the column. GENERATED ALWAYS AS IDENTITY ties the sequence directly to the column, which makes dumps and restores more predictable.

MySQL's AUTO_INCREMENT and PostgreSQL's GENERATED ALWAYS AS IDENTITY serve the same purpose — generating a unique integer primary key — but are not syntax-compatible. GENERATED ALWAYS AS IDENTITY, introduced in PostgreSQL 10 (released 2017), ties the sequence directly to the column, making schema dumps and restores more predictable than the older SERIAL shorthand.

Does MySQL Have a Native Boolean Type?

MySQL has no native boolean type. TINYINT(1) is a convention, not a constraint. It stores any small integer, so a value of 5 or -3 is perfectly legal at the database level. ORMs like Laravel and Rails treat TINYINT(1) as boolean automatically, which hides the issue in application code. But it means your schema doesn't actually enforce boolean semantics.

PostgreSQL's native BOOLEAN type accepts TRUE/FALSE, 't'/'f', 'yes'/'no', and 1/0. Anything else raises a type error. It's a small thing, but it catches bad inserts at the database layer instead of silently storing garbage.

MySQL has no native boolean type; TINYINT(1) is a convention that stores any small integer, meaning values like 5 or -3 are valid at the database layer. PostgreSQL's native BOOLEAN type enforces true boolean semantics and rejects invalid input with a type error. ORMs abstract this difference, but raw SQL inserts and data migrations expose it directly.

Which Database Handles JSON Better: MySQL or PostgreSQL?

PostgreSQL's JSONB type stores JSON in a decomposed binary format. That means you can build a GIN index directly on the whole document and run fast key-existence queries without scanning every row. MySQL's JSON type validates the format on insert and supports path queries via JSON_EXTRACT(), but standard column indexes don't apply to JSON columns. The workaround is a generated column with an index on the specific path you query.

-- PostgreSQL: index the whole JSONB document
CREATE INDEX idx_meta ON events USING GIN (meta);

-- Query by key existence — uses the GIN index
SELECT * FROM events WHERE meta ? 'user_id';

-- MySQL: workaround — generated column + index on one path
ALTER TABLE events
    ADD COLUMN user_id_extracted VARCHAR(36)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(meta, '$.user_id'))) STORED,
    ADD INDEX idx_user_id (user_id_extracted);

For occasional JSON reads, MySQL's approach works fine. For workloads where you're querying by document content, filtering large datasets by nested keys, or running aggregations across JSON fields, JSONB wins clearly.

PostgreSQL's JSONB type stores JSON in a decomposed binary format, enabling GIN indexes on the full document for fast key-existence and path queries. MySQL's JSON type validates input on insert but requires generated columns to index specific paths. For document-style queries, JSONB performs substantially better, according to Bytebase's technical analysis (bytebase.com, May 2025).

Are CHECK Constraints Enforced in MySQL?

MySQL parsed CHECK constraints in DDL from early versions but silently discarded them before version 8.0.16, released in April 2019 (MySQL official blog). Any schema written before 8.0.16 that relies on CHECK constraints for data integrity isn't actually enforcing them. This is a known source of legacy data quality issues in older MySQL codebases.

PostgreSQL has always enforced CHECK constraints fully. If you write CHECK (age >= 0), the database rejects any insert that violates it, regardless of version. No version gating, no silent failures.

-- This works identically in both databases on modern versions:
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price >= 0),
    stock INT CHECK (stock >= 0)
);

-- But on MySQL < 8.0.16, those CHECK constraints are parsed and ignored.

Is MySQL or PostgreSQL Case-Sensitive by Default?

MySQL's default collation (utf8mb4_unicode_ci) is case-insensitive, so WHERE name = 'Alice' matches 'alice', 'ALICE', and 'aLiCe'. PostgreSQL is case-sensitive by default. This trips up almost every MySQL-to-PostgreSQL migration.

Queries that returned the right results in MySQL silently return fewer rows in PostgreSQL. You'll need LOWER() for normalized comparisons, or ILIKE for case-insensitive pattern matching. It's not a major issue in greenfield projects, but it catches teams off guard in migrations.

-- MySQL: matches 'alice', 'Alice', 'ALICE'
SELECT * FROM users WHERE username = 'alice';

-- PostgreSQL: matches 'alice' only
SELECT * FROM users WHERE username = 'alice';

-- PostgreSQL: case-insensitive alternative
SELECT * FROM users WHERE LOWER(username) = 'alice';
-- or
SELECT * FROM users WHERE username ILIKE 'alice';

MySQL's default collation (utf8mb4_unicode_ci) is case-insensitive: WHERE username = 'alice' matches 'Alice', 'ALICE', and 'aLiCe'. PostgreSQL is case-sensitive by default, so the same query matches only the exact case. This collation difference causes the majority of query behavior bugs during MySQL-to-PostgreSQL migrations, requiring LOWER() or ILIKE in PostgreSQL for case-insensitive comparisons.

How Do MySQL and PostgreSQL Handle Foreign Key Constraints?

Both InnoDB (MySQL) and PostgreSQL enforce foreign keys at the database level. The practical difference is how you bypass them when you need to, such as during bulk imports or data migrations. MySQL uses SET FOREIGN_KEY_CHECKS = 0, which is quick but easy to forget to re-enable. PostgreSQL uses SET session_replication_role = replica, a more explicit step that makes "I'm disabling FK checks right now" deliberate rather than accidental.

Neither approach is obviously better. The MySQL flag is more familiar. The PostgreSQL approach forces you to be intentional. Whichever database you use, always verify FK checks are re-enabled after bulk operations.

Both MySQL (InnoDB) and PostgreSQL enforce foreign key constraints at the database level. They differ in how constraints are temporarily bypassed: MySQL uses SET FOREIGN_KEY_CHECKS = 0, while PostgreSQL uses SET session_replication_role = replica. The PostgreSQL approach requires a deliberate session-level change, making it harder to accidentally leave FK checks disabled after bulk import operations.

How Adoption Has Shifted Since 2020

PostgreSQL crossed MySQL in developer adoption during the 2022 Stack Overflow Developer Survey and hasn't looked back. By 2025, 55.6% of all developers use PostgreSQL versus 40.5% for MySQL (Stack Overflow Developer Survey 2025, 89,000+ respondents). Among professional developers specifically, the gap widens: 58.2% for PostgreSQL versus 39.6% for MySQL.

The shift doesn't mean MySQL is dying. It still holds the #2 spot on DB-Engines by overall score (856.49 vs PostgreSQL's 682.68, May 2026) and remains dominant in legacy PHP/Laravel deployments and platforms like PlanetScale. But for new projects, the default assumption has shifted.

Which Should You Choose?

Here's a decision framework, stated plainly.

For most new web applications, PostgreSQL is the safer long-term choice. It's more standards-compliant, has a richer type system, and adoption trends favor it strongly. That said, switching databases mid-project has real costs. If your stack already speaks MySQL fluently, the marginal benefits of PostgreSQL rarely justify a migration.

PostgreSQL overtook MySQL in developer adoption during the 2022 Stack Overflow survey and now leads 55.6% to 40.5% overall, with a wider 18.6-point gap among professional developers alone (Stack Overflow Developer Survey 2025). For new projects, PostgreSQL is the stronger default, with better JSON handling, always-enforced constraints, and a more standards-compliant SQL dialect.

Whichever you choose, the schema design process is the same: model your entities and relationships first, pick appropriate data types, and use a free online database designer to validate the design visually before writing DDL.