Jeff Janes <jeff.janes 'at' gmail.com> writes: > On Wed, Feb 26, 2020 at 1:02 PM Guillaume Cottenceau <gc@xxxxxx> wrote: > > It is actually consistent with using a restored backup on the dev > computer, as my understanding is this comes out without any > garbage and like a perfectly vacuumed database. > > I think I got that backwards in my previous email. It is the > dev that was restored, not the prod? But unless you went out of Yes (prod was also restored not so long ago, when updating to pg 11.5 tho). > your way to vacuum dev, it would not be perfectly vacuumed. If > it were a logical restore, it would be perfectly unvacuumed, > and if a physical restore would be in the same state of > vacuuming as the database it was cloned from. > > Btw do you have > any hint as to how to perform timings using production data which > are consistent with production? Backup/restore is maybe not the > way to go, but rather a block device level copy? > > block device copy seems like overkill, just using pg_basebackup should be good enough. > > Since postgresql 8, I have to say I rely entirely on autovacuum, > and did not notice it could really run too infrequently for the > work and create such difference. I see in documentation a default > autovacuum_vacuum_scale_factor = 0.2, is that something that is > typically lowered globally, e.g. maybe on a fairly active system? > I am worried that changing that configuration for that table to > 0.005 would fix this query and similar ones, but later I might > face the same situation on other tables. Or how would you elect > tables for a lowered value configuration? > > The autovacuum system has never been redesigned with the needs of index-only-scans in mind. If I have a table for which > index-only scans are important, I'd set autovacuum_vacuum_scale_factor = 0 and set autovacuum_vacuum_threshold to about 5% of > the number of blocks in the table. There is no syntax to say '5% of the number of blocks in the table' so you have to compute > it yourself and hardcode the result, which makes it unsuitable for a global setting. And this still only addresses UPDATE and It seems also difficult for us as this table grows over time (and is trimmed only infrequently). > DELETE operations, not INSERTs. If you have INSERT only or mostly table for which index-only-scans are important, you might > need to set up cron jobs to do vacuuming. Thanks! -- Guillaume Cottenceau
