First Off Note: Constraint Enforcement
Redshift uniqueness constraints, primary key constraints, and foreign key constraints are not enforced
Unfortunately Redshift uniqueness, primary keys and foreign key constraints defined on tables are not enforced they are only declared for informational purposes.
But, The query optimizer DOES use constraints to create query execution plans so there is definitely value in defining constraints.
Table maintenance to allow the optimizer to produce high performance SQL
Space in table blocks is not removed after deletes, and if the data is loaded in the wrong order after the initial load – a table “vacuum” may be in order to correct either condition or both simultaneously.
In the case of deletes blocks, vacuum reorganizes to remove wasted space.
In the case of data loaded out of order, vacuum re-orders the data based on sortkey.
Updates are essentially delete and insert append operations so large bulk updates disorganize a tables data for which a “vacuum” will be required to reorder as well. Additionally, after the table initial load, if subsequent copies load data out of order, a vacuum will also be beneficial.
Analyze tables to create statistics regularly especially after large jobs that change data (insert, update, or delete) so that the optimizer can use the statistics.
When should you run the analyze command:
SELECT "schema" + '.' + "table" FROM svv_table_info where stats_off > 12;
How can you confirm a table should be vacuumed – well in terms of sorts that are necessary (adjust you percentage as you like) – the example below is 12%:
SELECT "schema" + '.' + "table" FROM svv_table_info where unsorted > 12;
References:
https://docs.aws.amazon.com/redshift/latest/dg/t_Reclaiming_storage_space202.html
https://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html
Google: Amazon Redshift Vacuum and Analyze – Complete Guide by Sarad – last update I saw 2017
More from LonzoDB on AWS