Aurora PostgreSQL性能検証（簡易ETLケース）

### 本記事の目的

[Amazon Athena 性能検証（簡易ETLケース）](https://qiita.com/thashi/items/cbbc33b03608be2da2d9)

この記事と同データ、同ロジックを用いてAurora PostgreSQLで性能評価するのが目的。


### 検証の前提


Athena検証で利用したテーブルと同じテーブル。
データは1000万件で合計サイズは約700MB
```
CREATE TABLE test6
(
  col1        VARCHAR(30),
  col2        VARCHAR(30),
  col3        VARCHAR(30),
  col4        VARCHAR(30),
  col5        VARCHAR(30),
  col6        VARCHAR(30),
  col7        VARCHAR(30)
);
```

Athena検証で利用したLambda UDFと同じPL/pgSQLは次の通り
```
drop function if exists dateconvert(text);

CREATE OR REPLACE FUNCTION dateconvert(date_text text)
RETURNS TEXT AS $$
DECLARE
  parsed_date text;
  regex_pattern text;
  BEGIN
    regex_pattern := '^\d{4}-\d{2}-\d{2}$';
    IF date_text IS NULL OR date_text = '' OR length(date_text) != 8 THEN
      RETURN '0001-01-01';
    ELSEIF date_text = '99999999' THEN
      RETURN '9999-12-31';
    ELSE
      parsed_date := concat(substring(date_text from 1 for 4), '-', substring(date_text from 5 for 2), '-', substring(date_text from 7 for 2));
        IF NOT(parsed_date ~ regex_pattern) THEN
          RETURN '0001-01-01';
        ELSIF isDate(parsed_date, 'YYYY-MM-DD') THEN
          RETURN parsed_date;
        ELSE
          RETURN '0001-01-01';
        END IF;
    END IF;
  END;
$$ LANGUAGE plpgsql;
```
```
drop function if exists isDate(text, text);

create or replace function isDate(date_text text, format text)
returns boolean as $$
declare
  parsed_date date;
begin
  begin
    parsed_date := to_date(date_text, format);
  exception
    when others then
      return false;
    end;
    return true;
end;
$$ language plpgsql;
```

CTAS処理
基本パターンは下記の通りでテストケースによってselect文を入れ替えて実行している。
```
#!/bin/bash

su postgres <<-EOF
psql -d test -U postgres -c "drop table test6_converted"
EOF

su postgres <<-EOF
psql -d test -U postgres -c "create table test6_converted as select trim(both from col1) as col1, dateconvert(col2) as col2, col3, col4, trim(both from col5) as col5, concat(lpad(col6, 7, '0'), lpad(col7,7,'0')) as col6 from test6"
EOF
```


### 検証結果
AuroraはAthenaと比較して一桁遅い結果となった。
UDF呼び出し回数は一レコード一回。
後述するがUDF実行回数によってこの差は大きく広がる（＝Auroraがさらに性能劣化する。）
本検証ではLimitlessの想定効果（実行時間が1/16になる）がなかった。
ACUのメモリサイズ2GiBを超えるデータを対象にACUごとの並列処理となっているものと想像している。後日検証予定。

|サービス|条件|処理時間|備考|
|---|---|---|---|
|Athena||17秒|Athenaの場合S3import/export処理不要|
|Java(XFS)|C5.4xlarge|24秒|S3処理無し（追い風参考記録）|
|Aurora Serverless v2|0-256ACU|201秒|S3import/export合計が約40秒|
|Aurora Limitless|16-64ACU|202秒|※S3処理無し（追い風参考記録）|
|Glue PySpark|2DPU|197秒|UDF実行回数による性能劣化はない。|

※記事執筆時点ではLimitlessでのS3import/export設定を実行すると処理が終わらない不具合に遭遇した。関連するリソースを削除することで処理が元に戻ったが何回トライしてもServerlessと同じ設定はできなかった。この件についてはAWS社のバグfixを待ちたい。下手するとLimitless実行中のまま何もできない時間を過ごすことになり、割高なLimitless従量課金が無駄になるのでS3連携機能は避けたほうがよい。

### PL/pgSQLの呼び出し回数観点での性能比較
Athenaの場合はUDF実行回数によって性能劣後しないがPL/pgSQLの性能劣化は下記の通り大きい。この検証は手元のノートPCでの結果のため前述の実行時間とは比較できない。
AthenaのUDFが性能劣化しない理由はAthena内でのLambda最適化による。Athenaでは一レコード単位で一回Lambdaを呼び出すのではなくまとまったレコード単位で関数実行するとドキュメントに明記されている。
|PL/pgSQL実行回数|処理時間|
|---|---|
|0|7秒|
|1|83秒|
|2|165秒|
|3|234秒|

### まとめ
AWSからでているETLに関する記事で、Athenaを簡易ETLで使えるというものがある。本記事で記載したとおりS3を中心とするデータレイク環境においてAthenaは最速のツールとなる。
AWSからはAuroraを簡易ETLに使えるとの主張はないが、CTASならAuroraでもいけるのではと思うエンジニアは多いと想像できる。その際に注意したいのはCTAS中でのPL/pgSQL多用。本記事で検証した通り、性能面でアンチパターンとなることだけは覚えておきたい。

### おまけ
Auroraの実行時間の悪さを受けてOracle最強説がもちあがったが、PL/SQLも同様に性能がでなかった。

実行したOracleのPL/SQLは下記の通り

```
CREATE OR REPLACE FUNCTION dateconvert(date_text varchar2) return varchar2
IS
  parsed_date varchar2(30);

BEGIN
  IF date_text IS NULL OR date_text = '' OR length(date_text) != 8 THEN
    RETURN '0001-01-01';
  ELSIF date_text = '99999999' THEN
    RETURN '9999-12-31';
  ELSE
    parsed_date := SUBSTR(date_text,1,4) || '-' || SUBSTR(date_text,5, 2) || '-' || SUBSTR(date_text, 7, 2);
      IF isDate(parsed_date, 'YYYY-MM-DD') THEN
        RETURN parsed_date;
      ELSE
        RETURN '0001-01-01';    
      END IF;


  END IF;
END;
/


create or replace function isDate(date_text varchar2, format varchar2) RETURN boolean
IS
  parsed_date date;
begin
  begin
    parsed_date := to_date(date_text, format);
  exception
    when others then
      return false;
    end;
    return true;
end;
/
```
※このPL/SQLを実際に書いて思ったのはOracleからPostgreSQLへの移行の難易度。PL/SQLとPL/pgSQLの違いは地味に大きく賢い移行ツールがないと簡単に移行できないと予想。PostgreSQL開発者がまじめにOracle互換を意識して開発していればよかったが、残念な結果である。


|製品|自前function実行回数|処理時間|
|---|---|---|
|Oracle23ai|1|113秒|
|PostgreSQL|1|106秒|

標準SQL関数は製品側で最適化されており、性能への影響は軽微。下記はPostgreSQLで自前function実行なしでの計測結果
|標準のSQL関数|実行回数|処理時間|
|---|---|---|
|trim|0|9.3秒|
|trim|1|9.3秒|
|trim|2|9.7秒|
|trim|3|11秒|