MySQL利用者のためのPostgreSQL入門

Last updated at 2024-08-16Posted at 2024-08-14

1. トランザクション実行の流れ：

クライアントからPostgreSQLに接続し、SQL実行したうえでトランザクションを完了させる際の一連の流れ:

2. PostgreSQLにおけるWAL

WAL（Write Ahead Logging）って何？
その名の通り、データの変更をディスクに書き込む前には一旦ログに記録しましょうという取り決めのことです。

PostgreSQLにおいてWALが保存される領域は「WALバッファ」「WALファイル」と分かれています。前者がメモリ領域、後者がディスク領域です。
WALバッファがディスクに書き込まれるトリガーとしては、以下の二つがあります。
- ユーザが(BEGIN TRANSACTIONの後)COMMITコマンドを実行する
- WALバッファサイズが規定量を超える(設定値:wal_buffers)を超える
なぜデータの変更ごとにディスクに直接書き込まないのか？
- データの変更ごとにディスクに直接書き込むと、ランダムなディスクアクセスが増え、パフォーマンスが大幅に低下します。
- ディスクへの書き込みは時間がかかり、書き込んでいる途中でデータベースがクラッシュしてしまう場合、データが不完全な状態で残るため、クラッシュリカバリが難しくなります。つまり、トランザクションのACID特性の一つである一貫性（Consistency）が保証できなくなります。
書き込んだその履歴をログとして吐くなら、なぜ"書き込み"=>"ログ吐く"の順番じゃないの？どうしてログを先に出力するのか？
WALは単なる書き込んだその履歴を記録するのもではなく、トランザクションの原子性を保証する仕組みでもありますから。
トランザクションの途中でエラーが発生した時、ロールバックまたはロールフォワードによりDB状態の復旧が可能かと思います。
この時テーブルの実体であるデータファイルは中途半端な状態で止まっていることが想定されるため、別途復旧の手掛かりが必要です。

3. PostgreSQLストレージエンジン

MySqlではinnodb, myisam, memeryなど複数のstorage engineがある。

PostgreSQLでは、MySQLのように複数のストレージエンジンを切り替えて使用する機能は標準ではありません。PostgreSQLは、デフォルトで一つの統一されたストレージエンジンを使用します。このエンジンは、トランザクション管理やデータの永続化、インデックスの管理などを行い、ACID特性を備えた高度な機能を提供します。

しかし、最近のPostgreSQLのバージョンでは、「プラガブルストレージエンジン」と呼ばれる新しい機能が導入されました。この機能により、特定の用途に応じてカスタムストレージエンジンを作成・使用することが可能になっています。ただし、この機能はまだ発展途上であり、MySQLのストレージエンジンほど一般的ではありません。

例えば、PostgreSQL 12以降では、これにより異なるストレージエンジンを使用してデータの保存方法を最適化することができますが、これは特定のニーズに応じたケースに限られます。

4. PostgreSQLでのトランザクションログ

1. WAL Logとは

MySQLではbinlogとredo logという役割の異なる二種類のトランザクションログが存在しています。

PostgreSQLでは、MySQLのようにbinlogとredo logの二種類のトランザクションログを使用するのではなく、WAL Logと呼ばれる一つのログ機構を使用しています。
つまり、MySQLでは「トランザクションログ」という言葉が具体的にどのログを指しているかは文脈によりますが、PostgreSQLではトランザクションログ = WAL Logです。PostgreSQLのWAL logは、MySQLのbinlogとredo logの両方の役割を果たす統合されたログです。

2. WAL (Write-Ahead Log)の役割:

クラッシュリカバリ: MySQLのredo logに相当する機能を持ち、トランザクションがデータベースに適用される前に、まずWALにログが書き込まれます。これにより、クラッシュが発生した場合でも、ログをリプレイすることでデータの一貫性を保ちます。
レプリケーション: 詳細は後ほど説明
PITR（Point-In-Time Recovery）: MySQLのbinlogと同様に、WALはレプリケーションやポイントインタイムリカバリにも使用されます。

MySQLのようにbinlogとredo logの二種類の異なるログを利用さぜるえないが、postgresqlでは両方の役割を果たす統合されたログが利用できる根本的な原因は何でしょうか？

