Redshift : Vacuum

VACUUM [ FULL | DELETE ONLY | SORT ONLY ]

FULL - It is default. reclaims space and resorts the rows.
DELETE ONLY - reclaims space , but doesnt resort rows.
SORT ONLY - only sorts, will not reclaim disk space.




Table
 - It will have sorted region at the head followed by unsorted region. When you add data using COPY, the new set of rows is sorted on sort key and is added to unsorted region at the end of the table. It is ordered within their own set not with entire unsorted region.

2 stages of Vacuum

Sort Stage - Rows are sorted in the unsorted Region. During this stage all the rows/data in unsorted regions are sorted and new sorted region is created.

Merge Stage - Newly sorted rows are then merged at the end of the table with existing rows. If the newly sorted region overlap the keys in the sorted region, then merge needs to happen from the lowest sort key. Vacuum writes merged rows into a new set of blocks.

For Large tables, vacuum does it in series of steps consisting of incremental sorts followed by merges.

System performance will be affected if you run DML during vacuum. UPDATE/DELETE might block Incremental merges and vice versa. So it might take longer time to complete the operation.
DDL will be blocked until Vaccum completes.


Best Practicies -

To manage the size of unsorted region
- Run vacuum operations on regular schedule
- Run the largest load first
- Truncate table instead of deleting all rows
- Perform Deep Copy

To manage volume of merged rows
- Load data in sort key order
- Use time series tables