MySQL利用者のためのPostgreSQL入門

# 1. トランザクション実行の流れ：
クライアントからPostgreSQLに接続し、SQL実行したうえでトランザクションを完了させる際の一連の流れ:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/7d29e302-94db-ab10-4d39-eee8b07e088e.png)

https://blog.recruit-productdesign.jp/n/n999470a10b26


# 2. PostgreSQLにおけるWAL
https://hiro-tracks.net/postgres-wal/
1. WAL（Write Ahead Logging）って何？
その名の通り、<font color="red">データの変更をディスクに書き込む前には一旦ログに記録しましょう</font>という取り決めのことです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/86061ab3-5ff3-ddaa-7de0-aad70102e351.png)
PostgreSQLにおいてWALが保存される領域は「WALバッファ」「WALファイル」と分かれています。前者がメモリ領域、後者がディスク領域です。
WALバッファがディスクに書き込まれるトリガーとしては、以下の二つがあります。
    - ユーザが(BEGIN TRANSACTIONの後)COMMITコマンドを実行する
    - WALバッファサイズが規定量を超える(設定値:wal_buffers)を超える

2. なぜデータの変更ごとにディスクに直接書き込まないのか？
    - データの変更ごとにディスクに直接書き込むと、<font color="red">ランダム</font>なディスクアクセスが増え、パフォーマンスが大幅に低下します。
    - ディスクへの書き込みは時間がかかり、書き込んでいる途中でデータベースがクラッシュしてしまう場合、データが不完全な状態で残るため、クラッシュリカバリが難しくなります。つまり、トランザクションのACID特性の一つである一貫性（Consistency）が保証できなくなります。
3. 書き込んだその履歴をログとして吐くなら、なぜ"書き込み"=>"ログ吐く"の順番じゃないの？どうしてログを先に出力するのか？
WALは単なる書き込んだその履歴を記録するのもではなく、トランザクションの原子性を保証する仕組みでもありますから。
トランザクションの途中でエラーが発生した時、ロールバックまたはロールフォワードによりDB状態の復旧が可能かと思います。
この時テーブルの実体であるデータファイルは中途半端な状態で止まっていることが想定されるため、別途復旧の手掛かりが必要です。


# 3. PostgreSQLストレージエンジン
MySqlではinnodb, myisam, memeryなど複数のstorage engineがある。

https://qiita.com/dennis_wang/items/52d025ce176cf1a6082d#1-mysql%E3%81%AE%E5%85%A8%E4%BD%93%E7%9A%84%E3%81%AA%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/f78ff980-8496-d056-a6cb-d436abd09a18.png)

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

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

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


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

https://qiita.com/dennis_wang/items/52d025ce176cf1a6082d#5--redo-log%E3%81%A8binlog%E3%81%AE%E9%81%95%E3%81%84

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

## 2. WAL (Write-Ahead Log)の役割:
1. クラッシュリカバリ: MySQLのredo logに相当する機能を持ち、トランザクションがデータベースに適用される前に、まずWALにログが書き込まれます。これにより、クラッシュが発生した場合でも、ログをリプレイすることでデータの一貫性を保ちます。
2. レプリケーション: 詳細は後ほど説明
3. PITR（Point-In-Time Recovery）: MySQLのbinlogと同様に、WALはレプリケーションやポイントインタイムリカバリにも使用されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/a4e5ce04-7d0e-18a3-c657-42935027e5de.png)



:::note info
MySQLのようにbinlogとredo logの二種類の異なるログを利用さぜるえないが、postgresqlでは両方の役割を果たす統合されたログが利用できる根本的な原因は何でしょうか？
- MySQLは様々なストレージエンジン（InnoDB, MyISAMなど）をサポートしており、それぞれが異なるログメカニズムを持つため、ログの分離が必要です。
- そもそもbinlogはストレージエンジンの機能でもなく、server layerで実装されて、すべてのストレージエンジンに共有されるログ機能。そしてトランザクションをサポートさせるために、InnoDBが後から追加された。そしてInnoDBではすでにクラッシュリカバリサポートするredo log機能があった。<font color="red">結果的にredo logとbinlogが分離されるアーキテクチャになったが、分離されることが別に最適なアーキテクチャではるというわけではありません</font>
- 更にいうと、<font color="red">MySQLはトランザクションログに相当するファイルが2種類ある点は、若干ユニークなアーキテクチャであり、一般的ではありません。 Oracleでは`redo` logと呼ばれる一つのログしかありませんし、PostgreSQLでは`WAL` logに統一される</font>。
- PostgreSQLは、統一されたストレージエンジンを採用しているため、WALに一本化することで設計の一貫性を保つことができます。このため、WALはトランザクションの整合性を保ちながら、同時にクラッシュリカバリやレプリケーションをサポート
:::
https://cosol.jp/techdb/2020/05/backup_oracle_mysql_postgresql/

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/96964dbd-dbe8-9250-bbc4-ae2e4e84835e.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/cd8e7218-47cc-3213-1525-dadda8c34ad7.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/ca0148ed-7749-3b4f-e70f-b20961765383.png)


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

- WALログの出力サンプル
    ```shell
    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は、データベースのブロックやページに対する変更を物理的に記録するため、<font color="red">物理ログ</font>です。

https://www.kimullaa.com/posts/201910271500/#wal-%E3%81%AB%E3%81%AF%E4%BD%95%E3%82%92%E6%9B%B8%E3%81%8F%E3%81%AE

# 5. レプリケーション
PostgreSQLは、バージョン9.0からレプリケーション機能として「ストリーミングレプリケーション」をサポートしていますが、 バージョン10から新たに「ロジカルレプリケーション」が追加されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/b34f3144-1717-ffe1-0789-87b7414772a9.png)

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

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

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

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

#### サンプルを通じた流れ：
1. パブリッシャー側で新しいデータが挿入される:
    ```sql
    INSERT INTO products (name, price) VALUES ('Apple', 100);
    ```
2. 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
    ```
3. 論理的な解釈
論理レプリケーションでは、WALのこのような低レベルの記録を「論理的に解釈」し、高レベルのSQL操作として扱います。たとえば、上記の物理的な変更を以下のように解釈します。
    - 操作: INSERT
    - テーブル: products
    - 挿入されたデータ: {id: 1, name: 'Apple', price: 100}
4. サブスクライバーにデータ転送:
パブリッシャー側側で実行されたSQL文そのものは送信されません。代わりに、SQL文の結果として生じたデータ変更の情報がサブスクライバーに送信されます。
5. サブスクライバー側でデータが反映される:
サブスクライバー側では、このイベントに基づいて、同じデータ変更が再現されます。
パブリッシャー側で実行されたのと同じSQL文ではなく、その内容に基づいて生成された変更イベントです。　<font color="red">MySQLのbinlogではformatがROWである時の動きに似ています</font>。
:::

下記のような利用例が考えられます。
- 異なるメジャーバージョンのPostgreSQL間でレプリケーション⇒<font color="red">つまりメジャーバージョンのバージョンアップ</font>
- 異なるOS（例えばLinuxからWindows）のPostgreSQL間でレプリケーション(<font color="red">オンプレからクラウドや異なるクラウド間のデータベース移行</font>)
- 分析目的で必要なデータを複数のデータベースから集めて、一つのデータベース(例えば専用のdata warehouse)に統合する
- 複数のデータベース間で、特定のテーブルのみを共有する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/f521103f-1c26-a11d-6f31-9a866d87dfb2.png)


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

https://oss-db.jp/dojo/dojo_column_01

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

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

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

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html

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

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

https://dev.classmethod.jp/articles/rds_postgresql_wal_level/