Re: Idea for getting rid of VACUUM FREEZE on cold pages

On Thu, May 27, 2010 at 2:00 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Well, maybe I'm confused here, but arranging things so that we NEVER
>> have to visit the page after initially writing it seems like it's
>> setting the bar almost impossibly high.
>
> That is the use case, though.  What I've encountered so far at 3 client
> sites is tables which are largely append-only, with a few selects and
> very few updates (< 2%) on recent data.   In general, once data gets
> flushed out of memory, it goes to disk and never gets recalled, and
> certainly not written.

We might be able to optimize this case if the transactions are small,
such that they commit before dirtying too large a fraction of
shared_buffers. We could - at least in theory - teach the bgwriter or
some other process to freeze them before writing them to disk the
first time. But if the blocks have to be written to disk before
transaction commit it seems to me we're DOA, unless we're willing to
retain arbitrarily large amounts of CLOG.

What might be more practical is to try to find ways to spread out the
I/O so that it doesn't happen all at once in a huge ornery spike.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company