Subject: Re: [Bizgres-general] Re: faster INSERT with possible
From: Simon Riggs <simon@2ndquadrant.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org, John A Meinel <john@arbash-meinel.com>,
	bizgres-general <bizgres-general@pgfoundry.org>,
	Dan Harris <fbsd@drivefaster.net>
In-Reply-To: <200507270929.10251.josh@agliodbs.com>
References: <BF0BDD48.9FFF%llonergan@greenplum.com>
	<200507270929.10251.josh@agliodbs.com>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Wed, 27 Jul 2005 22:37:47 +0100
Message-Id: <1122500267.3670.184.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2005-07-27 at 09:29 -0700, Josh Berkus wrote:
> Luke,
> 
> > Well - now that I test it, it appears you are correct, temp table COPY
> > bypasses WAL - thanks for pointing it out!
> 
> RIght.  The problem is bypassing WAL for loading new "scratch" tables which 
> aren't TEMPORARY tables.   We need to do this for multi-threaded ETL, since:
> a) Temp tables can't be shared by several writers, and
> b) you can't index a temp table.

The description of "scratch" tables might need some slight
clarification. It kindof makes it sound like temp tables.

I had in mind the extra tables that an application sometimes needs to
operate faster. Denormalisations, pre-joined tables, pre-calculated
results, aggregated data. These are not temporary tables, just part of
the application - multi-user tables that stay across shutdown/restart.

If you have gallons of GB, you will probably by looking to make use of
such tables.

You can use such tables for the style of ETL known as ELT, but that is
not the only use.

Best Regards, Simon Riggs