MySQL to PostgreSQL: What I Learned

Moving a production API from MySQL to PostgreSQL sounded fairly straightforward at first.

Change the database driver, migrate the data, fix the SQL differences, and move on.

It turned out to be more involved than that.

The database engine was only one part of the migration. The ORM, SQL queries, date handling, migrations, connection configuration, foreign keys, and operational tooling all had assumptions built around MySQL.

This is what I learned from the process.

Why I moved

The decision wasn’t based on one dramatic MySQL problem.

The application had been running on MySQL successfully, but I wanted a stronger foundation for the next stage of the platform.

PostgreSQL gave me a better fit for the direction I wanted to take the application:

• a first-class relational database with strong SQL support
• excellent support for modern application workloads
• powerful indexing and query capabilities
• mature backup and recovery tooling
• a strong ecosystem around PostgreSQL operations

The important part is that I wasn’t trying to replace MySQL because it was “bad”.

It wasn’t.

I was changing the database because I wanted the platform underneath the application to evolve.

The migration was more than changing the driver

The first surprise was how many parts of the application depended on MySQL without explicitly saying so.

The application had previously used Sequelize with MySQL.

I moved the application to PostgreSQL and MikroORM.

That meant changing three things at once:

MySQL
  ↓
PostgreSQL

Sequelize
  ↓
MikroORM

MySQL-specific SQL
  ↓
PostgreSQL SQL

The ORM migration and database migration therefore became one larger application migration.

In hindsight, I would have separated those concerns if possible.

Changing the database and ORM at the same time increases the number of things that can be wrong when a query suddenly stops working.

SQL differences show up everywhere

Basic CRUD queries were mostly fine.

The surprises came from the queries that had accumulated over time.

Some MySQL-specific functions simply don’t exist in PostgreSQL.

For example, code that previously relied on:

YEAR(created_at)
MONTH(created_at)
DATE_FORMAT(created_at, '%Y-%m')

had to be rewritten using PostgreSQL expressions.

That sounds trivial, but these functions tend to appear in more places than you expect:

• reports
• statistics
• dashboards
• date filtering
• monthly summaries
• exports

The lesson was simple:

Don’t assume SQL portability just because both databases are relational.

The more application logic lives inside SQL, the more important this becomes.

AUTO_INCREMENT is another obvious example

MySQL commonly uses:

AUTO_INCREMENT

PostgreSQL has a different model based around identity columns and sequences.

That became particularly relevant when generating and running migrations.

A migration that was perfectly valid for MySQL could simply fail when executed against PostgreSQL.

One of the migration failures I hit was effectively a reminder that:

ORM migrations are generated from database assumptions, not from abstract relational theory.

I learned to inspect generated SQL instead of assuming the ORM had produced exactly what I wanted.

Dates and timestamps deserve more attention than they get

Date handling was another area that required deliberate changes.

The application had several places where date extraction and formatting were performed directly in SQL.

PostgreSQL has a different set of timestamp and date functions, and the semantics aren’t always identical.

I ended up moving some logic away from MySQL-specific expressions and toward PostgreSQL-native timestamp handling.

This was also a good reminder that:

A timestamp is not just a string with a date in it.

Timezone handling, database types, application serialization, and display formatting all need to agree.

Foreign keys and migrations were another pain point

One of the areas I paid the most attention to was foreign-key behavior.

During the migration work, generated migrations attempted to remove and recreate a significant number of foreign keys.

That can be dangerous when you’re dealing with an established schema.

A generated migration isn’t automatically a safe migration.

I started treating migration output as something to review manually:

Generate
   ↓
Read the SQL
   ↓
Check constraints
   ↓
Check indexes
   ↓
Check defaults
   ↓
Then execute

That extra step is worth it.

Connection handling changed too

The migration also forced me to revisit connection handling.

With MySQL, the application had been using a connection pool with fairly aggressive settings.

Moving to PostgreSQL made me reconsider what the application actually needed instead of carrying the old configuration across unchanged.

Connection pooling isn’t just a number you tune once.

It interacts with:

• database capacity
• application concurrency
• request duration
• connection acquisition time
• query behavior
• deployment topology

The same values that made sense for one database environment don’t necessarily make sense for another.

PostgreSQL also changed how I think about backups

The migration gave me an opportunity to improve the operational side as well.

For PostgreSQL I introduced pgBackRest and object storage for backups.

The important distinction is between:

logical backup

and:

physical backup + WAL

For a production database, recovery is more important than simply having a dump file sitting somewhere.

I wanted a backup strategy that answered:

How do I actually recover this database?

rather than:

Did yesterday’s backup command succeed?

That led me to:

• PostgreSQL physical backups
• WAL archiving
• S3 storage
• retention policies
• backup verification

The backup system became part of the platform rather than something bolted on after the migration.

The performance question

One of the questions I had before starting was:

Is the API going to become slower with PostgreSQL?

The honest answer is: not necessarily.

Database performance isn’t determined by the database name.

It depends on:

• query shape
• indexes
• connection management
• memory
• disk I/O
• caching
• data distribution
• ORM behavior
• application access patterns

The migration therefore shouldn’t be treated as:

MySQL → PostgreSQL = faster

or:

MySQL → PostgreSQL = slower

It’s more useful to think:

Application
    ↓
Queries
    ↓
ORM
    ↓
Connection pool
    ↓
Database
    ↓
Storage

Every layer matters.

Things I would do differently

The migration worked, but there are a few things I’d change if I had to do it again.

1. Separate the ORM migration from the database migration

Doing:

Sequelize → MikroORM

and:

MySQL → PostgreSQL

at the same time makes debugging harder.

A future migration would be easier if those changes were isolated.

2. Inventory database-specific SQL first

Before touching the database, I’d search the entire application for:

YEAR(
MONTH(
DATE_FORMAT(
AUTO_INCREMENT
ENUM
MySQL-specific functions

and other dialect-specific assumptions.

That gives you a much clearer migration scope.

3. Review generated migrations

Never assume generated migration SQL is production-safe.

Read it.

Especially when it touches:

• foreign keys
• indexes
• defaults
• identity columns
• constraints

4. Treat backups as part of the migration

A database migration without a tested recovery strategy is incomplete.

Before changing production, I want:

Backup
  ↓
Verify
  ↓
Migrate
  ↓
Validate
  ↓
Know how to recover

What actually surprised me

The database switch itself wasn’t the hardest part.

The hardest part was discovering all the places where the application had quietly learned to depend on MySQL.

That included:

• SQL functions
• timestamp handling
• migration behavior
• generated SQL
• foreign keys
• connection configuration
• ORM assumptions

That is probably the biggest lesson I’d take from the migration.

A database is not just another dependency.

It becomes part of the application’s behavior.

Would I do it again?

Yes.

But I wouldn’t describe PostgreSQL as universally better than MySQL.

Both are excellent relational databases, and the right choice depends on the workload, operational environment, team experience, and application requirements.

For this platform, PostgreSQL is a better fit for where I want to take the system.

The migration also gave me an opportunity to improve the surrounding platform, not just change the database engine.

That ended up being the more valuable part.

Final takeaway

The biggest lesson wasn’t:

PostgreSQL is better than MySQL.

It was:

Database migrations are application migrations.

Changing the engine affects SQL, ORM behavior, migrations, connection management, backups, and operational practices.

Plan for the whole system, not just the database.

And if you’re about to start the same migration yourself, my advice is simple:

inventory first, migrate deliberately, inspect the generated SQL, and have recovery tested before you touch production.