Recent test results have shown a substantial performance improvement (+25%) if WAL logging is disabled for large COPY statements. This is to be expected, though has a price attached: losing the ability to crash recover data loaded in this manner.
There are two parts to this proposal. First, when and whether to do this at all. Second, syntax and invocation. Why? Performance. The performance gain has a price and so should only be enabled if requested explicitly by the user. It is up to the user whether they accept this price, since in many useful cases it is a small price against a huge saving. The price is that if a crash occurs, then any table that was not empty to begin with would not be in a transactionally consistent state following crash recovery. It may have data in it, but it would be up to the user to determine whether that was satisfactory or not. It could be possible to sense what to do in this situation automatically, by putting the table into a needs-recovery type state... I don't propose to handle this *at this stage*. Syntax and invocation: Previously I had discussed adding a NOLOGGING option onto both COPY and CREATE TABLE AS SELECT that would bypass the creation of wal logging data. That is still a possibility, but would require manual code changes to much of the SQL submitted. Now, I would like to discuss adding an enable_logging USERSET GUC, that would apply *only* to COPY and CREATE TABLE AS SELECT. The default of this would be false. How can we gain this performance benefit for those willing to accept the restrictions imposed? Your comments are sought and are most welcome. Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
