Full Load in Redshift - DROP vs TRUNCATE

1
votes

As part of daily load in Redshift, I have a couple of tables to drop and full load all of them, (data size is small, less than 1 million).

My question is which of the below two strategies is better in terms of CPU utilization and memory in Redshift: 1) Truncate data 2) DROP and Recreate Table.

If I truncate tables, should I perform Vacuum on tables every day as I have read that frequent drop and recreate tables in the database cause fragmentation of pages.

And one of the tables I would like to enable compression. So, is there any downside creating DDL with encoding every day.

Please advise! Thank you!

1
amazon-redshifttruncatevacuum

1 Answers

1
votes

If you drop the tables you will lose assigned permissions to these tables. If you have views for these tables they will be obsolete.

Truncate is a better option, truncate does not require vacuum or analyze, it is built for use cases like this.

For further info Redshift Truncate documentation