Troubleshooting Redshift: Common Issues and Solutions

Introduction to Troubleshooting Redshift

Amazon Redshift is a fully managed, petabyte-scale data warehouse service designed for high-performance analysis and reporting. While Redshift is engineered for scale and speed, production workloads inevitably surface issues ranging from slow queries and table locks to storage pressure and concurrency bottlenecks. Troubleshooting Redshift effectively requires a solid understanding of its internal architecture—compute nodes, leader node, slices, and columnar storage—as well as familiarity with the system tables and diagnostic queries that expose runtime behavior.

This tutorial walks through the most common Redshift issues developers and data engineers encounter, explains why they occur, and provides ready-to-run SQL diagnostics and remediation steps. By the end, you will have a practical toolkit for identifying, diagnosing, and resolving the issues that most frequently degrade Redshift performance and reliability.

Why Troubleshooting Redshift Matters

Redshift powers mission-critical analytics for many organizations. When queries slow down or fail, downstream dashboards, ML pipelines, and reporting systems all suffer. Proactive troubleshooting matters because:

• Cost efficiency: Idle or misconfigured clusters waste compute budget; poorly tuned queries force over-provisioning.
• User experience: Slow BI dashboards erode trust in the data platform.
• Pipeline reliability: ETL jobs that exceed maintenance windows can break SLAs.
• Data accuracy: Stale tables, aborted transactions, and silent load failures can corrupt analytics.

Issue 1: Slow Query Performance

Slow queries are the most frequent Redshift complaint. Causes include poor distribution, lack of sort keys, data skew, unoptimized joins, and stale statistics. The first step is always to measure before optimizing.

Identifying Long-Running Queries

Use STL_QUERY and STV_WLM_QUERY_STATE to find queries with high elapsed time:

SELECT
  userid,
  query,
  pid,
  starttime,
  endtime,
  DATEDIFF(second, starttime, endtime) AS elapsed_seconds,
  SUBSTRING(querytxt, 1, 120) AS query_text
FROM stl_query
WHERE userid > 1
  AND endtime > DATEADD(hour, -24, GETDATE())
ORDER BY elapsed_seconds DESC
LIMIT 20;

This returns the slowest queries in the last 24 hours, excluding system user activity. Once you identify a culprit, examine its query plan.

Inspecting the Query Plan

Run EXPLAIN to inspect the plan before execution, and STL_EXPLAIN after execution to see what actually ran:

EXPLAIN
SELECT c.customer_id, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id;

Look for warning indicators in the plan:

• DS_BCAST_INNER — broadcast join, often a sign of missing or mismatched distribution keys.
• DS_DIST_NONE — good, data is co-located.
• Seq Scan on large tables without a sort key predicate — indicates a full table scan.
• High-cost Hash or Aggregate nodes — may indicate missing sort keys or statistics.

Checking Data Skew

If rows are unevenly distributed across slices, some slices become hotspots. Query STV_TBL_PERM joined with STV_BLOCKLIST to measure skew:

SELECT
  TRIM(name) AS table_name,
  MAX(rows) AS max_rows_per_slice,
  MIN(rows) AS min_rows_per_slice,
  ROUND(AVG(rows)::numeric, 0) AS avg_rows_per_slice,
  ROUND((MAX(rows) - MIN(rows))::numeric / NULLIF(AVG(rows), 0), 2) AS skew_ratio
FROM (
  SELECT
    TRIM(t.name) AS name,
    b.slice,
    SUM(b.num_rows) AS rows
  FROM stv_blocklist b
  JOIN stv_tbl_perm t ON b.tbl = t.id
  WHERE t.name IN ('customers', 'orders')
  GROUP BY t.name, b.slice
)
GROUP BY name
ORDER BY skew_ratio DESC;

A skew ratio above 1.5 suggests the distribution key is poorly chosen. Common fixes include switching to a DISTSTYLE KEY on a high-cardinality join column, or using DISTSTYLE ALL for small dimension tables.

Updating Table Statistics

The query planner relies on statistics. Out-of-date stats produce bad plans. Run ANALYZE after major loads:

ANALYZE customers;
ANALYZE orders;

You can also check the last analyze time per table:

SELECT
  TRIM(n.nspname) AS schema,
  TRIM(c.relname) AS table,
  info.stats_updated
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN (
  SELECT
    attrelid,
    MAX(statime) AS stats_updated
  FROM pg_statistic
  GROUP BY attrelid
) info ON info.attrelid = c.oid
WHERE c.relkind = 'r'
ORDER BY info.stats_updated ASC;

