On Tue, Apr 21, 2020 at 15:05 Kevin Brannen <kbran...@efji.com> wrote:
> *From:* Michael Lewis <mle...@entrata.com> > > > 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. > Why do vacuum full at all? A functional autovacuum will return the free pages to be reused. You just won’t see the reduction in disk usage at the OS level. Since the pages are clearly going to be used it doesn’t really make sense to do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping up a normal vacuum without the full. The on dusk sizes will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables. > > > 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! > > Kevin > 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. > -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler
