Re: Unlogged vs. In-Memory

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 actually think there is very little low-hanging fruit to be found in
terms of improving unlogged tables. 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. 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.

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