Merging duplicate customers without breaking orders: repoint foreign keys, keep history, then delete

create table merge_map (dup_id bigint primary key,      -- the row that goes
                        keep_id bigint not null unique); -- the row that stays

-- every column that points at customers(id), from the catalog
create or replace view customer_refs as
select c.conrelid::regclass as tbl, a.attname as col,
       (select array_agg(pa.attname order by pa.attnum)
          from pg_constraint pk
          join pg_attribute pa on pa.attrelid = pk.conrelid and pa.attnum = any (pk.conkey)
         where pk.conrelid = c.conrelid and pk.contype = 'p' and pa.attname <> a.attname) as key_cols,
       c.confdeltype as on_delete
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.confrelid = 'customers'::regclass and c.contype = 'f';

       tbl       |     col     | key_cols | on_delete
-----------------+-------------+----------+-----------
 addresses       | customer_id | {id}     | a          a = no action
 orders          | customer_id | {id}     | a
 payment_methods | customer_id | {id}     | a
 customer_tags   | customer_id | {tag}    | a
 loyalty         | customer_id |          | a
 reviews         | customer_id | {id}     | c          c = cascade

select r.tbl, i.indexrelid::regclass as unique_index, pg_get_expr(i.indpred, i.indrelid) as only_where
from customer_refs r
join pg_index i on i.indrelid = r.tbl and i.indisunique
join pg_attribute a on a.attrelid = i.indrelid and a.attname = r.col and a.attnum = any (i.indkey);
--       tbl       |                unique_index                 | only_where
--  addresses       | one_default_address                         | is_default
--  payment_methods | payment_methods_customer_id_fingerprint_key |
--  customer_tags   | customer_tags_pkey                          |
--  loyalty         | loyalty_pkey                                |

table             collision                           rule we used                         pairs hit
loyalty           both have a row (one per customer)  add the points, drop the duplicate's      5,000
customer_tags     same tag on both                    keep one                                  2,047 tags
payment_methods   same card fingerprint on both       keep the survivor's                       1,460
addresses         both have a default address         survivor's stays default, other demoted  5,000
orders, reviews   none (no unique key on customer)    just repoint                                 -

create or replace procedure merge_customers(p_keep_lock text default 'no key update')
language plpgsql as $$
declare r record; n bigint;
begin
  -- 1. lock the pairs in id order (two merges touching the same customer queue instead of deadlocking).
  --    The duplicate is deleted, so FOR UPDATE; the survivor only needs NO KEY UPDATE, which still lets
  --    other sessions insert orders for it (their foreign key check takes FOR KEY SHARE)
  perform 1 from customers where id in (select dup_id from merge_map) order by id for update;
  if p_keep_lock = 'update' then
    perform 1 from customers where id in (select keep_id from merge_map) order by id for update;
  else
    perform 1 from customers where id in (select keep_id from merge_map) order by id for no key update;
  end if;

  -- 2. child rows that would collide with the survivor's own rows
  --    loyalty: one row per customer -> add the points, drop the duplicate's row
  insert into merge_audit (dup_id, keep_id, tbl, action, row_data)
    select m.dup_id, m.keep_id, 'loyalty', 'points', to_jsonb(l) from loyalty l join merge_map m on l.customer_id = m.dup_id;
  update loyalty k set points = k.points + d.points
    from loyalty d join merge_map m on d.customer_id = m.dup_id where k.customer_id = m.keep_id;
  delete from loyalty l using merge_map m where l.customer_id = m.dup_id;
  --    tags and saved cards: the survivor already has the same one -> drop the duplicate's copy
  with d as (
    delete from customer_tags t using merge_map m
     where t.customer_id = m.dup_id
       and exists (select 1 from customer_tags k where k.customer_id = m.keep_id and k.tag = t.tag)
    returning m.dup_id, m.keep_id, to_jsonb(t) as j)
  insert into merge_audit (dup_id, keep_id, tbl, action, row_data) select dup_id, keep_id, 'customer_tags', 'deleted', j from d;
  with d as (
    delete from payment_methods p using merge_map m
     where p.customer_id = m.dup_id
       and exists (select 1 from payment_methods k where k.customer_id = m.keep_id and k.fingerprint = p.fingerprint)
    returning m.dup_id, m.keep_id, to_jsonb(p) as j)
  insert into merge_audit (dup_id, keep_id, tbl, action, row_data) select dup_id, keep_id, 'payment_methods', 'deleted', j from d;
  --    two default addresses: the survivor's stays default
  with d as (
    update addresses a set is_default = false from merge_map m
     where a.customer_id = m.dup_id and a.is_default
       and exists (select 1 from addresses k where k.customer_id = m.keep_id and k.is_default)
    returning m.dup_id, m.keep_id, a.id)
  insert into merge_audit (dup_id, keep_id, tbl, action, row_data)
    select dup_id, keep_id, 'addresses', 'demoted', jsonb_build_object('id', id) from d;

  -- 3. repoint every referencing table the catalog knows about
  for r in select * from customer_refs loop
    execute format(
      'with moved as (update %1$s t set %2$I = m.keep_id from merge_map m where t.%2$I = m.dup_id
                      returning m.dup_id, m.keep_id, jsonb_build_object(%3$s) as k)
       insert into merge_audit (dup_id, keep_id, tbl, action, row_data)
       select dup_id, keep_id, %4$L, ''moved'', k from moved',
      r.tbl, r.col,
      (select string_agg(format('%L, t.%I', c, c), ', ') from unnest(r.key_cols) c),
      r.tbl::text);
    get diagnostics n = row_count;
    raise notice '% rows of % moved', n, r.tbl;
  end loop;

  -- 4. the duplicate customer rows go last, kept whole in the audit
  with d as (delete from customers c using merge_map m where c.id = m.dup_id returning m.dup_id, m.keep_id, to_jsonb(c) as j)
  insert into merge_audit (dup_id, keep_id, tbl, action, row_data) select dup_id, keep_id, 'customers', 'customer', j from d;
