Re: COPY with no WAL, in certain circumstances

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