Alter distkey for a table with 22 Billions rows never ends

user_5556466 0
Viewed 143

I have a table with 22560627453 rows, with Even diststyle. The sortkey is date

I'm trying to run

ALTER TABLE movements 
    ALTER DISTSTYLE KEY DISTKEY id;

but this never ends. In fact, the cluster is at 75% of disk usage 4,5 TB/ 6TB and when running this alter the disk usage reaches 100% and doesn't end.

I tried creating a new table with the correct distkey and sortkey and then insert from the old one to the new one, but happens the same it reaches 100% of disk usage.

The total table size is 768327 (blocks of 1mb) so its 770 GB, then I don't understand why it reaches 100%. Because despite copying the table it should be around 700 gb free.

All table statistics have been achieved using:

select *
from svv_table_info
where "table" = 'movements';

enter image description here

How can I alter the distkey without reaching 100%. Is there a trick, or a tip to do it faster?

Old developers didn't care about distkeys and big part of the is using EVEN...

amazon-redshift query-optimization sql performance
1 Answers

I had the same issue and reached out to AWS support (enterprise plan) - did not really get a good answer. They mentioned something about memory allocation, varchar, and disk spill. That is, if you have a VARCHAR(100) column but only store single character strings, the operation would still allocate memory (and spill to disk) as if you had only 100 character strings. I am pretty sure that was not the reason in my case.

My guess is that a completely uncompressed copy of the table is created during the operation. Asked support about it but they could neither confirm or deny.

Their proposed solution was to UNLOAD > TRUNCATE > COPY. In my case a single unload for the complete table failed, I do not remember the error. Another one is deep copy, same here, it failed for me with the full table in one go.

In the end I managed to solve it by doing it in chunks. I have a load_timestamp TIMESTAMP DEFAULT GETDATE() column in my table (let's call it my_table).

  1. Create table my_table_temp based on SHOW TABLE my_table
  2. For each month, up until current month, do INSERT INTO my_table_temp SELECT * FROM my_table WHERE DATE_PART(year, load_timestamp) = x AND DATE_PART(month, load_timestamp)
  3. DELETE my_table WHERE load_timestamp < current_month and VACUUM DELETE ONLY my_table
  4. Alter my_table distkey to the desired key.
  5. Repeat step 2 but from my_table_temp into my_table.

Reason for doing it up until the current month is that my table is constantly being copied into, and it felt like the safest way to not lose any data. If you know for sure you can "pause" all write activity, you can save some time by loading all data into my_table_temp and perform a TRUNCATE in step 3 instead.

I am in no way saying this a nice and clever solution - but it was the first attempt of many that worked for me.

user_38055000
Related