Migrating from MySQL to PostgreSQL in Laravel 13: The Four Places Your App Breaks

Switching DB_CONNECTION to pgsql migrates nothing: the app boots, and a week later a GROUP BY, a tinyint(1) or an upsert() that needs a different index in PostgreSQL takes it down. These are the four places your app breaks, in the order you should fix them.

First: Decide Whether Migrating Is Worth It

Migrating from MySQL to PostgreSQL costs weeks of inventory, rehearsals and one cutover window. Before you open pgloader, confirm the change solves a problem you can measure today. If you cannot name that problem in one sentence with numbers in it, the answer is to wait.

Five Real Reasons to Move to PostgreSQL

• Reports and analytical queries. Aggregations over large tables, window functions, CTEs and GROUPING SETS are where the PostgreSQL planner feels at home.
• JSON you actually query. jsonb stores the document in binary form and supports GIN indexes, so filtering by content stops being a full scan.
• Write concurrency. Several queues writing to the same table coexist better: reads do not wait for writes.
• Transactional DDL. If a migration fails halfway, the schema rolls back completely instead of being left half-built.
• Extensions. pgvector for semantic search, PostGIS for geographic data, pg_trgm for fuzzy matching. Installing one is a CREATE EXTENSION, not a project.

Three Cases Where Staying on MySQL Is the Right Answer

• Your team only knows MySQL. Running an engine nobody masters costs more than any slow query you are trying to escape.
• Your host does not offer PostgreSQL. If the provider only sells managed MySQL, migrating means running the engine yourself, backups and upgrades included.
• Your app has no pain. A store with 20,000 rows and queries under 50 ms gains nothing from the move.

The Rule of Measurable Pain

Migrate when you can point at the number: queries crossing one second in the slow log, migrations that lock a table for twenty minutes, reports assembled in PHP because the SQL cannot do it. That number is your success criterion and the way to know, six months later, whether the migration paid off.

The Differences That Break Code

These are the ones that show up in production, not on the first boot. The table summarizes them; below it is the concrete symptom of each.

Read also

Your Query Takes Four Seconds: How to Find It and Fix It in MySQL 8.4

Laravel 13 Deployment on a VPS: Nginx, PHP-FPM, and Zero Downtime Step by Step

Identifiers, Case Folding and Quotes

PostgreSQL folds any identifier that is not wrapped in double quotes to lowercase: a table created as UserLogs without quotes actually lives as userlogs. MySQL on Linux, with a case-sensitive filesystem, treats UserLogs and userlogs as different tables. The rule that removes the problem entirely is snake_case for every table and column, with double quotes only when the name demands it. Forget backticks as well: PostgreSQL uses double quotes for identifiers and single quotes for strings.

AUTO_INCREMENT vs IDENTITY

Autoincrementing columns are declared as GENERATED BY DEFAULT AS IDENTITY. serial and bigserial still exist for compatibility, but IDENTITY is the standard form and what Laravel generates with $table->id(). The trap is not in the schema but in the data: when you copy rows with explicit ids, the attached sequence does not advance. The first INSERT after cutover tries id 1 and fails with duplicate key value violates unique constraint.

tinyint(1) and Booleans That Are Now true and false

In MySQL a boolean is a tinyint(1) the client returns as 0 or 1. In PostgreSQL it is a real boolean type the driver returns as true or false. Every check using === 1, == 1 or the value as an array index breaks silently. Find those comparisons and switch them to true/false or to an explicit cast at the application boundary.

JSON vs JSONB: Only One of Them Indexes

In PostgreSQL, json keeps the text exactly as it arrived and has no containment operator. jsonb stores it in binary form, supports the @> operator and accepts GIN indexes. If a column stays as json, whereJsonContains has nothing to work with. Change the type to jsonb in the schema and in the data, not only in the Laravel migration.

ON DUPLICATE KEY vs ON CONFLICT

MySQL fires the UPDATE when the statement collides with any unique key in the table. PostgreSQL requires you to name the column or constraint: ON CONFLICT (external_id) DO UPDATE SET .... That is more precise and therefore more fragile when the index is missing. With no unique index on that combination, the engine answers with there is no unique or exclusion constraint matching the ON CONFLICT specification.

LIKE, ILIKE and Strict GROUP BY

LIKE is case-sensitive in PostgreSQL. For a user search you need ILIKE or a lower() comparison; in Laravel, whereLike('name', $term, caseSensitive: false) picks the right operator per engine. GROUP BY is strict by default, the same as enabling ONLY_FULL_GROUP_BY in MySQL: every non-aggregated column in the SELECT must appear in the GROUP BY. There is also no UPDATE ... LIMIT and no UNSIGNED.

What Changes Inside Laravel 13

The pgsql Connection and Migrations

The driver already ships in config/database.php; what changes is the .env block and the queue workers, which keep the old connection in memory.

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=app_db
DB_USERNAME=app_user
DB_PASSWORD=secret

In migrations, $table->jsonb('meta') beats $table->json(), and $table->boolean() maps to a native boolean. $table->enum() does not create an enum type: it generates a varchar with a CHECK, which spares you the pain of altering types later.

upsert(): The Unique Index MySQL Did Not Need

Laravel requires a primary key or unique index on the conflict columns. If the index is missing, upsert() does not degrade gracefully: it fails, or inserts duplicates depending on the case.

DB::table('orders')->upsert(
    [['external_id' => 'A-1', 'status' => 'paid', 'updated_at' => now()]],
    ['external_id'],
    ['status', 'updated_at']
);

Review every upsert() in the project and confirm the matching unique index exists. If the code synced by email in MySQL because any unique key triggered the UPDATE, in PostgreSQL you either add the unique index on email or switch to updateOrCreate(), slower but explicit.

