Large-scale migration of records in the database: how it does Stripe

Translator's note : We at Latera are engaged in creating billing for telecom operators . We will write about the features of the system and the details of its development in our blog on Habré, but you can learn something interesting from the experience of other companies. Today we bring to your attention an adapted translation of a note by an engineer of financial startup Stripe about how his team migrated a huge number of records in the database.



Stripe for receiving payment uses a huge number of sellers, and recently the project team completed a project called “A very large migration of large amounts of data between several databases without loss, work stoppages and errors in the system responsible for the daily transfer of a huge amount of finance”.
')
As Stripe engineer Robert Heaton described the project: “Conceptually everything is simple here, but the devil (and the ability to sleep at night) is in the details.”

0. Principle

In Stripe, there is a table of Merchants and AccountApplication account applications. Each merchant has a AccountApplicaion, and previously these tables contained all the information about the seller, including such trivial data as email_font_color and self_estimated_yearly_turnover (annual turnover by own estimation) and much more important (required by law so-called know your customer, KYC) business_name and tax_id_number.

To launch the Stripe Connect project, it was necessary to create a system that tells Connect Applications what important information is required for each of the connected vendors. Requirements may vary by country, type of business and other factors. To make the system simple and convenient, it was necessary to extract all the KYC data and put them into one LegalEntity table.

As Khaton says: “If we could stop our system for a short time, and if we were robots who never make a hint of a mistake, then we would simply tell the sellers not to sell anything for a while, transferred all the data, and then launched the system. " However, in reality it was impossible to do so, of course.

During the migration of a huge amount of data, the system will continue to be used by a whole bunch of vendors who need to write and read new information. In such a situation, it is very easy to make a mistake, which will lead to the reading of old information and the failure to record a new one.

In addition, such migration is a large-scale project that cannot be done in one fell swoop and then pray that everything will work. Instead, it was decided to move in small steps and track all changes in the system.

Simplified, the migration scheme looks like a change in the data reading scheme with this:



on this:



And data records with this:



on this:



All this happened in four stages.

1. Data Migration

It all started with creating a LegalEntity model in ORM Stripe and a related table in the database. After creation, it did not contain any data on which no action was taken.

class LegalEntity end 

Then it was necessary to duplicate the records of the corresponding Merchant and AccountApplication entities into the equivalent of LegalEntity. That is, when writing occurred in Merchant#owner_first_name , then the data were simultaneously recorded in LegalEntity#first_name . At this point, the old data in LegalEntity has not yet been migrated, so the Merchant and AccountApplication tables remained the “source of truth”.

Here is the code used here:

 class Merchant # Each Merchant has a LegalEntity prop :legal_entity, foreign: LegalEntity def self.legal_entity_proxy(merchant_prop_name, legal_entity_prop_name) # Redefine the Merchant setter method to also write to the LegalEntity merchant_prop_name_set = :"#{merchant_prop_name}=" original_merchant_prop_name_set = :"original_#{merchant_prop_name_set}" alias_method original_merchant_prop_name_set, merchant_prop_name_set if method_defined?(merchant_prop_name_set) define_method(merchant_prop_name_set) do |val| self.public_send(original_merchant_prop_name_set, val) self.legal_entity.public_send(:"#{legal_entity_prop_name}=", val) end end legal_entity_proxy :owner_first_name, :first_name before_save do # Make sure that we actually save our LegalEntity double-write. # This "multi-save" can cause confusion and unnecessary database calls, # but is a necessary evil and will be unwound later self.legal_entity.save end end merchant.owner_first_name = 'Barry' merchant.save merchant.legal_entity.first_name # => Also 'Barry' 

All this was launched in production for a couple of days to see possible errors. As a result, the threads were updated as follows. Reading:



Record:



Then, a passage through all the records of Merchant and AccountApplication was performed with the subsequent migration of the necessary data to LegalEntity. Duplication of the record allows you to ensure that during the migration process, even data that is added to the initial tables after it has been transferred is transferred.

2. Start reading from LegalEntity

Thus, the synchronization of the LegalEntity table with the Merchant and AccountApplication tables was guaranteed. Then Stripe engineers redirected all eg calls. merchant.owner_first_name to read data from the new LegalEntity table. Data continued to be recorded in the two original tables. Proxy was implemented using a special flag that was set in the system interface. When problems were detected, you could switch to reading from the Merchant table to find the error.

 class Merchant prop :legal_entity, foreign: LegalEntity def self.legal_entity_proxy(merchant_prop_name, legal_entity_prop_name) # # UPDATED: Now we also redefine the Merchant getter method to read from the LegalEntity # alias_method :"original_#{merchant_prop_name}", merchant_prop_name if method_defined?(merchant_prop_name) define_method(merchant_prop_name) do self.legal_entity.public_send(legal_entity_prop_name) end # We continue to write to both tables for safety merchant_prop_name_set = :"#{merchant_prop_name}=" original_merchant_prop_name_set = :"original_#{merchant_prop_name_set}" alias_method original_merchant_prop_name_set, merchant_prop_name_set if method_defined?(merchant_prop_name_set) define_method(merchant_prop_name_set) do |val| self.public_send(original_merchant_prop_name_set, val) self.legal_entity.public_send(:"#{legal_entity_prop_name}=", val) end end legal_entity_proxy :owner_first_name, :first_name before_save do self.legal_entity.save end end merchant.owner_first_name # => calls legal_entity.first_name, which should be the same as Merchant#owner_first_name anyway 

