Skip site navigation (1) Skip section navigation (2)

Re: NOLOGGING option, or ?

From: Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: NOLOGGING option, or ?
Date: 2005-06-01 06:25:42
Message-ID: 429D54E6.4010005@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
Personally I don't think that it is a good idea to do that.
People will tend to corrupt their systems because they want speed 
(sometimes without thinking about the consequences).

I can only think of one scenario where nologging would actually make 
sense: Many people use session tables to keep track of user level 
information on a website. corrupting a session table (usually not very 
large) would not cause a lot of problems.

Doing it for COPY would be fatal. I can tell you from experience that 
80% of all users will use that if the manual says that PostgreSQL will 
beform better this way. This is a key feature to make people think that 
PostgreSQL is reliable.

	Best regards,

		Hans


Simon Riggs wrote:
> Recent test results have shown a substantial performance improvement
> (+25%) if WAL logging is disabled for large COPY statements. This is to
> be expected, though has a price attached: losing the ability to crash
> recover data loaded in this manner.
> 
> There are two parts to this proposal. First, when and whether to do this
> at all. Second, syntax and invocation.
> 
> Why?
> 
> Performance.
> 
> The performance gain has a price and so should only be enabled if
> requested explicitly by the user. It is up to the user whether they
> accept this price, since in many useful cases it is a small price
> against a huge saving.
> 
> The price is that if a crash occurs, then any table that was not empty
> to begin with would not be in a transactionally consistent state
> following crash recovery. It may have data in it, but it would be up to
> the user to determine whether that was satisfactory or not. It could be
> possible to sense what to do in this situation automatically, by putting
> the table into a needs-recovery type state... I don't propose to handle
> this *at this stage*.
> 
> Syntax and invocation:
> 
> Previously I had discussed adding a NOLOGGING option onto both COPY and
> CREATE TABLE AS SELECT that would bypass the creation of wal logging
> data. That is still a possibility, but would require manual code changes
> to much of the SQL submitted.
> 
> Now, I would like to discuss adding an enable_logging USERSET GUC, that
> would apply *only* to COPY and CREATE TABLE AS SELECT. The default of
> this would be false.
> 
> How can we gain this performance benefit for those willing to accept the
> restrictions imposed?
> 
> Your comments are sought and are most welcome.
> 
> Best Regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at


In response to

Responses

pgsql-hackers by date

Next:From: Qingqing ZhouDate: 2005-06-01 06:45:21
Subject: Re: Can we simplify win32 threading code
Previous:From: Oleg BartunovDate: 2005-06-01 05:48:14
Subject: Re: Deadlock with tsearch2 index ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group