end $$;

PostgreSQL 18.6, 5,000 pairs, 1,000,000 orders          rows
orders moved                                          25,120
addresses moved                                        6,459
addresses demoted (two defaults)                       5,000
customer_tags moved / deleted (same tag)          7,953 / 2,047
payment_methods moved / deleted (same card)       3,540 / 1,460
loyalty rows added into the survivor's                 5,000
reviews moved                                          2,506
customers deleted                                      5,000

time, alone                                           1.23 s
WAL written                                          53.6 MB
undo (below)                                          0.73 s

  -- 3. repoint every referencing table, with dynamic SQL from the catalog
  open refs;
  repoint: loop
    fetch refs into v_tbl, v_col, v_keys;
    if done then leave repoint; end if;
    set @sql = concat('insert into merge_audit (dup_id, keep_id, tbl, action, row_data) select m.dup_id, m.keep_id, ''',
                      v_tbl, ''', ''moved'', json_object(', coalesce(v_keys, ''), ') from ', v_tbl,
                      ' t join merge_map m on t.', v_col, ' = m.dup_id');
    prepare s from @sql; execute s; deallocate prepare s;
    set @sql = concat('update ', v_tbl, ' t join merge_map m on t.', v_col, ' = m.dup_id set t.', v_col, ' = m.keep_id');
    prepare s from @sql; execute s; deallocate prepare s;
  end loop;
  close refs;

create or replace procedure unmerge_customers()
language plpgsql as $$
declare r record;
begin
  insert into customers overriding system value
    select (jsonb_populate_record(null::customers, row_data)).* from merge_audit where action = 'customer';
  for r in select * from customer_refs loop
    execute format(
      'update %1$s t set %2$I = a.dup_id from merge_audit a
        where a.tbl = %3$L and a.action = ''moved'' and t.%2$I = a.keep_id and %4$s',
      r.tbl, r.col, r.tbl::text,
      coalesce((select string_agg(format('t.%1$I::text = a.row_data ->> %1$L', c), ' and ') from unnest(r.key_cols) c), 'true'));
  end loop;
  update addresses t set is_default = true from merge_audit a
   where a.tbl = 'addresses' and a.action = 'demoted' and t.id = (a.row_data ->> 'id')::bigint;
  insert into customer_tags select (jsonb_populate_record(null::customer_tags, row_data)).*
    from merge_audit where tbl = 'customer_tags' and action = 'deleted';
  insert into payment_methods overriding system value
    select (jsonb_populate_record(null::payment_methods, row_data)).* from merge_audit where tbl = 'payment_methods' and action = 'deleted';
  update loyalty k set points = k.points - (a.row_data ->> 'points')::int
    from merge_audit a where a.action = 'points' and k.customer_id = a.keep_id;
  insert into loyalty select (jsonb_populate_record(null::loyalty, row_data)).* from merge_audit where action = 'points';
  delete from merge_audit;
end $$;

longest wait during a ~1 s merge            PostgreSQL 18.6              MySQL 8.4.11
survivor locked with:                     FOR UPDATE  NO KEY UPDATE    FOR UPDATE  FOR SHARE
new order for a survivor                    1,003 ms       0.5 ms      1,115 ms      5.0 ms
update to a survivor's own row                  -        995 ms      1,100 ms      1,153 ms
new order for an unrelated customer          0.7 ms       0.6 ms        6.4 ms      5.0 ms
- : not measured; all four connections were already waiting on an order for a survivor

2.5% of requests for duplicate ids            PostgreSQL 18.6    MySQL 8.4.11
merge time                                            1.0 s           1.2 s
connections stuck behind a duplicate's lock          4 of 4          4 of 4
longest wait                                        975 ms        1,145 ms
writes failing in the second after the merge           915             456
  (foreign key: customer no longer exists)

batches of 500 (10 transactions)              PostgreSQL 18.6    MySQL 8.4.11
total time                                           2.95 s          2.31 s
longest batch                                        0.30 s          0.32 s
longest wait for any request                         285 ms          303 ms

1. pairs into merge_map; one survivor per group; no chains
2. list referencing tables from the catalog (pg_constraint / KEY_COLUMN_USAGE), note ON DELETE CASCADE
3. list unique indexes that include the customer column; write a rule for each collision
4. one transaction per batch: lock duplicates FOR UPDATE, survivors FOR NO KEY UPDATE / FOR SHARE (id order)
5. settle collisions, then repoint every table, then delete the duplicates; audit every change
6. run the undo on a copy and compare table hashes before you trust it
7. keep merge_map (or a merged_into tombstone) so late requests for old ids still land