One thing to bare in mind with Redshift is that deleted records are only actually "soft" deleted until VACUUM is run.
- They remain in the table, marked as to-be-ignored
- They're only deleted after a Vacuum
However, a VACUUM on a large table with deletes scattered through it is very often actually slower than a "Deep Copy". (Duplicate the data into another table, using GROUP BY
or DISTINCT
to eliminate the duplicates, TRUNCATE
the original table and re-insert the data or drop the original table and rename the new table.)
This is a general rational for why you may actually benefit from what feels like the "slow" process.
Also, if two rows really are identical then there is no way (by definition) to uniquely identify one row. That being the case you can't differentiate between one to be kept and ones to be deleted.
One "trick" in other RDBMS is to use ROW_NUMBER()
inside of a Common Table Expression and then delete from that CTE. (With the CTE creating the unique identifiers, allowing you to identify individual rows to be kept or deleted.) Unfortunately Redshift doesn't currently support deleting from a CTE.
Until this changes, Deep Copy (copying to a separate table while using GROUP BY
or DISTINCT
) is currently your only option.
Even so, the Deep Copy option may still be more valid in Redshift even if deleting from a CTE does ever become possible.
EDIT :
Correction:
If any row in a Redshift Table has been deleted, any subsequent VACUUM will reprocess the entire table (regardless of where the deleted rows are, or how many deleted rows there are).
(It's more sophisticated when VACUUMing following an INSERT, but down-right-ugly following a DELETE.)
I've also noticed that a Deep Copy uses less disk space than a VACUUM. (Which only came to my attention when we ran out of disk space...)
EDIT :
Code Example:
CREATE TABLE blah_temp (
<Exactly the same DDL as the original table, especially Distribution and Sort keys>
)
;
INSERT INTO
blah_temp
SELECT DISTINCT
*
FROM
blah
;
DROP TABLE blah;
ALTER TABLE blah_temp RENAME TO blah;
Or...
CREATE TABLE blah_temp (
<Exactly the same DDL as the original table, especially Distribution and Sort keys>
)
;
INSERT INTO
blah_temp
SELECT
*
FROM
blah
GROUP BY
a, b, c, d, e, f, g, etc
;
TRUNCATE TABLE blah;
INSERT INTO
blah
SELECT
*
FROM
blah_temp
;
DROP TABLE blah_temp;
Related Link: https://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html