From: Michael Lewis <mlewis@xxxxxxxxxxx>
> You say 12.2 is in testing but what are you using now? Have you tuned configs much? Would you be able to implement partitioning such that your deletes become truncates or simply a detaching of the old partition? Generally if you are doing a vacuum full, you perhaps need to tune autovacuum to be more aggressive. Consider pg_repack at least to avoid taking an exclusive lock for the entire duration. If partitioning is not an option, could you delete old records hourly rather than daily?
Good questions, it's always hard to know how much to include. 😊
Current production is 9.6, so things like partitioning aren't available there, but will be in the future.
We've tuned the configs some and don't having any issues with Pg at the moment. This does need to be relooked at; I have a few notes of things to revisit as our hardware changes.
Partitioning our larger tables by time is on the ToDo list. I hadn't thought about that helping with maintenance, so thanks for bringing that up. I'll increase the priority of this work as I can see this helping with the archiving part.
I don't particularly like doing the vacuum full, but when it will release 20-50% of disk space for a large table, then it's something we live with. As I understand, a normal vacuum won't release all the old pages that a "full" does, hence why we have to do that. It's painful enough I've restricted it to once quarter; I'd do it only once a year if I thought I could get away with it. Still this is something I'll put on the list to go research with practical trials. I don't think the lock for the vacuuming hurts us, but I've heard of pg_repack and I'll look into that too.
I have considered (like they say with vacuuming) that more often might be better. Of course that would mean doing some of this during the day when the DB is busier. Hmm, maybe 1000/minute wouldn't hurt and that would shorten the nightly run significantly. I may have to try that and see if it just adds to background noise or causes problems.
Thanks!
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Kevin
"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler
