- New versions are created for all rows, even if the data is identical. This quickly fills up the WAL and puts unnecessary load on the tablespace drives.
- If the conflict target is not the serial column, the sequence backing the serial column gets incremented for every row. This quickly reaches the point where the serial for new rows exceeds the range of an integer.
It sounds like you aren't adding a WHERE clause to prevent the duplicate rows from being updated. It would help if you could share your query, but in general this could look like this:
INSERT INTO my_table (col1, col2)
SELECT col1, col2 FROM other_table
ON CONFLICT (col1, col2) DO UPDATE SET (col1, col2) = (EXCLUDED.col1, EXCLUDED.col2)
WHERE (my_table.col1, my_table.col2) IS DISTINCT FROM (EXCLUDED.col1, EXCLUDED.col2);
