Aurora PostgreSQL 11.4 互換版がひっそりと出ていたので試してみた

こちらは **[PostgreSQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/postgresql)** 17 日目のエントリです。

昨日（16 日目）は tom-sato さんでした。

 - **[PostgreSQL Anonymizer でデータの匿名化・マスキングをしてみた](https://qiita.com/tom-sato/items/0adbee0b79382945712e)**

実は、元々 Alibaba Cloud の POLARDB PostgreSQL 互換版で [oss_fdw 機能を試そうとした](https://qiita.com/hmatsu47/items/7af490cf526d7b19a6ec#%E6%AC%A1%E5%9B%9E%E3%81%AF%E4%BB%8A%E3%81%AE%E3%81%A8%E3%81%93%E3%82%8D%E6%9C%AA%E5%AE%9A%E3%81%A7%E3%81%99)のですが、残念ながら実装が追い付いていなかったようなので、今回は AWS Aurora に戻ってきました。

そして、当アドベントカレンダーの中で一番内容の薄いエントリとなります（このアドベントカレンダー作ったの誰だよ？←私だ！）。

# Aurora PostgreSQL 11.4 互換版

re:Invent 2019 開催直前の 2019/11 下旬にリリースされていました。

 - **[Amazon Aurora (PostgreSQL 互換) で PostgreSQL 11.4 のサポートを開始](https://aws.amazon.com/jp/about-aws/whats-new/2019/11/amazon-aurora-postgresql-compatibility-supports-postgresql-11-4/)**

今回は東京リージョン（ap-northeast-1）も同時リリースだったようです。

PostgreSQL 11 の新機能については、こちらにあるページのリンク先を参照してください。

 - **[PostgreSQL11の新機能](https://lets.postgresql.jp/documents/technical/11)（Let's Postgres）**

とはいえ、本家 PostgreSQL 11 は 2018/10 にリリース、次バージョンの PostgreSQL 12 も 2019/10 にリリースされているので、ほぼ 1 年遅れのタイミングとなります。

# 試す内容（１）：10.5 互換版登場時に試したパラレルスキャンを R5 インスタンスで

こちらの記事と同じ内容を実行してみます。

 - **[Aurora PostgreSQL 互換版（10.5）東京リージョン登場記念でパラレルスキャンを試す](https://qiita.com/hmatsu47/items/ffc7636e52a83722afae)**

ただし、Aurora は、2019/2 に R5 インスタンスをサポートしています。

※PostgreSQL 互換版の R5 インスタンスサポートが同時だったかどうは記憶にございません…T3 インスタンスは遅れてサポートされたようですが。

前回は R4 インスタンスで 10.5 互換版を試しましたが、今回は、R5 インスタンス（db.r5.large）で

 - 10.7 互換版
 - 11.4 互換版

を試します。

また、

 - **[Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編](https://qiita.com/hmatsu47/items/ec485766341c79bdce99)**

の結果（POLARDB PostgreSQL 11.2 互換版）も引用します。

## 結果

| インスタンスタイプ／バージョン | シーケンシャル 1 回目 | シーケンシャル 2 回目 | インデックス 1 回目 | インデックス 2 回目 |
|:-:|--:|--:|--:|--:|
| db.r4.large／Aurora 10.5 ※昨年 | 5,707.766 | 2,921.991 | 42,457.903 | 493.760 |
| 4C16GB／POLARDB 11.2 | 5,388.365 | 968.011 | 6,091.151 | 264.254 |
| db.r5.large／Aurora 10.7 | 4,405.818 | 2,462.651 | 29,931.351 | 439.518 |
| db.r5.large／Aurora 11.4 | 4,048.994 | 2,314.962 | 32,709.778 | 426.766 |

それぞれの 1 回目が共有バッファにデータが載っていない状態、2 回目が載っている状態です。

 - R5 インスタンスは R4 と比べて高速（特にストレージアクセス）
 - PostgreSQL 10 と 11 の間では、大きな差は無し
 - Aurora と POLARDB の差は CPU コア数とストレージ構成の違いによるもの？

なお、Aurora 10.7 と 11.4 で 11.4 のほうが結果が悪くなっていますが、何度か実行していると「外れインスタンス」を掴むことがあり、これが影響している可能性があります（時間の都合で少ない試行回数の平均を取っています）。

Aurora R5 と POLARDB の違いですが、

 - CPU Core は Aurora が 2 に対し POLARDB が 4（AWS での m5.xlarge に近い）
 - ストレージは Aurora が 3AZ-6Copy、POLARDB が 1AZ-3Copy。
 - 当該インスタンスのストレージ I/O 帯域は Aurora が 4.75Gbps に対して POLARDB が 4Gbps
   - 2019/12/16 現在、AWS の日本語マニュアルの情報は古いです（× 3.5Gbps →〇 4.75Gbps）

なので、

 - メモリ上のデータを使った処理は CPU Core 数の分 POLARDB 有利（パラレルスキャンの負荷など）
 - ランダムで細かいストレージアクセスがある場合も POLARDB 有利（別 AZ からの ACK を待たなくても良いから？）
 - シーケンシャルなストレージアクセスの場合は Aurora のほうが多少有利？（遅延よりもI/O 帯域？）

ということでしょうか。

# 試す内容（２）：パーティショニング

PostgreSQL 10 / 11 の新機能と言えば「宣言的パーティショニング」ですね（やや強引）。

というわけで、今回は、

 - 先のテストパターンのうち、インデックススキャンのほうのテーブル定義にパーティショニングを含める
 - 10 の宣言的パーティショニングでは PK が設定できないため number 列の PK を外す
 - 11 では親テーブルに PK を設定できるのでそちらの形で試す（PK の一部にパーティションキーを含める必要があるので`id`列を単独のインデックスではなく PK の 2 列目に含める）

で試してみます。

なお、完全に手抜きですが、`WHERE id < 10`の条件に合わせて`id`が`0`～`9`のパーティションと`10`以上のパーティションの 2 つに分けただけ、です。

※元記事に書いた通り、`id`というネーミングが完全に罠になっています（ごめんなさい）。

```sql:10用のテーブル定義
CREATE TABLE table_i (number SERIAL NOT NULL, id INT, value INT) PARTITION BY RANGE (id);
CREATE TABLE table_i_1 PARTITION OF table_i FOR VALUES FROM (0) TO (10);
CREATE TABLE table_i_2 PARTITION OF table_i FOR VALUES FROM (10) TO (100);
CREATE INDEX idx_id_1 ON table_i_1 (id);
CREATE INDEX idx_id_2 ON table_i_2 (id);
```

```sql:11用のテーブル定義
CREATE TABLE table_i (number SERIAL NOT NULL, id INT, value INT) PARTITION BY RANGE (id);
CREATE TABLE table_i_1 PARTITION OF table_i FOR VALUES FROM (0) TO (10);
CREATE TABLE table_i_2 PARTITION OF table_i FOR VALUES FROM (10) TO (100);
ALTER TABLE table_i ADD PRIMARY KEY(number, id);
```

ちなみに、パーティショニングで気になる`INSERT`の速度ですが、パーティショニング無しと比較して概ね 10 ～ 20% 程度余分に時間が掛かりました（ばらつきが大きいので値を掲示するのは控えます）。

## 結果

| バージョン／パーティショニング有無 | インデックス 1 回目 | インデックス 2 回目 |
|:-:|--:|--:|
| Aurora 10.7／無 | 29,931.351 | 439.518 |
| Aurora 10.7／有 | 540.669 | 331.789 |
| Aurora 11.4／無 | 32,709.778 | 426.766 |
| Aurora 11.4／有 | 539.339 | 355.797 |

パーティショニング無しの結果は（１）からの引用です。

 - 共有バッファに載っていないと劇的に高速化
   - `WHERE`句の条件と完全にマッチする形で分割したため
 - パーティショニング有りでも 10 と 11 の差はほぼ無し

でした。

Aurora の場合、テーブル空間を別ストレージに分離する手法は取れないため、10 に対する 11 のアドバンテージは性能向上ではなくて

 - パーティション間のデータ移動ができる
 - デフォルトパーティションを作ることができる
 - 外部キーなどの制約を使うことができる

あたりでしょうか。

# 余談

Aurora PostgreSQL 互換版、この↓機能のおかげで便利にはなったものの、「共有バッファにデータが載っていない」状態でテストすることが難しくなってしまいました（設定で OFF にしても「あれ？載ってるのでは？」という挙動が何度も…）。

 - **[[アップデート] 32秒でパフォーマンス回復！Aurora PostgreSQL でクラスタキャッシュ管理がサポートされました！ #AWSSummit](https://dev.classmethod.jp/cloud/aws/aurora-with-postgresql-supports-cluster-cache-management/)（Developers.IO）**

---

**[PostgreSQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/postgresql)** の明日（18 日目）は sira さんです。

 - **[PostgreSQL ソースコードリーディング](https://sira.dev/2019/12/17/postgresql-source-code-reading.html)**