pleromer-stuff/deletes.md

Deletes are the danger zone, deleting activities from the table can and will introduce weird problems. For example, when a new activity federates in referencing a deleted activity, it'll cause notifications to temporarily break for the affected user(s). Pleroma will look in the table for the activity it's referencing, that'll time out, and the request to load notifications into pleroma-fe will 502 and they'll just be blank.

But if it's a choice between killing the instance entirely due to storage constraints or deleting activities and accepting the risk of errors, we can delete them. Before you delete anything, take a look at how to clean up pg_repack remnants here.

You can check the size of each table and get an idea of your worst offenders with this pgsql command:

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Since there's a LOT of stuff to delete, we don't want to do it all at once and piss off pgsql. Here's an example pgsql script to delete all activities, both local and remote, older than 2024-01-01:

do $$
declare
counter integer := 0;
begin
while counter < 1000000 loop
DELETE FROM activities WHERE id = any (array(SELECT id FROM activities WHERE inserted_at < '2024-01-01' LIMIT 100));
COMMIT;
PERFORM pg_sleep(2);
counter := counter + 1;
end loop;
end$$;

It will occasionally fail when something weird happens, but you can just run it again until you get no results from:

SELECT count(*) FROM activities WHERE inserted_at < '2024-01-01';

The objects table is a little different and requires coordination with the deliveries table.

do $$
declare
counter integer := 0;
begin
while counter < 1000000 loop
DELETE FROM deliveries WHERE object_id = any (array(SELECT id FROM objects WHERE inserted_at < '2024-01-01' LIMIT 1));
DELETE FROM objects WHERE id = any (array(SELECT id FROM objects WHERE inserted_at < '2024-01-01' LIMIT 1));
COMMIT;
PERFORM pg_sleep(2);
counter := counter + 1;
end loop;
end$$;