データベース「PostgreSQL」がどのように実際には内部で動いているかがシムシティっぽい3Dでわかる「PGSimCity」

PostgreSQLは世界中で広く使われているデータベース管理システムですが、SQLを実行したときに内部でどのプロセスが動き、データがどのようにメモリやディスクへ読み書きされるのかを直感的に把握するのは簡単ではありません。そんなPostgreSQLの内部構造を、シムシティのような3D都市として可視化してくれるウェブアプリが「PGSimCity」です。

PGSimCity · How PostgreSQL Works, in 3D
https://nikolays.github.io/PGSimCity/

GitHub - NikolayS/PGSimCity at pgsimcity-hud · GitHub
https://github.com/NikolayS/PGSimCity?ref=pgsimcity-hud

PGSimCityにアクセスするとこんな感じ。まず流れを把握するため、ガイドツアーを見て回ることにします。「Start the tour」をクリック。

ガイドツアーの最初では、PostgreSQLへの接続がどこから始まるのかが示されます。画面中央の「The service address」が接続先の入口で、クライアントからのTCP接続がPostmasterへ届き、そこから実際の処理を担当するバックエンドへ渡される流れが表現されています。右側のInspectorには「removed dead tuples」が表示されており、左下から伸びるクライアント接続の流れと、Postmasterがサーバー側の受付窓口として描かれています。

そして、PostgreSQLが接続ごとにバックエンドプロセスを割り当てる仕組みがこれ。画面手前に並ぶ建物群が各バックエンドを表しており、接続が増えるほど専用プロセスも増加します。PostgreSQLでは待機している接続であってもメモリやプロセス管理のコストがかかるため、接続プールが重要になります。

SQLが実行計画へ変換される「Query Lab」では、画面上部にはSELECT文が表示され、下部にはParser、Rewriter、Planner、Executorに相当する工程や、Seq Scan、Index Scan、Bitmap Heap Scanといった候補が並びます。SQLは「どのデータが欲しいか」を指定するもので、実際にどの経路で読むかはPostgreSQLが統計情報を基に見積もって決めるというわけです。

shared_buffersはPostgreSQLの共有バッファキャッシュを示しています。中央の白いタイル状の広場がバッファプールで、PostgreSQLはディスク上のデータを、1行ずつではなく8KiBのページ単位でここへ読み込みます。上部のCACHE HITが高いほど、必要なページがすでにメモリ上にあり、ディスクアクセスを避けられていることを意味します。

ディスク上のデータページの中身。地下のストレージ領域にあるaccountsテーブルは8KiBページの集まりとして描かれており、ページ内には行データや空き領域を管理する情報が含まれます。緑色の経路は、ディスク上のページがshared_buffersへ読み込まれる流れを表しています。

データページより先にWALへ変更内容を書き込む仕組み。UPDATEなどで行を変更すると、PostgreSQLはまずメモリ上のページを変更し、その変更内容をWALバッファへ記録します。画面中央下の円形施設がWALバッファで、ページ本体を後から書き出しても、先にWALが残っていれば障害発生後に復旧できます。

トランザクションのコミットでは、変更済みデータページそのものがすぐディスクへ書かれることを待つのではなく、WALレコードが安全にフラッシュされることを待ちます。画面右上にはsynchronous_commitをオフにした状態も表示されており、待ち時間を減らせる一方で、障害時に直近のコミットを失う可能性があるというトレードオフを示しています。

チェックポイントによって書き込み負荷が集中する様子。左側の緑色の施設がcheckpointerで、shared_buffers内のダーティページをまとめてストレージへ書き出しています。上部にはmax_wal_sizeによってチェックポイントが発生したことが表示されており、チェックポイントが頻発するとWALやデータページの書き込みが集中し、遅延がスパイクとして現れます。

PostgreSQLのMVCCにより、更新済みの古い行はすぐに消えません。UPDATEは既存の行をその場で上書きするのではなく、新しい行バージョンを作成し、古い行をdead tupleとして残します。以下の画像ではsessionsテーブルにdead tuplesが蓄積しており、読み取りと書き込みをブロックしにくくする代わりに、後でvacuumによる掃除が必要になります。

autovacuumがdead tuplesを回収する様子。autovacuumワーカーは統計情報を見て、不要な行バージョンが増えたテーブルを巡回し、テーブルやインデックスを読みながら空き領域を再利用可能にします。ただし、vacuumは通常ファイルサイズを直接縮小するのではなく、テーブル内部の空き領域を将来のINSERTやUPDATEで使えるようにする処理である点が重要です。

中央の赤い円で示された「XMIN HORIZON」は、古いトランザクションがまだ参照できる可能性のある行バージョンの境界を表しています。この境界が動かないと、autovacuumはdead tuplesを安全に削除できず、不要な行が蓄積してテーブル肥大化につながります。

プライマリ側で生成されたWALはwalsenderからスタンバイへ送られ、スタンバイ側のwalreceiverやstartup processが受け取って再生します。ここで送られているのはSQL文そのものではなく、データページに起きた物理的な変更の記録であり、スタンバイはそのWALを順番に適用してプライマリに追従します。

画面には送信済み、書き込み済み、フラッシュ済み、再生済みという4つのLSNに対応する状態が描かれており、スタンバイがWALを受け取っていても、まだ再生していなければ問い合わせ結果には反映されません。プライマリが送った位置とスタンバイが実際に適用した位置の差が、画面上部のREPL LAGとして現れます。

最後にPostgreSQL内部の一連の処理を街全体として振り返ります。クライアント接続、Postmaster、バックエンド、Query Lab、shared_buffers、WAL、ストレージ、メンテナンス、スタンバイが同じ空間に配置され、これまでに見た処理が1本の流れとしてつながっていることが分かります。PostgreSQLの性能問題は、このうちキャッシュ、WAL、チェックポイント、vacuum、レプリケーションなどのどこかで待ちやボトルネックが生じることで表面化します