Visitar URL original
Admin tool: purge old metrics reports from the relational JDBC metrics tables by sririshindra · Pull Request #6014 · apache/polaris · GitHub
Skip to content

Admin tool: purge old metrics reports from the relational JDBC metrics tables - #6014

Draft
sririshindra wants to merge 1 commit into
apache:mainfrom
sririshindra:metrics-reports-purge
Draft

sririshindra wants to merge 1 commit into
apache:mainfrom
sririshindra:metrics-reports-purge

Conversation

@sririshindra

Copy link
Copy Markdown
Contributor

With the relational JDBC persistence, the persisting Iceberg metrics reporter writes every scan and commit report to the scan_metrics_report and commit_metrics_report tables. Nothing in Polaris deletes these rows, so the tables grow without bound. This is the same problem as the events table, which #6013 addresses.

This adds a purge-metrics admin tool command that deletes the scan and commit metrics reports of the given realms that are older than a given age:

polaris-admin-tool purge-metrics -r realm1 --older-than P30D [--batch-size 1000]

Operators run it periodically (for example from a cron job) to enforce a retention period. It has the same shape and options as purge-events in #6013.

How it works:

  • Reports are deleted in batches, each in its own transaction, with DELETE ... WHERE realm_id = ? AND report_id IN (SELECT report_id ... WHERE realm_id = ? AND timestamp_ms < ? LIMIT ?). The subquery form is used because PostgreSQL has no DELETE ... LIMIT. It works on PostgreSQL, CockroachDB and H2. The loop stops when a batch deletes fewer rows than the batch size, so an interrupted run can be repeated. Both tables have the primary key (realm_id, report_id) and every delete is scoped to one realm.
  • No schema change. Both tables already have a (realm_id, timestamp_ms) index (idx_scan_report_timestamp, idx_commit_report_timestamp) in every schema version that has them (v4 and later), so each batch is an index range scan.
  • The delete logic is a JDBC-only bean (JdbcMetricsReportsPurger) in polaris-relational-jdbc, which owns the DDL of these tables. It lives there rather than in the metrics reports extension, so the admin tool does not depend on the extension (and through it on polaris-runtime-service). The admin tool now has a compile dependency on polaris-relational-jdbc (it was runtimeOnly). The table names are repeated in the purger rather than taken from the extension's Model*MetricsReport classes, for the same reason.
  • One run purges both tables with the same cutoff. A per-table option can be added later if someone needs different retention for scan and commit reports.
  • With another persistence type the command exits with a usage error. If the metrics reports extension is not in use, the tables (created by the core schema) are empty and the command reports 0.

Relation to other PRs:

  • Admin tool: purge old events from the relational JDBC events table #6013 (events purge) is the sibling of this PR. The two are independent and either can merge first. The only overlap is the runtimeOnly → implementation change in runtime/admin/build.gradle.kts and adjacent lines in PolarisAdminTool, admin-tool.md and CHANGELOG.md, which I'll rebase once one merges. If reviewers prefer a single generic command (for example purge-time-series --kind events|scan-metrics|commit-metrics) over one command per table, I'd reshape both PRs together. Once both are in, the batching loop shared by the two purgers could be factored out.
  • Drain JDBC realm time-series data via a batched purge command #5089 deletes time-series data when a whole realm is purged. This PR deletes by age and doesn't depend on it.

Things I'd like feedback on:

  1. One command per kind of data (purge-events, purge-metrics) vs. one generic command.
  2. Whether purging both report tables together is the right default.

Related to #2573

Checklist

  • 🛡️ Don't disclose security issues! (contact security@apache.org)
  • 🔗 Clearly explained why the changes are needed, or linked related issues: Related to Provide a mechanism to purge the events table #2573
  • 🧪 Added/updated tests with good coverage, or manually tested (and explained how)
  • 💡 Added comments for complex logic
  • 🧾 Updated CHANGELOG.md (if needed)
  • 📚 Updated documentation in site/content/in-dev/unreleased (if needed)

Tests: JdbcMetricsReportsPurgerTest (H2, parameterized over batch sizes, both tables, realm and table isolation, cutoff boundary) and admin tool Quarkus tests on PostgreSQL and CockroachDB (purge-metrics deletes exactly the old reports of each realm from both tables; invalid duration), plus NoSQL in-memory (unsupported persistence).

…s tables

The scan_metrics_report and commit_metrics_report tables, written by the
persisting Iceberg metrics reporter, grow without bound. Add a purge-metrics
admin tool command that deletes the reports of the given realms older than a
given age from both tables, in batches of one transaction each.

The delete logic is a JDBC-only bean in polaris-relational-jdbc, which owns
the DDL of these tables. No schema change is needed: both tables already have
a (realm_id, timestamp_ms) index.
@dimas-b

dimas-b commented Nov 12, 2026

Copy link
Copy Markdown
Contributor

I agree that the use case in this PR is different from #5089 and it makes sense to support.

Still, for ease of review, let's merge #5089 first and then proceed with this and #6013.

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants