【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テーブル作成

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