Geo: Make Registry tables SSOT so we can make backfilling simple
I think that joining over foreign tables, apart from the performance, also adds quite a lot of complexity to the system. As brilliant as those queries are, I'm worried about the long term maintainability.
We can start with a quick POC, including figuring out how
selective_sync is affected.
For this iteration only modify one data type, for example LFS objects.
find_unsyncedqueries to find never-synced registry records (instead of doing a cross-database join)
- Ensure other scopes/counts are not broken with this change
Add backfill worker to iterate over foreign table and insert missing registry records
- Since we are iterating by ID range, this probably could use a cross-database join, either FDW or legacy. Or we could select a batch from both databases and compare it in Ruby since it's not too much data.
- Handle selective sync changes properly
From discussion in gitlab-org/geo-team/discussions#45 (comment 228955133):
if we assume our event system is mostly reliable, we can use the tracking database as our SSOT and make our backfill workers only responsible for creating the missing registry records.
So instead of doing an unbounded cross-database join, we can take a less responsive approach:
iterates over the source foreign table and creates missing registry records
During initial backfill, this approach is fast enough since it can easily be implemented to be faster than sync workers can pick up and sync the new records.
After initial backfill, we rely on the event system for performant syncing. This system's role changes to picking up after edge cases (e.g. lost jobs). It doesn't need to be fast-- it doesn't need to evaluate the entire table at once (current world).
Note that I labeled this performance since this avoids large queries, not because it improves the responsiveness of syncing.
Summary of benefits
- Eliminate long selective sync queries
- Reduce complexity
- Eliminate FDW setup
- Eliminate FDW maintenance (i.e. refresh foreign tables)