Stephen Harris <lists@spuddy.org> writes:
> It's vacuumed every night after the updates. There are minimal (zero,
> most days) updates during the day. As I mentioned earlier, nightly we do:
> for host in list_of_hosts
> delete from sweep_users where hostid=host
> for user in users_for_host
> insert into sweep_users ....
> vacuum analyze sweep_users
Hmm ... no overlap between the sets of users for different hosts?
This looks like the worst-case bloat should be 2X (at most one dead
and one live row per user), but the numbers you are reporting ---
particularly the unused-item-pointer count --- show clearly that at
some point there was a bloat factor of more than 100, ie, there had
been at least 100 complete replacements of the table without a vacuum.
Perhaps that vacuum step was only recently added to this script?
> You recommend a "cluster sweep_users" before the vacuum, then?
I wouldn't think you need to do it every night, it's just a one-time
fix.
regards, tom lane