Primarily of interest to small, semi-private instances.

If you use Lemmy Community Seeder or other jumpstart method to automatically subscribe to communities you may find a large number of low-traffic communities build up over time. The following can be used to purge these communities en masse, without the overhead of using the API. Will not purge local instance communities regardless of activity level. Tested with 18.2.

I did not do the core legwork on this but am no longer able to locate the original source. If you recognize it, please let me know so I can provide proper attribution.

DO $$
DECLARE
-- Communities with less than postCount of posts will be purged
-- dryRun := true to simulate purge. dryRun := false to actually purge. Destructive. No warranties.	
postCount INT := 5;
dryRun BOOLEAN := true;
--
arow record;
communityID INT;	

BEGIN
    for arow in
    
    SELECT community_aggregates.community_id, community_aggregates.posts FROM
	   	public.community_aggregates INNER JOIN
    	public.community On public.community_aggregates.community_id = community.id
    	WHERE instance_id <> 1 AND posts < postCount

	loop
		communityID := arow.community_id;
		RAISE NOTICE 'Purging community ID: %', communityID;

		-- Easiest way to disable triggers for a transaction.
		SET session_replication_role = replica;

		-- Delete Comment Likes
		DELETE FROM comment_like WHERE post_id IN (SELECT id FROM post WHERE community_id = communityID);
		
		-- Delete Comment Aggregates
		DELETE FROM comment_aggregates WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Replies
		DELETE FROM comment_reply WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Reports
		DELETE FROM comment_report WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Saved
		DELETE FROM comment_saved WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM community_aggregates WHERE community_id = communityID;
		DELETE FROM community_block WHERE community_id = communityID;
		DELETE FROM community_follower WHERE community_id = communityID;
		DELETE FROM community_language WHERE community_id = communityID;
		DELETE FROM community_moderator WHERE community_id = communityID;
		DELETE FROM community_person_ban WHERE community_id = communityID;
		DELETE FROM mod_add_community WHERE community_id = communityID;
		DELETE FROM mod_ban_from_community WHERE community_id = communityID;
		
		DELETE FROM mod_feature_post WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM mod_hide_community WHERE community_id = communityID;
		
		DELETE FROM mod_lock_post WHERE post_id IN (
		    (SELECT id FROM post WHERE community_id = communityID)
		);
		
		DELETE FROM mod_remove_comment WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM mod_remove_community WHERE community_id = communityID;
		
		DELETE FROM mod_remove_post WHERE post_id IN (
		    (SELECT id FROM post WHERE community_id = communityID)
		);
		
		DELETE FROM mod_transfer_community WHERE community_id = communityID;
		
		DELETE FROM person_mention WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM post_aggregates WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		
		DELETE FROM post_like WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM post_read WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM post_report WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		
		DELETE FROM post_saved WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		-- Delete comments for posts IN removed community
		DELETE FROM comment WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		-- These last two must be last as they are used to find related entries IN all of the other tables.
		-- Delete the posts for the community
		DELETE FROM post WHERE community_id = communityID;
		    
		-- Delete the community
		DELETE FROM community WHERE id = communityID;
		
		--- If there are no errors or issues, commit
		IF dryRun = true THEN
			ROLLBACK;
		END IF;
			
		IF dryRun = false THEN
			COMMIT;
		END IF;
		
	END LOOP;

	IF dryRun = true THEN
		RAISE NOTICE 'Dry-run only. No communities actually purged.';
		RAISE NOTICE 'Set dryRun BOOLEAN := true to really purge';
		ROLLBACK;
	END IF;
	
	IF dryRun = false THEN
		COMMIT;
	END IF;
END $$;