Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and - Mailing list pgsql-hackers

> Rick Gigger wrote:
>> I was thinking the exact same thing.  Except  the "and just fsync()
>> dirty pages on commit" part.  Wouldn't that actually make the
>> situation worse?  I thought the whole point of WAL was that it was
>> more efficient to fsync all of the changes in one sequential write in
>> one file rather than fsyncing all of the separate dirty pages.
>
> Uh, supposedly the WAL traffic is not as efficient as fsyncing whole
> pages if you are doing lots of full pages.

So then you would want to use this particular feature only when  
updating/inserting large amounts of info at a time then?

For instance if you have a table with rows that are 12 bytes wide  
using this feature would be very bad because it would fsync out a  
whole bunch of extra data just to get those 12 bytes written.  But on  
a table that wrote out several k of data at a time it would help  
because it would be filling up entire pages and not doing any  
wasteful fsyncing?

I guess that probably would help session tables because it would  
avoid writing the data twice.

In the case of session tables though I for one don't care if that  
data is recoverable or not.  If my database just crashed I've  
probably got bigger problems then just dropped sessions.  Would it be  
possible to a) Not WAL log that table, b) not fsync that table at  
all.  Then if that table is in an inconsistent state just recreate  
the table?

I'm guessing that is getting a little too crazy but I have been  
thinking about setting up a separate postgres instance listening on a  
different port with fsync off as a possible way to avoid the fsync  
overhead for the sessions.


> ---------------------------------------------------------------------- 
> -----
>
>
>>
>> On Feb 6, 2006, at 7:24 PM, Christopher Kings-Lynne wrote:
>>
>>>>     * Allow WAL logging to be turned off for a table, but the table
>>>>       might be dropped or truncated during crash recovery [walcontrol]
>>>>     
>>>>       Allow tables to bypass WAL writes and just fsync() dirty  
>>>> pages on
>>>>       commit.  This should be implemented using ALTER TABLE, e.g.  
>>>> ALTER
>>>>       TABLE PERSISTENCE [ DROP | TRUNCATE | DEFAULT ].  Tables using
>>>>       non-default logging should not use referential integrity with
>>>>       default-logging tables.  A table without dirty buffers during a
>>>>       crash could perhaps avoid the drop/truncate.
>>>
>>> This would be such a sweet feature for website session tables...
>>>
>>> Chris
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 2: Don't 'kill -9' the postmaster
>>>
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>                http://archives.postgresql.org
>>
>
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,  
> Pennsylvania 19073
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>