Lessons learned from migrating large cross-database data onto a critical production system
By Alex Ghise, Staff Software Engineer
If you’re interested in distributed systems and building trusted and shared services to power all of Coinbase, the Platform team at hire!
In 2019, Coinbase aimed to strengthen the infrastructure on which its products are built and create a solid foundation to support current and future product lines. As part of that effort, we decided to adopt AWS RDS PostgreSQL as our database of choice for relational workloads and AWS DynamoDB as our key value repository.
One of the first areas we decided to focus on was how to keep track of balances and move money. Each of our products had their own solutions and some legacy systems were also plagued by technology debt and performance issues. The ability to process transactions quickly and accurately is central to Coinbase’s mission to build an open financial system for the world.
We designed and built a new ledger service to be fast, accurate and serve all current and future needs across products and have undertaken our largest migration to date, moving over 1 billion rows of corporate transactions and customers and balance information from the previous data store. to our new PostgreSQL based solution, with no scheduled maintenance and no discernible impact on users.
Our key learnings:
- Make it repeatable – You may not get it right the first time.
- Do it quickly – So you can repeat quickly.
- Make it irrational – By designing the process so that it runs without disrupting normal business operations.
Here’s how we did it …
Accuracy and Accuracy: As we are dealing with funds, we knew this would be a very sensitive organization and we wanted to take every precaution, make sure every last satoshi is accounted for.
Repeatable: In addition, the shape of our data was completely different in the new versus the legacy system. Further, we had to deal with technical and craft debt accumulated over time in our monolithic application. We knew we needed to account for the possibility of not getting everything right at the same time, so we wanted to devise a repeatable process that we could repeat until it got it right.
No Maintenance Time Time: We wanted all transactions on Coinbase to take action while working on this. We did not want to do any scheduled maintenance or take any idle time for the transition.
We can deconstruct the migration to 2 separate problems: Switching live writes and reads over the new service, and moving all historical data to the new service.
For the migration, we decided to take a dual write / dual read phasing approach. Phase 1 is before the migration, where we only have the legacy system in place. In Phase 2, we introduce the new system. We dual write to the legacy and the new system the read path we read from both, then log anomalies and return the result from the legacy system. With Step 3, we have gained confidence in our new setup, so we favor it when returning results. We still have the old system around and can switch back to it if needed. Finally, we are gradually removing unused code to finish the migration (Step 4).
We will not go into detail about our dual writing operation, as the general industry blog has covered the general idea before.
What’s interesting is something happening between Step 2 and Step 3, which is backfilling all customer data into our new system so that we can achieve parity.
We considered several methods of backfilling the billion-plus rows that represent all the transactions made on Coinbase from the outset, each with its advantages and disadvantages.
The simplest solution would be to do it all at application level, leveraging the ledger client implementation we had in place for the dual writes. It has the advantage of using the same code paths we have in place for live writing – one old to new mapping would be to maintain. However, we would have had to modify the service interface to allow for backfilling and we would have had to put in place long running processes and a pointing mechanism in case of failure. We also benchmarked this solution, and found that it would be too slow to meet our requirements for a fast iteration.
Eventually, we decided to pursue an ETL-based solution. At a high level, this involved generating the backfill data from the ETL-ed source database, dumping it into S3 and uploading it directly to the Postgres target database. A key advantage of this approach is that data conversion using SQL is fast and easy. We could run the entire data production phase in ~ 20 minutes, examine the output, check internal consistency and perform data quality checks directly on the output without having to run the entire backfilling pipeline.
Our data platform provider offers a variety of connectors and drivers for programmatic access. This means we could use our standard software development tools and lifecycle – the code we wrote for the data conversion was tested, reviewed and checked into a repository.
It also has state-of-the-art data download support for S3, which made it easy for us to export the data once the appropriate resources were provided.
One to the other, AWS provides the aws_s3 mailbox extension, which allows bulk data to be imported into a database from an S3 bucket. However, direct import into live production tables was problematic, as the insertion of hundreds of millions of rows in indexed tables was slow, and also affected the lateness of live writing.
We solved this problem by creating unordered copies of the live boards, as follows:
DROP TABLE IF EXISTS table1_backfill cascade;
CREATE TABLE table1_backfill (LIKE table1 INCLUDING DEFAULTS INCLUDING STORAGE);
The import is now restricted by the I / O, which becomes a bottleneck. We slowed it down a bit by splitting the data into multiple files and adding short sleep intervals between the sequential imports.
Next up, reconstructing the indexes on the tables. Fortunately, Postgres allows for index creation without writing-lock the table, by using the CONCURRENT keyword. This allows the table to continue writing as the index is created.
So far, so good. However, the real complexity comes from our requirement to have a migration that does not involve scheduled maintenance or prevent transaction processing. We want the target database to be able to maintain live writes without missing a single one, and we want the backfilled data to seamlessly connect to the live data. This is further complicated by the fact that each transaction stores information on the cumulative balances of all the accounts involved – this makes it easy for us to evaluate and maintain the accuracy of data and look at time balances in any account at any time stamp.
We solved for this by using triggers that duplicate inserts, updates, deletions to the live tables to the backfilling tables. Our concurrent index generation allows us to write to these tables as the indexes are created.
Once the indexing was complete, in one transaction, we flipped backfilling and live tables, dropped the triggers, and dropped the now anonymous tables. Live writings continue as if nothing has happened.
At the end of this process, we run another script that goes through all the data and restores data integrity by reconstructing the cumulative balances and links between sequential transactions.
Last but not least, we are conducting another round of integrity checks and comparisons against the legacy database to ensure the data is accurate and complete.
Putting it all together, the sequence looks as follows:
- Slate clean: reset ledger database, start dual writing
- Wait for dual written data to be loaded into ETL, so that we overlap between live written data and backfill data.
- Generate backfill data, download it to S3
- Create backfilling tables, set up triggers to duplicate data into backfill tables.
- Import data from S3
- Create indexes
- Turning tables, dropping triggers, dropping old boards.
- Run a repair script
- Checking data accuracy, accuracy, completeness
The process would take 4 to 6 hours to run and was largely automated. We did this over and over again while working through data quality issues and bug fixes.
Our last migration and backfilling was not a memorable one. We had no “war room”, no backup engineers, just another run of our process and after that we decided it was time to turn on the switch. Most people in the company were not well aware. An unreasonable day.
We’ve been live with the ledger service for almost a year now. We have the ability to conduct orders of magnitude more transactions per second than with our previous system, and with tight limits on lateness. Existing and new features, such as the Coinbase Card, all rely on the ledger service for quick and accurate balances and transactions.