Redshift Vacuum: Optimize your Redshift tables

in Redshift
 • Updated on 

Because Redshift does not automatically “reclaim” the space taken up by a deleted or updated row, occasionally you’ll need to resort your tables and clear out any unused space. Doing so can optimize performance and reduce the number of nodes you need to host your data (thereby reducing costs). This can be done using the VACUUM command.

The VACUUM command can only be run by a superuser or the owner of the table. Even worse, if you do not have those privileges, Redshift will tell you the command worked perfectly, while in reality, it will have no effect.

It’s really simple to do:

VACUUM table_name;

That command alone should handle 90%+ of cases, but there are a couple of caveats:

  1. In rare cases, the table size can only grow after you perform a VACUUM. This can be because there are no deleted rows that can be reclaimed and the new sort order results in a lower % of the data being able to be compressed.
  2. Generally speaking, it's a good idea to make sure that there are no WRITE operations going on while the VACUUM is running.
  3. You can't VACUUM within a BEGIN/END block.

And that's it! Feel free to reach out in the comments if you have any questions. :-)

Worry-free replication from source to Redshift & Snowflake
Unlimited sync during trial
No credit-card required
World class support
Try FlyData for free
Amazon Partner Logo Certified AWS
Redshift partner
Get started. Try FlyData.
Quick setup. No credit card required. Unlimited sync during trial.
Fast and secure Enterprise-grade security and near real-time sync.
World-class support Proactive monitoring from technical experts, 24/7.
Straightforward pricing Pay for the rows you use, and nothing you don’t.