Re: NOLOGGING option, or ?

On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Recent test results have shown a substantial performance improvement
> > (+25%) if WAL logging is disabled for large COPY statements.
>
> How much of that is left after we fix the 64-bit-CRC issue?

Well, I don't know. The I/O is the main thing I'm trying to avoid.

> > Now, I would like to discuss adding an enable_logging USERSET GUC,
>
> [ fear and loathing ... ]

OK. I needed to say the idea, to make sure we had considered it. I now
pronounce it dead and buried.

> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely. Which is surely sufficient
> reason not to let it be USERSET.

You're right, thank you. But I do need to be reminded to say "this would
only apply when archiving is not enabled" just as the other two existing
WAL-avoidance optimisations do.

In answer to the other points raised, the main use case for this
optimisation is to improve data load performance into an empty table.
This is a very timeconsuming stage on a big warehouse and needs
performance optimization. I agree with Hans-Jurgen that this is a
dangerous option for use on general COPY commands, since these can be
used on empty and already populated tables. I seek a way to improve the
main use case though without leaving any danger in other situations.

I have two suggested approaches:
1. Introduce a new LOAD command that only works on empty tables.
Following a crash, the table is dropped and the user accepts that the
action-on-recovery is to reload the table. (Though in PITR mode, the
data would be logged).

2. Introduce NOT LOGGED INITIALLY mode, as DB2 has done. The first COPY
into an empty table would avoid WAL logging, if the user invokes that
option on the specific COPY command.

There are some other arguments in favour of a LOAD command.... Alon?

Best Regards, Simon Riggs