Issue 2: Table Locks and Blocked Sessions

Redshift uses a locking model that can block concurrent DML and DDL. Long-running transactions holding locks on popular tables can stall ETL pipelines. Diagnosing locks requires inspecting STV_LOCKS and STV_RECENTS.

Detecting Active Locks

SELECT
  l.txn_owner,
  l.txn_db,
  t.xid,
  l.relation,
  TRIM(c.relname) AS tablename,
  l.granted,
  l.pid,
  a.starttime,
  a.text AS query_text
FROM stv_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN stv_recents a ON l.pid = a.pid
JOIN pg_stat_activity t ON l.pid = t.procpid
ORDER BY l.granted DESC, a.starttime;

Rows where granted = false represent sessions waiting to acquire a lock. The blocking session is the one holding the lock on the same relation with granted = true.

Terminating a Blocking Session

Once you confirm a session is stuck or runaway, terminate it with PG_TERMINATE_BACKEND:

SELECT PG_TERMINATE_BACKEND(12345);

Replace 12345 with the actual pid from the lock query. Verify termination:

SELECT pid, user_name, status, query
FROM stv_recents
WHERE status = 'Running';

Preventing Lock Contention

• Keep transactions short; commit frequently during ETL.
• Avoid mixing DDL and DML in the same transaction.
• Use COPY for bulk loads instead of multi-row INSERT.
• Schedule heavy maintenance jobs outside peak query windows.

Issue 3: Storage Pressure and Full Disk

Redshift clusters have finite node storage. When disk usage approaches 100%, queries fail and the cluster may become unresponsive. Monitoring disk usage is essential.

Checking Cluster Disk Usage

SELECT
  node,
  SUM(used) AS used_mb,
  SUM(capacity) AS capacity_mb,
  ROUND(SUM(used)::numeric / SUM(capacity) * 100, 2) AS pct_used
FROM stv_partitions
GROUP BY node
ORDER BY node;

If any node exceeds 80% usage, investigate the largest tables and uncommitted blocks.

Finding the Largest Tables

SELECT
  TRIM(n.nspname) AS schema,
  TRIM(c.relname) AS table,
  ROUND(SUM(b.size)::numeric / 1024.0 / 1024.0, 2) AS size_gb
FROM stv_tbl_perm b
JOIN pg_class c ON b.id = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
GROUP BY 1, 2
ORDER BY size_gb DESC
LIMIT 20;

Reclaiming Space

Deleted and updated rows are not immediately reclaimed. Run VACUUM to re-sort and reclaim space:

VACUUM FULL customers;
VACUUM FULL orders;

For very large tables, consider VACUUM REINDEX if the sort key has many deleted blocks:

VACUUM REINDEX orders;

After vacuuming, run ANALYZE to refresh statistics. If storage remains tight, consider deleting old data, archiving cold partitions, or resizing the cluster.

Issue 4: WLM Queue Wait Times

Redshift routes queries into Workload Management (WLM) queues. When a queue is saturated, queries wait for slots, increasing latency. Diagnosing WLM issues involves STV_WLM_QUERY_STATE and STL_WLM_QUERY.

Monitoring Queue Wait

SELECT
  query,
  service_class,
  slot_count,
  total_queue_time_ms,
  total_exec_time_ms,
  queue_position,
  state
FROM stv_wlm_query_state
WHERE state IN ('QueuedWaiting', 'Running')
ORDER BY total_queue_time_ms DESC;

High total_queue_time_ms relative to total_exec_time_ms indicates the cluster is queue-bound.

Identifying Queue Saturation

SELECT
  service_class,
  COUNT(*) AS queries_queued,
  AVG(total_queue_time_ms) AS avg_queue_ms,
  MAX(total_queue_time_ms) AS max_queue_ms
FROM stl_wlm_query
WHERE starttime > DATEADD(hour, -24, GETDATE())
GROUP BY service_class
ORDER BY avg_queue_ms DESC;

Remediation Strategies

• Increase slots for the affected queue in the WLM configuration.
• Use query priority (Auto WLM) to let Redshift manage resources dynamically.
• Use Query Monitoring Rules (QMR) to abort runaway queries automatically.
• Offload short queries to a separate queue to prevent long ETL jobs from blocking them.
• Consider Concurrency Scaling to add transient clusters during peak load.