MySQLは様々なストレージエンジン（InnoDB, MyISAMなど）をサポートしており、それぞれが異なるログメカニズムを持つため、ログの分離が必要です。
そもそもbinlogはストレージエンジンの機能でもなく、server layerで実装されて、すべてのストレージエンジンに共有されるログ機能。そしてトランザクションをサポートさせるために、InnoDBが後から追加された。そしてInnoDBではすでにクラッシュリカバリサポートするredo log機能があった。結果的にredo logとbinlogが分離されるアーキテクチャになったが、分離されることが別に最適なアーキテクチャではるというわけではありません
更にいうと、MySQLはトランザクションログに相当するファイルが2種類ある点は、若干ユニークなアーキテクチャであり、一般的ではありません。 Oracleではredo logと呼ばれる一つのログしかありませんし、PostgreSQLではWAL logに統一される。
PostgreSQLは、統一されたストレージエンジンを採用しているため、WALに一本化することで設計の一貫性を保つことができます。このため、WALはトランザクションの整合性を保ちながら、同時にクラッシュリカバリやレプリケーションをサポート

3. WAL logの中身

PostgreSQLのWAL (Write-Ahead Logging) ログはバイナリ形式で保存されているため、テキストエディタなどでそのまま内容を確認することは難しいです。pg_waldumpというツールを使用することで、WALログの内容を人間が読める形式に変換して表示することができます。

WALログの出力サンプル
```
pg_waldump /path/to/your/wal/files/
```

出力の一例

pg_waldump /tmp/sampledb/pg_wal/000000010000000000000003
rmgr: Heap        len (rec/tot):     61/    61, tx:        559, lsn: 0/03000028, prev 0/02000100, desc: INSERT off 4, blkref #0: rel 1663/13214/16384 blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        559, lsn: 0/03000068, prev 0/03000028, desc: COMMIT 2019-02-16 09:16:14.006604 JST
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/03000090, prev 0/03000068, desc: RUNNING_XACTS nextXid 560 latestCompletedXid 559 oldestRunningXid 560

サンプルの説明
- rmgr: リソースマネージャー、どのリソースに対する操作か(HEAP,Transaction, Btreeなど)
- len (rec/tot): 記録されたWALレコードのサイズ
- tx: トランザクションID
- lsn: WALログシーケンス番号
- desc: WALレコードの内容の説明 (どんな操作か(INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, INSERT_LEAFなど))

このサンプルは、PostgreSQLが実行中に記録したWALレコードの一部を示しています。内容は、チェックポイントの記録、ヒントビットの設定、インデックスの挿入操作など、さまざまなデータベース操作が含まれます。
MySQLのbinlogは論理ログであることに対して、PostgreSQLのWALは、データベースのブロックやページに対する変更を物理的に記録するため、物理ログです。

5. レプリケーション

PostgreSQLは、バージョン9.0からレプリケーション機能として「ストリーミングレプリケーション」をサポートしていますが、バージョン10から新たに「ロジカルレプリケーション」が追加されました。

5.1 ストリーミングレプリケーション(物理レプリケーション)

マスター側から全てのトランザクションログ(物理ログのWAL log)を転送し、スタンバイ側はWAL logを適用します。このWAL logを適用することで、データベースクラスタの完全なcopyが作成できます。そのため、ストリーミングレプリケーションは負荷分散や、PacemakerなどのHAソフトと組み合わせて可用性を高めるために利用されています。ただし、物理ログのWALファイルをそのまま適用するため、PostgreSQLのメジャーバージョンや、OSのアーキテクチャが異なる場合は、実施不可となります。

5.2 ロジカルレプリケーション

WALに記載されている変更情報(物理レベル)を先に論理的なレベルに変換（デコード）してから転送します。
そのため、PostgreSQLのメジャーバージョンや、OSのアーキテクチャが異なる場合でも実施可能です。
レプリケーションの上流側にパブリケーション、下流側にサブスクリプションを作成することで、レプリケーションの対象を制御することができます。具体的には特定のテーブルのみ、特定の操作のみ(INSERT・UPDATEはレプリケーションするがDELETEはしないなど)レプリケーションすることも可能です。

WALの内容を論理的に解釈とは

WALの物理ログと論理ログの違い

物理ログ: データベースのブロックやページ単位の変更をそのまま記録します。例えば、「ページXのオフセットYにデータを書き込む」といった低レベルの情報です。
論理ログ: SQL操作や行レベルの変更として、データベースの操作をより高レベルに記録します。例えば、「テーブルZに新しい行を挿入する」といった情報です。

サンプルを通じた流れ：

パブリッシャー側で新しいデータが挿入される:

INSERT INTO products (name, price) VALUES ('Apple', 100);

