Thanks, Ayush. I agree that events and metrics can grow large enough to justify deleting them in batches. Could we run those batches in the existing purge command and avoid adding a realm_purge table and a separate scheduled job?
If the operator can take the realm offline and let pending writes and buffered events finish, purge could delete events and metrics in batches, commit each batch, then remove the realm metadata last. Keeping the root principal until the end means that if the command is interrupted, the operator can rerun it to delete the remaining rows. The tradeoff is that the operator waits for cleanup to finish. Is there a use case where purge needs to return sooner? If the main concern is the size of a single transaction, I’d prefer to start with batching in the existing command. Yufei On Thu, Nov 12, 2026 at 6:53 PM Ayush Saxena <[email protected]> wrote: > Hi Yufei, > > Not the batching — that needs no schema. The schema change is the queue > itself. > > Purging a realm deletes its entities, so once the purge commits nothing > remains that says the realm existed, while its events and metrics reports > stay keyed by a realm id that no longer resolves. The new table records > that > intent in the same transaction as the purge, so the data is never left with > nothing pointing at it. It also carries the purge request time (so a later > bootstrap of the same name is not drained) and the claim fields (one > drainer > per realm across nodes). None of that is derivable after the fact. > > An anti-join against entities can approximate the orphans — the migration > uses that for realms purged before the upgrade — but it scans the largest > tables on every run, and it cannot tell a purged realm from one still being > bootstrapped. > > The no-schema option is deleting inline in the purge transaction: one > unbounded DELETE over tables that can dwarf the metadata, which is what > makes a purge time out or hold locks. So the deletion is asynchronous and > batched instead, and that needs the queue table. > > -Ayush > > On Fri, 13 Nov 2026 at 03:57, Yufei Gu <[email protected]> wrote: > > > > Ayush, thanks for raising this. It's critical to upgrade the schema > > version for any schema change. Why does this PR require a schema > > change? Is it due to the batch deletion? > > Yufei > > > > On Wed, Nov 11, 2026 at 11:04 PM Ayush Saxena <[email protected]> > wrote: > > > > > > Hi JB, > > > > > > Thanks for the review. I have incorporated the two documentation > points. > > > > > > Two-step migration: the table is a pure addition that no earlier > > > version reads, so the docs now create it while the previous version is > > > still serving and record the version last, leaving the restart as the > > > only window where no node can serve. > > > > > > Realms purged before the upgrade: the migration section now has an > > > optional one-off statement that queues every realm with time-series > > > rows but no entities, and states what skipping it leaves behind. It > > > needs to run while no realm is being bootstrapped, since a realm is > > > indistinguishable from a purged one until its first entity is written. > > > > > > Single v7: sure. This PR introduces v7 today, and I will keep an eye > > > on #6013 and #4756 — if either gets merged earlier I will update this > > > one to add to the v7 already there. One thing to flag for that > > > section: #4756 does not bump the version at all right now, and CREATE > > > INDEX IF NOT EXISTS keeps the existing four-column index, so its new > > > definition needs a DROP before the CREATE. > > > > > > Naming and shared delete logic: agreed, before both merge. The loop > > > here is already generic over the table and its key column and takes a > > > cutoff, so an age-based command needs only a different cutoff, not new > > > deletion logic. Happy to extract it, or to adopt whatever merges > > > first. On naming, grouping the three under one backend-specific > > > parent, as the NoSQL commands are, reads better to me than three > > > top-level commands that work on one backend only. > > > > > > -Ayush > > > > > > On Thu, 12 Nov 2026 at 12:08, Jean-Baptiste Onofré <[email protected]> > wrote: > > > > > > > > Hi Ayush, > > > > > > > > I agree that a required migration should not be a surprise in the > release > > > > note (it something we discussed while ago when we "moved" from > EclipseLink > > > > to JDBC afair). > > > > > > > > I'm +1 with the approach. > > > > > > > > I have a few comments though: > > > > - We have two PRs touching the schema. #6013 bumps the schema to v7, > to add > > > > an index on events(realm_id, timestamp_ms), while #4756 changes the > > > > definition of idx_scan_report_lookup and idx_commit_report_lookup. As > > > > CREATE INDEX IF NOT EXISTS keeps the existing index, existing > deployment > > > > won't get the new definition without a migration (same situation as > > > > idx_locations in v6). As 1.9.0 is already on vote, it means these > changes > > > > will land in 1.10.0. I would prefer a single schema v7 for 1.10.0, > with one > > > > migration section in the docs, and one note in the CHANGELOG, rather > than > > > > v7 and v8 in the same release. Whichever PR is merged first, > introducing > > > > v7, the others add their DDL to the same v7 bump. > > > > - Both PRs add an admin tool command deleting events in batches > (purge > > > > events by age, purge/drain for realms). It would be good to align the > > > > command naming and share the batched delete logic before both are > merged. > > > > - The schema version check is a strict equality: a 1.9.0 node fails > against > > > > v7 schema, and a 1.10.0 node fails against v6. So, we must no mix > versions > > > > during the upgrade. As realm_purge is pure addition (1.9.0 never > touches > > > > it), the docs could split the migration in two steps: create the > table > > > > while still running 1.9.0 (no impact), then bump the version. That > keeps > > > > the downtime window to the version update and restart. > > > > - The time series data of realms purged before the upgrade is not in > > > > realm_purge, so purge/drain will never clean it. Maybe we can either > state > > > > it explicitly in the docs, or provide an optional one-off SQL in the > v7 > > > > migration section enqueueing the realm ids present in events and the > > > > metrics report tables that no longer exist in entities. > > > > > > > > Regards > > > > JB > > > > > > > > On Thu, Nov 12, 2026 at 7:19 AM Ayush Saxena <[email protected]> > wrote: > > > > > > > > > Hi all, > > > > > > > > > > PR #5089 adds a table to the relational JDBC schema, so it needs a > > > > > schema version bump and a migration that operators must apply > before > > > > > starting the release that carries it. Raising it here for > awareness, > > > > > since a required migration should not be something people meet for > the > > > > > first time in a release note. > > > > > > > > > > The table records realms whose time-series data is still pending > > > > > deletion. Purging a realm today leaves its events and metrics > reports > > > > > behind, keyed by a realm id that no longer exists, so nothing > reclaims > > > > > them afterwards. A new admin tool command drains the queue in > bounded > > > > > batches, rather than deleting that data inline in the purge > > > > > transaction, which is the statement most likely to make a purge > time > > > > > out on a realm with a long history. > > > > > > > > > > The migration SQL and the rest of the detail are here: > > > > > https://github.com/apache/polaris/pull/5089 > > > > > > > > > > Let me know in case anyone has any objections, feedback or better > ideas. > > > > > > > > > > -Ayush > > > > > >
