【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
・ストリーミングレプリケーション環境

#試したことと結果

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

###▼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なテーブルで動きを確認

###▼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$

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

###▼ノーマル向けインデックス作成

###▼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=#