Migration for robotoff, see https://github.com/openfoodfacts/robotoff/pull/556

robotoff-migration.sql

-- FIRST: backup database before anything !!!!!


-- drop indexes for prediction table for perfs
DROP INDEX prediction_barcode, prediction_data, prediction_server_domain, prediction_source_image, prediction_timestamp, prediction_type;

-- move rows
WITH to_copy_rows AS (
    SELECT
        barcode,
        type,
        data,
        timestamp,
        value_tag,
        value,
        source_image,
        automatic_processing,
        server_domain,
        predictor
    FROM
        product_insight
)
INSERT INTO
    prediction(
        barcode,
        type,
        data,
        timestamp,
        value_tag,
        value,
        source_image,
        automatic_processing,
        server_domain,
        predictor
    )
SELECT
    *
FROM
    to_copy_rows;

-- recreate indexes for prediction
CREATE INDEX prediction_barcode ON public.prediction USING btree (barcode);
CREATE INDEX prediction_data ON public.prediction USING gin (data);
CREATE INDEX prediction_server_domain ON public.prediction USING btree (server_domain);
CREATE INDEX prediction_source_image ON public.prediction USING btree (source_image);
CREATE INDEX prediction_timestamp ON public.prediction USING btree (timestamp);
CREATE INDEX prediction_type ON public.prediction USING btree (type);

-- drop indexes for product insight to speed-up mass remove
DROP INDEX product_insight_annotation, product_insight_automatic_processing, product_insight_barcode, product_insight_brands, product_insight_countries, product_insight_data, product_insight_latent, product_insight_latent_null_annotation_type, product_insight_predictor, product_insight_process_after, product_insight_reserved_barcode, product_insight_server_domain, product_insight_server_type, product_insight_source_image, product_insight_timestamp, product_insight_type, product_insight_unique_scans_n, product_insight_username, product_insight_value, product_insight_value_tag;

-- remove
DELETE FROM
    product_insight
WHERE
    latent IS TRUE;
VACUUM FULL product_insight;
    
-- recreate indexes
CREATE INDEX product_insight_annotation ON public.product_insight USING btree (annotation);
CREATE INDEX product_insight_automatic_processing ON public.product_insight USING btree (automatic_processing);
CREATE INDEX product_insight_barcode ON public.product_insight USING btree (barcode);
CREATE INDEX product_insight_brands ON public.product_insight USING btree (brands);
CREATE INDEX product_insight_countries ON public.product_insight USING gin (countries);
CREATE INDEX product_insight_data ON public.product_insight USING gin (data);
CREATE INDEX product_insight_latent ON public.product_insight USING btree (latent);
CREATE INDEX product_insight_latent_null_annotation_type ON public.product_insight USING btree (latent, annotation, type);
CREATE INDEX product_insight_predictor ON public.product_insight USING btree (predictor);
CREATE INDEX product_insight_process_after ON public.product_insight USING btree (process_after);
CREATE INDEX product_insight_reserved_barcode ON public.product_insight USING btree (reserved_barcode);
CREATE INDEX product_insight_server_domain ON public.product_insight USING btree (server_domain);
CREATE INDEX product_insight_server_type ON public.product_insight USING btree (server_type);
CREATE INDEX product_insight_source_image ON public.product_insight USING btree (source_image);
CREATE INDEX product_insight_timestamp ON public.product_insight USING btree ("timestamp");
CREATE INDEX product_insight_type ON public.product_insight USING btree (type);
CREATE INDEX product_insight_unique_scans_n ON public.product_insight USING btree (unique_scans_n);
CREATE INDEX product_insight_username ON public.product_insight USING btree (username);
CREATE INDEX product_insight_value ON public.product_insight USING btree (value);
CREATE INDEX product_insight_value_tag ON public.product_insight USING btree (value_tag);

The run was a bit cahotic:

I had an error because of some references in user_annontations (which is legacy and should not exists).
So finally I get the ids with:

select p.id from product_insight p inner join user_annotation ua on p.id = ua.insight_id
 where p.latent is TRUE;

and add this id in the delete:

DELETE FROM
    product_insight
WHERE
    latent IS TRUE and id not in  ('xxxxxx', 'yyyyy', ...);

Approx timings:

• DB backup : 45 min
• Drop indexes : instantaneous
• Copy of data : 6 min
• recreate predictions indexes: 6 min 30s
• delete latent insights: 10 min
• vacuum: 42s
• recreate insights indexes: 1min 30s