id: postgresql-dead-tuples-and-table-bloat-sentinel
namespace: company.database
description: |
Audits PostgreSQL catalog telemetry to detect table bloat, unvacuumed dead tuple accumulation, and autovacuum starvation before query latency degrades.
inputs:
- id: postgres_host
type: STRING
displayName: PostgreSQL Host
defaults: "postgres.internal"
description: Hostname or IP address of the target PostgreSQL cluster.
- id: postgres_port
type: INT
displayName: PostgreSQL Port
defaults: 5432
description: Port number for the PostgreSQL connection.
- id: postgres_database
type: STRING
displayName: Database Name
defaults: "postgres"
description: Target database name to inspect for table bloat and dead tuples.
- id: min_dead_tuples_threshold
type: INT
displayName: Min Dead Tuples Threshold
defaults: 10000
description: Minimum number of dead tuples on a table before qualifying as bloated.
- id: min_dead_tuples_percentage
type: FLOAT
displayName: Min Dead Tuples Ratio (%)
defaults: 20.0
description: Minimum percentage of dead-to-total tuples on a table before
qualifying as bloated.
- id: slack_channel
type: STRING
displayName: Slack Channel
defaults: "#database-alerts"
description: Destination Slack channel for database bloat alerts and remediation
runbooks.
triggers:
- id: daily_bloat_audit
type: io.kestra.plugin.core.trigger.Schedule
cron: "0 4 * * *"
disabled: true
description: Automated schedule trigger to audit table bloat daily during
low-traffic windows.
tasks:
- id: query_table_bloat
type: io.kestra.plugin.jdbc.postgresql.Query
description: Queries pg_stat_user_tables to detect tables exceeding dead tuple
volume and ratio thresholds.
url: "jdbc:postgresql://{{ inputs.postgres_host }}:{{ inputs.postgres_port }}/{{
inputs.postgres_database }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
fetchType: FETCH
sql: |
SELECT
schemaname,
relname AS table_name,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_ratio,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup >= {{ inputs.min_dead_tuples_threshold }}
AND (n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0)) >= {{ inputs.min_dead_tuples_percentage }}
ORDER BY n_dead_tup DESC
LIMIT 25;
- id: evaluate_bloat_severity
type: io.kestra.plugin.core.flow.If
description: Evaluates whether any user tables breach dead tuple thresholds and
branches into Slack alerting or nominal logging.
condition: "{{ outputs.query_table_bloat.rows | length > 0 }}"
then:
- id: alert_bloat_slack
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Dispatches actionable operational alert to Slack detailing bloated
PostgreSQL tables.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"channel": "{{ inputs.slack_channel }}",
"attachments": [
{
"color": "#FF9800",
"title": "⚠️ PostgreSQL Table Bloat Alert: Dead Tuples Exceed Safety Threshold",
"text": "The Kestra PostgreSQL Sentinel detected {{ outputs.query_table_bloat.size }} bloated user table(s) in database `{{ inputs.postgres_database }}` on host `{{ inputs.postgres_host }}`.",
"fields": [
{
"title": "Primary Offending Table",
"value": "`{{ outputs.query_table_bloat.rows[0].schemaname }}.{{ outputs.query_table_bloat.rows[0].table_name }}`",
"short": true
},
{
"title": "Dead Tuples",
"value": "{{ outputs.query_table_bloat.rows[0].dead_tuples }} ({{ outputs.query_table_bloat.rows[0].dead_tuple_ratio }}% bloat)",
"short": true
},
{
"title": "Live Tuples",
"value": "{{ outputs.query_table_bloat.rows[0].live_tuples }}",
"short": true
},
{
"title": "Last Autovacuum",
"value": "{{ outputs.query_table_bloat.rows[0].last_autovacuum ?? 'Never' }}",
"short": true
},
{
"title": "Remediation Command",
"value": "`VACUUM (ANALYZE, VERBOSE) {{ outputs.query_table_bloat.rows[0].schemaname }}.{{ outputs.query_table_bloat.rows[0].table_name }};`",
"short": false
}
],
"footer": "Kestra PostgreSQL Sentinel • Execution ID: {{ execution.id }}"
}
]
}
- id: log_bloat_details
type: io.kestra.plugin.core.log.Log
description: Emits diagnostic log message for on-call database administrators.
message: "Bloat Detected: Table '{{ outputs.query_table_bloat.rows[0].schemaname
}}.{{ outputs.query_table_bloat.rows[0].table_name }}' has {{
outputs.query_table_bloat.rows[0].dead_tuples }} dead tuples ({{
outputs.query_table_bloat.rows[0].dead_tuple_ratio }}% dead ratio).
Last autovacuum: {{ outputs.query_table_bloat.rows[0].last_autovacuum
?? 'Never' }}."
else:
- id: log_nominal_state
type: io.kestra.plugin.core.log.Log
description: Emits confirmation that all tables operate within safe bloat
tolerances.
message: "PostgreSQL table hygiene nominal. 0 tables exceed the dead tuple ratio
threshold of {{ inputs.min_dead_tuples_percentage }}%."
- id: emit_audit_summary
type: io.kestra.plugin.core.log.Log
description: Records audit completion telemetry for compliance and audit lineage.
message: "PostgreSQL bloat audit pass completed for '{{ inputs.postgres_host
}}:{{ inputs.postgres_port }}/{{ inputs.postgres_database }}'. Bloated
tables detected: {{ outputs.query_table_bloat.size }}."
errors:
- id: alert_on_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Dispatches high-urgency Slack alert if the sentinel execution fails.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"channel": "{{ inputs.slack_channel }}",
"text": "❌ *PostgreSQL Sentinel Execution Failed*: Execution `{{ execution.id }}` crashed during bloat audit for host `{{ inputs.postgres_host }}`."
}