PostgreSQL Autovacuum Override Guard with Kestra

id: postgres-autovacuum-override-guard
namespace: company.team

description: |
  Find PostgreSQL tables where autovacuum was switched off with a storage parameter
  (autovacuum_enabled = false on the table or its TOAST table), usually for a bulk load and
  never switched back, and tables with a vacuum scale factor so lax that vacuum almost never
  runs. Also checks that autovacuum is on for the whole server. Alert Slack with the dead rows
  piling up, and optionally reset the parameter, run VACUUM (ANALYZE) on the table, and audit
  again to verify. Tables you disable on purpose can be allowlisted.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: JDBC URL
    defaults: "jdbc:postgresql://localhost:5432/app"

  - id: allowed_tables
    type: ARRAY
    itemType: STRING
    displayName: Tables allowed to have autovacuum off
    description: As schema.table, for example staging tables you load and truncate yourself.
    defaults:
      - public.staging_import

  - id: max_scale_factor
    type: FLOAT
    displayName: Highest acceptable autovacuum_vacuum_scale_factor
    defaults: 0.4

  - id: vacuum_after_reset
    type: BOOL
    displayName: Run VACUUM (ANALYZE) after the reset
    defaults: true

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for ALTER TABLE or
      VACUUM to run.
    defaults: true

variables:
  audit_sql: |
    WITH t AS (
      SELECT c.oid, n.nspname || '.' || c.relname AS name, c.oid::regclass AS rel, c.reloptions, tc.reloptions AS toast_options,
             s.n_live_tup, s.n_dead_tup, s.last_autovacuum, s.last_vacuum
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_class tc ON tc.oid = c.reltoastrelid
        LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
       WHERE c.relkind IN ('r', 'm', 'p') AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast'
    ), opt AS (
      SELECT t.*,
             EXISTS (SELECT 1 FROM unnest(t.reloptions) o WHERE lower(o) IN ('autovacuum_enabled=false', 'autovacuum_enabled=off', 'autovacuum_enabled=0', 'autovacuum_enabled=no')) AS table_off,
             EXISTS (SELECT 1 FROM unnest(t.toast_options) o WHERE lower(o) IN ('autovacuum_enabled=false', 'autovacuum_enabled=off', 'autovacuum_enabled=0', 'autovacuum_enabled=no')) AS toast_off,
             (SELECT split_part(o, '=', 2)::float FROM unnest(t.reloptions) o WHERE o LIKE 'autovacuum_vacuum_scale_factor=%') AS scale_factor
        FROM t
    )
    SELECT 'AUTOVACUUM_OFF_SERVER' AS finding, 'CRITICAL' AS severity, 'server' AS table_name, NULL::bigint AS dead_rows, NULL::bigint AS live_rows,
           'autovacuum is off for the whole server: no table is vacuumed automatically, and transaction ID wraparound is only a matter of time' AS detail,
           NULL AS fix_sql, NULL AS vacuum_sql
     WHERE current_setting('autovacuum') = 'off'
    UNION ALL
    SELECT CASE WHEN table_off THEN 'AUTOVACUUM_DISABLED' ELSE 'TOAST_AUTOVACUUM_DISABLED' END, 'HIGH', name, n_dead_tup, n_live_tup,
           CASE WHEN table_off AND toast_off THEN 'autovacuum_enabled off on the table and its TOAST table' WHEN table_off THEN 'autovacuum_enabled off' ELSE 'autovacuum_enabled off on its TOAST table' END
             || ', last autovacuum ' || coalesce(to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI'), 'never'),
           format('ALTER TABLE %s RESET (%s)', rel, concat_ws(', ', CASE WHEN table_off THEN 'autovacuum_enabled' END, CASE WHEN toast_off THEN 'toast.autovacuum_enabled' END)),
           format('VACUUM (ANALYZE) %s', rel)
      FROM opt WHERE (table_off OR toast_off) AND name NOT IN ({{ inputs.allowed_tables | jq('map("\u0027" + gsub("\u0027"; "\u0027\u0027") + "\u0027") | join(",")') | first }})
    UNION ALL
    SELECT 'ALLOWED_AUTOVACUUM_OFF', 'INFO', name, n_dead_tup, n_live_tup, 'autovacuum off, allowlisted', NULL, NULL
      FROM opt WHERE (table_off OR toast_off) AND name IN ({{ inputs.allowed_tables | jq('map("\u0027" + gsub("\u0027"; "\u0027\u0027") + "\u0027") | join(",")') | first }})
    UNION ALL
    SELECT 'LAX_SCALE_FACTOR', 'MEDIUM', name, n_dead_tup, n_live_tup,
           'autovacuum_vacuum_scale_factor=' || scale_factor || ': vacuum waits until ' || round(scale_factor * 100) || '% of the table is dead', NULL, NULL
      FROM opt WHERE scale_factor > {{ inputs.max_scale_factor }} AND NOT table_off
    ORDER BY 2, 1, 3

tasks:
  - id: audit
    type: io.kestra.plugin.jdbc.postgresql.Query
    description: Read-only. Statements are built with regclass.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ secret('POSTGRES_USERNAME') }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: actionable
    type: io.kestra.plugin.core.output.OutputValues
    values:
      items: "{{ outputs.audit.rows | jq('map(select(.severity != \"INFO\"))') | first
        | toJson }}"

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Autovacuum overrides on {{ inputs.jdbc_url }}: {{
      fromJson(outputs.actionable.values.items) | length }} findings. {% for r
      in outputs.audit.rows %}{{ r.severity }} {{ r.finding }} {{ r.table_name
      }}{% if (r.dead_rows ?? -1) >= 0 %} ({{ r.dead_rows }} dead / {{
      r.live_rows }} live rows){% endif %}: {{ r.detail }}. {% endfor %}

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ fromJson(outputs.actionable.values.items) | length > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'PostgreSQL ' ~ inputs.jdbc_url ~ ': autovacuum overridden: ' ~ (fromJson(outputs.actionable.values.items) | jq('map("\(.severity) \(.finding) \(.table_name)\(if .dead_rows != null then " (\(.dead_rows) dead rows)" else "" end)") | join("; ")') | first) ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Resetting now.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run and
          fromJson(outputs.actionable.values.items) | jq('map(select(.fix_sql !=
          null)) | length') | first > 0 }}"
        then:
          - id: remediate
            type: io.kestra.plugin.core.flow.Loop
            concurrencyLimit: 1
            description: Reset the parameter, then optionally vacuum the table now, one
              table at a time.
            values: "{{ fromJson(outputs.actionable.values.items) | jq('map(select(.fix_sql
              != null))') | first | toJson }}"
            tasks:
              - id: one
                type: io.kestra.plugin.core.flow.Sequential
                allowFailure: true
                tasks:
                  - id: reset
                    type: io.kestra.plugin.jdbc.postgresql.Query
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ secret('POSTGRES_USERNAME') }}"
                    password: "{{ secret('POSTGRES_PASSWORD') }}"
                    fetchType: NONE
                    sql: "{{ fromJson(item.value).fix_sql }}"
                  - id: vacuum
                    type: io.kestra.plugin.jdbc.postgresql.Query
                    runIf: "{{ inputs.vacuum_after_reset }}"
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ secret('POSTGRES_USERNAME') }}"
                    password: "{{ secret('POSTGRES_PASSWORD') }}"
                    fetchType: NONE
                    sql: "{{ fromJson(item.value).vacuum_sql }}"

          - id: reaudit
            type: io.kestra.plugin.jdbc.postgresql.Query
            url: "{{ inputs.jdbc_url }}"
            username: "{{ secret('POSTGRES_USERNAME') }}"
            password: "{{ secret('POSTGRES_PASSWORD') }}"
            fetchType: FETCH
            sql: "{{ render(vars.audit_sql) }}"

          - id: verify_result
            type: io.kestra.plugin.core.flow.If
            condition: >-
              {{ {'b': fromJson(outputs.actionable.values.items), 'a':
              outputs.reaudit.rows ?? []} | jq('([.a[] | .table_name + "|" +
              .finding]) as $a
                 | [.b[] | select(.fix_sql != null) | .table_name + "|" + .finding | select(. as $x | $a | index($x))] | length') | first > 0 }}
            then:
              - id: remediation_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: "Some tables still have autovacuum off: {{ outputs.reaudit.rows |
                  jq('map(select(.fix_sql != null) | .table_name) | join(\",
                  \")') | first }}. The connecting role must own the table."

          - id: dead_after
            type: io.kestra.plugin.jdbc.postgresql.Query
            description: Dead rows on the fixed tables now, from pg_stat_user_tables.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ secret('POSTGRES_USERNAME') }}"
            password: "{{ secret('POSTGRES_PASSWORD') }}"
            fetchType: FETCH
            sql: >-
              SELECT schemaname || '.' || relname AS table_name, n_dead_tup AS
              dead_rows, last_vacuum
                FROM pg_stat_user_tables WHERE schemaname || '.' || relname IN ({{ fromJson(outputs.actionable.values.items) | jq('map(select(.fix_sql != null) | "\u0027" + (.table_name | gsub("\u0027"; "\u0027\u0027")) + "\u0027") | join(",")') | first }})

          - id: remediated
            type: io.kestra.plugin.core.log.Log
            message: >-
              Autovacuum restored and verified on {{
              fromJson(outputs.actionable.values.items) |
              jq('map(select(.fix_sql != null) | .table_name) | join(", ")') |
              first }}. Dead rows now: {% for r in outputs.dead_after.rows %}{{
              r.table_name }} {{ r.dead_rows }}{% if (r.last_vacuum ?? '') != ''
              %} (vacuumed){% endif %}. {% endfor %}
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "Autovacuum is on everywhere. Allowlisted tables with autovacuum off:
          {{ outputs.audit.size }}."

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'PostgreSQL autovacuum override guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled, and always in safe mode.
    cron: "15 5 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true

