Draft: fix(indexer): read the newest row version in full-load extracts

Michael Angelo Riverarequested to merge
michaelangeloio/orbit-engine-ontology-prereqs into main

What does this MR do and why?

When we bump the schema, Orbit rebuilds the graph from the data lake. That full load could bring deleted rows back to life. For example, a user who left a group showed up as a member again. This MR makes the full load read only the newest version of each row, so a deleted row stays deleted.

How it works

The lake keeps every version of a row until ClickHouse merges them in the background. A plain read returns all versions, so the full load could pick an old, live version of a row that was later deleted.

The fix is FINAL, which tells ClickHouse to return only the newest version. Each extract template marks its main table with {{final}} (example). A full load turns the marker into FINAL. An incremental run leaves it empty, because it only reads recent changes.

FINAL can be expensive, so we keep it cheap:

  • It runs on one thread (settings), which keeps memory low.
  • Project and Group are sorted by id, not by namespace, so FINAL on the whole table is slow. Those two templates first find the newest version of each id, then read the full rows (group template).
Orbit question Before After
Who is a member of the group? includes the user who left correct

This MR also caps the test ClickHouse container log at two 50 MB files, because the log filled a dev VM disk.

Related Issues

Related to #1379. This is the base of the ontology stack.

Testing

  • A scenario puts a live row and its delete marker in separate parts. It fails on main and passes here.
  • On the e2e rig, a removed membership came back with the old build and stays gone with this one.
  • The MR pipeline is green.

Performance Analysis

Full loads now use less memory and CPU than before, measured on production with the largest group. Project and Group pages take 2 to 3 times longer in full mode, at about the same CPU on the lake and less memory. Full numbers are in the measurements snippet.

Full-load page Before After
Note, 100,000 rows 1.93 GiB, up to 7.6 s 517 MiB, up to 1.0 s
Job, 50,000 rows killed at the 4 GB cap 1.83 GiB, up to 2.3 s
  • This merge request does not introduce any performance regression.
Agent context: alternatives, file walk

Alternatives considered, the file walk, the thread-count probes, and the kept comment are in engine-stack-notes.md, full-load-final-vs-plain.md, and final-low-impact.md of the measurements snippet.

Edited by Michael Angelo Rivera

Merge request reports