http://archives.postgresql.org/pgsql...0/msg01172.php

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;

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

FYI, I am going need to add documentation in the COPY manual page or no
one will know about this performance enhancement.

---------------------------------------------------------------------------

Simon Riggs wrote:
> http://archives.postgresql.org/pgsql...0/msg01172.php
>
> 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;
>
> 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

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joshua D. Drake wrote:
> 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;

On ABORT, the entire table disappears, as well as the INSERT, so I don't
see any problem. I assume the INSERT is WAL logged.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Bruce Momjian <bruce@momjian.us> writes:
> FYI, I am going need to add documentation in the COPY manual page or no
> one will know about this performance enhancement.

I don't think it belongs in COPY. What would make more sense is another
item under the "populating a database" performance tips, suggesting that
wrapping the restore into a single transaction is a good idea. We don't
really want to be documenting this separately under COPY, CREATE INDEX,
and everywhere else that might eventually optimize the case.

Come to think of it, that page also fails to suggest that PITR logging
shouldn't be on during bulk load.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> > BEGIN;
> > CREATE TABLE foo...
> > INSERT INTO foo VALUES ('1');
> > COPY foo...
> >
> > COMMIT;
>
> On ABORT, the entire table disappears, as well as the INSERT, so I don't
> see any problem. I assume the INSERT is WAL logged.

No I don't see any problems, I am just trying to understand the
boundaries. E.g., is there some weird limitation where if I have any
values in the table before the copy (like the example above) that copy
will go through WAL.

Or in other words, does this patch mean that all COPY execution that is
within a transaction will ignore WAL?

Joshua D. Drake


>
--

=== 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




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Simon Riggs wrote:

> As discussed on -hackers, its possible to avoid writing any WAL at all
> for COPY in these circumstances:
>
Cool.

> 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.
>
IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot
of people use COPY because it's faster than INSERT but expects that it
will be in WAL. The default would be use_wal_in_copy = true.


--
Euler Taveira de Oliveira
http://www.timbira.com/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Sat, 2007-01-06 at 16:41 -0200, Euler Taveira de Oliveira wrote:
> Simon Riggs wrote:
>
> > As discussed on -hackers, its possible to avoid writing any WAL at all
> > for COPY in these circumstances:
> >
> Cool.
>
> > 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.
> >
> IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot
> of people use COPY because it's faster than INSERT but expects that it
> will be in WAL. The default would be use_wal_in_copy = true.

That I don't think makes sense. A copy is an all or nothing option, if a
copy fails in the middle the whole thing is rolled back.

Sincerely,

Joshua D. Drake


>
>
--

=== 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




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Euler Taveira de Oliveira <euler@timbira.com> writes:
> Simon Riggs wrote:
>> 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.
>>
> IMHO, this deserves an GUC parameter (use_wal_in_copy?).

Why? The whole point is that it's automatic and transparent.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joshua D. Drake wrote:
>
> > > BEGIN;
> > > CREATE TABLE foo...
> > > INSERT INTO foo VALUES ('1');
> > > COPY foo...
> > >
> > > COMMIT;
> >
> > On ABORT, the entire table disappears, as well as the INSERT, so I don't
> > see any problem. I assume the INSERT is WAL logged.
>
> No I don't see any problems, I am just trying to understand the
> boundaries. E.g., is there some weird limitation where if I have any
> values in the table before the copy (like the example above) that copy
> will go through WAL.
>
> Or in other words, does this patch mean that all COPY execution that is
> within a transaction will ignore WAL?

Yes, because it is possible to do in all cases.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate