Actian Vector Table Fragmentation Sentinel

id: actian-vector-table-fragmentation-and-storage-sentinel
namespace: company.dataops

inputs:
  - id: vector_url
    type: STRING
    defaults: "jdbc:vectorwise://localhost:27832/analytics_db"
    description: JDBC connection URL for the target Actian Vector (VectorWise)
      warehouse instance (e.g. jdbc:vectorwise://host:port/database).

  - id: target_table
    type: STRING
    defaults: "fact_orders"
    description: Name of the columnar table to audit for overflow block accumulation
      and Positional Delta Tree (PDT) fragmentation.

  - id: max_overflow_ratio_threshold
    type: FLOAT
    defaults: 20.0
    description: Warning threshold percentage of uncompacted overflow rows before
      triggering a table reorganization notice.

  - id: critical_overflow_ratio_threshold
    type: FLOAT
    defaults: 40.0
    description: Critical threshold percentage where columnar SIMD vector query
      performance begins to severely degrade.

triggers:
  - id: periodic_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "0 */4 * * *"
    disabled: true
    inputs:
      vector_url: "jdbc:vectorwise://localhost:27832/analytics_db"
      target_table: "fact_orders"
      max_overflow_ratio_threshold: 20.0
      critical_overflow_ratio_threshold: 40.0

tasks:
  - id: probe_table_fragmentation
    type: io.kestra.plugin.jdbc.actianvector.Query
    url: "{{ inputs.vector_url }}"
    username: "{{ secret('VECTOR_USERNAME') }}"
    password: "{{ secret('VECTOR_PASSWORD') }}"
    fetchType: FETCH_ONE
    allowFailure: true
    sql: |
      SELECT
        table_name,
        num_rows,
        overflow_rows,
        ROUND(100.0 * overflow_rows / NULLIF(num_rows, 0), 2) AS overflow_percent
      FROM iitables
      WHERE table_name = '{{ inputs.target_table }}'
    description: Queries Actian Vector system catalog iitables to inspect active row
      counts, overflow records, and calculate fragmentation ratio.

  - id: classify_storage_posture
    type: io.kestra.plugin.core.debug.Return
    format: >-
      {{
        (outputs.probe_table_fragmentation is not defined or outputs.probe_table_fragmentation.row is not defined or outputs.probe_table_fragmentation.row.overflow_percent is not defined) ? 'ACTIAN_CONNECTION_FAILED' :
        (outputs.probe_table_fragmentation.row.overflow_percent >= inputs.critical_overflow_ratio_threshold) ? 'CRITICAL_FRAGMENTATION_EXCEEDED' :
        (outputs.probe_table_fragmentation.row.overflow_percent >= inputs.max_overflow_ratio_threshold) ? 'WARNING_FRAGMENTATION_SURGE' :
        'HEALTHY'
      }}
    description: Evaluates calculated overflow block ratios against SLA thresholds
      using Pebble logic to determine operational health.

  - id: route_posture_action
    type: io.kestra.plugin.core.flow.Switch
    value: "{{ outputs.classify_storage_posture.value }}"
    cases:
      CRITICAL_FRAGMENTATION_EXCEEDED:
        - id: alert_critical_fragmentation
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '🚨 *CRITICAL: Actian Vector Severe Table Fragmentation*

            • *Table:* `' ~ inputs.target_table ~ '`

            • *Overflow Ratio:* `' ~
            outputs.probe_table_fragmentation.row.overflow_percent ~ '%`
            (Critical Ceiling: `' ~ inputs.critical_overflow_ratio_threshold ~
            '%`)

            • *Total Rows:* ' ~ outputs.probe_table_fragmentation.row.num_rows ~
            '

            • *Overflow Rows:* ' ~
            outputs.probe_table_fragmentation.row.overflow_rows ~ '

            • *Execution ID:* `' ~ execution.id ~ '`

            *Action Required:* Positional Delta Trees (PDT) and overflow blocks
            exceed operational thresholds; execute `COMBINE` or table
            reorganization to restore SIMD vectorized execution speeds.'} |
            toJson }}"

      WARNING_FRAGMENTATION_SURGE:
        - id: alert_warning_fragmentation
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '⚠️ *WARNING: Actian Vector Table Fragmentation Surge*

            • *Table:* `' ~ inputs.target_table ~ '`

            • *Overflow Ratio:* `' ~
            outputs.probe_table_fragmentation.row.overflow_percent ~ '%`
            (Warning Threshold: `' ~ inputs.max_overflow_ratio_threshold ~ '%`)

            • *Execution ID:* `' ~ execution.id ~ '`

            *Notice:* High-volume delta updates or uncommitted batch inserts
            detected.'} | toJson }}"

      ACTIAN_CONNECTION_FAILED:
        - id: alert_connection_failure
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '🔥 *ALERT: Actian Vector Warehouse Unreachable*

            • *URL:* `' ~ inputs.vector_url ~ '`

            • *Table:* `' ~ inputs.target_table ~ '`

            • *Error:* Unable to establish JDBC connection via Ingres TDS
            driver.

            • *Execution ID:* `' ~ execution.id ~ '`'} | toJson }}"

      HEALTHY:
        - id: log_table_healthy
          type: io.kestra.plugin.core.log.Log
          message: "Actian Vector table '{{ inputs.target_table }}' storage is healthy.
            Overflow ratio: {{
            outputs.probe_table_fragmentation.row.overflow_percent }}%, Total
            rows: {{ outputs.probe_table_fragmentation.row.num_rows }}."
    description: Routes Slack incident notifications or diagnostic execution logs
      based on evaluated table storage posture.

  - id: audit_sentinel_run
    type: io.kestra.plugin.core.log.Log
    message: "Actian Vector storage sentinel cycle complete for execution {{
      execution.id }}. Verdict: {{ outputs.classify_storage_posture.value }},
      Overflow Ratio: {{ outputs.probe_table_fragmentation.row.overflow_percent
      ?? 'N/A' }}%, Total Rows: {{
      outputs.probe_table_fragmentation.row.num_rows ?? 'N/A' }}."
    description: Emits an immutable structured audit log entry recording columnar
      storage metrics and cycle verdicts.

