Deduplicate while updating self-references in the table, and foreign references in another (PostgreSQL)

Question

I've read a few posts here that I've been able to find about this, and they usually involve a comment about how the poster has inherited a mess, and they're trying to clean it up. Unfortunately, I am the problem here. I wrote an application that has been ingesting data for 3 years now, and I've only just figured out that a mismatch for certain kinds of records, between Ruby's in-memory version of a field, and the string stored in the database, has caused duplicates that I thought I designed the entire application to avoid.

My schema looks like this:

calibrations -> variables <- tunings (<- tunings)

The tunings table is (possibly) self-referential in 2 fields. For 2D data, there might be an X-axis ID referenced, and for 3D data, there might be X- and Y-axis ID's.

I can find the dupes with this:

SELECT label, description, ..., data, count(label)
FROM tunings
GROUP BY label, description, ..., data
HAVING count(label) > 1

Currently, I am running a Ruby script to:

1. Find the duplicated tunings (with COUNTS > 1)
2. Get the instance ID's of each record of the dupe
3. Update the variables table to reference the FIRST ID of the dupes
4. Update the tunings table itself (x_axis_id or y_axis_id) to reference the FIRST ID
5. Delete all the other dupes but the first
6. GOTO 2

I have to do this, first for X axes, then for Y's, then for everything else, in order to keep it all straight.

There are 500M references in variables, and 95M records in tunings. It's taking about 3 minutes per duped tuning. That doesn't seem bad at first, but I have 48K dupes to fix, so that works out to about 4 days. I've tried to speed things up by deleting various indexes, but it doesn't seem to really matter.

It's hard for me to imagine that you can fix the self-references, the foreign references, and the dupes all in one statement. I tried writing the whole thing as a pgplsql function. Here's how far I got. Towards the end, it falls apart because I don't know how to reference the first row from the param_dupes cursor, nor how to grab all of the ID's returned. I left the Ruby code as a placeholder.

CREATE OR REPLACE FUNCTION public.dk_test() RETURNS integer AS $$
DECLARE
    param_rollup RECORD;
    param_dupes RECORD;
BEGIN

    FOR param_rollup IN
        SELECT label, description, count as points, size, data_type, max, min, min_resolution, units, subfile,
            significant_digits, x_axis_id, y_axis_id, data, label_lowercase, count(label) as number_of_dupes
        FROM tunings
        GROUP BY label, description, points, size, data_type, max, min, min_resolution, units, subfile,
            significant_digits, x_axis_id, y_axis_id, data, label_lowercase
        HAVING count(label) > 1

    LOOP
        
        RAISE NOTICE 'Duplicates: %', param_rollup.number_of_dupes;
        
        FOR param_dupes IN
            SELECT *
            FROM tunings
            WHERE label = param_rollup.label
                AND description = param_rollup.description
                AND "count" = param_rollup.points
                AND size = param_rollup.size
                AND data_type = param_rollup.data_type
                AND max = param_rollup.max
                AND min = param_rollup.min
                AND min_resolution = param_rollup.min_resolution
                AND units = param_rollup.units
                AND subfile = param_rollup.subfile
                AND significant_digits = param_rollup.significant_digits
                AND x_axis_id = param_rollup.x_axis_id
                AND y_axis_id = param_rollup.y_axis_id
                AND data = param_rollup.data
                AND label_lowercase = param_rollup.label_lowercase

        LOOP
            
            RAISE NOTICE 'Sub-duplicate count: %', ?;
            
            UPDATE variables SET tuning_id = #{param_dupes.first.id}
            WHERE variable_id IN (#{param_dupes.pluck(:id).join(',')});

            RAISE NOTICE 'Variables updated: %', ?; 
            
            UPDATE tunings SET x_axis_id = #{param_dupes.first.id}
            WHERE x_axis_id IN (#{param_dupes.pluck(:id).join(',')});

            RAISE NOTICE 'Tunings updated: %', ?; 
        
            DELETE FROM tunings WHERE id IN (#{param_dupes.drop(1).map{ |dt| dt.id }.join(',')});

            RAISE NOTICE 'Tunings deleted: %', ?; 
        
        END LOOP;

    END LOOP;

    RETURN 1;
    
END;
$$ LANGUAGE plpgsql;

I'm stuck here, but I could probably figure out how to finish this with enough searching. The larger question is: Is this the right approach? Is there a simpler way to speed this thing up?

BTW, before I remembered that I had to deal with the self-references in the tunings table, this would run in 12 hours. Even though there "only" 48K duplicated tunings, because of the way they're linked to 33K calibrations, the dupes account for ~60M of the 95M tuning records. When the axes info is properly taken into account, this should result in another substantial reduction. I say all that to say: I understand that this is doing a LOT of work, but a buddy who works with Oracle is incredulous that this is taking this long, so I'm trying to see if I'm missing something obvious.

UPDATE: I've run EXPLAIN (ANALYZE, BUFFERS)... on the UPDATE variables... query, for just one duped variable, and I get this:

Update on variables  (cost=0.57..174137.06 rows=0 width=0) (actual Update on variables  (cost=0.57..174137.06 rows=0 width=0) (actual time=29520.679..29520.680 rows=0 loops=1)
  Buffers: shared hit=785059 read=112232 dirtied=66919 written=50982
  ->  Index Scan using variables_pkey on variables  (cost=0.57..174137.06 rows=38201 width=14) (actual time=10.943..17629.846 rows=38201 loops=1)
      Index Cond: (id = ANY ('{27864476,,...,,702744985}'::bigint[]))
      Buffers: shared hit=189291 read=76313 dirtied=1345 written=34311
Planning:
  Buffers: shared hit=33 read=19
Planning Time: 40.554 ms
Execution Time: 29521.238 ms

And on the UPDATE tunings... query (for the self-references):

Update on tunings  (cost=184.32..7375960.48 rows=0 width=0) (actual time=72479.787..72479.787 rows=0 loops=1)
  Buffers: shared hit=2373392 read=6180042 dirtied=73911 written=40463
  ->  Seq Scan on tunings  (cost=184.32..7375960.48 rows=66585886 width=10) (actual time=12.778..47073.264 rows=35532 loops=1)
      Filter: (x_axis_id = ANY ('{1720794,...,51997123}'::integer[]))
      Rows Removed by Filter: 83809176
      Buffers: shared hit=11010 read=6102391 dirtied=186 written=4588
Planning:
   Buffers: shared hit=1 read=5
Planning Time: 124.096 ms
Execution Time: 72480.073 ms

Unfortunately, I have no idea what that is telling me.

UPDATE 2: I've tried the idea (from the comments) of building a mapping table, and doing the UPDATE on the actual table all at once:

UPDATE variables v
SET tuning_id = mt.new_id
FROM mappings_temp mt
WHERE v.tuning_id = mt.old_id

But this seems to take just as long as doing it directly. It still seems like I'm missing a trick here. I would have thought that taking ~2-3 minutes to update ~35K rows in a 500M-row table was reasonable, but others think this is crazy, so I keep looking.