So I wanted to do some attachment cleanup...
For context, I'm a very small instance but use relays that have tons of instances which is great for content variety; but some post more/larger attachments than others and aren't relevant to anyone on here. Mostly weather bots or ATC info etc. I wanted to find these more efficiently. So I wrote some SQL and wanted to share in case maybe someone else finds it useful!
⚠️ DISCLAIMER: ⚠️ Use at your own risk! I'm not responsible for any damage you do in running this nor will I support/update/maintain it. I'll accept criticism/feedback/improvements though.
SELECT pg_size_pretty(SUM(att.file_file_size)) AS total, acc.domain<br></br>FROM public.media_attachments att<br></br>JOIN public.statuses sta<br></br>ON att.status_id = sta.id<br></br>JOIN public.accounts acc<br></br>ON sta.account_id = acc.id<br></br>WHERE att.file_file_size IS NOT null<br></br>GROUP BY acc.domain<br></br>ORDER BY SUM(att.file_file_size) DESC <br></br>LIMIT 10<br></br>
Add comment