TECH BLOG
技術ブログ

データ移行やバッチ処理など、大量データの追加・更新処理を限られた時間内に完了させなければならない状況が多々あります。
PostgreSQL では設定を変更することで、デメリットは発生しますがデータの追加や更新処理を高速化させることができます。
デメリットを無視できるような状況であれば、データの追加や更新処理を高速化できるため、非常に有用な対応となります。
高速化のために変更する設定とそのデメリットは下記のとおりです。

高速化のための設定とデメリット

例えば

• データベースクラスタの初期構築時に、fsync と full_page_writes を off にしてデータ追加作業を高速化させる。
  • もし、PostgreSQL がクラッシュした場合には、データ追加作業を一からやり直す。
• テーブルのデータは 1 時間に 1 回のバッチ処理で、テーブルを TRUNCATE してから COPY でデータ追加するので、COPY 先の対象テーブルを UNLOGGED にする。
  • もし、PostgreSQL がクラッシュした場合には、COPY を再度行い、データを追加しなおす。

などのデメリットを無視できるような状況で有効な対応策となります。
では、設定を変更することで具体的にどのぐらい高速化されるのか、というのが気になりましたので、

• Ubuntu 20.04.
• PostgreSQL 14.4
• CPUコア数：4
• メモリサイズ：4 GB
• SSD

という環境で検証を行いました。

COPY 実行速度

pgbench の pgbench_accounts テーブルに対して、1000万件のデータを追加する COPY コマンドの処理時間を確認しました。
データ量としてはおおよそ 1.2 GB となります。
結果をグラフにしたものが下記となります（グラフ画像が小さくて文字などが読めない場合は、右クリックしてグラフ画像を新しいタブで開くか保存して確認してください）。

• 一番左のデフォルト設定となっているのが、PostgreSQL のデフォルト設定（下記）時の処理時間です。
  • synchronous_commit = on
  • fsync = on
  • full_page_writes = on
  • wal_level = replica
  • テーブルの WAL 出力 = LOGGED
• テーブルの WAL 出力を UNLOGGED にするのが、一番高速になりました。
• fsync と full_page_writes の両方を off にする方が、どちらか片方だけ off にするより高速となりました。
• synchronous_commit と wal_level については、デフォルト設定とそれほど処理時間が変わらない結果となりました。

UPDATE 実行速度

pgbench の pgbench_accounts テーブルに対して、今度は 1000万件のデータを更新する UPDATE コマンドの処理時間を確認しました。
COPY コマンド時と同じく、データ量としてはおおよそ 1.2 GB となります。
結果をグラフにしたものが下記となります。

• 設定変更時の高速化の度合いは、COPY コマンドとおおむね同様の傾向となりました。
  • WAL 出力を UNLOGGED にした場合と fsync = off、synchronous_commit = off にした場合、COPY コマンドよりもより高速化されました。
  • wal_level = minimal に変更した場合は、デフォルト設定よりも UPDATE 実行時間が若干遅くなりました（理論的には wal_level = minimal で高速化されてもよいのとは思うのですが・・）。
• COPY コマンドと同じく fsync と full_page_writes の両方とも off した方が高速化されています。
  • 補足的な話になりますが、fsync と full_page_writes は、どちらか一方を off にした時点で、PostgreSQL クラッシュ時にデータ破損などが発生する可能性がありますので、どちらかを off にする場合には、両方とも off にした方がより高速化されます。

最後に

今回の検証では pgbench_accounts という簡単なテーブルを利用したためか、思ったよりも設定変更による高速化の効果が出ませんでした。
個人的な経験では、今回の検証以上の高速化の効果があるという印象です。
実際の業務では限られた時間内に処理を終わらせる、レスポンスを返す、ということが求められますので、設定変更によるデメリットを無視できるのであれば試してみるのもよいと思います。

また、今回は触れませんでしたが、更新処理を高速にしたいという場合には、HOT 更新を行えるようにすることも効果的ですので、機会を作ってその効果を検証したいと考えています。