Amazon Redshift VACUUM REINDEX for Interleaved Sort Keys

DEA-C01 By islever Editorial Last updated

Maintain and monitor data pipelines.

Short answer

C Use VACUUM REINDEX Orders to analyze the interleaved sort key distribution and reclaim disk space from deleted rows.

Question

A company has used an Amazon Redshift table that is named Orders for 6 months. The company performs weekly updates and deletes on the table. The table has an interleaved sort key on a column that contains AWS Regions. The company wants to reclaim disk space so that the company will not run out of storage space. The company also wants to analyze the sort key column. Which Amazon Redshift command will meet these requirements?
  1. VACUUM FULL Orders
  2. VACUUM DELETE ONLY Orders
  3. VACUUM REINDEX Orders Correct Answer
  4. VACUUM SORT ONLY Orders

Key Takeaways

The common trap is assuming VACUUM FULL handles interleaved reindexing, but AWS documentation explicitly states VACUUM REINDEX is required to analyze interleaved sort keys while reclaiming space.

This question tests the specific behavior of Amazon Redshift's VACUUM commands on tables with interleaved sort keys, focusing on space reclamation and key analysis.

Common mistake: Many learners choose VACUUM FULL because it is the standard command for reclaiming space, but they overlook that it does not perform the additional reindex pass needed for interleaved sort key analysis.

Expert Analysis

Why the Answer Is Correct

The correct answer is C (VACUUM REINDEX) because the table uses an interleaved sort key and requires both disk space reclamation and sort key analysis. According to AWS documentation, a standard VACUUM FULL does not perform a reindex for interleaved tables. The VACUUM REINDEX command specifically analyzes the distribution of values in interleaved sort key columns and then performs a full vacuum operation to reclaim space occupied by deleted rows.

Why the Other Options Are Wrong

Option A (VACUUM FULL) reclaims space but fails to reindex interleaved sort keys, thus missing the requirement to analyze the sort key column. Option B (VACUUM DELETE ONLY) only removes marked-for-deletion rows without sorting or reindexing, failing both requirements. Option D (VACUUM SORT ONLY) sorts the data but does not reclaim disk space from deleted rows, leaving storage usage unchanged.

Community Comment Notes

Community consensus strongly favors option C, citing the official AWS Redshift documentation. As GiorgioGss noted, "A full vacuum doesn't perform a reindex for interleaved tables." Several users highlighted that VACUUM REINDEX makes an additional pass to analyze interleaved sort keys, which is crucial for this scenario. While some argued for VACUUM FULL, the specific mention of 'analyzing' the sort key points definitively to REINDEX.

Official Reference

https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html

This link opens the official vendor documentation in a new tab.

Exam Strategy

When dealing with Redshift maintenance commands, always check if the table has an interleaved sort key. If you need to optimize query performance on such a key while cleaning up space, remember that REINDEX is the specific action required, not just FULL.

Frequently Asked Questions

Why doesn't VACUUM FULL work for interleaved sort keys?

VACUUM FULL reclaims space but does not reindex interleaved sort keys. You must use VACUUM REINDEX to analyze the key distribution as well.

What is the difference between VACUUM DELETE ONLY and REINDEX?

DELETE ONLY removes marked rows but doesn't sort or reindex. REINDEX analyzes interleaved keys and then performs a full vacuum to reclaim space.

More DEA-C01 FAQ →

Community Votes

C
83%
A
17%

83% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 8 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 8

  1. GiorgioGss 👍 12 Answered C
    https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html "A full vacuum doesn't perform a reindex for interleaved tables. To reindex interleaved tables followed by a full vacuum, use the VACUUM REINDEX option." A - "A full vacuum doesn't perform a reindex for interleaved tables."- from the docs above B- "A DELETE ONLY vacuum operation doesn't sort table data." - from the docs above D - "without reclaiming space freed by deleted rows. " - from the docs above
  2. lucas_rfsb 👍 3 Answered A
    FULL is the only one which claims space and sorts. FULL Sorts the specified table (or all tables in the current database) and reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. VACUUM FULL is the default. https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html
  3. kj07 👍 1
    Option C Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation. If REINDEX is used, a table name is required. VACUUM REINDEX takes significantly longer than VACUUM FULL because it makes an additional pass to analyze the interleaved sort keys. The sort and merge operation can take longer for interleaved tables because the interleaved sort might need to rearrange more rows than a compound sort. If a VACUUM REINDEX operation terminates before it completes, the next VACUUM resumes the reindex operation before performing the full vacuum operation.
  4. arvehisa 👍 1 Answered C
    Correct Answer: C Requirements: 1. relcaim the disk space 2. analyze the sork key column Document: https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html#vacuum-reindex VACUUM FULL: A full vacuum doesn't perform a reindex for interleaved tables. To reindex interleaved tables followed by a full vacuum, use the VACUUM REINDEX option. VACUUM REINDEX: Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation.
  5. Christina666 👍 1 Answered C
    Reclaiming Space: After updates and deletes, Redshift tables can retain deleted data blocks, taking up space. The VACUUM REINDEX command: Reclaims the space taken up by the deleted rows. Rebuilds indexes on the sort key columns. Analyzing the Sort Key: Since the sort key column contains AWS Regions, rebuilding the indexes on this column will help cluster data according to region. This clustering can improve performance for queries that filter or group by region.
Show 3 more comments
  1. d8945a1 👍 1 Answered C
    VACUUM REINDEX makes an additional pass to analyze the interleaved sort keys. https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html#r_VACUUM_command-parameters
  2. CalvinL4 👍 1
    The answer should be B. The VACUUM DELETE ONLY command is used in Amazon Redshift to remove rows that have been marked for deletion due to updates and deletes in a table.
  3. damaldon 👍 1
    B is the answer

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide