【PostgreSQL】unloggedを試してみた

#はじめに
DBの運用において、トランザクションログ出力は大抵の作業において厄介となります。
常に、溢れることがないか、領域枯渇への懸念・警戒がイライラと。

処理高速化のメリットも踏まえ、ログ出力の抑制を検討することとなります。
OracleDatabaseでいう、nologgingですが、PostgreSQLではどうなのか
実際に手を動かして試してみることにしました。

#環境
・PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
・ストリーミングレプリケーション環境

#試したことと結果
|試したこと       |ログ出力|レプリケーション           | 感想 |
|:------------------|:-------|:-----------------------------|:------------|
|テーブルコピー|ログ出力されない。|スレーブに伝播されない|スレーブ側にテーブルが作成されません。|
|インデックス作成|ログ出力されない。|||
|データ挿入|ログ出力されない。|||
|設定変更<br>(unlogged→通常)|ログ出力されるようになる。|スレーブに伝播される|スレーブ側にテーブルが作成されます。マスタ側からのコピーが実施されるのか、既存テーブルのデータ量が多い場合、時間がかかります。負荷も懸念されます。|
|設定変更<br>(通常→unlogged)|ログ出力されなくなる。|スレーブに伝播されない|スレーブ側よりテーブルが存在しなくなります。裏でDROP実施？|

以下、実験の詳細です。

#データ挿入/テーブルコピー
##①通常のテーブルで普通の動きを確認
###▼WAL生成確認
はじめの状態です。14番まであります。

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 13 19:00 000000010000000000000011
    -rw-------. 1 postgres postgres 16777216 Jun 14 01:30 000000010000000000000012
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    drwx------. 2 postgres postgres     4096 Jun 14 06:00 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:10 000000010000000000000014
    -bash-4.2$

###▼ノーマルテーブル作成
unloggedではない、普通のテーブルを作成。

    create table nolog_test_normal(
    One integer,
    Two char(10)
    );

###▼データ装填
普通テーブルに、適当に、データを挿入します。

    insert into nolog_test_normal select generate_series(1,100000),'DAT';
    insert into nolog_test_normal select generate_series(1,100000),'DAT';
    insert into nolog_test_normal select generate_series(1,100000),'DAT';

###▼WAL生成確認
データ挿入に伴い、普通に生成されます。15番が新たに出てます。

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 14 01:30 000000010000000000000012
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000015
    drwx------. 2 postgres postgres     4096 Jun 26 02:13 archive_status
    -bash-4.2$

###▼ノーマルテーブルコピー
テーブルを複製します。いわゆる、単純なバックアップ作業的なイメージです。
```
create table nolog_test_normal_cp as select * from nolog_test_normal;
```
###▼WAL生成確認
はい、出ます。16番が出ました。

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000015
    drwx------. 2 postgres postgres     4096 Jun 26 02:17 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000016
    -bash-4.2$

##②unloggedなテーブルで同様の動きを確認
###▼コピー(create as)にてunloggedテーブル作成
```create unlogged table nolog_test_unlogged as select * from nolog_test_normal;
```

###▼WAL生成確認
生成なし。(通常テーブルでの実験時のまま)16番です。処理がはやい！！

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000015
    drwx------. 2 postgres postgres     4096 Jun 26 02:17 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:18 000000010000000000000016
    -bash-4.2$

###▼データ装填
入れます。

    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';
    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';
    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';

###▼WAL生成確認
引き続き、16番のままです。生成なし！！

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000015
    drwx------. 2 postgres postgres     4096 Jun 26 02:17 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:20 000000010000000000000016
    -bash-4.2$

#インデックス作成
##①unloggedなテーブルで動きを確認
```create index nolog_test_unlogged_idx on nolog_test_unlogged (one);
```

###▼WAL生成確認
前述での実験時より変わりありません。

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 14 06:00 000000010000000000000013
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000015
    drwx------. 2 postgres postgres     4096 Jun 26 02:17 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:22 000000010000000000000016
    -bash-4.2$

##②通常のテーブルで普通の動きを確認
まあ、いつもどおりの動きとなりますが。。

###▼ノーマル向けインデックス作成
```create index nolog_test_normal_idx on nolog_test_normal (one);
```

###▼WAL生成確認
新たに17番がでました。はい。

    -bash-4.2$ ls -lrt|tail -5
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:13 000000010000000000000014
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:17 000000010000000000000015
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:24 000000010000000000000016
    drwx------. 2 postgres postgres     4096 Jun 26 02:24 archive_status
    -rw-------. 1 postgres postgres 16777216 Jun 26 02:24 000000010000000000000017
    -bash-4.2$

#レプリケーション確認
当環境はストリーミングレプリケーション環境にあります。
すなわち、これまでの実験は更新可能なマスタ側で行われております。

通常、スレーブ側に伝播され、常に同期が図られることになりますが。
？？
WALが生成されずに、伝播が行われるのか！？
・・・実験しました。

