Amazon Redshift VACUUM REINDEX for Interleaved Sort Keys

Maintain and monitor data pipelines.

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

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?

VACUUM FULL Orders
VACUUM DELETE ONLY Orders
VACUUM REINDEX Orders Correct Answer
VACUUM SORT ONLY Orders

Community Votes

83%

17%

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

Community Insight

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.

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.

Community Discussion (9 comments)

GiorgioGss 👍 12 Selected: 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

Shatheesh 👍 1 Selected: A

d8945a1 👍 1 Selected: 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

Christina666 👍 1 Selected: 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.

arvehisa 👍 1 Selected: 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.

lucas_rfsb 👍 3 Selected: 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

damaldon 👍 1

B is the answer

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.

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.

Comments & Corrections

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

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

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 →

Related Analysis

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