← Back to DevBytes
Developer Tutorial July 18, 2026 13 min read

Monitoring Redshift: Metrics, Alarms, and Dashboards

Understanding Redshift Monitoring

Amazon Redshift is a fully managed petabyte-scale data warehouse service that runs complex analytical queries against large datasets. Monitoring Redshift involves tracking performance metrics, query execution patterns, resource utilization, and system health indicators to ensure your data warehouse operates efficiently and cost-effectively. Unlike transactional databases, Redshift's columnar storage and massively parallel processing (MPP) architecture requires a distinct monitoring approach focused on query throughput, disk utilization, and cluster coordination.

Effective monitoring provides visibility into three critical layers: the cluster layer (hardware resources like CPU, memory, disk I/O), the query layer (execution plans, queue times, concurrency), and the data layer (storage distribution, vacuum operations, table statistics). Each layer generates distinct metrics that collectively paint a complete picture of warehouse health.

Why Redshift Monitoring Matters

Without proper monitoring, Redshift clusters can silently degrade in performance, leading to slow queries, failed ETL jobs, and ballooning costs. Here are the key reasons monitoring is essential:

Key Redshift Metrics to Monitor

Redshift exposes metrics through several channels: AWS CloudWatch (host-level and cluster-level metrics), system tables (query-level and internal diagnostics), and the AWS Management Console. Below are the most important metrics organized by category.

1. Cluster Resource Metrics (CloudWatch)

These metrics are automatically collected and published to CloudWatch for every Redshift cluster. They reflect the physical resource consumption of your compute nodes.

2. Query Performance Metrics (System Tables)

Redshift's internal STL and STV system tables provide granular query-level data. These are not pushed to CloudWatch automatically — you must query them directly or via scheduled scripts.

Querying System Tables for Monitoring Data

The following practical queries extract actionable monitoring data directly from Redshift's system tables. Run these periodically (via cron jobs, Lambda functions, or scheduled queries) to build a comprehensive monitoring pipeline.

Top 10 Longest Recent Queries

SELECT 
    query,
    TRIM(REGEXP_SUBSTR(query_text, '\\n.*', 1, 1, 'e')) AS query_snippet,
    DATEDIFF(seconds, starttime, endtime) AS duration_seconds,
    aborted,
    elapsed_time / 1000000.0 AS elapsed_seconds
FROM STL_QUERY
WHERE starttime >= GETDATE() - INTERVAL '24 hours'
  AND query_text NOT LIKE '%STL_%' 
  AND query_text NOT LIKE '%SVL_%'
  AND query_text NOT LIKE '%pg_%'
ORDER BY duration_seconds DESC
LIMIT 10;

This query filters out internal system queries to surface only user-generated work. The REGEXP_SUBSTR extracts the first meaningful line of the SQL for quick identification.

WLM Queue Wait Time Analysis

SELECT 
    service_class,
    query,
    DATEDIFF(milliseconds, queue_starttime, queue_endtime) AS queue_wait_ms,
    DATEDIFF(milliseconds, queue_endtime, endtime) AS exec_time_ms,
    CASE 
        WHEN DATEDIFF(milliseconds, queue_starttime, queue_endtime) > 60000 
        THEN 'HIGH_WAIT'
        ELSE 'NORMAL'
    END AS wait_flag
FROM STL_WLM_QUERY
WHERE queue_starttime >= GETDATE() - INTERVAL '6 hours'
ORDER BY queue_wait_ms DESC
LIMIT 20;

This reveals whether queries are spending excessive time waiting for execution slots. Consistent HIGH_WAIT flags indicate that your WLM concurrency settings need adjustment.

Disk Usage by Table (Top Consumers)

SELECT 
    database,
    schema,
    table_id,
    "table" AS table_name,
    size AS bytes,
    size / 1024 / 1024 AS mb,
    size / 1024 / 1024 / 1024 AS gb,
    tbl_rows
FROM SVV_TABLE_INFO
WHERE "table" NOT LIKE '%_pqt_%'  -- exclude internal tables
ORDER BY size DESC
LIMIT 20;

Identifying the largest tables helps prioritize compression optimization (using ANALYZE COMPRESSION) and evaluate distribution key choices.

Table Distribution Skew Detection

SELECT 
    "schema",
    "table",
    diststyle,
    skew_rows,
    sliced_rows,
    CASE 
        WHEN skew_rows > 4.0 THEN 'CRITICAL_SKEW'
        WHEN skew_rows > 2.0 THEN 'MODERATE_SKEW'
        ELSE 'HEALTHY'
    END AS skew_status
