Monitoring Amazon Redshift lock contention and slow queries with custom CloudWatch alarms

Overview

Most Amazon Redshift monitoring starts with the native CloudWatch metrics, and they cover: CPUUtilization, PercentageDiskSpaceUsed, QueryDuration, WLMQueueLength, and more. Two signals that operators reach for during an incident live one level deeper—in Redshift's system tables rather than the native metric set:

• How many transactions are waiting on a lock right now? Lock contention is one of the most common causes of a query that appears "stuck." Redshift tracks lock state precisely in system tables such as STV_LOCKS, SVV_TRANSACTIONS, and STL_TR_CONFLICT.
• Has a single, specific query been running too long? The native QueryDuration metric is a 5-minute, cluster-wide average reported per latency band—ideal for trend and latency-band analysis, but by design an average smooths over the individual query you care about. The exact runtime of each running query is available in SYS_QUERY_HISTORY.

Because Redshift exposes both signals through its system tables, you can promote them into CloudWatch: run two lightweight queries on a schedule, publish the results as custom CloudWatch metrics, and put ordinary CloudWatch alarms on them—so they alarm, dashboard, and roll into composite alarms right alongside the native metrics. This article is a practical guide to setting that up on an Amazon Redshift provisioned cluster—the architecture, the exact SQL, the poller code, the IAM model, the alarms, and how to verify it works. Infrastructure-as-code (CloudFormation and CDK) is included at the end.

**A note: The current, supported way to inspect lock state is the STV_LOCKS table (live locks and their holders/waiters), together with SVV_TRANSACTIONS (open transactions and contention) and STL_TR_CONFLICT (a historical log of lock conflicts). This article uses those views.

How this relates to the existing "Create custom alerts in Amazon Redshift" framework.

AWS already publishes a canonical, config-driven pattern for custom Redshift alerts (Create custom alerts in Amazon Redshift), where an in-database stored procedure reads system tables against a configuration table and calls a Lambda UDF to notify Amazon SNS directly. That framework is oriented toward user-action auditing—large UNLOADs, attempted table drops, frequent reads of a sensitive table—and its output is a notification. This article is complementary and targets cluster-health signals instead (lock contention and query runtime), and it takes the results one step further by publishing them as CloudWatch metrics rather than sending straight to SNS. Emitting metrics is the key difference: it gives you CloudWatch alarms with thresholds and M-of-N evaluation, dashboards, metric history, and composite alarms that combine these signals with the native ones—none of which a direct-to-SNS notification provides. Choose the stored-procedure framework when you want in-database, per-table auditing routed to SNS; use this pattern when you want the signal to behave like any other CloudWatch metric. If you already run that framework, you can also add lock-wait and long-running-query checks to it directly—the two approaches are not mutually exclusive.

Setup (once)

You wire this up once; after that it runs on a schedule and every alarm reuses it. Four steps:

• Create a least-privilege monitoring identity. System tables like STV_LOCKS are visible only to superusers, and SYSLOG ACCESS UNRESTRICTED alone does not change that—it only widens which users' rows you can see in the log-style views, so a non-superuser would query STV_LOCKS successfully but get 0 rows, silently zeroing your LockWaitCount metric. To get system-table visibility without making the poller a superuser, grant the built-in sys:monitor role, which has permission to access catalog and system tables:

  -- Identity usable only via IAM temporary credentials (no password login)
  CREATE USER cw_monitor PASSWORD DISABLE;
  
  -- Built-in system role: read access to catalog and system tables (note the colon prefix)
  GRANT ROLE "sys:monitor" TO cw_monitor;
  
  -- Optional: also see other users' rows in log-style system views
  ALTER USER cw_monitor SYSLOG ACCESS UNRESTRICTED;

  PASSWORD DISABLE means this user cannot log in with a password—it is reachable only through Data API temporary credentials, which the next step's IAM policy authorizes via redshift:GetClusterCredentials.

• Choose how the poller reaches Redshift. The cleanest option is the Amazon Redshift Data API (redshift-data): it issues SQL over a regional endpoint with temporary IAM credentials, so the cluster stays private and the Lambda function needs no VPC attachment or database driver.

• Deploy the poller — an AWS Lambda function that runs the two queries and calls cloudwatch:PutMetricData. Code is in the next section.

• Schedule it with an Amazon EventBridge Scheduler schedule (a 1-minute rate is a good default) and route the resulting alarms to an Amazon SNS topic for email or pager delivery.

The monitoring queries

You never change your workload—these two read-only queries are the whole data-collection story. Run each once by hand first and confirm it returns quickly.

Lock waiters. SVV_TRANSACTIONS is the recommended view for identifying contention and reports whether each lockable object has been granted:

-- Number of transactions currently blocked waiting on a lock
SELECT COUNT(*) AS lock_wait_count
FROM svv_transactions
WHERE lockable_object_type IS NOT NULL
  AND granted = false;

If you prefer STV_LOCKS directly (superuser only), count rows whose lock_status indicates a wait:

SELECT COUNT(*) AS lock_wait_count
FROM stv_locks
WHERE lock_status ILIKE '%waiting%';

Longest running query. Because QueryDuration is an average, query the running statements directly. On current Redshift, the SYS_QUERY_HISTORY view exposes active queries:

-- Longest currently-running query, in seconds
SELECT COALESCE(MAX(EXTRACT(EPOCH FROM (GETDATE() - start_time))), 0)
       AS max_running_query_seconds
FROM sys_query_history
WHERE status = 'running';

On older clusters, STV_RECENTS gives the same signal:

SELECT COALESCE(MAX(DATEDIFF(second, starttime, GETDATE())), 0)
       AS max_running_query_seconds
FROM stv_recents
WHERE status = 'Running';

Watch the status casing—it differs by view. In SYS_QUERY_HISTORY the value is lowercase 'running' (per the AWS documentation, valid values are planning, queued, running, returning, failed, canceled, success), while STV_RECENTS uses capitalized 'Running'. Matching the wrong case returns 0 rows and makes MaxRunningQuerySeconds always report 0.

The poller (provisioned cluster)

This Python Lambda uses the Redshift Data API, runs both queries, reads the scalar results, and publishes two metrics dimensioned by cluster identifier. Set CLUSTER_ID, DATABASE, and DB_USER (cw_monitor) as environment variables.

import os
import time
import boto3

redshift_data = boto3.client("redshift-data")
cloudwatch = boto3.client("cloudwatch")

CLUSTER_ID = os.environ["CLUSTER_ID"]
DATABASE = os.environ["DATABASE"]
DB_USER = os.environ["DB_USER"]
NAMESPACE = "Custom/Redshift"

LOCK_WAIT_SQL = """
    SELECT COUNT(*)
    FROM svv_transactions
    WHERE lockable_object_type IS NOT NULL
      AND granted = false;
"""

MAX_RUNTIME_SQL = """
    SELECT COALESCE(MAX(EXTRACT(EPOCH FROM (GETDATE() - start_time))), 0)
    FROM sys_query_history
    WHERE status = 'running';
"""


