PostgreSQLのデータの物理的な保存とログ先行書き込みについて

はじめに

PostgreSQL で保存されるテーブルのレコードなどは物理的にはファイルとして保存されるが、どこに、どのように、どのタイミングで保存されるかを調べてみました。 PostgreSQL のバージョンは 11 です。

テーブルファイルの構造

まず試しに空のテーブルを作り、作成したテーブルのファイルノード番号を取得します。 ファイルノード番号とはテーブルデータファイルの ID のようなもので、テーブルデータのファイル名にもなります。

create table sample (id int);
select relname, relfilenode from pg_class where relname = 'sample';
 relname | relfilenode
---------+-------------
 sample  |       24622

-- 下記のようにテーブルファイルのパスを取得することもできる。
select pg_relation_filepath('sample');
 pg_relation_filepath
----------------------
 base/13091/24622

このテーブルファイル名でデータベースの物理テーブルファイルを保存している場所を探してみます。 テーブルファイルは$PGDATA/base/内に保存されています。 $PGDATAは多くの場合/var/lib/postgresql/11/main/などです。 base ディレクトリはデータベースごとに下記のファイルが格納されます。

当然ながら物理データファイルが格納されるディレクトリの中でも最も重要なディレクトリになります。 base ディレクトリ直下はデータベースの oid を名前としたディレクトリが配置されており、その中に上のファイルが格納されています。 データベースの oid は pg_database テーブルから取得できます。

select oid, datname from pg_database where datname = 'postgres';
  oid  | datname
-------+----------
 13091 | postgres

今回は postgres データベースにテーブルを作成したので、base/13091ディレクトリを確認すればよいことがわかります。

$ cd /var/lib/postgresql/11/main
$ ls -la ./base/13091/24622
-rw------- 1 postgres postgres      0 May 23 22:34 24622

ありました。 テーブルファイルは存在したがファイルサイズが 0 なのでまだ何も書き込まれていません。 テーブルを作成しただけでレコードがない段階では、まだメタデータしか存在しないのでテーブルファイル自体は空のようです。

次に先程作成した空のテーブルに 1 行インサートしてみます。

insert into sample values (1), (2), (3);

root@DESKTOP-J70J3S6:/var/lib/postgresql/11/main# ls -la ./base/13091/24622
-rw------- 1 postgres postgres   8192 May 23 23:45 24622

今度はテーブルファイルのサイズが 8192 バイトになったのでデータの書き込みがなされたことがわかります。 今回インサートしたのは int 列 1 行でしかないためデータとしては実体のデータとしては 4 バイトしかありませんが、テーブルファイルは 8192 バイトの「ページ」と呼ばれる単位で構成されるため、8192 バイトずつしか増加していきません。 そしてテーブルファイルの 1 ファイルの上限は 1GB までと決まっており、それ以降は 24632.1、24632.2...のように 1GB ごとにファイルが増えていきます。

テーブルファイルをバイナリエディタで見てみます。

テーブルファイルは主に次の要素で構成されます。

アイテム ID データとアイテムデータ

アイテム ID データとアイテムデータは対の存在でありページには同じ個数分存在しています。 アイテム ID データは先頭から順に次の情報が保存されています。

上の画像でいうとアイテム ID データはE0 9F 38 00, C0 9F 38 00, A0 9F 38 00です。 E0 9F 38 00は bit に直すと、11100000 10011111 00111000 00000000となります。 これはバイトごとに逆順に格納されているので、上の情報を見るときは00000000 00111000 10011111 11100000と並べ替えます。 これで下記のような情報がわかります。()内は 10 進数に直した数値です。

• lp_off: 001111111100000 (8160)
• lp_flags: 01 (1)
• lp_len: 000000000011100 (28)

つまり、先頭から 8160 バイトの位置から 28 バイト分の情報に対応するアイテムデータが存在しており、使用中だということがわかります。 実際に先頭から 8160 バイトの位置は上の画像でちょうど一番下のオレンジ枠にあたるので正しそうです。 ここで INSERT したのは1という INT 列 1 行なので、データの実体は 4 バイトしかないが INSERT で格納されたなどの情報も入ってくるのでそれ以上のバイト数が専有されているものと思われます。 PostgreSQL は追記型のアーキテクチャなので、挿入だけでなく更新・削除でもテーブルファイルの実体データには追記されていきます。