FROM SVV_TABLE_INFO
WHERE diststyle NOT IN ('AUTO(ALL)', 'AUTO(EVEN)')
  AND skew_rows IS NOT NULL
ORDER BY skew_rows DESC;

Skew ratios above 4 indicate that some slices hold significantly more data than others, causing uneven query workload distribution and slower overall performance.

Active Connections and Session Load

SELECT 
    COUNT(*) AS total_connections,
    COUNT(CASE WHEN current_query <> '' THEN 1 END) AS active_queries,
    MAX(DATEDIFF(minutes, query_start, GETDATE())) AS longest_running_minutes
FROM STV_SESSIONS
WHERE user_name NOT IN ('rdsdb', 'rdsadmin');

This quick health check reveals current concurrency pressure and helps identify runaway sessions that may need termination.

Setting Up CloudWatch Alarms

CloudWatch Alarms are the primary mechanism for automated alerting on Redshift metrics. They trigger when a metric crosses a threshold and can send notifications via Amazon SNS (email, SMS, Lambda invocation, or third-party integrations like PagerDuty or Slack).

Creating Alarms via AWS CLI

The following command creates a disk space alarm that triggers when PercentageDiskSpaceUsed exceeds 85% for a sustained 15-minute period (3 consecutive 5-minute evaluation periods):

aws cloudwatch put-metric-alarm \
  --alarm-name "Redshift-DiskSpace-Critical" \
  --alarm-description "Alert when Redshift disk usage exceeds 85%" \
  --namespace "AWS/Redshift" \
  --metric-name "PercentageDiskSpaceUsed" \
  --statistic "Average" \
  --period 300 \
  --evaluation-periods 3 \
  --threshold 85 \
  --comparison-operator "GreaterThanThreshold" \
  --dimensions "Name=ClusterIdentifier,Value=my-redshift-cluster" \
  --alarm-actions "arn:aws:sns:us-east-1:123456789012:RedshiftAlerts" \
  --treat-missing-data "breaching"

Key parameters explained:

CPU Utilization Alarm (High Severity)

aws cloudwatch put-metric-alarm \
  --alarm-name "Redshift-HighCPU" \
  --alarm-description "CPU utilization sustained above 90% for 20 minutes" \
  --namespace "AWS/Redshift" \
  --metric-name "CPUUtilization" \
  --statistic "Average" \
  --period 300 \
  --evaluation-periods 4 \
  --threshold 90 \
  --comparison-operator "GreaterThanThreshold" \
  --dimensions "Name=ClusterIdentifier,Value=my-redshift-cluster" \
  --alarm-actions "arn:aws:sns:us-east-1:123456789012:RedshiftAlerts" \
  --treat-missing-data "breaching"

Health Status Alarm (Critical)

aws cloudwatch put-metric-alarm \
  --alarm-name "Redshift-HealthCheck-Failed" \
  --alarm-description "Cluster health status indicates impairment" \
  --namespace "AWS/Redshift" \
  --metric-name "HealthStatus" \
  --statistic "Minimum" \
  --period 60 \
  --evaluation-periods 1 \
  --threshold 1 \
  --comparison-operator "LessThanThreshold" \
  --dimensions "Name=ClusterIdentifier,Value=my-redshift-cluster" \
  --alarm-actions "arn:aws:sns:us-east-1:123456789012:RedshiftAlerts" \
  --treat-missing-data "breaching"

Since HealthStatus is 1 for healthy and 0 for impaired, we use LessThanThreshold with threshold 1 to catch any degradation immediately (60-second period, single evaluation).

Provisioning an SNS Topic for Alarm Notifications

# Create the SNS topic
aws sns create-topic --name "RedshiftAlerts"

# Subscribe an email endpoint
aws sns subscribe \
  --topic-arn "arn:aws:sns:us-east-1:123456789012:RedshiftAlerts" \
  --protocol "email" \
  --notification-endpoint "dba-team@example.com"

# Subscribe a Lambda function for automated remediation
aws sns subscribe \
  --topic-arn "arn:aws:sns:us-east-1:123456789012:RedshiftAlerts" \
  --protocol "lambda" \
  --notification-endpoint "arn:aws:lambda:us-east-1:123456789012:function:RedshiftAutoRemediator"

Building CloudWatch Dashboards

CloudWatch Dashboards provide a unified visual interface for monitoring Redshift metrics alongside related infrastructure. Dashboards are defined as JSON structures containing widgets (graphs, text, metric queries) arranged in a grid layout. You can create them via the AWS Console, CLI, or SDK.

Complete Dashboard JSON Definition

Below is a production-ready dashboard definition that covers the most critical Redshift monitoring dimensions. Save this as redshift-dashboard.json and deploy via CLI:

{
  "widgets": [
    {
      "type": "metric",
      "x": 0,
      "y": 0,
      "width": 8,
      "height": 6,
      "properties": {
        "view": "timeSeries",
        "stacked": false,
        "region": "us-east-1",
        "title": "CPU Utilization",
        "metrics": [
          [ "AWS/Redshift", "CPUUtilization", { "stat": "Average" } ],
          [ ".", "CPUUtilization", { "stat": "p95" } ],
          [ ".", "CPUUtilization", { "stat": "Maximum" } ]
        ],
        "period": 300,
        "yAxis": { "left": { "min": 0, "max": 100 } },
        "annotations": {
          "horizontal": [
            { "value": 80, "label": "Warning 80%", "color": "#ff9900" },
            { "value": 95, "label": "Critical 95%", "color": "#d13212" }
          ]
        }
      }
    },
    {
      "type": "metric",
      "x": 8,
      "y": 0,
      "width": 8,
      "height": 6,
      "properties": {
        "view": "timeSeries",
        "stacked": false,
        "region": "us-east-1",
        "title": "Disk Space Used (%)",
        "metrics": [
          [ "AWS/Redshift", "PercentageDiskSpaceUsed", { "stat": "Average" } ]
        ],
        "period": 300,
        "yAxis": { "left": { "min": 0, "max": 100 } },
        "annotations": {
          "horizontal": [
            { "value": 70, "label": "Warning 70%", "color": "#ff9900" },
            { "value": 85, "label": "Critical 85%", "color": "#d13212" }
          ]
        }
      }
    },
    {
      "type": "metric",
      "x": 0,
      "y": 6,
      "width": 8,
      "height": 6,
      "properties": {
        "view": "timeSeries",
        "stacked": false,
        "region": "us-east-1",
        "title": "Read/Write IOPS",
        "metrics": [
          [ "AWS/Redshift", "ReadIOPS", { "stat": "Sum", "color": "#1f77b4" } ],
          [ ".", "WriteIOPS", { "stat": "Sum", "color": "#ff7f0e" } ]
        ],
        "period": 300
      }
    },
    {
      "type": "metric",
      "x": 8,
      "y": 6,
      "width": 8,
      "height": 6,
      "properties": {
        "view": "timeSeries",
        "stacked": false,
        "region": "us-east-1",
        "title": "Network Throughput (Bytes/s)",
        "metrics": [
          [ "AWS/Redshift", "NetworkThroughput", { "stat": "Average" } ]
        ],
        "period": 300
      }
    },
    {
      "type": "metric",
      "x": 0,
      "y": 12,
      "width": 16,
      "height": 6,
      "properties": {
        "view": "timeSeries",
        "stacked": false,
        "region": "us-east-1",
        "title": "Health Status & Maintenance Mode",
        "metrics": [
          [ "AWS/Redshift", "HealthStatus", { "stat": "Minimum", "color": "#2ca02c" } ],
          [ ".", "MaintenanceMode", { "stat": "Maximum", "color": "#d62728" } ]
        ],
        "period": 60,
        "yAxis": { "left": { "min": 0, "max": 1.5 } }
      }
    },
    {
      "type": "text",
      "x": 0,
      "y": 18,
      "width": 16,
      "height": 3,
      "properties": {
        "markdown": "## Redshift Cluster Monitoring Dashboard\n\n**Cluster:** my-redshift-cluster | **Region:** us-east-1 | **Last Updated:** Auto-refresh every 5 min\n\nUse this dashboard to monitor cluster health, resource utilization, and query performance trends. Cross-reference with STL table queries for granular diagnostics."
      }
    }
  ]
}

Deploying the Dashboard via CLI

aws cloudwatch put-dashboard \
  --dashboard-name "Redshift-Monitoring" \
  --dashboard-body file://redshift-dashboard.json

After deployment, the dashboard appears in the CloudWatch console under "Dashboards." You can also embed these dashboards in operational portals using the CloudWatch Dashboard API or share read-only access via IAM policies.

Adding Custom Metrics to the Dashboard

To surface query-level metrics (queue wait times, long-running queries) on the dashboard, publish custom metrics from a scheduled Lambda function that queries Redshift system tables:

import boto3
import psycopg2
from datetime import datetime