def run_scalar(sql):
    """Execute a single-value query via the Redshift Data API.

    Numeric scalars only: parses long/string/double cells to float. A query
    returning a boolean or other type would fall through and yield 0.0.
    """
    stmt = redshift_data.execute_statement(
        ClusterIdentifier=CLUSTER_ID,
        Database=DATABASE,
        DbUser=DB_USER,
        Sql=sql,
    )
    statement_id = stmt["Id"]

    while True:  # poll until the statement finishes
        desc = redshift_data.describe_statement(Id=statement_id)
        status = desc["Status"]
        if status == "FINISHED":
            break
        if status in ("FAILED", "ABORTED"):
            raise RuntimeError(f"Query {status}: {desc.get('Error')}")
        time.sleep(0.25)

    result = redshift_data.get_statement_result(Id=statement_id)

    # Empty payload (shouldn't happen for these aggregate queries, but be safe)
    if not result.get("Records"):
        return 0.0

    # The Data API returns a typed cell. COUNT(*) comes back as longValue;
    # EXTRACT(EPOCH ...) / COALESCE arithmetic comes back as stringValue
    # (not doubleValue), so check each typed key explicitly.
    cell = result["Records"][0][0]
    if cell.get("isNull"):
        return 0.0
    if "longValue" in cell:
        return float(cell["longValue"])
    if "stringValue" in cell:
        return float(cell["stringValue"])
    if "doubleValue" in cell:
        return float(cell["doubleValue"])
    return 0.0


def put_metric(name, value, unit):
    cloudwatch.put_metric_data(
        Namespace=NAMESPACE,
        MetricData=[{
            "MetricName": name,
            "Dimensions": [{"Name": "ClusterIdentifier", "Value": CLUSTER_ID}],
            "Value": value,
            "Unit": unit,
        }],
    )


def handler(event, context):
    lock_waiters = run_scalar(LOCK_WAIT_SQL)
    max_runtime = run_scalar(MAX_RUNTIME_SQL)
    put_metric("LockWaitCount", lock_waiters, "Count")
    put_metric("MaxRunningQuerySeconds", max_runtime, "Seconds")
    return {"LockWaitCount": lock_waiters, "MaxRunningQuerySeconds": max_runtime}

Publishing a metric on every run—including a 0 when there is no contention—keeps the metric continuous, so an alarm can distinguish "healthy" from "no data."

Because SVV_TRANSACTIONS and SYS_QUERY_HISTORY show a non-privileged user only their own rows, the sys:monitor role granted during setup is what lets the poller see cluster-wide activity—so the counts reflect every session rather than just the monitoring user's.

IAM permissions

The Lambda execution role needs to call the Data API and publish metrics. Scope resources to your cluster and monitoring namespace.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "RedshiftDataApi",
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult"
      ],
      "Resource": "*"
    },
    {
      "Sid": "TempCredentials",
      "Effect": "Allow",
      "Action": "redshift:GetClusterCredentials",
      "Resource": [
        "arn:aws:redshift:REGION:ACCOUNT_ID:dbuser:my-cluster/cw_monitor",
        "arn:aws:redshift:REGION:ACCOUNT_ID:dbname:my-cluster/my-db"
      ]
    },
    {
      "Sid": "PublishMetrics",
      "Effect": "Allow",
      "Action": "cloudwatch:PutMetricData",
      "Condition": { "StringEquals": { "cloudwatch:namespace": "Custom/Redshift" } },
      "Resource": "*"
    }
  ]
}

cloudwatch:PutMetricData does not support resource-level permissions, so it must use "Resource": "*"—the Condition on the namespace is the recommended way to constrain it. In the TempCredentials ARNs, replace my-cluster, cw_monitor, and my-db with your actual cluster identifier, monitoring user, and database name; the dbuser:/dbname: prefixes and the cluster identifier are lowercase, matching the --dimensions Name=ClusterIdentifier,Value=my-cluster used in the alarms below.

Schedule and alarms

Schedule the poller with EventBridge Scheduler (rate(1 minute)), then create an SNS topic and two alarms.

Lock contention—fire when one or more transactions have been waiting across 3 consecutive minutes (avoids flapping on brief, self-clearing contention):

aws cloudwatch put-metric-alarm \
  --alarm-name "Redshift-LockContention" \
  --namespace "Custom/Redshift" --metric-name "LockWaitCount" \
  --dimensions Name=ClusterIdentifier,Value=my-cluster \
  --statistic Maximum --period 60 \
  --evaluation-periods 3 --datapoints-to-alarm 3 \
  --threshold 1 --comparison-operator GreaterThanOrEqualToThreshold \
  --treat-missing-data notBreaching \
  --alarm-actions arn:aws:sns:REGION:ACCOUNT_ID:redshift-alerts

