/* PostgreSQL Cleaning and shrinking the table */ :::1. Delet | SergeyGurin

/*
PostgreSQL
Cleaning and shrinking the table
*/

:::1. Delete unnecessary data from the table
do
$$
DECLARE
r guest_engine.dino_speed_of_service%rowtype;
i integer := 0;
BEGIN
FOR r IN
select *
from guest_engine.dino_speed_of_service
where insert_date <= current_date - 100 * INTERVAL '1 DAY'
--and date_part('year',insert_date) = date_part('year', CURRENT_DATE)
--limit 1000000
LOOP
BEGIN
delete from guest_engine.dino_speed_of_service where uid = r.uid;
i := i + 1;
IF (i > 1000) THEN
commit;
i := 0;
END IF;
END;
END LOOP;
commit;
END
$$ language plpgsql;

:::2. Reclaim storage occupied by dead tuples
VACUUM (FULL, VERBOSE, ANALYZE) guest_engine.dino_speed_of_service;