Kattsu Sandbox

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 ディレクトリはデータベースごとに下記のファイルが格納されます。

名称説明
テーブルファイルテーブルデータの実体が格納される。8192 バイトの「ページ」と呼ばれる単位で構成される
インデックスファイル検索性能向上のインデックス情報が格納される。こちらも 8192 バイトの「ページ」単位で構成される
TOAST ファイルテーブル内の巨大な行(通常は 2kB 以上)をテーブルファイルとは別で格納するためのもの
Free Space Map ファイル空き領域を追跡するための情報が格納されたファイル
Visiblity Map ファイルテーブルの可視性を管理するファイル

当然ながら物理データファイルが格納されるディレクトリの中でも最も重要なディレクトリになります。 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 ごとにファイルが増えていきます。

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

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

名称説明
ページヘッダ自ページの情報を格納する。ページの先頭から 24 バイト。
アイテム ID データ更新したデータのメタ情報を格納する。
ページヘッダの次からひとつにつき 4 バイト。
アイテムデータ更新したデータの実体データ情報を格納する。
ページの末尾から先頭に向かって格納される。サイズは可変。
空き領域アイテム ID データとアイテムデータの間の 00 が連続している領域。
ページに更新があるとここにアイテム ID データとアイテムデータが入る。
ここにデータが入らなくなったら新たなページが追加される。

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

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

名称長さ内容
lp_off15対応するタプルの開始オフセット
lp_flags2タプルの状態を示すフラグ(0:未使用, 1:使用中, 2:HOT 更新でリダイレクト, 3:無効)
lp_len15対応するタプルの開始オフセット

上の画像でいうとアイテム 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 が追記型のアーキテクチャというのは図に表すと下記のようなイメージです(あくまでイメージであり実体としては他の情報も記録されているはず)

image.png

アイテムデータは末尾から先頭に向かって下から順に格納されていきます。 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 ページ存在していることがわかります。 インデックスファイルは下記のようなページ種があるツリー構造になっています。

名称説明
メタページインデックスファイルの先頭。制御用の情報が格納されている
ルートページメタページを除くすべてのページの先頭。ここから検索を始める
リーフページ木構造の葉となるページ。テーブルファイルへのポインタを持つ
インターナルページルートページとリーフページの間に存在するページ

image.png

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

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

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

まとめ

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

書いている人

大阪でソフトウェアエンジニアとして働いています。

© 2020 Kattsu Sandbox