The usual advice for a large bulk load is ALTER TABLE ... SET (autovacuum_enabled = false), then switch it back after. The second step is the one that gets forgotten. From then on:

dead rows pile up and the table and its indexes bloat;
queries slow down as visibility checks grow;
the table moves toward transaction ID wraparound, which ends in a forced shutdown.

The same happens to TOAST tables, set separately with toast.autovacuum_enabled, and to tables given a scale factor so lax that vacuum almost never triggers.

This blueprint finds those tables, with their dead and live row counts, and checks that autovacuum is on for the server. It alerts Slack, and can reset the parameter and run VACUUM (ANALYZE) on each table. It then audits again to verify. Tables you switch off on purpose, such as staging tables you truncate yourself, can be allowlisted.

This blueprint was created by zkasuran.

Findings

Finding	Severity	Rule	Fix
`AUTOVACUUM_OFF_SERVER`	Critical	`autovacuum` is off for the whole server	Report only: change it in `postgresql.conf` or with `ALTER SYSTEM`
`AUTOVACUUM_DISABLED`	High	`autovacuum_enabled` off on the table, and possibly its TOAST table	`ALTER TABLE ... RESET (...)`, then `VACUUM (ANALYZE)`
`TOAST_AUTOVACUUM_DISABLED`	High	Off on the TOAST table only	`RESET (toast.autovacuum_enabled)`, then `VACUUM (ANALYZE)`
`LAX_SCALE_FACTOR`	Medium	`autovacuum_vacuum_scale_factor` above `max_scale_factor`	Report only
`ALLOWED_AUTOVACUUM_OFF`	Info	Off, but in `allowed_tables`	None

How it works

audit (io.kestra.plugin.jdbc.postgresql.Query, read-only) reads reloptions of every table and its TOAST table, the dead and live rows from pg_stat_user_tables, and the server autovacuum setting. It returns each finding with its fix, built with regclass.
actionable drops the informational rows. summary logs everything.
route (io.kestra.plugin.core.flow.If):
- clean: logs it.
- findings: alert posts to Slack with the dead row counts.
  - remediate_gate runs only when auto_remediate is true and dry_run is false.
  - remediate (io.kestra.plugin.core.flow.Loop) resets the parameter on each table and, with vacuum_after_reset, runs VACUUM (ANALYZE) right away, one table at a time.
  - reaudit and verify_result fail if a table still has autovacuum off. dead_after and remediated log the dead rows now.
