PostgreSQLの内部構造が「シムシティ風3D都市」で丸わかり 🏙️ 「PGSimCity」で学ぶDBの仕組み

PostgreSQLの内部構造が「シムシティ風3D都市」で丸わかり 🏙️ 「PGSimCity」で学ぶDBの仕組み #news
PostgreSQLの内部構造をシムシティ風の3D都市として可視化する「PGSimCity」を紹介。SQLの実行計画、shared_buffers、WAL、MVCC、Autovacuum、ストリーミングレプリケーションまで、データベース内部で何が起きているのかを分かりやすく解説します。

🏙️ PostgreSQLの内部を「街」に変換したPGSimCity

PostgreSQLは世界中のWebサービスや業務システムで利用されている高機能なオープンソースRDBMSですが、SQLを1行実行した裏側では、接続管理、SQL解析、実行計画の作成、メモリキャッシュ、ディスクI/O、WAL、MVCC、VACUUM、レプリケーションなど多数の仕組みが同時に動いています。こうした内部構造を文章や構成図ではなく、シムシティのような3D都市として歩き回りながら理解できるWebアプリが「PGSimCity」です。開発したのはPostgreSQL分野で活動するNikolay Samokhvalov氏で、ソースコードもGitHubで公開されています。ただしPGSimCityの中で本物のPostgreSQLが実行されているわけではなく、実際の仕組みを視覚的に再現した「教育用モデル」です。この点を理解したうえで使えば、PostgreSQLの複雑な内部処理をつなげて理解する教材として非常に面白い存在です。

🚦 SQLを送ると何が起きる？ 接続から実行計画までを街で追跡

PGSimCityのガイドツアーでは、まずクライアントからPostgreSQLへ接続する場面から始まります。PostgreSQLではサーバープロセスが接続を受け付け、通常はクライアント接続ごとに処理を担当するバックエンドプロセスが動きます。PGSimCityではこれらが建物や道路として配置され、接続数が増えるにつれて街が忙しくなるため、「大量のアイドル接続でもプロセスやメモリを消費するので、PgBouncerなどの接続プールが利用される理由」も視覚的に理解しやすくなっています。さらに「Query Lab」では、送られてきたSQLが解析され、Plannerが統計情報などを使ってSeq Scan、Index Scan、Bitmap Heap Scanといった候補から実行計画を選択し、Executorが実際に処理する流れを確認できます。SQLは基本的に「どんな結果が欲しいか」を記述する宣言型言語であり、データまでどの道を通って取りに行くかをPostgreSQL自身が判断していることが、この都市表現によって直感的に分かります。

PGSimCityで特に理解しやすい主要施設を整理すると、次のようになります。

  • 🏢 Backend：各クライアントのSQL処理を担当
  • 🧠 Query Lab：Parser・Planner・ExecutorなどSQL処理の流れを可視化
  • 💾 shared_buffers：頻繁に利用するデータページを保持する共有メモリ領域
  • 📝 WAL：障害復旧に必要な変更記録
  • 🧹 Autovacuum：不要になった行バージョンを回収
  • 💿 Storage：テーブルやインデックスの実データ
  • 🔄 Replica / Standby：WALを受け取りプライマリへ追従するサーバー

💾 shared_buffersとWAL、「メモリ→ログ→ディスク」の仕組みが見える

データベース性能を理解するうえで重要なのが、メモリとストレージの関係です。PostgreSQLでは通常、データを1行ずつ直接ディスクから読むのではなく、標準構成では8KiBの「ページ（ブロック）」単位で扱います。PGSimCityではこのページがタイルのように表現され、ストレージから読み出されたページがshared_buffersへ入り、その後のアクセスではメモリ上のページを再利用する流れを観察できます。実際にPostgreSQL 18の公式ドキュメントでも、shared_buffersはサーバーが共有バッファとして使用するメモリ量を指定し、標準ブロックサイズは8192バイトであることが確認できます。キャッシュヒット率が高ければ低速なストレージI/Oを回避しやすくなるため、PGSimCityの「街のどこにデータがあるか」という表現は、DB性能を考えるうえで非常に分かりやすいものです。

さらにUPDATEなどでデータを書き換える場合には「WAL（Write-Ahead Logging）」が登場します。PostgreSQLは変更されたデータページを即座にすべて永続ストレージへ書き込んでから処理を終えるのではなく、復旧に必要となる変更情報をWALへ先行して記録することで、障害発生時に状態を再構築できるようにしています。PGSimCityではWALバッファ、ストレージ、チェックポイントが別々の施設として描かれ、「コミット」「WALの永続化」「ダーティページの書き戻し」が同じ処理ではないことが視覚的に分かります。また、ダーティページを大量に書き戻すチェックポイントが短時間に集中すればI/O負荷が増え、レイテンシが跳ね上がることもあります。データベースで時折発生する「普段は速いのに一定間隔だけ突然遅くなる」という現象の一部を、都市の交通渋滞のように理解できるわけです。

