pleromer-stuff/database_prune.sql

\c pleroma
do $$
declare
maxA integer := (SELECT count(*) from activities WHERE inserted_at < now() - interval '4 months');
maxO integer := (SELECT count(*) from objects WHERE inserted_at < now() - interval '4 months');
maxN integer := (SELECT count(*) from notifications WHERE inserted_at < now() - interval '4 months');
counter integer := 0;
begin
RAISE NOTICE 'Marked for deletion: % activities', maxA;
while counter < maxA loop
DELETE FROM activities WHERE id = any (array(SELECT id FROM activities WHERE inserted_at < now() - interval '4 months' LIMIT 1000));
COMMIT;
PERFORM pg_sleep(3);
counter := counter + 1000;
end loop;
RAISE NOTICE 'Marked for deletion: % objects', maxO;
counter := 0;
while counter < maxO loop
DELETE FROM deliveries WHERE object_id = any (array(SELECT id FROM objects WHERE inserted_at < now() - interval '4 months' LIMIT 10000));
COMMIT;
PERFORM pg_sleep(3);
counter := counter + 1000;
end loop;
counter := 0;
while counter < maxO loop
DELETE FROM objects WHERE id = any (array(SELECT id FROM objects WHERE inserted_at < now() - interval '4 months' LIMIT 1000));
COMMIT;
PERFORM pg_sleep(3);
counter := counter + 1000;
end loop;
RAISE NOTICE 'Marked for deletion: % notifications', maxN;
counter := 0;
while counter < maxO loop
DELETE FROM notifications WHERE id = any (array(SELECT id FROM notifications WHERE inserted_at < now() - interval '4 months' LIMIT 1000));
COMMIT;
PERFORM pg_sleep(3);
counter := counter + 1000;
end loop;
end$$;