To other Mastodon sysadmins: Check what pg_repack can do for you, especially if your server has been around for a really long time. PostgreSQL indexes get bloated over time and VACUUM does nothing about it. pg_repack can re-build indexes without downtime.
I shaved off 40 GB from the mastodon.social database just by using pg_repack on indexes tonight.
@Gargron will check
@Gargron how does that differ from rebuild index concurrently
@ben Older postgres versions don't have rebuild index concurrently
Thank you very much, you are the best
@Gargron How big are the database and media directories in total? What size of server is required to host a community like mastodon.social?
@Gargron Ah, so you're starting the year with a thorough cleaning of the house. Quite respectable.
@Gargron at work we have an intense INSERT/UPDATE’ activity on the core DB so we must rebuild indexes every week to ensure performance and mitigate storage waste.
@Gargron Next Hogmanay come for a ceilidh instead :)
Bliadhna Ur Mhath
@gargron "Eugen used this one weird trick to reduce the size of his PostgreSQL database by 40 GB! Hosting vendors hate him!!"
start the year off right, start the year’s mastodon off light
@Gargron thank you I know what this means
@ldopa If you’re on masto.host you can’t do anything with this information
@Gargron oooooh i should check this
@Gargron would be great if you could write up some instructions or docs for this 😀
@Gargron how I wish I understood what you are saying! How glad I am that you who do use your knowledge to help us have a voice!
@Gargron postgres 12 has reindex concurrently
Server run by the main developers of the project It is not focused on any particular niche interest - everyone is welcome as long as you follow our code of conduct!