errors alerts Slack when the audit cannot run.

What you get

Every table that autovacuum is not allowed to clean, with how much dead data it holds.
TOAST overrides and lax scale factors, which \d+ does not make obvious.
A reset and an immediate vacuum, verified, without touching the tables you allowlist.

Who it's for

Teams that bulk load into PostgreSQL and tune autovacuum per table.
DBAs chasing table bloat or wraparound warnings.
Teams evaluating the PostgreSQL plugin who want an example of a catalog query driving ALTER TABLE and VACUUM.

Why orchestrate this with Kestra

The override is set once and forgotten, because nothing checks it later. Kestra runs the check every day and keeps the allowlist next to the flow. It sends the dead row counts to Slack, resets and vacuums behind auto_remediate and dry_run, verifies the result, and keeps a history of when each table was switched off and back on.

Tested end to end

On Kestra 2.0.5 OSS against PostgreSQL 16, with a local HTTP sink standing in for Slack:

events (600,000 rows, 200,000 dead) with autovacuum_enabled=false;
order_items (320,000 live, 80,000 dead) off on the table and its TOAST table;
shipments with autovacuum_vacuum_scale_factor=0.9;
staging_import off and allowlisted.

Run	Inputs	Result
1	defaults	2 high findings with their dead rows, 1 lax scale factor, the allowlisted table reported as info. Alert sent
2	`auto_remediate: true`, `dry_run: true`	Same report. `events` still has `{autovacuum_enabled=false}`
3	`auto_remediate: true`, `dry_run: false`	Parameters reset on both tables and the TOAST table, `VACUUM (ANALYZE)` run, verified. `pg_class` shows no options left, and `pg_stat_user_tables` shows 0 dead rows and a recorded vacuum
4	defaults	Only the lax scale factor left, report only
5	after resetting the scale factor	Clean, with 1 allowlisted table
6	`ALTER SYSTEM SET autovacuum = off`	Critical server finding
7	unreachable database	`errors` alert sent

Prerequisites

For the audit, any role can read the catalog and statistics. For remediation, the owner of the tables.
Local testing:
- docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:16;
- ALTER TABLE t SET (autovacuum_enabled = false) on a table, then update or delete some rows.

Secrets

POSTGRES_USERNAME / POSTGRES_PASSWORD: credentials used by every query.
SLACK_WEBHOOK_URL: used by alert and the errors block.
In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input	Default	Purpose
`jdbc_url`	`jdbc:postgresql://localhost:5432/app`	Database
`allowed_tables`	`[public.staging_import]`	Tables allowed to have autovacuum off
`max_scale_factor`	`0.4`	Highest acceptable vacuum scale factor
`vacuum_after_reset`	`true`	Vacuum right after the reset
`auto_remediate`	`false`	Allow `ALTER TABLE` and `VACUUM`
`dry_run`	`true`	Must be `false` for a statement to run

Quick start

Add the POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL secrets.
Import the flow and set jdbc_url and allowed_tables.
Run it once and read the findings.
Run it with auto_remediate: true and dry_run: false outside peak hours, since VACUUM reads the whole table.
Enable the daily trigger, which always runs in safe mode.

How to extend

Call the flow at the end of your bulk load pipeline, so the override never outlives the load.
Add the transaction ID age of each table (age(relfrozenxid)) to rank the findings by urgency.
Loop over a list of JDBC URLs to check every database of a cluster.
Also report autovacuum_analyze_scale_factor and autovacuum_vacuum_cost_delay overrides.

PostgreSQL Autovacuum Override Guard

Findings

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

PostgreSQL Autovacuum Override Guard

Findings

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

More Infrastructure Blueprints

New to Kestra?