ALTER SORTKEY vs CTAS: Choosing the Right Approach for Redshift Table Reorganization

Understanding ALTER TABLE Locking Behavior

ALTER TABLE operations require AccessExclusiveLock, the strongest lock type in Redshift. This lock blocks all other operations on the table. However, the actual lock duration is minimal—typically under 1 second—because ALTER TABLE only modifies metadata immediately.

The real challenge occurs during lock acquisition. In production environments with continuous SELECT queries, the ALTER command waits in queue until all existing queries complete. Once ALTER enters the queue, it blocks all subsequent queries, potentially causing service disruption.

After metadata changes complete, Automatic Table Optimization (ATO) handles actual data reorganization asynchronously in the background. During this phase, SELECT, INSERT, and COPY operations continue without blocking, minimizing operational downtime to under 1 second.

When to Use ALTER TABLE

ALTER TABLE is recommended for production environments when:

• Immediate performance improvement is not critical
• You want to leverage ATO's intelligent background processing
• The table experiences moderate I/O activity
• You need minimal operational disruption (under 1 second)

Execute ALTER commands during low-traffic periods or temporarily restrict table access to avoid lock contention.

When to Use CTAS

CTAS (CREATE TABLE AS SELECT) is preferred when:

• Immediate performance improvement is required
• Multiple schema changes need simultaneous application
• Tables experience frequent, heavy I/O operations
• You need predictable execution time without lock contention

CTAS creates a new table with optimized structure, allowing you to apply compression, distribution, and sort keys in one operation. However, it requires additional storage during execution and involves table renaming steps.

Practical Recommendations

For tables with frequent I/O operations, CTAS provides more predictable results. For standard production scenarios where minimal downtime is acceptable, ALTER TABLE with ATO offers the simplest approach with automatic optimization.

Monitor lock acquisition status using SVV_TRANSACTIONS and track background data reorganization progress via SVL_AUTO_WORKER_ACTION.

Additional Resources

• Automatic Table Optimization
• ALTER TABLE Documentation
• CTAS Usage Notes