9.5 Feature: ALTER TABLE SET LOGGED / UNLOGGED

With PostgreSQL 9.5 you now can alternate tables between LOGGED and UNLOGGED mode on the fly:

CREATE TABLE demo (id INTEGER, val INTEGER); -- LOGGED

INSERT INTO demo (id,val) select s.*,s.* FROM generate_series(1,1000000) s;

-- Query returned successfully: 1000000 rows affected, 5.4 secs execution time.

TRUNCATE TABLE demo;

ALTER TABLE demo SET UNLOGGED; -- now UNLOGGED

INSERT INTO demo (id,val) select s.*,s.* FROM generate_series(1,1000000) s;

-- Query returned successfully: 1000000 rows affected, 515 msec execution time.

ALTER TABLE demo SET LOGGED;  -- and back again

Noted the INSERT speed difference between LOGGED and UNLOGGED?

Very convenient, e.g. when initially populating large tables and then switch to production mode.

So, 9.5 is not only jsonb, there is much more goodness inside... :-)

One reader pointed out that:

You haven't showed the time for "ALTER TABLE demo SET LOGGED" and haven't said that it gets AccessExclusiveLock on the table so you can't even read from it.

OK:

ALTER TABLE demo SET LOGGED;

-- Query returned successfully with no result in 3.9 secs.

So it takes some time. I still maintain my opinion that this is a useful feature given the fact, that it was impossible before 9.5 to do it in-place.

Especially for tasks that do not require constant availability like: "initially populating [sic] large tables and then switch to production mode".