The key to data archiving is to choose the appropriate method to ensure that the system operation is not affected, and to ensure that the archived data can be queried. 1. Select data, usually divided into time, such as keeping data online within one year; 2. Develop an archive plan, such as using partition tables or writing scripts to migrate data to the archive library regularly; 3. Determine the storage method, and use a separate database instance or exported as CSV or Parquet files to store it in object storage; 4. Build a query mechanism, such as an API query agent or index table, to ensure that the archive data is easy to retrieve; 5. Regularly check the archive strategy and cooperate with the monitoring and alarm mechanism to ensure that the archive process continues and effectively runs.

Data archiving is ultimately about how to move old data out without affecting the system operation. The key point is to choose the right method, so that the old data will not slow down the database, and at the same time, you can check it back at any time. The strategies used in different scenarios are different, but the core ideas are just a few: picking data, setting plans, where to store, and how to check.

Archiving by time is the most common practice

The data in many systems has a life cycle, such as orders, logs, and transaction records. The older the data, the less it is. At this time, it is quite reasonable to keep the online data for the past year and move it away earlier.

In terms of operation, you can use partition tables, such as partitioning by month, and move the old partition directly to the archive library. You can also write a script that regularly deletes the data before a certain point in time from the main table and saves it to another archive database. However, before deleting, you have to confirm that the backup and archive data are safe, otherwise it will be lost.

• Use the time field as the judgment criteria, such as created_at
• Archive tasks can be performed regularly in conjunction with the Event Scheduler
• It is best to make an index after archiving, so as to facilitate subsequent queries.

Choosing the right storage method can save a lot of trouble

Archive data is not just stored, but you have to consider whether it can be checked or not in the future. The most basic approach is to store it in another database instance, such as a separate archive library, with the structure consistent, but the data is old. More cost-saving can be used to export files, such as CSV and Parquet, and stored in object storage, such as S3 and OSS.

• If you want to check occasionally, it is recommended to use compressed CSV or Parquet files, which have clear structure and can also be checked with tools.
• If the query is frequent, it is recommended to keep the database structure, add an index, or even do a front-end interface search
• If you want to save for a long time, remember to add compression and encryption to save trouble

Query archived data, don't let "cold data" turn into "dead data"

Archives are not thrown away, but "hide". So how to check it back is a place that many people are prone to overlook. If you finish archived, you will ignore it. One day the boss suddenly asked, "Is there any abnormality in the order data at this time last year?" You may have to manually flip the backup.

A more practical method is to create a query proxy layer, such as adding an API, entering the order number, user ID, or time range, which automatically determines whether the data is in the main library or the archive library, and then returns the result. This is still a system for users, and they don’t have to flip through files by themselves.

The other is to make an index table to record which data is archived, exists, and what format is, so that it can quickly locate it when checking.

Archive strategies need to be checked regularly, don't forget them after setting them

Many people designed an archive process at the beginning, and then left it alone after running. As a result, after a year, I found that the archived data was getting more and more, and even the archived library was slower; or the script went wrong and the old data was not re-entered, and the main library was still getting heavier.

It is recommended to check the archived data volume and archived execution regularly, and it is best to use monitoring and alarm. For example, if the archive task fails or the archive data grows abnormally, you can know it as soon as possible.

• Check whether the archive rules still apply to business requirements once a quarter
• Check the frequency of archived data access once a month to decide whether to adjust the storage method
• Monitor the execution time and number of failures of archive tasks

Basically that's it. Archives are not technical problems, but they are easily overlooked. The key is to plan ahead and maintain it regularly, and don’t wait until the system is slow before remembering this.

The above is the detailed content of SQL Data Archiving Strategies. For more information, please follow other related articles on the PHP Chinese website!