Deduplikasi baris duplikat di PostgreSQL pakai CTID

Tabel yang sudah lama jalan kadang punya duplikat — import berulang, race condition tanpa unique constraint, atau migration yang gagal halfway. Snippet ini hapus duplikat berdasarkan kombinasi kolom (mis. email + phone_number), retain satu (yang paling baru created_at-nya), pakai CTID (physical row identifier PostgreSQL).

Skenario

Tabel customers punya kolom: id, email, phone_number, name, created_at. Beberapa email punya entry duplikat dengan ID berbeda. Kita ingin retain satu per email (yang paling baru created_at-nya).

Preview duplikat dulu

SELECT
  email,
  COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC
LIMIT 20;

Output:

       email          | duplicate_count
----------------------+-----------------
 ari@example.com      |               5
 budi@example.com     |               3
 ...

Lihat detail per group

WITH duplicates AS (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
)
SELECT c.id, c.email, c.name, c.phone_number, c.created_at
FROM customers c
JOIN duplicates d USING (email)
ORDER BY c.email, c.created_at DESC;

Hapus duplikat — pakai CTID

-- DRY RUN dulu: lihat baris yang akan dihapus
SELECT id, email, name, created_at
FROM customers
WHERE ctid IN (
  SELECT ctid
  FROM (
    SELECT
      ctid,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at DESC, id DESC
      ) AS rn
    FROM customers
  ) ranked
  WHERE rn > 1
);

-- Kalau preview-nya OK, baru DELETE
BEGIN;

DELETE FROM customers
WHERE ctid IN (
  SELECT ctid
  FROM (
    SELECT
      ctid,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at DESC, id DESC
      ) AS rn
    FROM customers
  ) ranked
  WHERE rn > 1
);

-- Cek hasil sebelum commit
SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;
-- Harus return 0 rows.

COMMIT;
-- Atau ROLLBACK kalau ada yang salah.

Kenapa CTID, bukan PK?

ctid adalah identifier unik per baris fisik di tabel (tuple ID = (page, slot)). Beberapa keuntungan dibanding pakai id:

• Tidak butuh primary key — bisa dipakai untuk tabel yang lupa di-define PK.
• Lebih cepat — CTID langsung point ke baris di heap, tidak butuh index lookup.
• Aman dari false positive — kalau ada PK duplikat (shouldn’t happen tapi pernah lihat dari migration glitch), CTID tetap unique.

Kapan dipakai

• Data quality cleanup sebelum migrasi besar.
• Setelah import CSV manual yang di-trigger dua kali oleh user.
• Untuk menghapus event log duplikat di tabel analytics.

Catatan & gotcha

• CTID berubah setelah VACUUM FULL atau CLUSTER — jadi snippet ini harus dijalankan dalam satu transaksi (BEGIN/COMMIT) supaya CTID stabil selama operasi.
• Pakai PARTITION BY yang tepat. Kalau yang dianggap “duplikat” itu email + phone_number, partition by keduanya. Kalau hanya email, beberapa user dengan email sama tapi nomor beda akan kehilangan record.
• ORDER BY di window function menentukan yang RETAIN. created_at DESC = retain yang paling baru. created_at ASC = retain yang paling lama (original).
• Backup dulu — pg_dump -t customers > backup.sql sebelum DELETE. Sekali commit, susah rollback.
• Setelah cleanup, tambah UNIQUE constraint supaya duplikat tidak lagi terjadi:

ALTER TABLE customers ADD CONSTRAINT customers_email_unique UNIQUE (email);

Variasi untuk MySQL

MySQL tidak ada CTID. Pakai self-join atau ROW_NUMBER (MySQL 8+):

-- MySQL 8+
DELETE c1 FROM customers c1
JOIN (
  SELECT id
  FROM (
    SELECT id, ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY created_at DESC, id DESC
    ) AS rn
    FROM customers
  ) ranked
  WHERE rn > 1
) duplicates ON c1.id = duplicates.id;

Test di staging environment dengan dataset copy production. Saya pernah lihat dedup script delete 40% tabel karena salah PARTITION BY di kolom yang ada NULL — NULL = NULL itu false di SQL, jadi setiap NULL dianggap unique. Tambah COALESCE(email, '') kalau email bisa NULL.