Snowflake, BigQuery & Databricks · Amazon Redshift

Resolving disk spill and commit queue contention in Redshift

awsHardredshiftdisk-spillcommit-queuetroubleshooting

Interview question

What causes disk spill to RMS and commit queue wait times during heavy ETL, and how do you resolve them?

How to answer

The short answer

During intensive batch transformations, data platforms often experience unexplained slowdowns. Two common underlying culprits in Redshift are intermediate disk spills and commit queue serialization.

Resolving disk spill to RMS

When a query performs a hash join, sort, or high-cardinality GROUP BY, Redshift builds an in-memory hash table. If the data exceeds the memory allocated to that query slot, Redshift spills intermediate state to local NVMe drives, and if that fills, to Redshift Managed Storage (RMS). Disk spills cause massive I/O overhead.

To detect spills:

SQL
SELECT query, seg, step, workmem, is_spilled, rows, bytes
FROM svl_query_summary
WHERE is_spilled = 't'
ORDER BY bytes DESC;

To resolve disk spills:

  • Increase the WLM queue memory allocation or reduce queue concurrency so each query slot receives more RAM.
  • Eliminate Cartesian joins or uncolocated joins that cause data explosion.
  • Ensure statistics are up-to-date with ANALYZE so the optimizer does not misjudge hash table sizes.

Commit queue contention

In Redshift, commits are serialized at the cluster level. While multiple sessions can read and execute queries simultaneously, only one transaction can hold the commit lock at any given moment.

If a poorly written pipeline runs hundreds of single-row inserts or individual DDL statements with autocommit=True, every statement acquires and releases the commit lock. Other transactions stall, waiting in the commit queue.

To resolve commit contention:

  • Group multiple related SQL statements inside a single BEGIN ... COMMIT block.
  • Batch micro-inserts into staging tables followed by a single set-based merge transaction.
  • Avoid running frequent DDL operations (like TRUNCATE or CREATE TABLE) during peak query hours.