Subject: Re: Benchmark Data requested
From: Simon Riggs <simon@2ndquadrant.com>
To: "Jignesh K. Shah" <J.K.Shah@Sun.COM>
Cc: Dimitri Fontaine <dfontaine@hi-media.com>,
	pgsql-performance@postgresql.org, Greg Smith <gsmith@gregsmith.com>
In-Reply-To: <47A8AF31.8070304@sun.com>
References: <1202150236.4252.521.camel@ebony.site>
	<200802051506.51581.dfontaine@hi-media.com>
	<1202221496.4252.680.camel@ebony.site>
	<200802051815.25800.dfontaine@hi-media.com>
	<1202232753.4252.789.camel@ebony.site>  <47A8AF31.8070304@sun.com>
Content-Type: text/plain
Date: Tue, 05 Feb 2008 19:48:26 +0000
Message-Id: <1202240906.4252.832.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2008-02-05 at 13:47 -0500, Jignesh K. Shah wrote:
> That sounds cool to me too..
> 
> How much work is to make pg_bulkload to work on 8.3? An Integrated 
> version is certainly more beneficial.

> Specially I think it will also help for other setups like TPC-E too 
> where this is a problem.
   
If you don't write WAL then you can lose all your writes in a crash.
That issue is surmountable on a table with no indexes, or even
conceivably with one monotonically ascending index. With other indexes
if we crash then we have a likely corrupt index.

For most production systems I'm aware of, losing an index on a huge
table is not anything you'd want to trade for performance. Assuming
you've ever been knee-deep in it on a real server.

Maybe we can have a "load mode" for a table where we skip writing any
WAL, but if we crash we just truncate the whole table to nothing? Issue
a WARNING if we enable this mode while any data in table. I'm nervous of
it, but maybe people really want it?

I don't really want to invent ext2 all over again, so we have to run an
fsck on a table of we crash while loading. My concern is that many
people would choose that then blame us for delivering unreliable
software. e.g. direct path loader on Oracle used to corrupt a PK index
if you loaded duplicate rows with it (whether it still does I couldn't
care). That kind of behaviour is simply incompatible with production
usage, even if it does good benchmark.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com