We moved a live store from MySQL to Postgres. Here's what actually broke.

We run a multi-vendor e-commerce platform, one of our own projects: shops, catalogs, carts, orders, subscriptions, referrals, the whole thing. Ninety-plus migrations, real traffic, real money. And we moved it from MySQL to Postgres.

Here's the honest part nobody puts in the tutorial: the data moves fine. It's the assumptions baked into your app over the years that break. MySQL is forgiving. Postgres is strict. Every place you leaned on MySQL being lenient is a bug waiting for you on the other side.

This is the list I wish I'd had before I started.

First, the strategy: don't hand-move the data

Two roads work. Pick based on how much you trust your migrations.

• pgloader — a single tool built for exactly this. It reads your MySQL database and writes it into Postgres, converting types as it goes (TINYINT(1) → boolean, AUTO_INCREMENT → sequence, and so on). One command, most of the job done.
• Laravel's query builder — because it's database-agnostic, you can read from the old connection and write to the new one in PHP, transforming rows as needed. More control, more code. Good when your schema has weird corners pgloader trips on.

But the real first test isn't the data at all. It's this:

And you don't delete MySQL the day you switch. You keep it running, in the corner, for days or weeks — until you're sure. Cutovers get reverted. Give yourself the road back.

Gotcha 1: search stopped matching (case sensitivity)

This is the one that bites an e-commerce app hardest. MySQL's default collation is case-insensitive. Postgres is case-sensitive.

So this query, which happily found "iPhone" on MySQL:

// MySQL: 'iphone' matches 'iPhone'
Product::where('name', 'LIKE', "%{$q}%")
    ->get();

// on Postgres, LIKE is
// case-SENSITIVE. no match.

// ILIKE = case-insensitive LIKE
Product::where('name', 'ILIKE', "%{$q}%")
    ->get();

// or normalize both sides:
// whereRaw('LOWER(name) LIKE ?', ...)

Every product search, every "find by email", every filter that a customer types by hand — audit all of it. ILIKE is the quick fix. For heavy search, the citext extension or a LOWER() index is the real one.

Gotcha 2: 0 and 1 are not true and false

MySQL has no real boolean — TINYINT(1) holds 0 or 1. Postgres has an actual boolean type holding TRUE/FALSE. Laravel casts smooth over most of this, but the moment you compare in raw SQL or feed a literal 1, it bites.

// works on MySQL, errors on PG
DB::table('shops')
  ->whereRaw('is_active = 1')
  ->get();

// PG: operator does not exist:
// boolean = integer

Shop::where('is_active', true)->get();

// let Eloquent + the cast
// handle the dialect for you
protected $casts = [
  'is_active' => 'boolean',
];

Gotcha 3: the auto-increment sequence that forgot where it was

This is the nastiest, because it passes every test and then explodes in production. When you bulk-import existing rows into Postgres, the table's id sequence does not advance to match. Your data has ids up to 40,000; the sequence still thinks the next id is 1.

The first time a customer creates an order, Postgres hands out id 1 — which already exists — and you get a duplicate-key crash on a brand-new record. On checkout. In production.

Gotcha 4: your raw SQL speaks MySQL

Anywhere you dropped down to DB::raw, whereRaw, selectRaw, or a raw MySQL function, Postgres may not understand it. We had these scattered across services and controllers — the exact places that never show up until they run:

• FIND_IN_SET(), GROUP_CONCAT() → Postgres uses arrays / string_agg().
• DATE_FORMAT() → Postgres uses to_char().
• RAND() → Postgres is RANDOM().
• Backtick quoting `column` → Postgres uses double quotes "column".

Grep your whole app for DB::raw, whereRaw, selectRaw before you cut over. Each hit is a manual review.

Gotcha 5: Postgres won't let sloppy queries slide

Two more places MySQL let you be lazy and Postgres won't:

• GROUP BY is strict. Every non-aggregated column in your SELECT must appear in GROUP BY. MySQL would guess; Postgres refuses. Reporting and dashboard queries are where this hides.
• No silent truncation. Insert a string longer than the column and MySQL quietly trims it; Postgres throws. Same for an invalid value in a uuid column — MySQL stores any string, Postgres validates it's a real UUID. This is Postgres protecting your data, but old code that relied on the trim will now error.

Was it worth it?

Yes. On the other side you get JSONB with real indexing (a gift for a product catalog full of attributes), stricter data integrity that catches bugs MySQL swallowed, and query planning that holds up as the data grows. But go in clear-eyed: the migration isn't a data transfer, it's an audit of every lazy assumption your app has made since day one. Postgres is the strict senior reviewer your codebase never had. It's going to find things. That's the point.

If your app has been on MySQL for years, do the empty-database migration test this week. You'll learn more about your own schema in one afternoon than in the last two years of shipping on it.