November 27, 2021 - 1 minutes read

How to Work With Historical Data: Use Separate Tables for Active and Historical Data

Dominika is a Python software developer. She graduated from the Warsaw University of Technology with a degree in computer science. At Vertabelo, she creates content as a technical writer. In her free time, she runs, practices yoga, and learns foreign languages.

What do you do if you need to keep both current and historical versions of your database records? If there are a lot of them, storing everything in one table may cause performance problems and complicate logic, especially if most of the operations are performed on the active data.

You can solve this problem by storing the current and historical versions in separate tables. In our example, the current and historical account plans were put in two different tables: current_account_plan and account_plan_history.

With such a design, you can query just one of the tables and reduce the number of records that need to be scanned.