errors:
  - id: alert_flow_error
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: "{{ {'text': '❌ *Actian Vector Sentinel Execution Error*

      Flow failed unexpectedly during execution `' ~ execution.id ~ '` against
      `' ~ inputs.vector_url ~ '`. Inspect Kestra task error logs.'} | toJson
      }}"

In enterprise analytical data warehouses powered by Actian Vector (formerly VectorWise), high-speed query performance relies heavily on vectorized SIMD execution against contiguous columnar storage blocks. When frequent updates, merges, or incremental trickle-inserts take place, modified records are buffered in Positional Delta Trees (PDT) and uncompacted overflow pages. Over time, excessive overflow blocks force query engines to perform random I/O and costly on-the-fly reconciliation, degrading analytic query throughput without raising explicit database errors.

This blueprint implements an automated Day 2 operations sentinel for Actian Vector data warehouses. On a scheduled cadence, it queries catalog metadata from iitables via native JDBC, calculates exact overflow row ratios, classifies storage health against strict SLA thresholds, and alerts data engineering teams via Slack before report latency deteriorates.

How it works

The periodic_schedule trigger runs the flow on a 4-hour cadence with default connection parameters, configured in a disabled state for safe deployment.
probe_table_fragmentation (io.kestra.plugin.jdbc.actianvector.Query) connects via native Ingres JDBC driver and executes a single-row diagnostic query on iitables fetching row counts, overflow records, and computing overflow percentage.
classify_storage_posture (io.kestra.plugin.core.debug.Return) evaluates query outputs against configured warning and critical thresholds via Pebble, classifying state into ACTIAN_CONNECTION_FAILED, CRITICAL_FRAGMENTATION_EXCEEDED, WARNING_FRAGMENTATION_SURGE, or HEALTHY.
route_posture_action (io.kestra.plugin.core.flow.Switch) evaluates the classification verdict: critical conditions dispatch urgent Slack incident alerts detailing overflow metrics and remediation steps, while healthy runs emit diagnostic metrics.
audit_sentinel_run (io.kestra.plugin.core.log.Log) writes an immutable execution record detailing evaluated metrics for compliance and long-term storage trend analysis.
A flow-level errors handler catches network partitions, JDBC timeouts, or authentication issues, ensuring monitoring disruptions are immediately flagged.

