Categories-sub categories to go over 5000-10000

[HypreX]

Hi all,

I am new here, been recommended to use Prestshop by our developer. Been using Magento and Wordpress before, but PS is lighting fast compare to those

We building our new webshop on PS, there has been few modifications done to meet our requirements and we load up first batch of products (around 20.000 going to about 200.000-500.000 within next few weeks/months). All is loaded at the moment to home category, we got tree of categories done - but is hard to work with it as is too general for this what we sell. 

In ideal scenario our webshop will need nearly 40.000 end categories (20 Main, 400 - Sub categories, 40.000 end categories will be spread out between "20-400")

We use dedicated server, Intel E3-1270 (4C+4HT) 32GB Ram, 2x800GB SSD Raid 1

So short term is we will have 200.000 products loaded - they would use about 5-10k categories (from pool of 40.000 all possible categories) and I am trying to work out what would be the best, that's 3 options what i can do:

1. Load up all 40.000 categories (and disable/hide all) - assign products to them and then activate/enable those which have a products inside

2. Load up only those which will have products inside, When we load up new products we can check if they have their categories and if not upload new categories

3. Compress list of 40.000 to about let say 1.000 - 5.000 (which will create "wider" categories) but then when someone will browse it - it will have 1k products displayed instead of 200 for example.

3a. Compress them and then do what in option 2. 

For managing categories, quickness of data loading etc option 1 is the best. But my concern is about the speed of the webshop, database (hosted on same server - or we should get 2nd server for database only?)

I read a little and about this topic and people dont recommend have few k's products in one category, But breaking that 1 category into 4-10 will create massive category tree and i am not sure if Prestashop/server would be able to cope with it and be still effective/fast, 

From Data processing perspective: 
Option 1 & 2 is best choice, for short and long term. Source file for products loads have already assigned 1 of 40.000 categories where it needs to seat. 
Option 3 & 4 - it will be much more time consuming, harder to keep up with changes etc. 

Anyone was dealing with such wide category structure and products number? Would be great to hear some feedback and some tips what would be better at the website performance angle.

 

Thanks in advance!