Bulk delete value set data

[marschall]

JdbcValueSetDataServiceImpl#deleteOldValueSets() uses N + 1 to delete data. This could be done in a single query / database round trip, for example using a window function.

DELETE FROM t_value_set_data
WHERE pk_value_set_data_id IN (
   SELECT pk_value_set_data_id
     FROM (SELECT pk_value_set_data_id,
                row_number() OVER (PARTITION BY value_set_id ORDER BY created_at DESC) as row_number
            FROM t_value_set_data) as partitioned_value_set_data
    WHERE row_number > :max_history);

If you prefer a CTE

WITH partitioned_value_set_data(pk_value_set_data_id, row_number) AS (
    SELECT pk_value_set_data_id,
           row_number() OVER (PARTITION BY value_set_id ORDER BY created_at DESC) AS row_number
      FROM t_value_set_data)
DELETE FROM t_value_set_data
      WHERE pk_value_set_data_id IN (SELECT pk_value_set_data_id
                                       FROM partitioned_value_set_data
                                       WHERE row_number > :max_history);