What you get

Proactive visibility into Actian Vector columnar block fragmentation and overflow growth.
Automated detection of Positional Delta Tree (PDT) saturation before analytical queries degrade.
Decoupled threshold alerting directly into operational Slack channels with actionable DBA context.
Comprehensive audit logs capturing row allocations and overflow ratios across execution cycles.
Fault-tolerant error handling preventing silent workflow drops during network hiccups.

Who it's for

Data Platform Engineers and Warehouse Administrators managing Actian Vector and VectorWise clusters.
BI Engineers maintaining SLA-sensitive analytical reporting marts and OLAP pipelines.
Site Reliability Engineers (SREs) monitoring enterprise data warehouse performance and storage health.

Why orchestrate this with Kestra

Monitoring columnar warehouses with disparate cron scripts creates unmanaged infrastructure, plain-text credential leaks, and silent script failures. Kestra delivers certified Actian Vector JDBC connectivity, centralized secret masking, declarative scheduling, robust failure retries, and unified alerting without installing or maintaining specialized host monitoring daemons.

Prerequisites

A running Actian Vector or VectorWise warehouse instance (version 5.0+ or 6.0+) reachable from the Kestra worker environment.
Database user credentials with permissions to query system catalogs (iitables).
An incoming Slack Webhook URL configured in Kestra namespace secrets.

Secrets

VECTOR_USERNAME: Database user login authorized to inspect Actian Vector system tables.
VECTOR_PASSWORD: Password for the designated Actian Vector user.
SLACK_WEBHOOK_URL: The destination Slack webhook URL for operational incident dispatching.

Quick start

Configure VECTOR_USERNAME, VECTOR_PASSWORD, and SLACK_WEBHOOK_URL in your Kestra namespace secrets.

Start an Actian Vector container for local validation:

docker run -d --name actian-sentinel \
  -p 27832:27832 \
  -e VECTOR_PASSWORD=my_secret_pass \
  actian/vector:latest

Connect the container to your local Kestra network:

docker network connect YOUR_KESTRA_NETWORK actian-sentinel

Execute a manual test run in the Kestra UI with vector_url: "jdbc:vectorwise://actian-sentinel:27832/analytics_db".
Verify execution logs confirm successful query execution and storage classification (HEALTHY).
Enable the periodic_schedule trigger to activate autonomous background monitoring.

How to extend

Add automated table reorganization by chaining an io.kestra.plugin.jdbc.actianvector.Query executing COMBINE TABLE target_table when critical thresholds are reached.
Implement multi-table sweeps by wrapping the check in an io.kestra.plugin.core.flow.ForEach loop iterating over all active analytical tables returned from iitables.
Integrate multi-channel incident dispatch by routing critical alerts to PagerDuty via io.kestra.plugin.pagerduty when overflow ratio exceeds 50%.

Actian Vector Table Fragmentation and Storage Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Actian Vector Table Fragmentation and Storage Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Aerospike Stop-Writes Headroom Sentinel

Invoice Math Discrepancy Gate with In-Memory DuckDB Reconciliation

Aerospike Namespace Device High Watermark and Eviction Sentinel

Adanos Asset Sentiment Drift Sentinel

Automate Customer Feedback Triage, Sentiment Analysis, and Escalation with Google Gemini and Slack

AI Data Quality Anomaly Advisor and SQL Remediation Generator

New to Kestra?