Best practice to avoid table locks when performing hot swaps for tables in Redshift

Question

I'm running a periodic ETL process to sync tables from MySQL to Redshift. This involves table swapping - for each MySQL table I create a staging table in Redshift named \<table>_tmp, load data into it, and when ready I rename the production table to \<table>_old and rename \<table>_tmp to \<table>.

My BI platform is based on this Redshift instance, meaning that the above happens while tables may be queried (sometime by multiple queries).

Renaming a table in Redshift requires an AccessExclusiveLock, which can't be obtained while the table is being queried (i.e. while there are active AccessShareLock locks).

My concern is that my table rename will fail, time out or forever hang in case new queries will keep hitting the original table - which would mean my table-swapping logic will not hold.

Does Redshift have any locks queue that would guarantee my name change happens before new queries keep locking the table?

Otherwise - are there any other best practices to manage table swapping with Redshift?

Answer 1

This is a common problem with any database. Renaming tables causes problems when queries are in progress, and could possibly cause problems with materialized views.

An alternate method would be to define a View that simply queries from the table (eg SELECT * FROM table1).

Then, instead of renaming the tables, simply redefine the view (eg SELECT * from table2).

So, think of this more as a blue/green swap rather than promoting a table from staging to production.