Monday, January 11, 2016

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".

3 comments:

  1. 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.

    ReplyDelete
  2. Hello, Well, i am not enthousiastic : Here are the figures with 10000000 instead of 1000000 :
    INSERT 0 10000000
    Temps : 11924,935 ms
    TRUNCATE TABLE
    Temps : 85,808 ms
    ALTER TABLE
    Temps : 17,463 ms
    INSERT 0 10000000
    Temps : 5758,444 ms
    ALTER TABLE
    Temps : 4278,453 ms
    If you are adding the insert + the alter table SET LOGGED, you are comparing 10036,897ms to 11924,935ms

    May i have missed something ?

    ReplyDelete
    Replies
    1. well, this simple test does not show real life load with just inserting 2 fields with no data at all. I bet that with few dozen field insert, gain should be a little bit greater

      Delete