id: postgres-stat-statements-query-performance-guard
namespace: company.data
description: |
Audit PostgreSQL query performance and buffer cache hit efficiency using pg_stat_statements.
Identifies slow queries exceeding latency SLAs, evaluates shared buffer hit ratios,
exports a downloadable CSV performance report, triggers automated table statistics refresh,
persists execution telemetry in KV, and dispatches automated Slack incident alerts.
concurrency:
limit: 1
behavior: QUEUE
inputs:
- id: cache_hit_ratio_threshold
type: FLOAT
displayName: Minimum Cache Hit Ratio (%)
description: Minimum acceptable shared buffer cache hit ratio percentage. Ratios
below this threshold indicate memory pressure or missing indexes.
defaults: 95.0
min: 50.0
max: 100.0
- id: mean_exec_threshold_ms
type: FLOAT
displayName: Mean Execution Time Threshold (ms)
description: Average query execution time threshold in milliseconds. Queries
exceeding this mean are flagged as slow.
defaults: 250.0
min: 10.0
max: 60000.0
- id: max_slow_queries_reported
type: INT
displayName: Maximum Slow Queries to Report
description: Maximum number of degraded query plans to return in the diagnostic summary.
defaults: 10
min: 1
max: 50
- id: auto_analyze_on_degradation
type: BOOL
displayName: Automatically Refresh Statistics on Degradation
description: Execute an ANALYZE statement on the database when performance
degradation is detected to refresh stale query planner statistics.
defaults: true
- id: database_name
type: STRING
displayName: PostgreSQL Database Name
description: Target database name to scope the pg_stat_statements query analysis.
defaults: postgres
validator: ^[A-Za-z_][A-Za-z0-9_]{0,63}$
tasks:
- id: verify_extension
type: io.kestra.plugin.jdbc.postgresql.Query
description: Confirm that pg_stat_statements extension is installed and
available in the target database.
url: "{{ secret('POSTGRES_JDBC_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
timeZoneId: UTC
fetchType: FETCH_ONE
sql: |
SELECT EXISTS (
SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements'
) AS is_installed;
- id: evaluate_cache_and_queries
type: io.kestra.plugin.jdbc.postgresql.Query
description: Query pg_stat_statements and pg_stat_database for cache hit ratios
and slow query candidates.
url: "{{ secret('POSTGRES_JDBC_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
timeZoneId: UTC
fetchType: FETCH_ONE
sql: |
WITH cache_metrics AS (
SELECT
datname,
COALESCE(ROUND((blks_hit::numeric / NULLIF(blks_hit + blks_read, 0)::numeric) * 100.0, 2), 100.0) AS cache_hit_ratio,
blks_read,
blks_hit
FROM pg_stat_database
WHERE datname = '{{ inputs.database_name }}'
),
query_metrics AS (
SELECT
COUNT(*) AS slow_query_count,
COALESCE(MAX(mean_exec_time), 0.0) AS highest_mean_exec_time_ms,
COALESCE(MAX(max_exec_time), 0.0) AS highest_max_exec_time_ms
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE d.datname = '{{ inputs.database_name }}'
AND s.mean_exec_time > {{ inputs.mean_exec_threshold_ms }}
)
SELECT
c.datname,
c.cache_hit_ratio,
c.blks_read,
c.blks_hit,
q.slow_query_count,
ROUND(q.highest_mean_exec_time_ms::numeric, 2) AS highest_mean_exec_time_ms,
ROUND(q.highest_max_exec_time_ms::numeric, 2) AS highest_max_exec_time_ms,
CASE
WHEN c.cache_hit_ratio < {{ inputs.cache_hit_ratio_threshold }} OR q.slow_query_count > 0 THEN true
ELSE false
END AS is_degraded
FROM cache_metrics c
CROSS JOIN query_metrics q;
- id: fetch_top_slow_queries
type: io.kestra.plugin.jdbc.postgresql.Query
description: Store detailed query text and timing statistics in internal storage
for report generation.
url: "{{ secret('POSTGRES_JDBC_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
timeZoneId: UTC
fetchType: STORE
sql: |
SELECT
queryid,
LEFT(query, 120) AS query_preview,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_exec_ms,
ROUND(max_exec_time::numeric, 2) AS max_exec_ms,
ROUND(total_exec_time::numeric, 2) AS total_exec_ms,
rows AS total_rows,
shared_blks_hit,
shared_blks_read
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE d.datname = '{{ inputs.database_name }}'
AND s.mean_exec_time > {{ inputs.mean_exec_threshold_ms }}
ORDER BY mean_exec_time DESC
LIMIT {{ inputs.max_slow_queries_reported }};
- id: export_diagnostic_csv
type: io.kestra.plugin.serdes.csv.IonToCsv
description: Serialize the slow query diagnostic telemetry into a downloadable
CSV artifact.
from: "{{ outputs.fetch_top_slow_queries.uri }}"
- id: check_health_and_remediate
type: io.kestra.plugin.core.flow.If
description: Evaluate performance metrics and execute automated remediation when
degraded.
condition: "{{ outputs.evaluate_cache_and_queries.row.is_degraded }}"
then:
- id: log_degraded_performance
type: io.kestra.plugin.core.log.Log
message: >-
Performance Warning on database '{{ inputs.database_name }}': Cache
Hit Ratio: {{ outputs.evaluate_cache_and_queries.row.cache_hit_ratio
}}% (Threshold: {{ inputs.cache_hit_ratio_threshold }}%). Slow Queries
Count: {{ outputs.evaluate_cache_and_queries.row.slow_query_count }}
queries exceeding {{ inputs.mean_exec_threshold_ms }}ms mean latency.
Highest Mean Latency: {{
outputs.evaluate_cache_and_queries.row.highest_mean_exec_time_ms }}ms.
Diagnostic CSV Report URI: {{ outputs.export_diagnostic_csv.uri }}
- id: conditional_remediation
type: io.kestra.plugin.core.flow.If
description: Run automated statistics refresh if enabled by workflow inputs.
condition: "{{ inputs.auto_analyze_on_degradation }}"
then:
- id: refresh_planner_statistics
type: io.kestra.plugin.jdbc.postgresql.Query
description: Execute ANALYZE to refresh stale query planner statistics on
degraded performance.
url: "{{ secret('POSTGRES_JDBC_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
timeZoneId: UTC
sql: ANALYZE;
- id: log_remediation_completed
type: io.kestra.plugin.core.log.Log
message: "Automated ANALYZE executed successfully on database '{{
inputs.database_name }}'."
- id: notify_slack_alert
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Send incident alert to Slack with performance metrics and
remediation status.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: >-
[WARNING] *PostgreSQL Performance Degradation Alert* *Database:* `{{
inputs.database_name }}` *Cache Hit Ratio:* `{{
outputs.evaluate_cache_and_queries.row.cache_hit_ratio }}%` (Target:
>= `{{ inputs.cache_hit_ratio_threshold }}%`) *Slow Queries Detected:*
`{{ outputs.evaluate_cache_and_queries.row.slow_query_count }}` *Peak
Query Mean Latency:* `{{
outputs.evaluate_cache_and_queries.row.highest_mean_exec_time_ms }}
ms` *Statistics Refreshed:* `{{ inputs.auto_analyze_on_degradation ?
'Yes (ANALYZE executed)' : 'No' }}` *Diagnostic CSV Report:* `{{
outputs.export_diagnostic_csv.uri }}` *Execution ID:* `{{ execution.id
}}`
else:
- id: log_healthy_state
type: io.kestra.plugin.core.log.Log
message: >-
PostgreSQL database '{{ inputs.database_name }}' is healthy. Cache Hit
Ratio is {{ outputs.evaluate_cache_and_queries.row.cache_hit_ratio }}%
(>= {{ inputs.cache_hit_ratio_threshold }}%), and 0 queries exceed the
{{ inputs.mean_exec_threshold_ms }}ms latency threshold.
- id: update_audit_watermark
type: io.kestra.plugin.core.kv.Set
description: Record telemetry snapshot in Kestra KV store for historical
tracking and trend analysis.
key: "postgres_stat_audit_{{ inputs.database_name }}"
value:
last_audit_timestamp: "{{ now() }}"
cache_hit_ratio: "{{ outputs.evaluate_cache_and_queries.row.cache_hit_ratio }}"
slow_query_count: "{{ outputs.evaluate_cache_and_queries.row.slow_query_count }}"
is_degraded: "{{ outputs.evaluate_cache_and_queries.row.is_degraded }}"
diagnostic_csv_uri: "{{ outputs.export_diagnostic_csv.uri }}"
errors:
- id: alert_audit_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Notify on task failure if database connectivity or query execution fails.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: >-
[ERROR] PostgreSQL Performance Audit failed in execution `{{ execution.id
}}` on database `{{ inputs.database_name }}`. Failed task: `{{
(errorLogs() | length) > 0 ? errorLogs()[0].taskId : 'unknown' }}`. Error
message: `{{ (errorLogs() | length) > 0 ? errorLogs()[0].message :
'inspect execution logs' }}`.
triggers:
- id: hourly_audit_schedule
type: io.kestra.plugin.core.trigger.Schedule
cron: "0 * * * *"