On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster