Cleaning Up Duplicate Rows

GoArchive can remove duplicate rows from a table: keep one row per business key, and archive or purge every other row. No helper table, script or stored procedure is needed. One job with one where does it, and every safeguard of a normal run applies.

The rule: keep the smallest primary key #

For each value of the duplicated key, the row with the smallest primary key is the keeper. Every row whose primary key is larger than its key’s keeper is a duplicate. The keeper itself can never match, since its primary key is not larger than itself.

The job #

Given a table payment whose rows are duplicated by base_payment_id, indexed by idx_base_payment_id:

jobs:
  payment_dedup:
    job_type: archive
    root_table: payment
    primary_key: id
    where: >-
      payment.base_payment_id IS NOT NULL
      AND payment.id > (
        SELECT keeper.id
        FROM payment AS keeper FORCE INDEX (idx_base_payment_id)
        WHERE keeper.base_payment_id = payment.base_payment_id
        ORDER BY keeper.id
        LIMIT 1
      )

• Name the outer table by its table name (payment.id, payment.base_payment_id) and give the inner one an alias (keeper). The root table is queried without an alias.
• IS NOT NULL leaves rows without a key alone. NULL never equals NULL, so such rows are never duplicates of each other.
• Index the key column. The subquery runs once per candidate row; without an index on base_payment_id, each run of it scans the table. FORCE INDEX pins that index in case the optimizer picks a worse plan; the query means the same without it.
• One statement. The where may contain subqueries, but not a second statement.

Archive or purge #

Prefer archive for a first cleanup: the destination is your record of exactly which rows were removed. archive needs the table in the destination schema with a compatible structure, which preflight checks. A purge job runs under goarchive purge.

Run it #

goarchive validate -c archiver.yaml --job payment_dedup
goarchive dry-run  -c archiver.yaml --job payment_dedup
goarchive archive  -c archiver.yaml --job payment_dedup

Before the real run, check that the where selects exactly the duplicates. dry-run prints the number of rows it matches; compare it with your own count of duplicates:

SELECT COALESCE(SUM(n - 1), 0) FROM (
  SELECT COUNT(*) AS n FROM payment
  WHERE base_payment_id IS NOT NULL
  GROUP BY base_payment_id
) AS g;

For a job over the whole table, the two numbers must be equal. If they are not, the where does not say what you mean: fix it before running. dry-run also shows the plan of the root fetch, so you can confirm that the subquery uses the index.

Why batching is safe here #

The run fetches duplicates in primary-key order, one batch at a time, and deletes them while later batches are still to come. That never moves a keeper: deleting a duplicate does not change which row of its key has the smallest primary key. Each batch’s where therefore selects the same rows it would have selected at the start.

Large tables #

On a large table, add a primary-key range to the where and move it forward from one run to the next (AND payment.id BETWEEN 1 AND 10000000). Each run stays short, its count stays cheap, and you can stop between ranges.

With a range, the count is that range’s share of the whole-table figure above, and the shares of all ranges add up to it. Do not compare a range’s count with a GROUP BY limited to the same range: the subquery finds each key’s keeper anywhere in the table, so a range whose keys have their keeper in an earlier range holds more duplicates than a range-limited GROUP BY reports.

Pace the deletes with batch_delete_size and delete_sleep_seconds if replicas lag (Batch Processing ).

Limits that apply #

• Cold data. Do not let the application write duplicates, or delete keepers, while the job runs (Limitations ).
• Child tables. If other tables reference the duplicated table by foreign key, declare them as relations so their rows move with each duplicate. Preflight refuses a job whose incoming foreign keys are not covered.
• Primary key. The where above compares a single-column key; a composite key needs a comparison over all its columns.