Re: Turning off HOT/Cleanup sometimes

Greg Stark <stark(at)mit(dot)edu> wrote:

> And it's a major headache, people are always being surprised that
> their selects cause lots of I/O and slow down dramatically after
> a big update or data load has finished. It's characterized as
> "why is the database writing everything twice" (and saying it's
> actually writing everything three times doesn't make people feel
> better).

When I looked at the life-cycle of a heap tuple in a database I was
using, I found that (ignoring related index access and ignoring
WAL-file copying, etc., for our backups), each tuple that existed
long enough to freeze and be eventually deleted caused a lot of
writes.

(1) WAL log the insert.
(2) Write the tuple.
(3) Hint and rewrite the tuple.
(4) WAL log the freeze of the tuple.
(5) Rewrite the frozen tuple.
(6) WAL-log the delete.
(7) Rewrite the deleted tuple.
(8) Prune and rewrite the page.
(9) Free line pointers and rewrite the page.

If I was lucky some of the writes could be combined in cache
because they happened close enough together. Also, one could hope
that not too much of the WAL-logging involved full page writes to
the WAL -- again, keeping steps close together in time helps with
that. If all of (1) through (5) are done in quick succession, you
save two physical writes of the heap page and save one full page

write to WAL. If steps (7) through (9) are done in quick
succession, you save two more physical writes to the heap. This is
part of what makes the aggressive incremental freezing being
discussed on a nearby thread appealing -- at least for some
workloads.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company