Now the streams have undergone regular changes. Reading:



Record:



After the testing of the new code was completed successfully, it is time to disable the data recording in both the old tables and the new LegalEntity. To do this, in the old tables the corresponding fields and columns were deleted, which stopped writing to them. Now merchant.owner_first_name written and read only to and from the LegalEntity table, and there is no longer an owner_first_name entity in the Merchant table.

Now the reading process looks like this:



A record - so:



Data migration is complete, but optimization remains. When saving objects, numerous requests to the database are still performed, and the whole process depends on several pieces of code and a proxy created on the knee. The code definitely needed to be cleaned up.

3. Reading and writing directly to the LegalEntity table

For each Merchant and AccountApplication entity that is proxied to LegalEntity, grep selects the code for reading and writing and replaces it with one that indicates direct operation with LegalEntity. For example, the code:

merchant.owner_first_name = 'Barry'


would look like this:

legal_entity.first_name = 'Barry'


As a result, data streams once again change. Reading:



Record:



It may also be that someone adds calls to the fields that engineers are trying to remove. There is nothing wrong with that, since these calls will be proxied to the corresponding LegalEntity entities.

However, as a result, proxying will be disabled, and at this point you need to log all obsolete fields and add code that makes requests to these fields not work, but no errors are issued:

 class Merchant prop :legal_entity, foreign: LegalEntity def self.legal_entity_proxy(merchant_prop_name, legal_entity_prop_name) alias_method :"original_#{merchant_prop_name}", merchant_prop_name if method_defined?(merchant_prop_name) define_method(merchant_prop_name) do # # UPDATED: We add in logging # log.info('Deprecated method called') self.legal_entity.public_send(legal_entity_prop_name) end merchant_prop_name_set = :"#{merchant_prop_name}=" original_merchant_prop_name_set = :"original_#{merchant_prop_name_set}" alias_method original_merchant_prop_name_set, merchant_prop_name_set if method_defined?(merchant_prop_name_set) define_method(merchant_prop_name_set) do |val| # # UPDATED: We add in logging # log.info('Deprecated method called') self.legal_entity.public_send(:"#{legal_entity_prop_name}=", val) end end end 

When requests to obsolete fields in the database cease to appear in the logs (according to the plan, this should take from 2-7 days), proxying is permanently deleted, since it is no longer necessary.

 class Merchant prop :legal_entity, foreign: LegalEntity # REMOVED # # def self.legal_entity_proxy(merchant_prop_name, legal_entity_prop_name) # # etc # end # # legal_entity_proxy :owner_first_name, :first_name before_save do self.legal_entity.save end end 

4. Disable multisaving

All data is now read and written directly to LegalEntity. However, the saving process is still duplicated - Merchant still saves LegalEntity data. You often see such lines:

 legal_entity.first_name = 'Barry' merchant.save 

In principle, it works, but not as beautiful as it could be. To remove confusing elements, but at the same time save everything you need, you can write to the logs all the places where merchant.save (or similar things) somehow affect the change of fields in legal_entity, and change the before_save sections as follows:

 class Merchant prop :legal_entity, foreign: LegalEntity before_save do # Our ORM's implementation of "dirty" fields unless self.legal_entity.updated_fields.empty? self.legal_entity.save log.info('Multi-saved an updated model') end end end 

It was also decided to implement a flag to enable the multistore option. If necessary (if someone is too nervous), it can always be easily activated.

Over the next few days, the Stripe team studied the logs for the presence of the phrase 'Multi-saved an updated model' in order to find out all the places where saving Merchant and AccountApplicaion also affects saving New Data to LegalEntity. This table is saved before changes are saved in another model, which leads to setting an empty value in the field legal_entity.updated_fields - in such a situation nothing will appear in the logs. And here is the code:

 legal_entity.first_name = 'Barry' merchant.business_url = 'http://foobar.com' merchant.save 

activates logging, as merchant.save will also save the new LegalEntity#owner_name . It needs to be changed to:

 legal_entity.first_name = 'Barry' legal_entity.save merchant.business_url = 'http://foobar.com' merchant.save 

Here legal_entity save itself previously.

After the logs were empty and all such flaws were cleared, the multi-save was removed. Now all the data is where it is supposed to be, there are no crutches in the form of proxying queries and no meta-programming is used.

 class Merchant prop :legal_entity, foreign: LegalEntity end 

5. Conclusion

Throughout the migration, the Stripe team did not make a single change without subsequent empirical verification of the fact that it did not cause errors in the operation of the system. All updates were carried out gradually, step by step, which made it possible to minimize the risk of serious damage to the system. The approach with the use of duplicate records in the old and new source, with the subsequent gradual shutdown of the first - is a safe way to make such transfers.

As Stripe engineer Robert Heaton says:

If you ever want to write one huge pull request to migrate a huge amount of anything, think twice about whether there is any possibility to make the whole process not so terribly dependent on luck. If the answer is “no”, before starting the process, you should take care of false documents and credit history, which will help you to leave the country (and most likely you will have to do it soon) and start a new life in Brazil.