Long-running query—fire when the longest running query exceeds 15 minutes (900 seconds):

aws cloudwatch put-metric-alarm \
  --alarm-name "Redshift-LongRunningQuery" \
  --namespace "Custom/Redshift" --metric-name "MaxRunningQuerySeconds" \
  --dimensions Name=ClusterIdentifier,Value=my-cluster \
  --statistic Maximum --period 60 \
  --evaluation-periods 2 --datapoints-to-alarm 2 \
  --threshold 900 --comparison-operator GreaterThanThreshold \
  --treat-missing-data notBreaching \
  --alarm-actions arn:aws:sns:REGION:ACCOUNT_ID:redshift-alerts

Tune the thresholds to your workload: a reporting cluster where multi-minute queries are normal wants a higher MaxRunningQuerySeconds threshold than an OLTP-style ingestion cluster.

Verifying it works

• Trigger a lock wait. In one session, BEGIN; UPDATE ...; a table and leave it uncommitted. In a second session, run a conflicting ALTER TABLE or update on the same table—it blocks.

• Confirm the metric appears after one polling interval:

  aws cloudwatch get-metric-statistics \
    --namespace "Custom/Redshift" --metric-name "LockWaitCount" \
    --dimensions Name=ClusterIdentifier,Value=my-cluster \
    --start-time "$(date -u -d '10 minutes ago' +%Y-%m-%dT%H:%M:%SZ)" \
    --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
    --period 60 --statistics Maximum

• Clear it. COMMIT or ROLLBACK the blocking transaction and confirm the metric returns to 0 and the alarm goes back to OK.

Deploy the whole stack with infrastructure as code

For a repeatable deployment, provision the SNS topic, IAM roles, Lambda poller, EventBridge Scheduler schedule, and both alarms together. A complete CloudFormation template is below (Lambda inlined for readability—package from source for anything beyond a demo).

AWSTemplateFormatVersion: "2010-09-09"
Description: Redshift lock and long-query custom CloudWatch monitoring.

Parameters:
  ClusterIdentifier: { Type: String }
  Database: { Type: String, Default: dev }
  DbUser: { Type: String, Default: cw_monitor }
  NotificationEmail: { Type: String }
  LockWaitThreshold: { Type: Number, Default: 1 }
  LongQuerySecondsThreshold: { Type: Number, Default: 900 }

