Re: HOT patch, missing things

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:

> Because we can truncate dead tuples, even from cold updates and deletes,
> to redirected dead line pointers which take much less space than dead
> tuples, maybe we should increase the default autovacuum threshold?

That would be the logical conclusion except that I think the original
threshold was way too high.

If you have 100 tuples on the page we were waiting until there were 20 dead
tuples before vacuuming. In that scenario 20 dead line pointers would take 80
bytes or about the size of one tuple. In other words about the point when it
might be useful to vacuum.

On the other hand if you only have 20 tuples per page then 20% would only be 4
tuples or 16 bytes when your tuples are 400 bytes each and there's no point
vacuuming yet. Or if you have 500 tuples per page then 20% means 100 line
pointers or 400 bytes when each tuple is only 16 bytes so we would be putting
off vacuuming until there's enough space for 25 tuples.

It seems that previously percentage of tuples made sense because dead tuples
took about the same amount of space as new tuples that need that space. But
line pointers take much less space than the new tuples so the number of dead
line pointers we need before we can recover a useful amount of space depends
on the ratio of line pointer size to tuple size.

Perhaps we should be gathering "bytes of dead tuples" in pg_stat not just
n_dead_tuples.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com