def publish_custom_metrics(event, context):
    # Connect to Redshift
    conn = psycopg2.connect(
        host='my-redshift-cluster.abc123.us-east-1.redshift.amazonaws.com',
        port=5439,
        database='dev',
        user='monitoring_user',
        password='secure_password'
    )
    cursor = conn.cursor()
    
    # Query for active queue wait times
    cursor.execute("""
        SELECT 
            COALESCE(MAX(DATEDIFF(seconds, queue_starttime, queue_endtime)), 0)
        FROM STL_WLM_QUERY
        WHERE queue_endtime >= GETDATE() - INTERVAL '5 minutes'
          AND queue_starttime IS NOT NULL
    """)
    max_queue_wait = cursor.fetchone()[0]
    
    # Query for long-running queries (> 300 seconds)
    cursor.execute("""
        SELECT COUNT(*)
        FROM STV_RECRLY
        WHERE DATEDIFF(seconds, query_start, GETDATE()) > 300
    """)
    long_running_count = cursor.fetchone()[0]
    
    cursor.close()
    conn.close()
    
    # Publish to CloudWatch
    cw = boto3.client('cloudwatch')
    cw.put_metric_data(
        Namespace='Redshift/Custom',
        MetricData=[
            {
                'MetricName': 'MaxQueueWaitSeconds',
                'Value': max_queue_wait,
                'Unit': 'Seconds',
                'Timestamp': datetime.utcnow()
            },
            {
                'MetricName': 'LongRunningQueries',
                'Value': long_running_count,
                'Unit': 'Count',
                'Timestamp': datetime.utcnow()
            }
        ]
    )
    
    return {
        'statusCode': 200,
        'body': f'Published metrics: queue_wait={max_queue_wait}s, long_running={long_running_count}'
    }

After publishing custom metrics, add them to the dashboard with metric queries targeting Redshift/Custom namespace:

# Example widget snippet for custom metrics dashboard JSON
{
  "type": "metric",
  "x": 0, "y": 24, "width": 8, "height": 6,
  "properties": {
    "view": "timeSeries",
    "region": "us-east-1",
    "title": "Max WLM Queue Wait (seconds)",
    "metrics": [
      [ "Redshift/Custom", "MaxQueueWaitSeconds", { "stat": "Maximum" } ]
    ],
    "period": 300
  }
}

Automating Monitoring with Scheduled Queries

Redshift now supports scheduled queries natively via the Redshift Data API or the console. This lets you run monitoring queries on a cron-like schedule and optionally export results to S3 or trigger notifications.

Creating a Scheduled Monitoring Query via CLI

aws redshift-data create-schedule \
  --schedule-expression "cron(0/5 * * * ? *)" \
  --schedule-description "Every 5 minutes: check for long-running queries" \
  --database "dev" \
  --sql "INSERT INTO monitoring.long_query_log 
         SELECT query, DATEDIFF(minutes, starttime, GETDATE()) 
         FROM STV_RECRLY 
         WHERE DATEDIFF(minutes, starttime, GETDATE()) > 10;" \
  --role-arn "arn:aws:iam::123456789012:role/RedshiftScheduledQueryRole" \
  --schedule-name "monitor-long-queries" \
  --cluster-identifier "my-redshift-cluster" \
  --enabled

This schedules a query that logs any query running longer than 10 minutes into a monitoring table every 5 minutes. You can then build alarms around the row count in that table.

Integrating with Third-Party Monitoring Tools

While CloudWatch provides the native monitoring foundation, many teams augment it with specialized observability platforms. The integration pattern typically involves:

Enabling Audit Logging for Query-Level Visibility

# Enable audit logging on the cluster
aws redshift enable-logging \
  --cluster-identifier "my-redshift-cluster" \
  --bucket-name "my-redshift-logs-bucket" \
  --s3-key-prefix "audit-logs/" \
  --log-destination-type "s3" \
  --log-exports "connectionlog" "userlog" "useractivitylog"

Once enabled, all queries (including user, connection, and activity logs) are delivered to S3. You can build Athena tables over these logs for historical analysis and join them with CloudWatch metric data for complete observability.

Best Practices for Redshift Monitoring

Conclusion

Monitoring Redshift is a multi-layered discipline that spans infrastructure metrics via CloudWatch, query-level diagnostics through system tables, and user activity tracking through audit logs. By instrumenting all three layers with well-configured alarms and dashboards, you gain the visibility needed to maintain performance, control costs, and respond rapidly to issues. Start with the essential CloudWatch alarms — disk space, CPU, and health status — then progressively layer in custom metrics from system table queries as your operational maturity grows. The combination of automated alerting, rich dashboards, and scheduled diagnostic queries transforms Redshift monitoring from a reactive chore into a proactive engineering practice that keeps your data warehouse fast, reliable, and cost-efficient.

🚀 Need a reliable AI agent for your project?

Deploy Hermes Agent in 10 minutes. Managed hosting, zero DevOps.

Get Started — $23.99/mo
← Back to all articles