Issue 5: COPY Load Failures

The COPY command is the recommended way to load data into Redshift. Failures typically stem from malformed source files, encoding mismatches, or permission issues. The STL_LOAD_ERRORS table is your primary diagnostic tool.

Retrieving Load Errors

SELECT
  starttime,
  filename,
  line_number,
  colname,
  type,
  TRIM(col_length) AS col_length,
  TRIM(position) AS position,
  TRIM(raw_field_value) AS raw_value,
  TRIM(err_reason) AS err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 50;

The err_reason column explains the failure, such as Invalid digit, Value 'X', Pos 0, Type: Integer or Missing data for not-null field.

Handling Bad Rows Gracefully

Use MAXERROR and ACCEPTANYDATE to tolerate minor data quality issues during loads:

COPY sales
FROM 's3://my-bucket/sales/2024/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS CSV
DELIMITER ','
QUOTE '"'
MAXERROR 100
ACCEPTANYDATE
DATEFORMAT 'YYYY-MM-DD'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1;

For stricter pipelines, use COPY with STATUPDATE ON and validate row counts against source files immediately after loading.

Validating Load Results

SELECT
  query,
  TRIM(filename) AS filename,
  lines_scanned,
  lines_inserted
FROM stl_load_commits
WHERE query = <your_copy_query_id>;

Compare lines_scanned against lines_inserted to detect silent row drops.

Issue 6: Connection Limits and Authentication Errors

Redshift enforces a maximum number of concurrent connections per cluster. When exceeded, new connections are rejected. Authentication failures usually stem from IAM role misconfiguration or expired credentials.

Checking Active Connections

SELECT
  user_name,
  COUNT(*) AS active_connections,
  MAX(starttime) AS most_recent
FROM stv_sessions
WHERE user_name <> 'rdsdb'
GROUP BY user_name
ORDER BY active_connections DESC;

Diagnosing Authentication Failures

Check STL_CONNECTION_LOG for connection attempts and their outcomes:

SELECT
  eventtime,
  remotehost,
  username,
  event,
  err_reason
FROM stl_connection_log
WHERE eventtime > DATEADD(hour, -1, GETDATE())
ORDER BY eventtime DESC;

Common event values include authenticated, authentication failed, and disconnecting. For IAM authentication, verify the IAM role trust policy and that the Redshift cluster is authorized to assume it.

Best Practices for Redshift Health

Design for Performance from the Start

• Choose distribution keys that align with frequent join columns to minimize data movement.
• Define sort keys on columns used in range filters and joins to enable zone map pruning.
• Use ENCODING compression wisely; let Redshift auto-encode with COPY.
• Prefer DISTSTYLE KEY for large fact tables and DISTSTYLE ALL for small dimensions.

Operate with Monitoring Discipline

• Schedule nightly VACUUM and ANALYZE jobs for high-churn tables.
• Set CloudWatch alarms on PercentageDiskSpaceUsed and CPUUtilization.
• Enable system table export to S3 for long-term retention of diagnostic data.
• Use Redshift Advisor recommendations to surface tuning opportunities automatically.

Automate Diagnostics

Create a library of diagnostic views that wrap common system table queries. For example:

CREATE VIEW admin.v_slow_queries AS
SELECT
  query,
  starttime,
  endtime,
  DATEDIFF(second, starttime, endtime) AS elapsed_seconds,
  SUBSTRING(querytxt, 1, 200) AS query_text
FROM stl_query
WHERE userid > 1
  AND endtime > DATEADD(hour, -24, GETDATE())
  AND DATEDIFF(second, starttime, endtime) > 60
ORDER BY elapsed_seconds DESC;

Then your on-call engineers can simply run SELECT * FROM admin.v_slow_queries; during incidents.

Conclusion

Troubleshooting Redshift is a systematic discipline built on understanding the system tables, interpreting query plans, and applying targeted remediations. The most common issues—slow queries, table locks, storage pressure, WLM queue saturation, COPY failures, and connection limits—each have well-defined diagnostic paths using STL_ and STV_ tables. By building a reusable set of diagnostic views, automating routine maintenance like VACUUM and ANALYZE, and designing tables with appropriate distribution and sort keys from the outset, you can keep your Redshift cluster performant, reliable, and cost-effective. The key is to measure first, understand the root cause, and then act—rather than guessing at fixes—so that each troubleshooting session also improves the long-term health of your data warehouse.