Archiving a database and tables / fields based on time intervals

Question

I am working on a web application for employees.

Lead / Manager sets goals for team members after discussion with them. This is the annual / semi-annual / quarterly period, depending on the assessment cycle that the organization follows.

Now the question is a more efficient approach to adding fields based on time periods or in the data archive of the previous quarter / year. When the user wants to see previous goals (not so frequent), the archive belonging to this date can be restored in some temporary table and shown to the employee.

Points to start with

archiving: reduces db size, leads to simpler db requests, adds overhead when someone tried to see old data.

Field / tables based on a time period: one or more additional joins in queries, previous data is processed similarly to current data, so there is no overhead for retrieving old data.

PS: this is not a space cost, I want to say that we can achieve some optimization in terms of performance, since this is a web application, and at peak times all employees in the organization will search / update it. so deleting a time period makes my queries a lot easier. Thanks

Answer 1

, , , , , "" () . , . .

, , , . , , ( , ).

, .

Answer 2

, . , , , .

, , , .

Answer 3

, , , .

:

, , .

My recommendation would be to periodically (read when absolutely necessary) move the "very old" data to another table for this purpose. Disk space is currently extremely cheap, so storing data around is not as expensive as introducing a system that can return at any time and restore the archive.