Resources:
  AlertsTopic:
    Type: AWS::SNS::Topic
    Properties: { TopicName: redshift-alerts }

  AlertsSubscription:
    Type: AWS::SNS::Subscription
    Properties:
      TopicArn: !Ref AlertsTopic
      Protocol: email
      Endpoint: !Ref NotificationEmail

  PollerRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal: { Service: lambda.amazonaws.com }
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
      Policies:
        - PolicyName: redshift-monitor
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - redshift-data:ExecuteStatement
                  - redshift-data:DescribeStatement
                  - redshift-data:GetStatementResult
                Resource: "*"
              - Effect: Allow
                Action: redshift:GetClusterCredentials
                Resource:
                  - !Sub arn:aws:redshift:${AWS::Region}:${AWS::AccountId}:dbuser:${ClusterIdentifier}/${DbUser}
                  - !Sub arn:aws:redshift:${AWS::Region}:${AWS::AccountId}:dbname:${ClusterIdentifier}/${Database}
              - Effect: Allow
                Action: cloudwatch:PutMetricData
                Resource: "*"
                Condition:
                  StringEquals: { "cloudwatch:namespace": "Custom/Redshift" }

  PollerFunction:
    Type: AWS::Lambda::Function
    Properties:
      FunctionName: redshift-lock-monitor
      Runtime: python3.12
      Handler: index.handler
      Timeout: 30
      Role: !GetAtt PollerRole.Arn
      Environment:
        Variables:
          CLUSTER_ID: !Ref ClusterIdentifier
          DATABASE: !Ref Database
          DB_USER: !Ref DbUser
      Code:
        ZipFile: |
          import os, time, boto3
          rd = boto3.client("redshift-data")
          cw = boto3.client("cloudwatch")
          CID = os.environ["CLUSTER_ID"]; DB = os.environ["DATABASE"]; USER = os.environ["DB_USER"]
          NS = "Custom/Redshift"
          LOCK_SQL = ("SELECT COUNT(*) FROM svv_transactions "
                      "WHERE lockable_object_type IS NOT NULL AND granted = false;")
          RUNTIME_SQL = ("SELECT COALESCE(MAX(EXTRACT(EPOCH FROM (GETDATE()-start_time))),0) "
                         "FROM sys_query_history WHERE status='running';")
          def scalar(sql):
              sid = rd.execute_statement(ClusterIdentifier=CID, Database=DB, DbUser=USER, Sql=sql)["Id"]
              while True:
                  d = rd.describe_statement(Id=sid)
                  if d["Status"] == "FINISHED": break
                  if d["Status"] in ("FAILED","ABORTED"): raise RuntimeError(d.get("Error"))
                  time.sleep(0.25)
              recs = rd.get_statement_result(Id=sid).get("Records")
              if not recs: return 0.0
              c = recs[0][0]
              if c.get("isNull"): return 0.0
              for k in ("longValue", "stringValue", "doubleValue"):
                  if k in c: return float(c[k])
              return 0.0
          def put(name, val, unit):
              cw.put_metric_data(Namespace=NS, MetricData=[{
                  "MetricName": name, "Dimensions": [{"Name":"ClusterIdentifier","Value":CID}],
                  "Value": val, "Unit": unit}])
          def handler(event, context):
              lw = scalar(LOCK_SQL)
              mr = scalar(RUNTIME_SQL)
              put("LockWaitCount", lw, "Count")
              put("MaxRunningQuerySeconds", mr, "Seconds")
              return {"LockWaitCount": lw, "MaxRunningQuerySeconds": mr}

  SchedulerInvokeRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal: { Service: scheduler.amazonaws.com }
            Action: sts:AssumeRole
            Condition:
              StringEquals: { "aws:SourceAccount": !Ref "AWS::AccountId" }
      Policies:
        - PolicyName: invoke-poller
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action: lambda:InvokeFunction
                Resource: !GetAtt PollerFunction.Arn

  PollerSchedule:
    Type: AWS::Scheduler::Schedule
    Properties:
      Name: redshift-lock-monitor
      FlexibleTimeWindow: { Mode: "OFF" }
      ScheduleExpression: rate(1 minute)
      Target:
        Arn: !GetAtt PollerFunction.Arn
        RoleArn: !GetAtt SchedulerInvokeRole.Arn

  LockContentionAlarm:
    Type: AWS::CloudWatch::Alarm
    Properties:
      AlarmName: Redshift-LockContention
      Namespace: Custom/Redshift
      MetricName: LockWaitCount
      Dimensions:
        - { Name: ClusterIdentifier, Value: !Ref ClusterIdentifier }
      Statistic: Maximum
      Period: 60
      EvaluationPeriods: 3
      DatapointsToAlarm: 3
      Threshold: !Ref LockWaitThreshold
      ComparisonOperator: GreaterThanOrEqualToThreshold
      TreatMissingData: notBreaching
      AlarmActions: [!Ref AlertsTopic]

  LongQueryAlarm:
    Type: AWS::CloudWatch::Alarm
    Properties:
      AlarmName: Redshift-LongRunningQuery
      Namespace: Custom/Redshift
      MetricName: MaxRunningQuerySeconds
      Dimensions:
        - { Name: ClusterIdentifier, Value: !Ref ClusterIdentifier }
      Statistic: Maximum
      Period: 60
      EvaluationPeriods: 2
      DatapointsToAlarm: 2
      Threshold: !Ref LongQuerySecondsThreshold
      ComparisonOperator: GreaterThanThreshold
      TreatMissingData: notBreaching
      AlarmActions: [!Ref AlertsTopic]

