On Wed, Jul 5, 2017 at 1:00 PM, PT <wmo...@potentialtech.com> wrote: > > 2x the working size for a frequently updated table isn't terrible bloat. > Or are > you saying it grows 2x every 24 hours and keeps growing? The real question > is > how often the table is being vacuumed. How long have you let the > experiment run > for? Does the table find an equilibrium size where it stops growing? Have > you > turned on logging for autovacuum to see how often it actually runs on this > table? >
If it were only twice it would not bother me. The fact that it is twice after 24 hrs, 3x after 48 hrs and 4x after 72 hrs is alarming. > > No unremovable rows does not indicate that autovaccum is keeping up. It > just > indicates that you don't have a problem with uncommitted transactions > holding > rows for long periods of time. > Right. I should have specified that I also have not seen auto vacuum in pg_stat_activity with an unusual duration. > > Have you looked at tuning the autovacuum parameters for this table? More > frequent > vacuums should keep things more under control. However, if the write load > is > heavy, you'll probably want to lower autovacuum_vacuum_cost_delay. > Personally, > I feel like the default value for this should be 0, but there are likely > those > that would debate that. In any event, if that setting is too high it can > cause > autovacuum to take so long that it can't keep up. In theory, setting it > too low > can cause autovaccum to have a negative performance impact, but I've never > seen > that happen on modern hardware. > Most of the writes are periodic (hourly?) batch updates which are fairly big. > > But that's all speculation until you know how frequently autovacuum runs on > that table and how long it takes to do its work. > Given the other time I have seen similar behaviour, the question in my mind is why free pages near the beginning of the table don't seem to be re-used. I would like to try to verify that however, if you have any ideas. > > -- > PT <wmo...@potentialtech.com> > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
