Row-level retention

This page explains how to configure row-level retention, which removes selected rows from aging HDB partitions instead of deleting whole partitions.

Partition-level retention (hdbRetPrtns) and table-level retention (the partitions property in the schema file) both work at a coarse granularity: once a partition passes its retention period, the whole partition, or the whole table within it, is deleted. Row-level retention works inside that boundary. You give a table a where clause and an age in days, and DBM removes the rows the clause doesn't select once a partition reaches that age. Everything the clause keeps stays until the whole-partition boundary deletes it.

Use row-level retention when different groups of rows in the same table need different retention periods — for example, when readings for most tools are kept for 100 days but a subset of tools must be kept for 400 days.

Row-level retention is disabled by default.

How it works

Row-level retention reuses the tier-migration pipeline described in Compression. What differs is why DBM rewrites a partition and what it applies while doing so; staging, hand-off, and commit are unchanged:

1. After EOD completes, DBM scans the HDB partitions as usual, as interruptible background tasks. DBM's unit of work is a single table within a single partition, and it processes one table per DBM thread.
2. For each table with a retention policy, DBM works out which retention stages apply at that partition's age, and compares them against the retention marker recorded on disk with the table. A table whose marker already matches is skipped.
3. DBM checks the projected disk usage of the target volume against the tier's transPctThr threshold, since a rewrite transiently holds a second copy of the table.
4. DBM reads the columns the configured clause references, in chunks, to identify the rows to keep. If the clause keeps every row already on disk, DBM updates only the marker and leaves the data untouched.
5. Otherwise DBM stages the retained rows as a new copy of the table, preserving column order and the tier's compression and encryption, and stamps the marker on the staged copy.
6. DBM notifies DBW of the staged copies by publishing a HdbMigrate table. It publishes after each configured thread completes a table, rather than waiting for the whole run to finish.
7. At the next EOD or EOP, DBW commits each staged entry and removes the old directory.

Because it's the same pipeline, a partition that needs both a tier move and a prune gets both in a single rewrite: one copy that relocates, compresses, encrypts, and prunes. For best performance, align your retention durations with the ages at which partitions migrate between tiers, so DBM does both in one pass. Row-level retention also works in a single-tier deployment, where partitions are rewritten in place in the tier they already occupy.

Retention rewrites stop as soon as the next EOD or EOP begins, so they never delay write-down. Work that didn't finish is picked up on the next run, and DBM sweeps any abandoned staging directory at the start of its next scan.

Enable row-level retention

Set the dynamic system parameter dbmRetention to true in systemParams.yaml:

systemParams:
  values:
   - name: dbmRetention
     value: true

While dbmRetention is false, DBM ignores retentionDuration and retentionWC entirely, so you can stage schema changes before you turn the feature on. See System parameters systemParams.yaml.

Configure a retention policy

You configure a policy per table, with two properties in the table's schema file:

Property	Description
retentionDuration	One or more ages in days. Once a partition is at least this old, the paired clause in retentionWC is applied to the table in that partition.
retentionWC	One or more functional where clauses, each written as a q expression that evaluates to a where clause identifying the rows to keep. Each clause is paired with the entry at the same position in retentionDuration.

In the following example, DBM removes the rows for Tool1, Tool3, and Tool5 from Table1 in every partition that's at least 100 days old:

Table1:
  m-meta: schema.yaml
  type: partitioned
  description: Partitioned table
  retentionDuration: [ 100 ]
  retentionWC: [ "enlist(not;(in;`toolID;enlist`Tool1`Tool3`Tool5))" ]
  columns:
   - name: toolID
     type: symbol
   - name: runID
     type: integer

A clause can reference more than one column, and a single entry can combine several where phrases:

  retentionWC: [ "((in;`toolID;enlist`Tool1`Tool2);(>;`runID;1000))" ]

The same clause can be reused across tables, as long as each table has the columns the clause references.

Retention properties are ordinary schema properties, so you add or change a policy the way you make any other schema change — through a package deploy. See About schema changes.

Define multiple retention stages

retentionDuration and retentionWC are lists, so one table can have several stages. DBM applies every stage whose duration is less than or equal to the partition's age, and combines their clauses, so each stage prunes what the earlier stages left behind.

In the following example, DBM removes Tool1 and Tool2 once a partition reaches 150 days, removes Tool3 and Tool4 once it reaches 300 days, and deletes the whole partition at 400 days — three retention periods for one table:

Table4:
  m-meta: schema.yaml
  type: partitioned
  description: Partitioned table
  partitions: 400
  retentionDuration: [ 150, 300 ]
  retentionWC: [ "enlist(not;(in;`toolID;enlist`Tool1`Tool2))", "enlist(not;(in;`toolID;enlist`Tool3`Tool4))" ]
  columns:
   - name: toolID
     type: symbol
   - name: runID
     type: integer

The two lists must be the same length. If they aren't, DBM logs an error and applies no row-level retention to that table.

Understand retention precedence

Several parameters act on a partition as it ages. From highest precedence to lowest:

1. hdbMinPrtns (system-wide) — partitions younger than this are never deleted.
2. hdbDiskPctThr (system-wide) — when HDB disk usage breaches this threshold, DBW deletes whole partitions, oldest first, until usage is below the threshold. Row-level retention plays no part in this.
3. partitions (table-level) — deletes the table's data from partitions older than this many days, overriding hdbRetPrtns in either direction.
4. hdbRetPrtns (system-wide) — the default whole-partition retention period.
5. retentionDuration (row-level) — for partitions past this age but still inside the whole-partition boundary, retentionWC selects the rows to keep.

Table-level retention, or the partition-level default when it isn't set, is the outer boundary: no row survives beyond it. Row-level retention operates inside that boundary, culling shorter-lived rows earlier while longer-lived rows persist until the partition itself is deleted.

To keep one group of rows longer than another, set the whole-partition retention to the longest period any group needs, and use retentionDuration and retentionWC to cull the shorter-lived groups earlier. retentionDuration must be shorter than the whole-partition retention. If it's equal or longer, the partition is deleted before the clause is ever applied:

Table2:
  m-meta: schema.yaml
  type: partitioned
  description: Partitioned table
  partitions: 365
  retentionDuration: [ 700 ]
  retentionWC: [ "enlist(not;(in;`toolID;enlist`Tool1`Tool3`Tool5))" ]
  columns:
   - name: toolID
     type: symbol
   - name: runID
     type: integer

In this example, table-level retention removes anything older than 365 days, so the 700-day stage never runs.

Change or remove a policy

DBM records what it has done by writing a hidden .retention marker file alongside each table it has processed. The marker holds an MD5 hash of the clauses that applied to that partition, and it's committed atomically with the data. It can't drift from the table it describes and it travels with the table through copy and restore operations.

On each run, DBM compares the marker against the clauses that currently apply:

• A matching marker means the partition is up to date, so DBM skips it.
• A missing marker means the partition has never been processed.
• A differing marker means the policy changed, so DBM re-evaluates the partition.

Detection is by content hash, so any edit to a clause — or a change to which stages apply at a given age — is picked up automatically, with no renaming or manual intervention. Re-evaluation doesn't always mean a rewrite: if the new clauses remove no further rows, DBM updates only the marker.

The hash covers the clause definitions and nothing else. If a clause depends on data defined outside it, such as a lookup table or a global that maps rows to groups, changing that data doesn't change the hash and doesn't trigger reprocessing.

Safeguards

DBM protects a running system in the following ways:

• Disk space. A rewrite transiently holds a second copy of the table in the same tier, so before each unit of work DBM projects the target volume's resulting disk usage, based on the current size of the tables in the unit of work, and compares it against the tier's transPctThr. If the projection exceeds the threshold, DBM stages nothing for that volume: it drops the set of tables, along with every table still queued against the same volume, and continues with tables targeting other volumes. The deferred tables are picked up on a later run, once the pressure is relieved.

  Tables that DBM finished in earlier sets are unaffected, because DBM publishes each set's results as it goes. When a run is cut short — by the disk threshold, or by the next EOD or EOP — the tables pruned so far are still committed at the next EOD or EOP, and the rest are retried later. A partition can therefore be left with some of its tables pruned and others not, until a subsequent run finishes the job.

• Memory. DBM never reads a partition's table into memory in full. The identify phase reads only the columns the clause references, and the rewrite phase copies retained rows, both in chunks of dbmChunkSize rows. Peak memory is proportional to the chunk size rather than the partition size, so multi-terabyte partitions can be processed without exhausting memory.

• Clause errors. If a clause can't be evaluated or can't be applied to the table, DBM logs an error and leaves the partition's data unchanged.

• Incomplete data. If a clause references a column that isn't present in the partition, DBM keeps every row rather than pruning against partial data.

• No-op rewrites. If the clause keeps every row already on disk, DBM updates the marker without rewriting the partition, so the check isn't repeated on every subsequent run.

• Resource use. The number of threads assigned to the DBM service class bounds how much work DBM does at once, as it does for compression and encryption.

Only one DBM instance runs per node, and each node prunes its own partitions independently.

Monitor row-level retention

DBM logs each run: the partitions it scans, the partitions it rewrites, the target of each copy, disk-threshold deferrals, and clause errors. The MonDBM monitoring table reports the run itself, including lastRun, runDur, quanta, pendingTbls, activeTbls, migratedTbls, migratedBytes, and isError. The table counts are counts of table segments, so they show how much of a run remains and how much of it completed. Marker-only updates change no data, so they're not counted in migratedTbls.

See Access performance monitoring data and Application logs.

Back up and restore pruned data

You have two options for backing up pruned data:

• Include HDB changes in the backup. Pruned partitions are updated in the backup as they're pruned, at the cost of additional writes to the backup copy, and you don't need to reapply the policy after a restore.
• Exclude HDB changes from the backup. This avoids the additional writes, but a restore can reintroduce rows that were previously pruned, and DBM prunes them again after the restore completes. A full restore may then need more space than is available. In that case, restore incrementally, for example, starting with the most recent partitions and working back in time, and let DBM re-prune each batch before you restore the next.

See Recover your data.

Next steps

• Compression — the tiering and migration pipeline that row-level retention reuses, including transPctThr and dbmChunkSize.
• Delete historical partitions — remove whole partitions instead of pruning rows within them.
• Tables and schemas — the full set of table properties you can set in a schema file.