File Handling Scalability Improvements

[MTRichards]

Placeholder to discuss mechanisms for file handling performance improvemenents.

[DeepDiver1975]

@butonic please like your issue regarding alternative DB design for the filecache in here.

Thx

[DeepDiver1975]

Please note that a different DB design here will result in a hugh migration step!

I once more cry for proper migrations!

[MTRichards]

@karlitschek looking at this for the filecache research as an example.

[butonic]

This issue description has a few links. Other implementation alternatives are mentioned in a PR by icewind.

I do have an initial branch that returns to a simple adjacency list, dropping path_hash and path from the filecache. It does of course cause multiple queries to the table to resolve the file id for a path. however I do not consider that a big problem because SELECT can easily be scaled by caching or adding more DB slaves. Scaling updates is much harder IMO. The POC breaks sharing because it does a few joins with the filecache ... so I stopped looking into it until sharing is in a better shape ... and because, as @DeepDiver1975 correctly cried for: we need a more robust migraton.

Until then I hope we can get some more traction behind investigating the scalability of storing hierarchical data in a relational database. The best approach might be to use DB specific implementations for this. Might is the key here. We need a test scenario to measure against.

[PVince81]

@icewind1991 @nickvergessen @schiesbn in Berlin we also had a discussion about adding a "mount points" table that could significantly improve performance, was there a ticket for that ?

[RobinMcCorkell]

@PVince81 #18771

[jospoortvliet]

Related issues, I suppose: #11588
PR's related: #13956 #21519 and many more ;-)