🧹 MVCCとAutovacuumが分かれば「PostgreSQLが太る理由」も分かる

PGSimCityの中でも特に教育的なのが、MVCC（Multi-Version Concurrency Control）とVACUUMの可視化です。PostgreSQLでは複数のユーザーが同時に読み書きしても互いを極力ブロックしないためにMVCCを採用しており、読み取りと書き込みが基本的に競合しにくい仕組みになっています。例えばUPDATEでは、利用中の行を単純にその場で上書きするのではなく、新しい行バージョンを生成し、古いバージョンを一定期間残します。これによって別のトランザクションが過去の状態を参照していても整合性を維持できます。PostgreSQL公式ドキュメントも、MVCCでは読み取りのロックと書き込みのロックが競合せず、「読み取りが書き込みを、書き込みが読み取りをブロックしない」という性質を説明しています。

しかし、この便利な仕組みには「古い行バージョンが残る」という代償があります。不要になったdead tupleが増え続ければ、テーブルやインデックスが肥大化し、キャッシュやストレージを無駄に消費します。そこで登場するのがAutovacuumです。PGSimCityではAutovacuumワーカーが街を清掃する作業員のように巡回し、不要なtupleを回収して内部領域を再利用可能にしていく様子を確認できます。ただし通常のVACUUMは「ファイルを小さく圧縮する処理」ではなく、主として不要になった領域を今後のINSERTやUPDATEで再利用可能にする処理です。また、長時間終了しないトランザクションなどが存在すると、古い行をまだ参照する可能性があるためVACUUMが安全に削除できず、XMIN horizonが進まなくなることがあります。これは本番環境で発生するテーブル肥大化を理解するうえでも非常に重要な概念です。PostgreSQL 18でもAutovacuumによる定期的なVACUUMは基本的なメンテナンス機構として位置付けられています。

PGSimCityから実運用につながるポイントをまとめると、次のようになります。

  • 📈 接続数が多い → バックエンドプロセスやメモリ消費を確認
  • 💾 キャッシュヒットが低い → shared_buffersやワークロード、I/Oを調査
  • 📝 書き込みが遅い → WAL・チェックポイント・ストレージ性能を確認
  • 🧹 テーブルが膨張する → dead tuples、Autovacuum、長時間トランザクションを確認
  • 🔄 スタンバイが遅れる → WALの送信・受信・再生位置とReplication Lagを確認

🔄 レプリケーションまで3D化、PostgreSQL全体を「ひとつの都市」として理解できる

PGSimCityでは最後に、プライマリで生成されたWALがwalsenderによってスタンバイへ送られ、スタンバイ側が受信・適用して追従する物理ストリーミングレプリケーションも可視化されます。PostgreSQLのストリーミングレプリケーションはデフォルトでは非同期で、プライマリ側でコミットしたデータがスタンバイへ完全に反映される前に障害が起これば、Replication Lagに相当する範囲のデータを失う可能性があります。逆に同期レプリケーションを利用すれば耐久性を高められるものの、スタンバイ側の応答を待つ分だけ書き込みレイテンシとのトレードオフが発生します。PostgreSQL公式ドキュメントでも、この非同期・同期の違いが明確に説明されています。

そしてPGSimCity最大の魅力は、それぞれの知識を独立した用語として暗記するのではなく、「接続 → SQL解析 → 実行計画 → shared_buffers → WAL → ストレージ → VACUUM → レプリカ」という一連のシステムとして眺められることです。現実のPostgreSQLではさらにOSのページキャッシュ、ロック、インデックス構造、バックグラウンドプロセス、並列実行、I/Oサブシステムなど多数の要素が絡むためPGSimCityだけですべてを説明できるわけではありません。それでも「なぜDBが遅くなるのか」を考える際に、SQLだけを見るのではなく、キャッシュなのか、I/Oなのか、WALなのか、VACUUMなのか、レプリケーションなのかとボトルネックの位置を切り分ける思考を身につける教材として非常に優れています。なお2026年8月現在の安定版PostgreSQL 18では非同期I/O関連の仕組みも導入・拡張されており、実際のPostgreSQL内部はPGSimCityで示される基本概念を土台にしながら現在も進化を続けています。

📌 まとめ：複雑なPostgreSQLを「見て理解する」ための優れた教材

PostgreSQLを使うだけなら、shared_buffersやWAL、MVCCを詳しく知らなくてもSQLは実行できます。しかしシステムの規模が大きくなり、突然の性能低下やテーブル肥大化、レプリケーション遅延などを調査する段階になると、内部構造の理解が重要になります。PGSimCityは、そんな抽象的な仕組みを3Dの街として可視化することで、「SQLの裏側でデータベースが何をしているのか」を直感的に学べるWebアプリです。PostgreSQL初心者の学習用途はもちろん、普段DBを利用している開発者が内部構造を復習する教材としても、一度街を歩いてみる価値がありそうです。

📚 参考・出典

タイトルとURLをコピーしました