Loading Data in Sortkey Order and VACUUM

Question

I am loading a nightly snapshot of a table into Redshift. I have added a column called "rundate" at the end of the table, which just represents when the data was pulled through my ETL process. It is also the primary sortkey.

The tables every night just gets longer and longer, and there are 400+ columns in many of them.

Right now I am using FILLRECORD in conjunction with EMPTYASNULL in order to get NULLS into the table, and once the COPY command has finished, I use

update table set rundate = 'date' where rundate is NULL

in order to have the correct snapshot date.

I am wondering if it is still considered as "loading the data in sortkey order", where I will not need to vacuum. Aside from this, no updates/deletes are done to any of the records.

Answer 1

Unfortunately no. An update will perform a delete/insert leaving your entire load each day as dead records requiring VACUUM. I would recommend loading into an empty stage table instead and then inserting the data with the extra rundate column after staging.