Loading data into UNLOGGED tables, and considering ALTER TABLE

UNLOGGED table has been introduced in 9.1, and it would improve INSERT/UPDATE/DELETE performance in several situations. At this time, I'm curious how it would work well on data loading.

To load large amount of rows into PostgreSQL, WAL logging would be a performance penalty, especially under the situation that have several tables on different spindles, because WAL logging forces data loading being serialized.

I have created a small function, called "enable_logging()", to switch table persistence mode for existing tables between (regular) logging and UNLOGGED.

bool enable_logging(Oid relid, bool mode);

You can see the source at github, and an example at the bottom of this entry.

By using this function, DBA can disable WAL logging while loading data into the table to improve loading performance, and switch it back after loading to keep durability of the table.

According to my experiments (pic on the right), loading 10,000,000 rows into pgbench_accounts table with COPY command took 109 seconds with WAL logging, and 53 seconds under UNLOGGED mode. The COPY performance was doubled.

So, providing this persistence switching feature, as one of ALTER TABLE options, would help DBA to load data faster, especially having multiple tablespaces with different spindles.

Now, my next question is:

"Should we bring it into the core? If so, which syntax should we use to bring it into ALTER TABLE?".

Any comments?

---

Example:

snaga=# SELECT oid,relname,relpersistence FROM pg_class WHERE relname LIKE 'pgbench%';
  oid  |        relname        | relpersistence
-------+-----------------------+----------------
 16685 | pgbench_accounts      | p
 16696 | pgbench_accounts_pkey | p
 16679 | pgbench_branches      | p
 16692 | pgbench_branches_pkey | p
 16688 | pgbench_history       | p
 16682 | pgbench_tellers       | p
 16694 | pgbench_tellers_pkey  | p
(7 rows)

Time: 0.646 ms
snaga=# SELECT enable_logging( (SELECT oid FROM pg_class WHERE relname='pgbench_accounts'), false);
 enable_logging
----------------
 t
(1 row)

Time: 20.560 ms
snaga=# SELECT enable_logging( (SELECT oid FROM pg_class WHERE relname='pgbench_branches'), false);
 enable_logging
----------------
 t
(1 row)

Time: 6.292 ms
snaga=# SELECT enable_logging( (SELECT oid FROM pg_class WHERE relname='pgbench_history'), false);
 enable_logging
----------------
 t
(1 row)

Time: 4.900 ms
snaga=# SELECT enable_logging( (SELECT oid FROM pg_class WHERE relname='pgbench_tellers'), false);
 enable_logging
----------------
 t
(1 row)

Time: 4.479 ms
snaga=#  SELECT oid,relname,relpersistence FROM pg_class WHERE relname LIKE 'pgbench%';
  oid  |        relname        | relpersistence
-------+-----------------------+----------------
 16679 | pgbench_branches      | u
 16692 | pgbench_branches_pkey | u
 16688 | pgbench_history       | u
 16682 | pgbench_tellers       | u
 16694 | pgbench_tellers_pkey  | u
 16685 | pgbench_accounts      | u
 16696 | pgbench_accounts_pkey | u
(7 rows)

Time: 0.860 ms
snaga=#