ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL の TEMPORARY TABLE と UNLOGGED TABLE にパフォーマンスの差異はあるか？

データベース

はじめに

PostgreSQL にはテーブルの種類として TEMPORARY と UNLOGGED があります。この2つはワークテーブルとしてしばしば使われます。

一時テーブルまたはログに記録されないテーブルをデータのロードに使用する
非常に大量のデータをロードする場合、WAL に書き込まれないテーブルにデータを “ステージング” すると役立つ場合があります。これらはクラッシュに対して安全なテーブルではないため、一定のリスクはありますが、Extract-Transform-Load プロセスの一環として有用なツールになる可能性があります。

一時テーブルは CREATE TEMPORARY TABLE​ を使用して、ログに記録されないテーブルは CREATE UNLOGGED TABLE​ を使用して作成できます。

Postgres のログ先行書き込みの使用法 | Heroku Dev Center

TEMPORARY TABLE に注目して、その挙動を詳細に追った記事があります。
TEMPORARY TABLE（一時テーブル）を探る - Qiita

この記事によれば TEMPORARY と UNLOGGED は以下の特徴があります。

  • TEMPORARY と UNLOGGED ともにログ (WAL) に書き出さない。
  • TEMPORARY はテーブルの内容がローカルバッファからあふれるとディスクに書き出される。

別の資料（スライド）では UNLOGGED もテーブルの内容をディスクに書き出すとしています。

UNLOGGED TABLE

  • トランザクションログを書かないテーブル
    • CREATE UNLOGGED TABLE ...
    • テーブルのページブロックだけディスクに（非同期に）書き出される。
    • クラッシュした場合、リスタートのリカバリの際、TRUNCATEされる。
PostgreSQLアーキテクチャ入門（INSIGHT OUT 2011）

ここまでの情報を踏まえると、ディスクへの書き出しが起こる（テーブルの内容がバッファに収まらない）程度に大きいデータサイズの場合、パフォーマンスにはそこまで差が出ないようにも見えます。そこで、今回の記事では TEMPORARY と UNLOGGED のパフォーマンス面に注目して検証を行いました。

結論

シンプルな条件下では UNLOGGED より TEMPORARY のパフォーマンスが良い結果となりました。ただし、今回の調査では内部動作の違いを解明できなかったため、必ずしも TEMPORARY が有利とは言い切れませんでした。

以下に挙げるような基準で考えるのが良いかもしれません。

  • セッション若しくはトランザクション単位でテーブルを分離したいのであれば TEMPORARY、共有したいのであれば UNLOGGED を選択する。
  • セッション若しくはトランザクションの開始時点でテーブルを空にしたいなら TEMPORARY を選択する。
  • システム実装の要件が UNLOGGED / TEMPORARY のいずれでも満たせるのであれば、TEMPORARY を採用する。

検証環境

検証方法

テーブルの種類ごとに INSERT / SELECT / UPDATE / DELETE を行い、その処理時間を計測しました。組み合わせごとに5回測定し、その中央値を採用しました。

対象としたテーブルの種類は以下の通りです。

  • 通常
  • UNLOGGED
  • TEMPORARY

クエリの内容を以下に示します。

-- いずれかの CREATE TABLE を実行する
CREATE TABLE tab(c1 INTEGER);
CREATE UNLOGGED TABLE tab(c1 INTEGER);
CREATE TEMPORARY TABLE tab(c1 INTEGER);

INSERT INTO tab SELECT generate_series(1, 1e+8);

VACUUM ANALYZE;

SELECT COUNT(*) FROM tab;

UPDATE tab SET c1 = (c1 + 1);

VACUUM ANALYZE;

DELETE FROM tab;

DROP TABLE tab;

検証結果

[INSERT]

種類 処理時間[ms] NORMAL比[%]
NORMAL 79554.635 100.0
UNLOGGED 40842.508 51.3
TEMPORARY 35110.147 44.1

[SELECT]

種類 処理時間[ms] NORMAL比[%]
NORMAL 7814.027 100.0
UNLOGGED 7838.642 100.3
TEMPORARY 7260.491 92.9

[UPDATE]

種類 処理時間[ms] NORMAL比[%]
NORMAL 165388.554 100.0
UNLOGGED 64857.803 39.2
TEMPORARY 56086.556 33.9

[DELETE]

種類 処理時間[ms] NORMAL比[%]
NORMAL 85761.934 100.0
UNLOGGED 34065.439 39.7
TEMPORARY 30771.315 35.9

考察

パフォーマンス（処理時間）の観点で結果を確認します。

SELECT で NORMAL と比較して UNLOGGED はほぼ変わらない結果でしたが、TEMPORARY は若干良い結果となりました。
また、INSERT/UPDATE/DELETE の全てで NORMAL と比較して UNLOGGED, TEMPORARY 共に良い結果となっており、TEMPORARY は最も良い結果となりました。

今回の計測で UNLOGGED よりも TEMPORARY は常に良い結果となっていました。これがテーブルサイズの違いによるものではないかと考え、テーブルの種類ごとに INSERT 直後のページ数を確認しました。

-- いずれかの CREATE TABLE を実行する
CREATE TABLE tab(c1 INTEGER);
CREATE UNLOGGED TABLE tab(c1 INTEGER);
CREATE TEMPORARY TABLE tab(c1 INTEGER);

INSERT INTO tab SELECT generate_series(1, 1e+8);

VACUUM ANALYZE;

SELECT relpages FROM pg_class WHERE relname = 'tab';
種類 ページ数
NORMAL 442,478
UNLOGGED 442,478
TEMPORARY 442,478

テーブルの種類に関わらず同じサイズとなりました。ページサイズが 8KB なので、テーブルサイズは約 3.38GB となります。

一時バッファは、一時テーブルにアクセスする時にのみ使用されるセッションローカルのバッファです。

19.4. 資源の消費

一時バッファ (temp_buffers) はデフォルトの 8MB なので、テーブルサイズはこれより十分に大きく、これが良い結果と関係しているとは考えにくいです。

ソースコードから UNLOGGED と TEMPORARY の差分を見つけようとしましたが、手掛かりを見つけることができませんでした。

UNLOGGED と TEMPORARY のパフォーマンスに差が出たということは、内部動作が異なると推測できます。ただし、今回の調査ではその内部動作の違いを完全に解明できておらず、TEMPORARY が常に優位とは言い切れませんでした。

追加情報

記事を公開後、お寄せいただいた情報を紹介いたします。

id:interdb さんからはコメントで情報をお寄せいただきました。記事下部のコメント欄を参照ください。主に以下の観点で情報を提示いただきました。

@tatsuo_ishii さんからは Twitter で情報を提供いただきました。

tweet 内のリンク先の議論（Tom Lane さん）から引用します。

Temp tables *are* unlogged. They also live in session-private buffers, which eliminates a great deal of synchronization overhead; at the cost that any writing that does happen has to be done by the backend process itself, without help from the background writer. It's possible that there's something about your specific test case that makes that scenario look bad. Another likely source of bogus results is if you were testing a tiny temp_buffers setting versus a more appropriately sized shared_buffers setting.

PostgreSQL: Re: UNLOGGED TEMPORARY tables?

ここでのポイントを以下に挙げます。

  • TEMPORARY テーブルは排他制御のオーバーヘッドが少ないこと。
  • バッファサイズが性能の測定結果に影響を与えた可能性。

id:interdb さんがコメントで指摘されているように、『存在意義の違いを無視したとしても、性能的にTEMPORARYテーブルが有利かどうかは、実際のワークロードに深く依存すると思います』という結論になるかもしれません。