Query icon
IonToCsv icon
If icon
Log icon
SlackIncomingWebhook icon
Set icon
Schedule icon

PostgreSQL pg_stat_statements Query Performance and Cache Efficiency Guard

Audit PostgreSQL buffer cache hit ratios and slow queries using pg_stat_statements, export CSV reports, and refresh statistics automatically in Kestra.

Categories
Data

Unindexed queries and declining buffer cache hit ratios degrade application response times and drive up cloud database compute costs. Detecting query regressions early requires continuous inspection of execution statistics rather than waiting for user-facing latency spikes. This flow audits PostgreSQL's pg_stat_statements and database buffer statistics on a schedule, evaluates health thresholds, exports a downloadable CSV diagnostic report, triggers automated query planner statistics refresh (ANALYZE), records telemetry in Kestra's Key-Value store, and dispatches rich Slack alerts when degradation occurs.

How it works

  1. The Schedule trigger initiates the workflow hourly, or developers can trigger manual executions via the UI.
  2. verify_extension queries pg_extension to ensure pg_stat_statements is active in the target database.
  3. evaluate_cache_and_queries aggregates database-level metrics from pg_stat_database and flags queries exceeding mean_exec_threshold_ms.
  4. fetch_top_slow_queries streams degraded SQL statements into Kestra internal storage (STORE fetch type).
  5. export_diagnostic_csv transforms the raw query telemetry into a downloadable CSV artifact using IonToCsv.
  6. check_health_and_remediate (If condition) verifies whether the buffer cache hit ratio or slow query count breaches configured SLA boundaries.
  7. If degraded, conditional_remediation runs an ANALYZE statement on the database to update planner statistics, and notify_slack_alert posts an alert with the metrics and CSV download link.
  8. update_audit_watermark updates the postgres_stat_audit_<database> key in Kestra's KV store with the latest metrics for historical trend analysis.
  9. The errors block intercepts connection or query failures and immediately alerts the engineering on-call channel.

What you get

  • Automated detection of slow SQL queries before they cause production downtime.
  • Continuous validation of PostgreSQL buffer cache hit ratio (alerting when memory is insufficient or indexes are missing).
  • Automated export of slow query diagnostics into downloadable CSV artifacts.
  • Self-healing database statistics refresh (ANALYZE) when performance regressions are detected.
  • Automated incident alerts delivered directly to Slack with report links.
  • Persistent state tracking via Kestra KV store without external monitoring infrastructure.

Who it's for

  • Database Administrators (DBAs) managing PostgreSQL clusters in production.
  • Site Reliability Engineers (SREs) monitoring application database SLAs.
  • Backend Engineers seeking proactive query performance insights.

Why orchestrate this with Kestra

Monitoring query performance directly through declarative Kestra workflows eliminates the need for heavyweight proprietary APM agents. Kestra natively handles database retries, credential isolation via secrets, conditional branching, artifact generation, and key-value state persistence in a single audited YAML pipeline.

Prerequisites

  • PostgreSQL 12 or newer with the pg_stat_statements extension enabled (shared_preload_libraries = 'pg_stat_statements' in postgresql.conf).
  • Database user with SELECT permissions on pg_stat_database and pg_stat_statements, and ANALYZE permissions.
  • Incoming Slack Webhook URL for alerting notifications.

Secrets

  • POSTGRES_JDBC_URL: JDBC connection string (e.g., jdbc:postgresql://postgres.company.internal:5432/postgres).
  • POSTGRES_USERNAME: Database username with read permissions.
  • POSTGRES_PASSWORD: Database user password.
  • SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint for alerting.

Quick start

  1. Configure the POSTGRES_JDBC_URL, POSTGRES_USERNAME, POSTGRES_PASSWORD, and SLACK_WEBHOOK_URL secrets in your Kestra namespace.
  2. Import this blueprint into your Kestra workspace.
  3. Click Execute with default inputs to perform an initial audit.
  4. Verify the generated output logs, download the generated CSV report from the Outputs tab, and inspect the stored record in the KV Store tab.
  5. Enable the hourly schedule trigger for continuous monitoring.

How to extend

  • Add an automated query reset step (SELECT pg_stat_statements_reset();) on a weekly maintenance schedule.
  • Archive periodic slow-query CSV reports to Amazon S3 or MinIO using io.kestra.plugin.aws.s3.Upload.
  • Integrate with PagerDuty or Opsgenie using Kestra's notification plugins for P1 latency incidents.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.