I learned something today and will share it here. I use the PostgreSQL Bulk Loader for an initial load job, but I noticed that my server was still generating write-ahead log (WAL) files during the bulk loads. My job has some regular Table Output steps and other, non-bulk-loading outputs, so I expected some WAL, but as much as I saw.

http://www.postgresql.org/docs/9.3/s...onfig-wal.html mentions that

In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include:
CREATE TABLE AS
CREATE INDEX
CLUSTER
COPY into tables that were created or truncated in the same transaction


The PostgreSQL Bulk Loader step runs psql to execute the COPY command, and you can check a Truncate option in that step, but I didn't see that the step wrapped the truncate and COPY calls in a transaction. I use BASH, and the step asks you for the location of psql, so I created a one-line wrapper script called psql_single_trans and told the step to use that for the psql path:

Code:
/usr/local/bin/psql --single-transaction "$@"


When the PostgreSQL Bulk Loader step calls psql_single_trans, the commands issued by the step are wrapped inside a BEGIN/COMMIT pair because of the --single-transaction switch. I tested this and it cut my WAL in half. I was expecting a bigger improvement, but some of the remaining WAL could be due to indexes and other, WAL-generating steps in my job.

I make no guarantees that this suggestion will work for you, and you should test this in your environment before using it in production. In particular, if you are using Postgres replication, be sure you understand why you probably do NOT want to use this tip.