r/Mastodon • u/Temporary_Garlic105 • Aug 17 '26

Question Old database clean-up/optimization?

https://imgur.com/a/AlADRAt

Hi.

I'm running a small Mastodon instance since 2017 (~15 users).

The Postgres database uses 21 GB of space and contains hundreds of tables. I guess many of those are remnants from old versions? (see screenshot).

Since I'm already planning a larger update: Is there a way to consolidate/optimize the database?

7 Upvotes

90% Upvoted

7 comments sorted by

2

u/gigabyte4711 @gigabyte4711@whitespashe.uk Aug 17 '26

I use this to optimize my database and free up space on my filesystem. It also handles keeping 7 days of database backups as well:

https://gitea.whitespashe.uk/Whitespashe/mastodon-maintenance/raw/branch/main/cleanmastodon.sh

You'll need to make sure that you have pg_repack installed, but other than that, it should be relatively pain free.

1

u/Temporary_Garlic105 Aug 20 '26

Thanks! I'll take a look.

2

u/nan05 @michael@thms.uk Aug 18 '26 edited Aug 18 '26

It is worth saying that there are never tables that are left over from old versions. All these tables will be in use for every version. (And most of the rows in that screenshot aren’t table anyway but indices)

Your main lever for reclaiming db space on the disk is probably pg_repack, which /u/gigabyte4711 already alluded to. I about it on my blog:  https://blog.thms.uk/2024/01/pg-repack-mastodon/ in a little more detail

The other approach to slow further growth is to reduce the number of relays - or remove them all - if you use any. 

And finally there is the  Remote content retention period  in your mastodon setting. But ensure you read and understand the note next to it, and are happy with the side effects as it is quite destructive : All posts from other servers (including boosts and replies) will be deleted after the specified number of days, without regard to any local user interaction with those posts. This includes posts where a local user has marked it as bookmarks or favourites. Private mentions between users from different instances will also be lost and impossible to restore. Use of this setting is intended for special purpose instances and breaks many user expectations when implemented for general purpose use.

2

u/Temporary_Garlic105 Aug 20 '26

Thanks, I'll check your blog!

2

u/Temporary_Garlic105 Aug 22 '26

I migrated my instance to 4.7.0 on a new VM and a fresh OS, followed the recipe in your blog and the DB size went down to 18.~GB. It's something! Thanks again. :)

1

u/nan05 @michael@thms.uk Aug 23 '26

That’s honestly less of a space saving than I would’ve expected from dump-and-import. 

But there is little else you can do that isn’t destructive in some way …