BlocklistHash is kind of unusual in several ways. I mentioned that it identifies a block which is a blocklist, however its BlocksetID refers to an ID in Blockset table, so perhaps it’s the parent rather than the child.
ChangeJournalData is part of the USN change journal scheme that I don’t know well, so no comment…
Database and destination internals has background and links to some more. Nice job so far without that, although I suppose this series of statements might be the most concise place to try to infer relationships.
Documentation for the local database format also exists, but the relationship arrows don’t show direction.
I have a draft of an attempt to write that all up as text tables, but it got big, so I just did above internals.
My SQL is not nearly as good as yours, but I would change “either” to “any” for the needed requirement.
I’m looking at only the top part of the SQL (above the OR) now, and thinking it does a great job of finding blocks that the fileset expands into, but I’m not sure I’m seeing where it looks at block usage from others.
Maybe I’m missing something in the remaining textual explanation, but I thought I’d give initial comment.
EDIT:
To give an example of total backup deduplication, set option upload-unchanged-backups to make a new fileset with the same content as the current last (if no file changes). Delete one. What happens to other?