Data archiving can be a challenge with any database system. Particularly, transaction data archiving presents significant problems for many database systems in payment industry.
Companies with high transactional throughput have to store enormous data volumes, because they accumulate transactional data very fast. Transactional data has to be stored and easily accessed. Access to data must be ensured in order to make it possible to compile historical reports and get back to transactions which resulted in refunds, chargebacks, and ACH returns.
Transaction data archiving problems concern several aspects of database maintenance. For example, database size may become very large and occupy all the space on the hard drive, large data volume may lead to decrease in the overall database performance, long indexes may slow down the search. Finally, the larger the table, the longer it takes to introduce any changes into it.
In order to solve the above mentioned problems, several approaches are utilized. The most common one is data partitioning according to time intervals (on weekly or monthly basis). Beside that, in order to optimize transaction data archiving and record search process in large-size databases, two tables are maintained, each intended for some particular operations: authorization table and archiving table.
Read more about transaction data archiving techniques in the respective article on Paylosophy.