Manual workaround below.

This gets rid of REMOTE posts older than a year, except those that

• are from people followed by local users
• or have been favourited by local users
• or have been reacted to by local users
• or have been watched by local users

Season to taste

DELETE

FROM "note" n

WHERE n."userHost" IS NOT NULL

AND n."createdAt"::date <= (CURRENT_DATE - interval '365' DAY)

AND NOT EXISTS

    (SELECT id

     FROM "following" f

     WHERE n."userId" = f."followeeId"

       OR n."userId" = f."followerId"

       OR n."renoteId" = f."followeeId"

       OR n."renoteId" = f."followerId")

AND NOT EXISTS

    (SELECT id

     FROM "note_like" nf

     WHERE nf."userId" IN

         (SELECT id

          FROM "user" u

          WHERE u."host" IS NULL

            AND nf."noteId" = n.id))

AND NOT EXISTS

    (SELECT id

     FROM "note_reaction" nr

     WHERE nr."userId" IN

         (SELECT id

          FROM "user" u

          WHERE u."host" IS NULL

            AND nr."noteId" = n.id))

AND NOT EXISTS

    (SELECT id

     FROM "note_watching" nw

     WHERE nw."userId" IN

         (SELECT id

          FROM "user" u

          WHERE u."host" IS NULL

            AND nw."noteId" = n.id));

Unfortunately, if you have a super large notes table, that just fills up all of your shared memory and dies. However, you put me on the right track. This does the trick for anyone who comes hear via search with this problem:

DO $$

DECLARE

    cursor_date DATE := (CURRENT_DATE - interval '183 days')::date;

    stop_date DATE := (CURRENT_DATE - interval '10 years')::date;

    rows_deleted INT;

BEGIN

    RAISE NOTICE 'Starting Time Walker deletion...';

    CREATE TEMP TABLE IF NOT EXISTS temp_local_users AS

    SELECT id FROM "user" WHERE "host" IS NULL;

    CREATE INDEX IF NOT EXISTS idx_temp_local_users ON temp_local_users(id);

    WHILE cursor_date > stop_date LOOP

        

        DELETE FROM "note" n

        WHERE n."createdAt" >= cursor_date

          AND n."createdAt" < (cursor_date + interval '1 day')

          AND n."userHost" IS NOT NULL

          

          AND NOT EXISTS (

              SELECT 1 FROM "following" f

              WHERE n."userId" = f."followeeId" OR n."userId" = f."followerId"

       OR n."renoteId" = f."followeeId"

       OR n."renoteId" = f."followerId"

          )

          

          AND NOT EXISTS (

              SELECT 1 FROM "note_like" nl

              WHERE nl."noteId" = n.id

              AND EXISTS (SELECT 1 FROM temp_local_users u WHERE u.id = nl."userId")

          )

          

          AND NOT EXISTS (

              SELECT 1 FROM "note_reaction" nr

              WHERE nr."noteId" = n.id

              AND EXISTS (SELECT 1 FROM temp_local_users u WHERE u.id = nr."userId")

          )

          AND NOT EXISTS (

              SELECT 1 FROM "note_watching" nw

              WHERE nw."noteId" = n.id

              AND EXISTS (SELECT 1 FROM temp_local_users u WHERE u.id = nw."userId")

          );

        GET DIAGNOSTICS rows_deleted = ROW_COUNT;

        

        IF rows_deleted > 0 THEN

            RAISE NOTICE 'Cleared %: deleted % rows', cursor_date, rows_deleted;

        END IF;

        COMMIT;

        

        cursor_date := cursor_date - interval '1 day';

        

        PERFORM pg_sleep(0.05);

        

    END LOOP;

    

    DROP TABLE IF EXISTS temp_local_users;

    RAISE NOTICE 'Cleanup complete.';

END $$;

Nice. I would like to see something official eventually but this works well for me too.