Watching and reading a presentation from PGConf NYC 2022, "How to Tame A Mastodon: Lessons for PostgreSQL at Scale by David Christensen"
-
Watching and reading a presentation from PGConf NYC 2022, "How to Tame A Mastodon: Lessons for PostgreSQL at Scale by David Christensen"
-
2/
My self-hosted Mastodon database was 40gb.For the first time in 2 years, I ran VACUUM(FULL, ANALYZE, VERBOSE); in psql.
I hadn't done a full, ever, on this database. I've repacked.
In less than 30 minutes, my database went down to 16.7GB.
I've been hesitant to work with my database due to my Parkinson's. I don't want to erroneously mess it up with a series of bad, unintentional clicks. For that reason, I probably overkill on the database backups when I do work with it.ðŸ¤
-
@paul your postgres thanks you
-
It's a load off my mind.
-
@paul My checkmk would go crazy if i let that slide for that long of a time.
-
It's weighed heavily on my mind for a long time.
-
@paul Can you script the operation to make it less sensitive to a misclick? If you have a chance to fix typoes once and then keep it on hand for next time maybe it's easier to do regularly.
I'm thinking just as an automation nerd rather than necessarily about accommodations so you're definitely free to flip me off and say it doesn't work for you.
-
You can. I am also going to setup AUTOVACUUM. It does it in increments.
Still, a full every so often is a good habit to get into, IMO.
Auto Vacuum Explained: Postgres Internals
https://dev.to/yusadolat/auto-vacuum-explained-postgres-internals-4gin -
@paul Thanks Paul for reminding me to do the same. Went from 7 to 6.5 GB so not much gain here. However, I found that after my recent Ubuntu 20.04 --> 22.04 upgrade, I was having this issue:
https://dba.stackexchange.com/questions/324649/collation-version-mismatch
Mastodon was working perfectly, but the VACUUM was spitting out these messages. The REINDEX and ALTER DATABASE commands from the link solved the issue and the in a second try the VACUUM ran without any issues.
-
I cringe every time there is a major OS or software upgrade. Glad you got it sorted out easily.