WALに記録される:
WALには、データベースブロックに対する物理的な変更が記録される。
物理的なWALログには、以下のような低レベルの変更(特定のブロック（ページ）に対する挿入操作)が記録されます:
```
rmgr: Heap len (rec/tot): 48/96, tx:483, lsn:0/016C8BA0, prev 0/016C8B30, desc: INSERT off 3, blkref #0: rel 1663/1/1259 blk 2
```
論理的な解釈
論理レプリケーションでは、WALのこのような低レベルの記録を「論理的に解釈」し、高レベルのSQL操作として扱います。たとえば、上記の物理的な変更を以下のように解釈します。
- 操作: INSERT
- テーブル: products
- 挿入されたデータ: {id: 1, name: 'Apple', price: 100}
サブスクライバーにデータ転送:
パブリッシャー側側で実行されたSQL文そのものは送信されません。代わりに、SQL文の結果として生じたデータ変更の情報がサブスクライバーに送信されます。
サブスクライバー側でデータが反映される:
サブスクライバー側では、このイベントに基づいて、同じデータ変更が再現されます。
パブリッシャー側で実行されたのと同じSQL文ではなく、その内容に基づいて生成された変更イベントです。　MySQLのbinlogではformatがROWである時の動きに似ています。

下記のような利用例が考えられます。

異なるメジャーバージョンのPostgreSQL間でレプリケーション⇒つまりメジャーバージョンのバージョンアップ
異なるOS（例えばLinuxからWindows）のPostgreSQL間でレプリケーション(オンプレからクラウドや異なるクラウド間のデータベース移行)
分析目的で必要なデータを複数のデータベースから集めて、一つのデータベース(例えば専用のdata warehouse)に統合する
複数のデータベース間で、特定のテーブルのみを共有する

	streaming replication	logical replication
レプリケーションの方法	全てのWAL log(物理ログ)をそのまま転送	WALをデコード(ロジカルレベルに変換)して転送
レプリケーション対象	インスタンス単位	データベース単位(テーブル、操作を指定可能)※ただし、DDLコマンドと一部のオブジェクトがレプリケーションされない
速度	非常に高速、低遅延	WALの内容を論理的に解釈してからサブスクライバーに送信するため、若干の遅延が発生することがある
利用例	・読み取りの負荷分散・高可用性構成	・メジャーバージョンのバージョンアップ・異なるOS間のレプリケーション(データベース移行) ・複数のデータベースの情報を統合・特定のテーブルのみの共有
スタンバイ側/サブスクライバー側の操作	参照のみ	参照と更新が可能 ※コンフリクト（衝突）が発生する可能性がある
対応バージョン	9.0以降	10以降

5.3 wal_level設定によってreplicationを制御する

wal_level	記録される情報	できること	利用シーン
minial	最小限のWALデータ: 主にクラッシュリカバリに必要な情報のみ記録	クラッシュリカバリしかできない	1. パフォーマンス重視: レプリケーションやPITRを使用しないシナリオで、WALの生成量を抑えてパフォーマンスを最大化したい場合 2. テスト環境: 高速にトランザクションを処理したいテストや開発環境で有効。
replica(default値)	クラッシュリカバリに加え、物理レプリケーションのための情報。	1. クラッシュリカバリ 2. レプリケーション 3. データのPITR(point-in-time-recovery)	1.ストリーミングレプリケーション: 主に物理レプリケーション 2.高可用性構成
logical	`replica`で出力される情報に加えて、論理レプリケーションに必要な行・テーブルレベルの変更内容も出力	1. replicaできることがすべてできる 2. 更にロジカルデコーディングをサポートするのに必要な情報を追加	1. 論理レプリケーション 2. データ統合 3. データ移行

それぞれのレベルは、下位のレベルで出力される情報も含まれる。
ストリーミングレプリケーションを有効にするには、少なくともreplicaレベルに設定する必要があります。
このパラメータを調整するにはデータベースを再起動する必要があります。
以前はarchiveとhot_standbyもありましたが、PostgreSQL9.6（2016年9月リリース）では、archiveとhot_standbyが新しい「replica」に統合されました。

5.4 AWS Auroraでlogical replicationを利用する方法

Auroraではwal_levelのデフォルト設定はreplicaです。ロジカルレプリケーションを利用する場合は、wal_levelをlogicalに設定する必要があります。ただし、Aurora PostgreSQLはマネージドサービスであるため、wal_level直接変更はできないようですね。代わりにrds.logical_replication(default値0)を1に変更すればよいらしい。

AWS DMSでAurora PostgreSQLのデータを他のデータベースにデータ同期/移行する場合もrds.logical_replicationを変更する必要があるようです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up