2026-10-14 20:04 UTC
@jaz@toot.wales This gives a sum by media type for remote posts (accounts.domain for the associated id is not NULL - those would be local):
select
case
when m.type='0' then 'image'
when m.type='1' then 'gifv'
when m.type='2' then 'video'
when m.type='3' then 'unknown'
when m.type='4' then 'audio'
else 'undefined'
end as content_type_text,
sum(m.file_file_size)/1024/1024 as media_size_mb
from media_attachments m, accounts a
where m.file_file_name is not null and m.account_id=a.id and a.domain is not null group by content_type_text;
Replies (1)
-
@galaxis@mastodon.infra.de 2026-10-14 20:06
@jaz@toot.wales Also sometimes use this to find out who most media in the cache is from: select sum(m.file_file_size)/1024/1024 as media_size_mb, a.domain, a.username from media_attachments m, accounts a where m.file_file_name is not null and m.account_id=a.id and a.domain is not null group by a.username, a.domain order by media_size_mb desc limit 10; (change to "a.domain is null" for local users)