Outputs:
  AlertsTopicArn:
    Value: !Ref AlertsTopic

If you prefer AWS CDK, the same stack is a handful of constructs—an sns.Topic with an EmailSubscription, a lambda.Function with two PolicyStatements (the Data API actions and a namespace-scoped PutMetricData), a scheduler.CfnSchedule (or the aws-scheduler-alpha Schedule construct) on a 1-minute rate with a Lambda target and an invoke role, and two cloudwatch.Alarms with datapointsToAlarm and TreatMissingData.NOT_BREACHING, each wired to an SnsAction.

Security

Control	Why it matters
Read-only queries	The poller only runs SELECTs against system views—it reads evidence and never mutates data or schema.
Least-privilege identity	Use a dedicated cw_monitor user (or role) granted the built-in sys:monitor role, not an admin/superuser login used for other work.
IAM-based DB auth	The Redshift Data API issues temporary, IAM-scoped credentials—no stored database password.
Namespace-scoped metrics	PutMetricData is constrained with a cloudwatch:namespace condition so the role can publish only to Custom/Redshift.
Private cluster	The Data API is a regional endpoint, so the cluster stays private—no public accessibility or Lambda VPC attachment required.

Cost and operational considerations

• Custom metrics are billed per metric per month plus PutMetricData calls. Two metrics polled once a minute is inexpensive; if you fan out one metric per query or per table, the metric count—and cost—grows fast. Prefer a small number of aggregate metrics (a max/count) over one metric per query.
• System-table load. The two queries are lightweight, but polling system tables every few seconds on a busy cluster adds overhead. One minute is a good balance.
• Missing data. Publishing an explicit 0 each cycle plus treat-missing-data notBreaching prevents false alarms when the poller has a transient failure, while still surfacing genuine contention.

Why it helps

• Alarms on the signal, not a proxy. Lock waits and per-query runtime are exactly the conditions behind "my query is hung"—this alarms on them directly instead of inferring from CPU or an averaged QueryDuration.
• No workload changes. The poller only reads system tables; your queries, schemas, and WLM configuration are untouched.
• Cheap and self-contained. Two metrics, one small Lambda, two alarms—single-digit dollars a month, deployable from the included template.
• Reusable pattern. "Query a system table on a schedule → publish a custom metric → alarm" generalizes to any in-database signal CloudWatch doesn't expose natively.

Related resources

• Create custom alerts in Amazon Redshift — AWS's config-driven, in-database framework for custom Redshift alerts (stored procedure + Lambda UDF → SNS); complementary to the CloudWatch-metric approach in this article
• Monitoring Amazon Redshift cluster performance — which signals are published as CloudWatch metrics vs. query/load data that is not
• Amazon Redshift system tables and views used here:
  • SVV_TRANSACTIONS — open transactions and lock contention (granted, lockable_object_type)
  • STV_LOCKS — current table locks and their holders/waiters
  • STL_TR_CONFLICT — historical log of transaction/lock conflicts
  • SYS_QUERY_HISTORY — running and finished queries (status, start_time)
  • STV_RECENTS — active and recently run queries (older-cluster fallback)
• Using the Amazon Redshift Data API — run SQL over a regional endpoint with no persistent connection
• Publish custom metrics (PutMetricData) — namespaces, dimensions, high-resolution metrics, and publishing zero
• Using Amazon CloudWatch alarms — metric alarms, thresholds, and SNS actions
• Amazon EventBridge Scheduler — the recommended way to invoke the poller on a rate() schedule (scheduled rules are now a legacy EventBridge feature)