01Why it is hard
A payment gateway has no maintenance window that merchants will accept. Authorisations, callbacks, refunds and reporting all hit the database continuously, and a transaction caught mid-flight during a cutover can end up charged but unrecorded, or recorded twice.
I migrated a production gateway database from SingleStore to MariaDB under live transaction load with no downtime and no data loss. It was the most operationally complex work I have done. The principles below are what made it possible.
02Compatibility before data
Moving between engines is first a query problem. A distributed store and a single-node relational database differ in data types, indexing, transaction behaviour and what a slow query looks like. Every query the application runs has to be checked against the target, and the heavy ones rewritten and tested at production volume before any data moves.
03Copy, keep in sync, prove it
Historical data is copied in the background in bounded batches so the copy never competes with live traffic. While that runs, new writes have to reach both sides, so the target stays current.
Then comes verification, which deserves more time than the copy. Row counts per table and per day, sums of amounts per merchant, and spot comparisons of individual transactions. In payments, almost-equal is a defect.
04Cut over in steps, keep the way back
Reads move first, starting with the least critical: reporting and exports before transaction lookups. Writes move last, at the quietest point of the day, with the team watching error rates and latency in real time.
The old database stays in sync until the new one has carried full production load for long enough to trust it. A migration without a rollback path is a bet, not a plan.