Greg Stark <st...@mit.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
