Impressive work and great writeup! Unfortunately, someone better at SQL than I am should take a look. Possibly I can add some Duplicati-specific thoughts (eventually) because I know some better than SQL.
@tarianjed is raising performance too, getting a change in master and 2.0.6.101_canary_2022-03-13.
Improved database query performance, thanks @jedthe3rd
Identified another slow query during backup
DB Query Performance Testing, Fixes, and Maintainability
I feel very fortunate that Duplicati now has two great people doing SQL, and I wonder if you could work together not just on performance, but on integrity, maintainability, etc. Maybe also review pull requests?
Current staff is very depleted (from attrition) of people who know their way around SQL. It’s unfortunate. Automated unit tests run when a PR goes in, but they don’t catch everything. Expert review is important.
There are also, frankly, still some backup integrity issues that people hit (though it used to be far worse). Because SQL is so central to Duplicati, SQL expertise is important to trying to either figure out what had happened (difficult, especially if it’s reported externally), improve defensive C# or SQL code, and so on.