I separated the data in different tables to be able to better handle recovery szenarios, rebuild index or anything else.

20 Mio rows * 365 = 7,3 billion every year.

I created 3 tables for each year. (3 to represent my current partioning scheme for "normal" tables)

20 mio. rows each day are splittet about 40% 40%, 20% over the three tables. So that is 3 bil, 3bil, 1,5 bil rows for each table a year.

I will hold data for at least 5 years.

However I partioned the table within by date as well, as most of the queries do not need the hole year -> a common query is 30days from a reporting day