Purge expired Neo4j sessions in approved bounded batches
Remove eligible expired Neo4j session and event nodes while preserving labels and relationships outside the retention policy.
Detect Vertica projections whose delete vectors pass a row threshold, alert Slack, advance the AHM and run PURGE_TABLE to reclaim scan time.
Vertica stores every UPDATE and DELETE as a delete vector, a marker that hides the old rows without rewriting the data. The marker sits on disk next to the projection and is read on every scan until it is purged, so a table that takes a steady stream of deletes keeps getting slower while its visible row count stays flat. The space those rows hold is not given back either.
This guard reads v_monitor.delete_vectors, sums the reclaimable rows per projection and flags the projections over a threshold you set. It alerts Slack. When you let it, it advances the Ancient History Mark with MAKE_AHM_NOW, runs PURGE_TABLE on each flagged table one at a time, reads v_monitor.delete_vectors again then fails the run if any projection is still over the threshold.
This blueprint was created by zkasuran.
| Finding | Meaning |
|---|---|
| Delete vectors over threshold | A projection carries delete vectors covering more rows than reclaimable_rows_threshold. Every scan reads them, so the projection slows down and holds disk until it is purged. |
PURGE only reclaims delete vectors older than the Ancient History Mark (AHM), so the remediation advances the AHM with MAKE_AHM_NOW before it purges. Otherwise the purge can free nothing while the AHM lags. MAKE_AHM_NOW is database wide and drops history before now, so AT EPOCH queries cannot reach past it. The guard stays read-only until auto_remediate is true and dry_run is false. The purge loop runs one table at a time. The re-check reads the system table a second time so a purge that did not land fails the run.
audit (io.kestra.plugin.jdbc.vertica.Query, read-only) reads v_monitor.delete_vectors, joins each projection to its anchor table through v_catalog.projections and v_catalog.tables, sums the delete-vector rows per projection and returns the ones over reclaimable_rows_threshold with a PURGE_TABLE statement for each anchor table.purges keeps the distinct PURGE_TABLE statements, since one table can carry several projections. summary logs the flagged projections.route (io.kestra.plugin.core.flow.If) branches on whether anything was flagged.alert posts to Slack. remediate_gate runs only when auto_remediate is true and dry_run is false.advance_ahm runs SELECT MAKE_AHM_NOW() so the delete vectors become purgeable.remediate (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs each PURGE_TABLE in turn.reaudit and verify_result fail the run if any projection is still over the threshold.errors posts a Slack alert when any task fails.PURGE_TABLE to run.auto_remediate true and dry_run false.v_monitor.delete_vectors as proof, so a purge that did not reclaim fails the run.Vertica purges delete vectors on its own only as the Tuple Mover ages them past the AHM. Running the purge on demand needs the AHM advance, the purge and the re-check wired together in the right order, with an alert when a table is behind and a hard stop when a purge does not land. Kestra runs that on a schedule, keeps the write behind two inputs, purges one table at a time, verifies the result and keeps a history of every run.
On Kestra 2.0.5 OSS against Vertica Community Edition 9.2 (database docker, schema analytics), with the shared HTTP sink standing in for Slack. A table analytics.events was loaded with 5000 rows through COPY ... DIRECT, then rows were deleted and moved out to create delete vectors. v_monitor.delete_vectors was read directly before and after each run to confirm the result.
| Run | Inputs | Result |
|---|---|---|
| 1 | clean table, threshold 100 | SUCCESS (63soIhBwuFhnCo3zfoHJMr). audit returned 0 projections. v_monitor.delete_vectors stayed at 0 rows. Clean path logged |
| 2 | degraded table, threshold 500 | SUCCESS (2B31SQQJ9g4ddGb84KZgtT). 1 projection flagged (analytics.events, 1334 rows). Slack alert sent (HTTP 200). delete_vectors unchanged at 1334 |
| 3 | auto_remediate: true, dry_run: true |
SUCCESS (5V8lXJzaQbtFlzgU5nfMgk). Same report. dry_run held the purge back, so delete_vectors stayed at 1334 |
| 4 | auto_remediate: true, dry_run: false |
SUCCESS (3txYU1SDyoIrJQJ6LxXL67). MAKE_AHM_NOW then PURGE_TABLE('analytics.events'). The re-check came back clean. delete_vectors went 1334 to 0, confirmed in vsql |
| 5 | unreachable database | FAILED (1cgnbNKhtdhVnQQ6yFnoF7). audit could not connect, so the errors block posted the failure alert to Slack (HTTP 200) |
jdbc_url.v_monitor.delete_vectors, v_catalog.projections and v_catalog.tables. For remediation, a user that owns the flagged tables or is a superuser, since PURGE_TABLE and MAKE_AHM_NOW need that.docker run -d --name vertica -p 5433:5433 jbfavre/vertica:latest brings up a single node with a passwordless dbadmin and a database named docker.VERTICA_PASSWORD: password for the Vertica user. The password input defaults to it.SLACK_WEBHOOK_URL: used by alert and the errors block.SECRET_.| Input | Default | Purpose |
|---|---|---|
jdbc_url |
jdbc:vertica://vertica:5433/VMart |
Vertica cluster to check |
username |
dbadmin |
Vertica user |
password |
{{ secret('VERTICA_PASSWORD') }} |
Password for that user |
reclaimable_rows_threshold |
100000 |
Flag a projection over this many delete-vector rows |
auto_remediate |
false |
Allow MAKE_AHM_NOW and PURGE_TABLE |
dry_run |
true |
Must be false for a purge to run |
VERTICA_PASSWORD and SLACK_WEBHOOK_URL secrets.jdbc_url, username and reclaimable_rows_threshold.auto_remediate: true and dry_run: false.daily trigger, which always runs in safe mode.reclaimable_rows_threshold on tables where scan latency matters more than purge cost.PURGE_TABLE for PURGE_PROJECTION to reclaim one projection at a time on very large tables.