Remove all duplicates from Redshift database

Question

I have a very large Redshift database. The records do not have unique keys or ids. I'd like to remove all of the duplicates with the most efficient query possible.

Other stackoverflow questions about typical sql databases suggested copying the table and skipping the duplicates during that process, but that seems suboptimal for a giant redshift database.

Any better solutions out there?

Answer 1

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