既に、上述のとおり、「通常」および「unlogged」なテーブルの作成やデータ挿入を
実施しておりますので、スレーブ側で両者テーブルがどのような状態にあるのか
見てみます。

###▼レプリケーション確認
簡素ですが、件数比較してみます。
マスタ/スレーブ間で、「通常」および「unlogged」なテーブルを対象。

    select count(*) from nolog_test_normal;
    select count(*) from nolog_test_unlogged;

⇒どうやら、「unlogged」なテーブルはそもそもDDLから伝播されておらず
　スレーブ環境には居ないようです。

　まあ、
　テーブルコピー時より、unloggedで作成しましたので
　結果としては納得です。

<マスタ>

    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
     count
    --------
     600000
    (1 row)
    
    postgres=#


<スレーブ>

    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
    ERROR:  cannot access temporary or unlogged relations during recovery
    postgres=#

#設定の変更(unlogged→通常へ)
手順/コマンドの確認もそうですが
レプリケーション環境で非同期状態にある「unlogged」なテーブルを
「通常」に設定変更たら、どうなる？

⇒伝播されました。
　一度に全データ伝播が起こり、設定処理が終了するまでに時間がかかりました。
　業務時間帯でこの事象が発生すると、サービス影響は確実かも。

###▼設定確認
「Unlogged table」表示であることを確認。

    \d nolog_test_unlogged
    
    postgres=# \d nolog_test_unlogged
    Unlogged table "public.nolog_test_unlogged"
     Column |     Type      | Modifiers
    --------+---------------+-----------
     one    | integer       |
     two    | character(10) |
    Indexes:
        "nolog_test_unlogged_idx" btree (one)
    
    postgres=#


###▼設定変更
alter table nolog_test_unlogged set logged;

    postgres=# alter table nolog_test_unlogged set logged;
    ALTER TABLE
    postgres=#

###▼設定確認
再度確認しますと、
「Unlogged table」表示ではなくなっています。

    postgres=# \d nolog_test_unlogged
     Table "public.nolog_test_unlogged"
     Column |     Type      | Modifiers
    --------+---------------+-----------
     one    | integer       |
     two    | character(10) |
    Indexes:
        "nolog_test_unlogged_idx" btree (one)
    
    postgres=#

###▼レプリケーション確認
あらためて、件数にて状況を確認します。
今回は、マスタ側の確認は割愛。
先ほどは、スレーブ側で「unlogged」なテーブルは存在しませんでしたが、、

    select count(*) from nolog_test_normal;
    select count(*) from nolog_test_unlogged;

⇒スレーブにも存在！　伝播されています。

<スレーブ>

    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
     count
    --------
     600000
    (1 row)
    
    postgres=#


惰性的ですが、レプリケーションが行われるか、確認。

###▼データ装填

    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';
    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';
    insert into nolog_test_unlogged select generate_series(1,100000),'XXX';

###▼レプリケーション確認
両環境で同じ件数です。

＜マスタ＞
    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
     count
    --------
     800000
    (1 row)
    
    postgres=#

＜スレーブ＞

    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
     count
    --------
     800000
    (1 row)
    
    postgres=#

#設定の変更(通常→unloggedへ)
逆は、どうなるのでしょう。
レプリケーションの状態変化が気になるところです。

⇒スレーブ側から、テーブルが消えました。
　動作仕様としては、物理的には安全策かもしれません。
　ビジネス的に、片環境しか居ないのは、どうなのでしょう。
　pg_pool2などで参照クエリを分散した場合、SQLが成功したり失敗したり
　するのか。。

###▼設定変更
alter table nolog_test_unlogged set unlogged;

    postgres=# alter table nolog_test_unlogged set unlogged;
    ALTER TABLE
    postgres=#

###▼設定確認
「Unlogged table」表示ありを確認。

    \d nolog_test_unlogged
    
    postgres=# \d nolog_test_unlogged
    Unlogged table "public.nolog_test_unlogged"
     Column |     Type      | Modifiers
    --------+---------------+-----------
     one    | integer       |
     two    | character(10) |
    Indexes:
        "nolog_test_unlogged_idx" btree (one)
    
    postgres=#


###▼レプリケーション確認
    select count(*) from nolog_test_normal;
    select count(*) from nolog_test_unlogged;

「unlogged」なテーブルを作成したときと同じ状態に
戻りました。
つまり、スレーブから、「unlogged」設定したテーブルは
存在しなくなりました。
裏でDROPしているのかな。

<マスタ>
    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
     count
    --------
     800000
    (1 row)
    
    postgres=#

<スレーブ>

    postgres=# select count(*) from nolog_test_normal;
     count
    --------
     300000
    (1 row)
    
    postgres=# select count(*) from nolog_test_unlogged;
    ERROR:  cannot access temporary or unlogged relations during recovery
    postgres=#