I host a lemmy instance for just my account. I don't host any active community or anything, but after 4 years my docker volumes grew to 155GB in total size so I tried to do some cleaning as I don't care that old content is not viewable anymore and I have limited storage (and no object storate for now).

I wanted to retain only the last year of content, but it turned out to be kind of difficult to achieve.

First of all, I didn't find any documentation that explains how this should be done so I did a full database and pictrs backup, stopped lemmy (but started PostgreSQL back up), and did some digging myself, with pretty much no prior knowledge whatsoever of how lemmy works internally, but some postgres experience.

Database

Database size was 35GB.

Reviewing the database schema, I found that purging content should be pretty straightforward. Deleting old posts should be enough because the post table has a trigger that handles deleting associated comments.

Set retention to 1 year :

retention=365

Remove posts older than ${retention} :

docker compose exec -it postgres psql -U lemmy -d lemmy -c "DELETE FROM post WHERE published < NOW() - INTERVAL '${retention} days';"

This started computing, but failed after a while because a LIMIT statement within some trigger function ends up having an illegal negative value, probably a bug as far as I can tell. So I revised the above command in order to perform the DELETE by one day steps from 1500 days old onward, in an effort to locate where things start to fail.

for ((age=1500; age>=${retention}; age-=1)); do
    echo "Older than ${age} days:"
    docker compose exec -it postgres psql -U lemmy -d lemmy -c "DELETE FROM post WHERE published < NOW() - INTERVAL '${age} days';"
done

Doing this, I could pinpoint the age of the culprit post because the DELETE statement started failing at "older than 949 days".

For those posts from this particular time interval, I disabled the triggers and performed the cleanup manually on the post and comment tables form psql.

• Open a psql prompt on the container :

docker compose exec -it postgres psql -U lemmy -d lemmy

• Disable the triggers on tables

ALTER TABLE comment DISABLE TRIGGER USER;
ALTER TABLE post DISABLE TRIGGER USER;

• Delete comments of posts older than 949 days :

DELETE FROM comment 
WHERE post_id IN (
    SELECT id FROM post WHERE published < NOW() - INTERVAL '949 days';
);

• Delete posts older than 949 days :

DELETE FROM post 
WHERE published < NOW() - INTERVAL '949 days';

• Re-enable the triggers :

ALTER TABLE comment ENABLE TRIGGER USER;
ALTER TABLE post ENABLE TRIGGER USER;

Running the previous stepped DELETE again, it proceeded to purge the rest of the data without issue, although it took hours to complete on my modest VPS.

This purge deleted lots of rows, but to reclaim the space on filesystem (and rebuild the tables and indices fresh, which doesn't hurt), I ran a VACUUM FULL on the database :

VACUUM FULL;

Database size is now 8GB. That's 27GB reclaimed!

Pict-rs

This volume was taking 120GB. While I do know my way around PostgreSQL, Pict-rs is totally foreign to me. I figured I could try to purge files older than a year old based on find's -mtime, not entirely sure if that would wreck the Pict-rs database or not, but having my volumes safely backed up, I tried it anyways :

• Set retention to 1 year :

retention=365

• Delete images older than the set retention :

find ./volumes/pictrs/files -type f -mtime +${retention} -delete

• Clean up empty directories left behind :

find ./volumes/pictrs/files -type d -empty -delete

This took forever but it cleared up a lot of space, as the pict-rs volume is now 12GB. That's 108 GB reclaimed!

I started lemmy to check things out, and while there are a few missing images here and there, all content from the last year is fine. My profile picture was gone, but I just uploaded another one.

For purging pict-rs, I realize that this method is kind of dumb but I didn't know what better to do. I would have liked to remove only images from posts and comment I had just purged from the database, so that things like profile pictures, community thumbnails, profile banners and such are left untouched, but I don't know how that could be done.

I'm relatively happy with the purge, and I won't restore from backup. But I can't help but feel this whole thing I did was pretty janky. Why is it such a massive pain to do housekeeping on lemmy? Are we expected to store everything until the end of time?

For a small/personal instance I would add a caveat to keep anything you have upvoted or minimum saved. That will come in handy later, and it's probably less than 1% of the data being removed.

Interesting, thank you. I could probably work out the SQL queries to do that next time.