追記型アーキテクチャ

PostgreSQL が追記型のアーキテクチャというのは図に表すと下記のようなイメージです（あくまでイメージであり実体としては他の情報も記録されているはず）

アイテムデータは末尾から先頭に向かって下から順に格納されていきます。 id=2 のデータは 4 回目の操作で UPDATE されていますが、もともと INSERT id=2 となっていたアイテムを更新するのではなく追記していく形になります。DELETE も同様です。

これを見ると ORDER BY をつけなければ SELECT でのデータ取得で順序が確定できないイメージできます。 挿入・更新・削除によりテーブルファイル内のデータはバラバラになるので、あくまで論理的なテーブルから取得しているわけではなく物理的なファイルから取得するイメージをすると順序の確定が思ったよりコストのかかる処理であることが理解できます。

テーブルファイルの性能低下を防ぐ VACCUM

更新・削除でもテーブルファイルが増えていくとページ内のアイテムに実際には使用されていないものが増えていきこれが性能低下に繋がることがあります。 これを防ぐために PostgreSQL では定期的に VACUUM と呼ばれる処理が行われています。

VACUUM では各テーブルのページを走査していき、Visiblity Map ファイルをチェックして不要行を含むかを確認しあれば不要行を削除します。また、対象テーブルのインデックスメンテナンスも行います。 そして、削除した行の情報をもとに Free Space Map ファイルを更新します。

大抵は VACUUM でテーブルファイル、インデックスファイルの掃除は行われますが、長期化しているトランザクションがある場合など、定期的な VACUUM 実行では対応し切れない場合に VACUUM FULL によるメンテナンスも行います。

定期的な VACUUM 中は参照が可能であり、更新も VACUUM 中のページ以外での更新は可能なので実質可能ですが、VACUUM FULL 中は対象テーブルへの参照・更新はすべて待たされるので注意が必要です。

ログ先行書き込み

何回かデータをインサートしていきテーブルファイルを確認すると、コミット時点では必ずしもテーブルファイルにデータが追記されていないことに気づきます。 これはいわゆるログ先行書き込みと呼ばれる仕組みによるものです。

ログ先行書き込みとは、テーブルファイルに書き込む前に WAL(Write Ahead Log)ファイルと呼ばれるファイルにまず更新内容を追記し、一定のタイミングでテーブルファイルに後から書き込むことです。

これを行う最も大きな理由は DB の ACID 属性のひとつ原子性を担保するためです。 原子性とは 1 トランザクション内の処理がすべて実行されるかすべて実行されないかのどちらかとなり、中途半端な状態は許さないことです。

先に WAL ファイルに書き込んでおけば、途中でクラッシュしてもその内容からテーブルファイルをロールバックしたり、リカバリできたりします。 直接テーブルファイルに書き込むだけだと、途中でクラッシュした場合などには半端な状態が残ってしまいます。

また、コミット時に即 WAL ファイルに書き込むわけではなく、一度 WAL バッファと呼ばれるメモリ領域に更新内容は保存されます。 つまり、WALバッファ(メモリ) → WALファイル(ディスク) → テーブルファイル(ディスク)という順で書き込まれていきます。 上は時間軸的な書き込み順であって、バッファに内容があれば書き込み自体はバッファからテーブルファイルになされたりします。

これは書き込みのパフォーマンスを上げるためで、ディスクへの書き込みはメモリに比べて非常に遅いため、都度ディスクにあるテーブルファイルや WAL ファイルに書き込むと頻繁な更新がある際に問題となります。 WAL バッファに変更を溜め込み、一定のタイミングで WAL ファイルに書き込むことでディスクへの書き込み回数を減らすことができます。 常にWALバッファ → テーブルファイル、ではなく WAL ファイルが必要なのはメモリは有限で更新内容をすべて保持仕切れるとは限らないからです。 WALバッファ → WALファイル → テーブルファイルとすれば、バッファがあふれたときも WAL ファイルに記録することができます。 またテーブルファイルへの追記は先程見たようにページ内での追記位置を決めていかなければいけませんが、WAL ファイルは末尾にアペンドしていくだけなのでこちらも速いです。

WAL ファイルはpg_walディレクトリに存在しています。 コミット時点で更新日時が更新されているので、テーブルファイルより先にこちらに追記されることがわかります。 また、中身を見ると先頭からしばらくデータが記録されているが途中から空き領域となっているので、テーブルファイルのページ構成と異なり先頭からただ追記していくだけだということもわかります。

$ ls -l pg_wal/
total 16384
-rw------- 1 postgres postgres 16777216 May 24 15:39 000000010000000000000001
drwx------ 1 postgres postgres     4096 May 24 11:39 archive_status

WAL ファイル、テーブルファイルそれぞれの書き込みタイミングは下記となっています。

WAL ファイルへの書き込みタイミング

• COMMIT 発行時
• wal writer プロセスの定期実行時
• WAL バッファあふれ
• CHECKPOINT, VACUUM 実行時
• 共有バッファあふれ

テーブルファイルへの書き込みタイミング

• CHECKPOINT 実行時
• writer プロセスの定期実行時
• 共有バッファあふれ

こちらは下記ページで詳しく説明されていました。 PostgreSQL WAL ログの仕組みとタイミングを理解したい

インデックス

例えば select で特定の 1 行を選択するとき、インデックスが活用されれば取得は速いが、そうでないときは実質テーブルの全行を走査した上で取得するので遅くなってしまいます。 取得時は実体のテーブルファイルを見に行くので、インデックスが活用されるかどうかはテーブルファイルでのデータの位置を特定できるかどうかにつながります。 ではインデックスデータはどのように保存されているのでしょうか。

まず先程作成したテーブルにインデックスを作り、ファイルノード番号を取得します。 インデックスのファイルノード番号もテーブルと同じく pg_class から取得でき、また base ディレクトリに同じファイル名で保存されています。

create index on sample (id);
select relname, relfilenode from pg_class where relname = 'sample_id_idx';
    relname    | relfilenode
---------------+-------------
 sample_id_idx |       16390

今回もbase/13091ディレクトリで探してみます。

$ ls -la ./base/13091/ | grep 16390
-rw------- 1 postgres postgres  16384 May 24 15:36 16390

あった。 今回ははじめから 16384 バイト存在しています。 インデックスファイルもテーブルファイルと同じく 8192 バイトのページ単位で構成されているので、今回はすでに 2 ページ存在していることがわかります。 インデックスファイルは下記のようなページ種があるツリー構造になっています。

シーケンシャルアクセスとインデックスアクセス

シーケンシャルアクセスとは、インデックスを使用せずテーブルファイルを全ページを順番に見てアクセスする方法です。上で述べたように、テーブルファイルのページはテーブルの全レコードのタプルにとどまらず更新、削除ごとにタプルが追加されているので、挿入・更新・削除が頻繁に行われるテーブルほどシーケンシャルアクセスは遅くなってしまいます。

インデックスアクセスとは、インデックスページの木構造により、ルートから順々に目的のリーフまで辿っていき、リーフには格納されているテーブルファイルのポインタからアクセスする方法です。 二分探索的にアクセスできるのでテーブルファイルを全走査するより当然高速であり、これはテーブルファイルのページ量が増えるほど顕著になっていきます。 テーブルの全レコード数が少量だったり、取得する行数が大きい場合にはインデックスアクセスの方が遅くなってしまうのはイメージができると思います。 もっとも、そのような場合にはオプティマイザがインデックスアクセスを選択しませんが、インデックスファイルを作る分のコストもあるので、不要なインデックスは作成しない方が好ましいです。

まとめ

• テーブルファイルはページ単位で構成されており、挿入・更新・削除の度にページ内のアイテムは増加する
• ログ先行書き込みの仕組みにより、コミット時には WAL バッファから WAL ファイルに書き込みされる
• テーブルファイルには CHECKPOINT 実行時や writer プロセスの定期実行などであとでまとめて書き込まれる
• インデックスファイルによりテーブルファイルの特定のポインタに高速にアクセスできる