whereJsonContains on jsonb and GIN Indexes

The method relies on @>, which only exists on jsonb. Once the type is fixed, add the index:

CREATE INDEX orders_meta_gin ON orders USING gin (meta);

For a single key, an expression index is far smaller: CREATE INDEX orders_meta_status ON orders ((meta->>'status'));.

CREATE INDEX CONCURRENTLY Outside the Transaction

A concurrent index cannot run inside a transaction, and Laravel migrations wrap everything by default. The migration has to step out of that block:

public $withinTransaction = false;

public function up(): void
{
    DB::statement('CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_meta_gin ON orders USING gin (meta)');
}

If your version ignores that property, the equivalent is running the DB::statement from an artisan command during deploy, outside DB::transaction.

Copying the Data with pgloader

The Minimal Command and What It Does Under the Hood

pgloader reads the MySQL catalog, creates tables and indexes in PostgreSQL, converts types and copies rows with COPY. The minimal command is enough for a quick test against an empty database:

pgloader mysql://app_user:secret@127.0.0.1/app_db \
         postgresql://app_user:secret@127.0.0.1/app_db

A Load File with CAST and reset sequences

A real migration deserves a load file, where every decision is written down and versioned:

LOAD DATABASE
     FROM mysql://app_user:secret@127.0.0.1/app_db
     INTO postgresql://app_user:secret@127.0.0.1/app_db

WITH include drop, create tables, create indexes, reset sequences,
     foreign keys, workers = 8, concurrency = 1,
     prefetch rows = 10000

SET MySQL PARAMETERS
    net_read_timeout = '600',
    net_write_timeout = '600'

CAST type datetime to timestamptz drop default drop not null
           using zero-dates-to-null,
     type date drop not null drop default
           using zero-dates-to-null,
     type tinyint when unsigned to smallint drop typemod,
     type json to jsonb;

Three pieces matter: zero-dates-to-null turns MySQL's 0000-00-00 into NULL, because PostgreSQL rejects that date; unsigned tinyint has no direct equivalent and becomes smallint; and json to jsonb is what enables GIN indexes. reset sequences aligns sequences with the current maximum and prevents the first INSERT from failing.

Verifying the Copy: Counts, Checksums and Sequences

Compare per-table counts in both engines using count(*): estimates from pg_stat_user_tables are useless for validation. Sum or hash a key column in both databases and compare the result. Then check sequences by hand:

SELECT setval(pg_get_serial_sequence('orders','id'),
              COALESCE((SELECT MAX(id) FROM orders), 1));

The COALESCE covers the empty table: without it the query returns NULL and the sequence is left unusable. Repeat for every table with an autoincrementing id, pivot tables included.

The Cutover Plan, Step by Step

A Raw SQL Inventory Before Touching Anything

Hand-written SQL is where the surprises live, because it never passes through Eloquent's query builder. Search for DB::raw, selectRaw, whereRaw, orderByRaw and havingRaw across app/ and database/, and also review database views and jobs that build SQL by concatenation. The slow queries you already identified in the MySQL log are the best starting point: finding and fixing slow queries in MySQL 8.4 explains how to pull them out of the log.

A Full Rehearsal Against a Real Postgres

Spin up a container with the same version you will run in production and execute the full suite against it:

docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=secret postgres:18

SQLite does not validate this migration: it has no jsonb, no ILIKE, it does not enforce strict types and it produces false positives that surface in production. A serious rehearsal uses a real copy of the data, not a ten-row seed. If the deploy runs on a VPS, the walkthrough on deploying Laravel 13 on a VPS with zero downtime covers the .env, the workers and the queue restart.

Cutover Window, Dual Writes and Rollback

With a window, the order is: read-only mode in the app, final incremental copy, count verification, connection switch, php artisan queue:restart, schema cache flush and reopen. Without a window, dual writes for a few days solve the cutover, at the cost of duplicating write logic and watching for differences between engines.

Rollback is prepared beforehand, not during: MySQL stays frozen and available for 72 hours, with the connection chosen by environment variable. Going back is a one-line change and a worker restart, not a backup restore.

Performance in the First Month

EXPLAIN (ANALYZE, BUFFERS) Instead of SHOW PROFILE

PostgreSQL has no SHOW PROFILE. The tool is EXPLAIN (ANALYZE, BUFFERS), which reports real time per node and how many pages came from cache and from disk. A Seq Scan over a million-row table with a hundred buffers usually means a missing index, not a lack of memory.

Autovacuum, Connections and PgBouncer

Autovacuum is on by default and you do not turn it off: it is what keeps space reusable and statistics current. Check pg_stat_user_tables to spot tables with many dead rows. If you run many queue workers, the first bottleneck is the connection limit, and the answer is PgBouncer in transaction mode before raising max_connections.

Checklist and Next Steps

1. Pain measured and written down before starting.
2. Raw SQL inventory finished, views and jobs included.
3. Real copy migrated with pgloader and a versioned load file.
4. Counts and checksums matching across both engines.
5. Sequences reset on every table with an id.
6. Test suite green against PostgreSQL 18, not against SQLite.
7. Every upsert() with its unique index verified.
8. JSON columns migrated to jsonb with GIN indexes where needed.
9. Rollback actually tested, with workers restarted.
10. Follow-up plan: slow queries and EXPLAIN during the first month.

Migrating from MySQL to PostgreSQL is a change of semantics, not a driver swap: data, types and queries follow new rules. If the next step is picking a version, the decision today is PostgreSQL 18 in production, because 19 slipped to November 19 and is not stable yet. The blog has more Laravel production guides to continue with deployment and performance.