Redshift Automatic Table Optimization and table swaps

Question

A customer swaps out table nightly.

For example table_1 built last night. Then tonight:

- table_1_tmp is built
 - table_1 renamed to table_1_stale
 - table_1_tmp renamed to table_1
 - table_1_stale is dropped

Customer wants know if the new "Automatic Table Optimization" discussed in the [blog][1] is able to recognize the table swaps when gathering data for recommendations.

Does it go by table name or a unique table identifier?

[1]: https://aws.amazon.com/blogs/big-data/optimizing-tables-in-amazon-redshift-using-automatic-table-optimization/

AWS-User-2618129 · Accepted Answer

Redshift Automatic Table Optimization (ATO) uses the internal "table_id" identifier.
As such ATO won't recognize the new table (after swapping) and won't automatically apply what what it had
done previously. Of course not all is lost. You can create the new table_1_tmp by using "create
table table_1_tmp (like table_1)". But to be sure that you want ATO to continue working on the new table,
also do "ALTER TABLE table_1_tmp ALTER DISTSTYLE/SORTKEY AUTO".

More details. Redshift Automatic Table Optimization (ATO) uses the same mechanism as Redshift Advisor for
sort and distribution key recommendations. With ATO, all recommendations are recorded in the SVV_ALTER_TABLE_RECOMMENDATIONS system table. If you take a closer look at its DDL:

https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALTER_TABLE_RECOMMENDATIONS.html

You will notice that it ONLY contains the internal "table_id" NOT the "table_name".

Redshift Automatic Table Optimization and table swaps

Relevant content