Re: Unlogged vs. In-Memory

On Wed, May 4, 2011 at 5:21 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, May 4, 2011 at 11:51 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> Excerpts from Simon Riggs's message of mié may 04 12:30:03 -0300 2011:
>>> On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>
>>> > To make PG do it automatically, we'd need to store the _init forks in
>>> > a different tablespace from the remaining forks.  That's probably
>>> > possible, but it seems complicated.
>>>
>>> Sounds much better way actually and also quite easy. All we do is keep
>>> the init forks in a subdirectory that identifies the tablespace they
>>> relate to.
>>
>> Is there a way to "update" the init fork after table creation?  If so,
>> you could periodically copy stuff from the current contents (main fork)
>> into the init fork; or update the init fork with some command (think
>> UPDATE or COPY).
>
> Well, the _init fork can go arbitrarily long without being used, so
> you can't put any unfrozen tuples in there.  There may be some game
> that can be played here, but it's not simple, especially since the
> heap and indices have to stay in sync.

I don't think that's a sufficient response. It's clear that people
expect unlogged tables would be used in conjunction with RAM disks,
but they clearly don't work in that situation.

That is exactly the main use case of "cache tables".

> I actually think there is very little low-hanging fruit to be found in
> terms of improving unlogged tables.

Solving Rob's complaint seems very easy to me.

> The things that I didn't tackle
> got skipped because they were really hard or low-value or had
> significant downsides or all three.

> We're not going to find a general
> solution to this problem that is cheaper than WAL-logging everything;
> that's why WAL-logging is basically the only form of crash-safety used
> by any modern database product.

That's not accurate. Many products provide a means to load bulk data
without hitting the transaction log, without the need to truncate the
table.

> I think that the solution to the
> problem of "I don't want to lose the whole table when the database
> crashes" is going to involve partitioning - have a logged partition
> and an unlogged partition, and periodically move stuff over.  Even we
> ultimately provide some automated way to have that happen under the
> covers, I think that's still what it's going to be doing.  I might be
> all wet, of course, but that's what I think.

That's very roughly what NOLOGGING hint does on an Oracle table, but
without the partitioning.

Definitely too late to add that to 9.1, I agree.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services