Designing a Data Migrator in Go

Our Journey of migrating millions of records from MongoDB to Postgres

First Attempt — The quick and dirty way

Image for post
Image for post
Distributing workload to an equal number of Go-routines

The average time to migrate 1 million records on a steady database was ~30 minutes. That means, theoretically, it would take more than 5 hours to migrate 10 million records if we do it during low traffic hours. And, we had a lot more to migrate.

Debugging the tool

Image for post
Image for post
Postgres: 99th Percentile metric for our insert/update query
Image for post
Image for post
Postgres: Average total time vs Number of calls per minute
Image for post
Image for post
Mongo: Average execution time in seconds per read operation
Image for post
Image for post
Mongo: Average documents returned per second
Image for post
Image for post
Mongo: System CPU usage
Image for post
Image for post
Mongo: The ratio of the number of indexed items scanned to the number of documents returned by queries

Identifying the issue

Rethinking the design

Image for post
Image for post
Adding a layer to limit interaction with MongoDB
An example implementation of the revised approach: https://play.golang.org/p/rrLajaJDOIu

Testing the new design

The average time to migrate 1 million records was ~25 seconds now. 5 million records took ~125 seconds on average.

Checking DB metrics

Image for post
Image for post
Postgres: Average total time vs Number of calls per minute
Image for post
Image for post
Mongo: Average execution time in seconds per read operation (300ms)
Image for post
Image for post
Mongo: Documents returned over a given sample period (53k/s)
Image for post
Image for post
Image for post
Image for post
Mongo: System CPU usage (18%), Query targeting (1)

Can we do better than this?

Image for post
Image for post
…and we were not an exception!
Image for post
Image for post
Alternate design with separate layer to communicate with Postgres

Takeaway

Written by

…always confused 🙃

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store