Schedule icon
Query icon
If icon
SlackIncomingWebhook icon
Log icon

Audit PostgreSQL Dead Tuples and Table Bloat

Audit PostgreSQL user tables to detect dead tuple accumulation and autovacuum starvation with automated Slack remediation alerts.

Categories
DataInfrastructure

How it works

This sentinel workflow automates Day 2 database maintenance and table bloat detection for PostgreSQL clusters:

  1. Scheduled Audit Trigger: Periodically executes on a configurable cron schedule (0 4 * * *, shipped disabled for safe initial onboarding) during low-traffic maintenance windows.
  2. Catalog Telemetry Probe: Executes a SQL query against PostgreSQL's internal pg_stat_user_tables catalog to compute the ratio of dead tuples to total tuples across all user schemas.
  3. Threshold Filtering: Filters for user tables where the count of dead tuples exceeds min_dead_tuples_threshold (default 10,000) and the dead-to-total ratio exceeds min_dead_tuples_percentage (default 20%).
  4. Flow Control & Slack Alerting: Uses Kestra's native io.kestra.plugin.core.flow.If task to check whether any bloated tables were discovered. If detected, it dispatches an actionable Slack notification detailing table names, bloat ratios, and exact VACUUM ANALYZE commands.
  5. Failure Resilience: A top-level errors handler catches connection drops or credential misconfigurations and alerts on-call engineers directly on Slack.

What you get

  • Continuous prevention of table bloat and disk space waste across relational workloads.
  • Early warning before sequential table scans experience severe I/O degradation due to unvacuumed dead tuples.
  • Structured query telemetry and audit lineage preserved in Kestra execution history.
  • Instant Slack alerts with exact SQL runbook remediation commands.

Who it's for

  • Database Administrators (DBAs): Managing high-write PostgreSQL clusters and preventing autovacuum freeze events.
  • Site Reliability Engineers (SREs): Automating proactive database health checks across production microservices.
  • Data Platform Engineers: Ensuring operational analytics workloads do not suffer from stale query plans and bloated tables.

Why orchestrate this with Kestra

  • Native Declarative SQL Tasks: Leverage io.kestra.plugin.jdbc.postgresql.Query with connection pooling and secure credential injection without hosting external Python daemons.
  • Encrypted Secrets Management: Mask sensitive database passwords and Slack webhook URLs using Kestra's encrypted secrets manager ({{ secret('...') }}).
  • Automated Remediation Chaining: Easily extend the workflow to trigger an automated VACUUM job during off-peak hours for eligible tables.
  • Complete Observability: Review visual execution logs, audit summaries, and task metrics directly in the Kestra UI.

Prerequisites

  • An accessible PostgreSQL instance (version 12+) with network connectivity from the Kestra worker.
  • A database user with SELECT privileges on pg_stat_user_tables.
  • A Slack Incoming Webhook URL.

Secrets

Configure the following secrets in your Kestra namespace or instance settings:

  • POSTGRES_USERNAME: Username for authenticating with the target PostgreSQL cluster.
  • POSTGRES_PASSWORD: Password for the PostgreSQL user.
  • SLACK_WEBHOOK_URL: The incoming webhook URL for your designated database alerts channel.

Quick start

  1. Click Use Blueprint to install this flow in your Kestra instance.
  2. Add POSTGRES_USERNAME, POSTGRES_PASSWORD, and SLACK_WEBHOOK_URL to your Kestra secrets store.
  3. Adjust postgres_host, postgres_port, and postgres_database to match your cluster topology.
  4. Execute a manual test run, then toggle the schedule trigger to enable daily morning audits.

How to extend

  • Automated VACUUM Execution: Add a downstream task that automatically runs VACUUM (ANALYZE) <table_name> during off-peak hours when bloat exceeds 40%.
  • Index Bloat Surveillance: Correlate table bloat with pg_stat_user_indexes to identify unindexed foreign keys and bloated B-tree index structures.
  • PagerDuty & Opsgenie Integration: Route alerts to PagerDuty if critical business tables exceed 50% dead tuple ratios.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.