User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Sat, 03 Dec 2005 11:42:02 -0800
Subject: Re: Database restore speed
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
cc: "Stephen Frost" <sfrost@snowman.net>, "David Lang" <dlang@invendra.net>,
	"Steve Oualline" <soualline@stbernard.com>,
	pgsql-performance@postgresql.org
Message-ID: <BFB7350A.14FC7%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Database restore speed
Thread-Index: AcX3lFUed8lUKdKITBSk01X39MKJ9wArUycQ
In-Reply-To: <8549.1133564459@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom,

On 12/2/05 3:00 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> 
> Sure it does ... at least as long as you are willing to assume everybody
> uses IEEE floats, and if they don't you have semantic problems
> translating float datums anyhow.
> 
> What we lack is documentation, more than functionality.

Cool - sounds like the transport part might be there - the thing we desire
is a file format that allows for efficient representation of portable binary
datums.

Last I looked at the Postgres binary dump format, it was not portable or
efficient enough to suit the need.  The efficiency problem with it was that
there was descriptive information attached to each individual data item, as
compared to the approach where that information is specified once for the
data group as a template for input.

Oracle's format allows for the expression of fixed width fields within the
input file, and specifies the data type of the fields in the metadata.  We
could choose to support exactly the specification of the SQL*Loader format,
which would certainly be general enough, and would have the advantage of
providing a compatibility option with Oracle SQL*Loader input.

Note that Oracle does not provide a similar functionality for the expression
of *output* files, those that can be dumped from an Oracle database.  Their
mechanism for database dump is the exp/imp utility pair, and it is a
proprietary "shifting sands" specification AFAIK.  This limits the benefit
of implementing the Oracle SQL*Loader compatibility to those customers who
have designed utilities to emit that format, which may still be valuable.

The alternative is to design a Postgres portable binary input file format.
I'd like to see a record oriented format like that of FORTRAN unformatted,
which uses bookends around each record to identify the length of each
record.  This allows for fast record oriented positioning within the file,
and provides some self-description for integrity checking, etc.

- Luke