Mastodon media cache ages by percentile

cache_age_percentiles.sh

#!/bin/sh

# Shows various percentiles for the age of your media attachments, i.e. the
# stuff under mastodon/web/system/cache/media_attachments/
# Usage:
#   - Have psql installed so you can connect to your database.
#   - Make sure to set PGUSER, PGHOST and PGDATABASE environment variables.
#   - Also set PGPASSWORD otherwise it will ask you.
#   ./cache_age_percentiles.sh

PGUSER=${PGUSER-mastodon}
PGHOST=${PGHOST-172.20.0.3}
PGDATABASE=${PGDATABASE-mastodon_production}

# Horrible query. I'm sure it could be improved.
# file_file_size is in there too, so perhaps it could show how many bytes would
# be inside that percentile.
sql=$(cat <<EOQ
SELECT
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.10) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "10th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.25) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "25th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.50) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "50th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.75) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "75th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.95) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "95th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(0.99) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "99th percentile",
    justify_interval(
        date_trunc('second', make_interval(
            secs =>  percentile_disc(1.00) within group (
                order by extract(
                    epoch from now() - file_updated_at
                )
            )
        ))
    ) as "Oldest"
FROM media_attachments
WHERE file_updated_at IS NOT NULL;
EOQ
)

echo "$sql" | psql -U "$PGUSER" \
    -h "$PGHOST" \
    -d "$PGDATABASE" \
    -t \
    -A \
    -q \
    --csv \
    | awk -F, '{
    printf "10%% of media is younger than… %20s\n", $1
    printf "25%% of media is younger than… %20s\n", $2
    printf "50%% of media is younger than… %20s\n", $3
    printf "75%% of media is younger than… %20s\n", $4
    printf "95%% of media is younger than… %20s\n", $5
    printf "99%% of media is younger than… %20s\n", $6
    printf "                Oldest media: %20s\n", $7
}'