Everything I learned migrating two massive codebases from MySQL 8 RDS to Postgres RDS, so you don’t have to figure it out alone.
Press enter or click to view image in full size
This article will outline how I led two separate companies’ migrations from MySQL to Postgres hosted using RDS (AWS). We’ll explore why the migrations were necessary, how the data was moved and how the codebases were updated. We’ll also reflect on the aftermath of the migrations. By the time you’re done with the article you should have a good understanding of the process and key decisions involved.
Introduction
There are very few situations that can force an organization to change their database. The biggest reason to avoid the change is the stability. Why risk everything on a new database? Pricing is a reason, but rarely good enough by itself. The much bigger reason that I’ve faced in real life is when the technology leads to dissatisfied customers and ends up costing you actual revenue. When your database causes enough fires in production, even a battle-tested provider isn’t safe from replacement. This is one of those stories.
The Two Codebases
The migration has been performed on two separate code bases. Here are some details about them.
The first codebase I was migrating had about 350 tables and a few million lines of code on the backend. It was written in Java using Hibernate. This migration took ~4 weeks. The monthly spend on RDS was ~80k partially due to many ephemeral environments with full replication running.
The second one had 80 tables and a few hundred thousand lines of code. It was a Go codebase using the lit orm. The migration took 1 week. The monthly spend on RDS was $2k and there were no ephemeral environments used.
The methodology outlined here was the same for both of them.
Reasons to Migrate
First reason to migrate: Locks
I used MySQL for well over a decade and never had issues with it until we hit a certain scale on version 8. The root cause was MySQL’s metadata locking (MDL) behavior. Any ALTER TABLE statement requires an exclusive MDL on the table, and while waiting for that lock, all subsequent queries, even simple SELECT statements, queue up behind it. If any long-running or uncommitted transaction is holding a shared MDL, the ALTER can’t acquire its exclusive lock, and every query after it piles up waiting. This behavior seemed to be compounded by the RDS replication, we were seeing the lock hang even when no other transaction has touched that table.
In theory this is manageable. In practice, on busy production tables with constant traffic, it meant that most ALTER TABLE statements during deployments led to cascading lock queues that could only be resolved by a database restart. This turned routine releases into potential outages. Failed migrations had to be fixed and rerun manually, which compounded the problem further. I felt dread each time I knew an ALTER TABLE was coming, especially on our busiest tables.
This MySQL behavior combined with how often we were modifying our existing tables meant it was no longer working at our scale.
Second reason to migrate: Price/Performance
Having a really large RDS bill that towers all your other cloud spending will lead to suits asking questions. Why are we paying $80k a month for RDS? How can we save money? The answer is not obvious or always just use Postgres but it often can be, different tradeoffs and continuous improvements have made Postgres a web standard for new projects. I relied on MySQL for over a decade and didn’t understand the hype until I tried it. Postgres has outperformed MySQL on all workloads that I cared about (mixed read/write), every single time I’ve run a test it had better response times, more predictable behavior, better resource utilization and fewer resource requirements. This is an anecdotal example. You should definitely not blindly believe that Postgres is instantly better but you should definitely test your workload with it, the performance might surprise you.
With both the stability and cost problems clear, here’s how we actually did it.
Schema Migration
The schema migration is necessary because MySQL and Postgres have slightly different data types, for example in Postgres it’s a common practice to use the text type for all textual fields, whereas in MySQL varchar(length) is often preferred for short text fields. Another big reason for migrating the schema is that Postgres is not forgiving when it comes to type conversions, making sure your codebase matches the Postgres types is crucial. To migrate the schema we can use the AWS tool called Database Migration Service (DMS). First we’d connect the database we’re transferring from and the database we’re transferring to and the new schema suggestion would be auto generated. We need to manually validate this new schema and make sure it’s correct, anything we disagree with we should change at this point. After we have the schema in place we should export it as this will be our starting point. Now we’re ready for the second step: moving the data over.
Data Migration
After the schema is in place we have to move our existing data over, this can also be achieved with DMS. I recommend you use a dedicated EC2 instance for this as using a powerful instance can drastically speed up the transition process. The full load phase of close to a terabyte of data took about 30min. The biggest factor was the biggest single table size. The migration is done in parallel on the per-table level. The total time is usually bound by the largest table. There are two ways to run the migration, one where the replication stops after it’s done moving (perfect for testing) and the continuous replication mode which allows you to keep your new instance in sync with the current one (perfect for the final production deployment). After the data moves over you should make sure to recreate your indexes.
Code Migration
This is the hardest and most time-consuming part of the migration. It breaks down into two steps: updating the driver and updating the queries.
Updating the driver/connection is easy and dependent on your database access patterns. This is usually a straight up rename operation that can be done quickly.
Updating the queries is a completely different beast. There are quite a few differences between MySQL and Postgres. Here are the main ones with examples:
Datetime functions:
-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
-- Postgres
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;Parameter binding:
-- MySQL
SELECT * FROM users WHERE id = ? AND status = ?;
-- Postgres
SELECT * FROM users WHERE id = $1 AND status = $2;Updates with joins:
-- MySQL
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'active'
WHERE u.role = 'admin';
-- Postgres
UPDATE orders o
SET status = 'active'
FROM users u
WHERE o.user_id = u.id AND u.role = 'admin';JSON operations:
-- MySQL
SELECT json_extract(metadata, '$.name') FROM products;
-- Postgres
SELECT metadata->>'name' FROM products;Case-insensitive matching:
-- MySQL (case-insensitive by default with most collations)
SELECT * FROM users WHERE name LIKE 'john%';
-- Postgres (case-sensitive by default, use ILIKE)
SELECT * FROM users WHERE name ILIKE 'john%';Type casting:
-- MySQL
SELECT CAST(price AS SIGNED) FROM products;
-- Postgres
SELECT price::integer FROM products;To update the queries to fully support all those differences in a large codebase we have a few options. The easiest option is using a regex search, unfortunately this is not always easy or accurate. The more complex option to set up but the one I went with is using an AST parser. I would find a library that can parse out your specific language’s (in my case Java and Go) code and generate an AST that I can iterate for specific strings/patterns.
For Go, the standard library’s go/ast and go/parser packages were all we needed. Here's a simplified version of the parameter binding migration — one of the 5 automated fixes we ran on the second codebase:
func processFile(filename string) (bool, error) {
fset := token.NewFileSet()
node, err := parser.ParseFile(fset, filename, nil, parser.ParseComments)
if err != nil {
return false, err
}
modified := false
ast.Inspect(node, func(n ast.Node) bool {
callExpr, ok := n.(*ast.CallExpr)
if !ok {
return true
}
for _, arg := range callExpr.Args {
if lit, ok := arg.(*ast.BasicLit); ok && lit.Kind == token.STRING {
originalSQL := strings.Trim(lit.Value, "`\"")
if !strings.Contains(originalSQL, "?") {
continue
}
// Convert ? placeholders to $1, $2, ...
result := strings.Builder{}
paramCount := 1
for i := 0; i < len(originalSQL); i++ {
if originalSQL[i] == '?' {
result.WriteString(fmt.Sprintf("$%d", paramCount))
paramCount++
} else {
result.WriteByte(originalSQL[i])
}
}
lit.Value = "`" + result.String() + "`"
modified = true
}
}
return true
})
if modified {
var buf bytes.Buffer
format.Node(&buf, fset, node)
os.WriteFile(filename, buf.Bytes(), 0644)
}
return modified, nil
}This reads the file, finds SQL string literals containing ? placeholders, and rewrites them to Postgres-style $1, $2 parameters, all without breaking the surrounding code. We had similar scripts for each of the query differences listed above.
Now that we have a mostly automated setup for fixing issues it’s time to start on our fix it loop:
- Find an issue (manually or via tests)
- Write an automated fix for the issue
- Run the automated fix
- Confirm the issue is resolved in all places
The first code base ended up having (I believe) around 60 code migrations, the second one had about 5. It all will depend on the ORM being used, some will be easier to migrate, some will not. The more manual SQL there is to fix, the more fixes will be needed. The actual backend work took 98% of the time on both migrations.
Good end-to-end tests will definitely help you catch the issues quickly, manually testing the codebase is possible but you have to be realistic and be ready to deal with queries that get missed post deploy. If this is acceptable then you can proceed, in any other case you should add automated tests first.
Deployment
Both deployments had a scheduled downtime of 30min planned, the actual downtime was about 2–3min for both of them. This is the order of operations that was performed:
- DMS runs until the data is moved over
- Server shutdown/stop writes
- DMS catches up
- Verify data (row counts, spot check the data)
- Switch connection string
- Start new servers
Having DMS catch up to the live system allowed most of the data migration to be performed while the system was running. Shutting down the previous server allowed DMS to replicate the DB fully and the new servers deployment to take over using the Postgres instance. The DMS replication task was then shut down, MySQL instance was snapshot one final time and then turned off. We continued monitoring the release after but had no major issues on either one of the systems.
We had practiced and performed this deployment scenario quite a few times before doing it on the live database so we felt quite comfortable with it.
Our rollback strategy was straightforward: push forward. The MySQL instance was kept as a snapshot but we didn’t plan to switch back to it. If issues appeared post-cutover, we’d fix them in place, whether that meant manually moving missing data or hotfixing broken queries. This might sound reckless, but by the time we deployed we had already validated the migration multiple times in staging and felt confident in the data integrity. Rolling back would have meant re-migrating all data written to Postgres back to MySQL, which carried its own risks. Moving forward was the simpler path.
Results
Drum roll… the first codebase ended up running so much more efficiently that all instances got scaled down and had their price cut in half. The next month’s bill was $40k, leading to the yearly savings of about $480k. The second code base had one of its recurring tasks go from 1min to 6sec leading to a more “realtime” feel of the platform, the actual response times got cut in half as well across the board. The second company decided to take the performance win without scaling their RDS up or down.
Lessons Learned
I really enjoyed doing both of these migrations personally. They were massive projects that required a lot of engineering effort and ingenuity and I was lucky to have support from two incredible teams including both manual testers and additional software engineers. Here are the biggest takeaways I’d pass on to anyone considering a similar migration.
The code migration is the real project.
Backend work consumed 98% of the total migration time on both projects. Don’t let stakeholders think this is a “database team” problem. The schema and data move relatively quickly, it’s the thousands of query differences buried across your codebase that will eat your timeline. Budget at least 2x what you think the code work will take. The long tail of edge cases is what gets you.
Invest in automated testing before you start.
Having solid end-to-end test coverage is invaluable for catching broken queries early. If you don’t have that, seriously consider adding it before you begin. Finding query issues in production is significantly more stressful than finding them in a test suite.
Choose AST parsing over regex.
Regex feels faster to set up but falls apart on edge cases quickly. Writing an AST-based migration tool took more upfront effort but paid for itself many times over, especially on the larger codebase with 60 separate code migrations to apply. It gave us confidence that fixes were applied consistently everywhere, not just in the obvious places.
Use continuous replication to minimize downtime.
Running DMS in continuous replication mode meant the bulk of the data was already in Postgres before we ever touched production. This single decision is what turned a potentially multi-hour cutover into a 2–3min one.
Practice the cutover. Then practice it again.
We rehearsed the full deployment sequence multiple times before going live. By the time we did it for real, every step was muscle memory. That’s why our planned 30min window shrank to minutes with zero surprises.