Re: COPY with no WAL, in certain circumstances - Mailing list pgsql-patches

From Joshua D. Drake
Subject Re: COPY with no WAL, in certain circumstances
Date
Msg-id 1168103804.20486.1.camel@localhost.localdomain
Whole thread Raw
In response to Re: COPY with no WAL, in certain circumstances  (Bruce Momjian <bruce@momjian.us>)
Responses Re: COPY with no WAL, in certain circumstances  (Bruce Momjian <bruce@momjian.us>)
List pgsql-patches
On Sat, 2007-01-06 at 11:05 -0500, Bruce Momjian wrote:
> FYI, I am going need to add documentation in the COPY manual page or no
> one will know about this performance enhancement.

I have some questions:

> > As discussed on -hackers, its possible to avoid writing any WAL at all
> > for COPY in these circumstances:
> >
> > BEGIN;
> >   CREATE TABLE foo..
> >   COPY foo...
> > COMMIT;

What if I do this?

BEGIN;
   CREATE TABLE foo...
   INSERT INTO foo VALUES ('1');
   COPY foo...

COMMIT;

?

E.g., what are the boundaries of ignoring the WAL?

Joshua D. Drake



> >
> > BEGIN;
> >   TRUNCATE foo..
> >   COPY foo...
> > COMMIT;
> >
> > The enclosed patch implements this, as discussed. There is no user
> > interface to enable/disable, just as with CTAS and CREATE INDEX; no
> > docs, just code comments.
> >
> > This plays nicely with the --single-transaction option in psql to allow
> > fast restores/upgrades.
> >
> > YMMV but disk bound COPY will benefit greatly from this patch, some
> > tests showing 100% gain. COPY is still *very* CPU intensive, so some
> > tests have shown negligible benefit, fyi, but that isn't the typical
> > case.
> >
> > Applies cleanly to CVS HEAD, passes make check.
> >
> > --
> >   Simon Riggs
> >   EnterpriseDB   http://www.enterprisedb.com
> >
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: COPY with no WAL, in certain circumstances
Next
From: Bruce Momjian
Date:
Subject: Re: COPY with no WAL, in certain circumstances