回答編集履歴
2
INSERT SELECT で一括処理する場合の性能の参考URLを追加
answer
CHANGED
|
@@ -13,6 +13,7 @@
|
|
|
13
13
|
- 一括処理する塊は大きければ大きいほど有利である
|
|
14
14
|
|
|
15
15
|
というのがあります。つまり、COPY とか INSERT SELECT は普通の INSERT 文を実行するより数倍早いはずです。正確な数値で示すことができませんが、[大量データ取込のいろいろ PostgreSQL編](http://www.techscore.com/blog/2012/12/14/%E5%A4%A7%E9%87%8F%E3%83%87%E3%83%BC%E3%82%BF%E5%8F%96%E8%BE%BC%E3%81%AE%E3%81%84%E3%82%8D%E3%81%84%E3%82%8D-postgresql%E7%B7%A8/)によると、数十倍の差があります。
|
|
16
|
+
複数回 INSERT VS INSERT SELECT のほうは、ちょうどいいのがぱっと見つけられなかったのですが、[カーソルのループでINSERT100万とSELECT INSERT100万の速度比較してみた](http://kagamihoge.hatenablog.com/entry/20120603/1338727402)に ORACLE で10倍以上差がついている記事がありました。
|
|
16
17
|
|
|
17
18
|
具体的には、テーブルへの登録処理を2段のパイプラインに分けます。1段目の処理でデータを加工処理せずに受信したままの形でデータベースに登録します。この登録処理では速度を最大にするために TSV ファイル形式からのCOPY命令を利用します。今回の質問の 1. の考え方に近いです。
|
|
18
19
|
|
1
説明を詳細化し、テーブルを2個に分ける理由の説明を追加しました
answer
CHANGED
|
@@ -7,17 +7,30 @@
|
|
|
7
7
|
>InsertやUpdate時にWhere文等で条件を追加しているため、 COPYコマンドは使用できません
|
|
8
8
|
|
|
9
9
|
とおっしゃってますが、実装されようとしているアプリの性能要件から、 COPY でのインポートが必須であると思われます。
|
|
10
|
-
それで、生データのテーブルと完成テーブルを分けてはどうでしょうか。
|
|
10
|
+
それで、生データのテーブルと完成テーブルを分けてはどうでしょうか。分ける前提として、
|
|
11
11
|
|
|
12
|
-
-
|
|
12
|
+
- PostgreSQL はデータを一括して処理するほうが1件ずつ処理するより数倍早い
|
|
13
|
-
- 生データテーブルには[シリアルデータ型](https://www.postgresql.jp/document/7.3/user/datatype.html#DATATYPE-SERIAL)で通番をふる
|
|
14
|
-
-
|
|
13
|
+
- 一括処理する塊は大きければ大きいほど有利である
|
|
15
|
-
- 一定時間ごとのバッチ処理で生データテーブルの内容から完成形テーブルに INSERT 文で転記する（WHERE や JOIN のロジックはこの転記時に実行する:参考 [表をSELECTして別の表へINSERTする](http://www.projectgroup.info/tips/SQLServer/SQL/SQL000004.html)：今回の質問の2.に近い)
|
|
16
|
-
- 転記時にどこまで転記したかを別テーブルに記録しておく（前述の通番を参照）
|
|
17
|
-
- 転記が終わった範囲のデータを生データテーブルから削除する（性能を上げるためにここを[テーブルパーティショニング](https://www.postgresql.jp/document/9.4/html/ddl-partitioning.html)を使って DELETE ではなく DROP で掃除するようにする必要があります）
|
|
18
14
|
|
|
15
|
+
というのがあります。つまり、COPY とか INSERT SELECT は普通の INSERT 文を実行するより数倍早いはずです。正確な数値で示すことができませんが、[大量データ取込のいろいろ PostgreSQL編](http://www.techscore.com/blog/2012/12/14/%E5%A4%A7%E9%87%8F%E3%83%87%E3%83%BC%E3%82%BF%E5%8F%96%E8%BE%BC%E3%81%AE%E3%81%84%E3%82%8D%E3%81%84%E3%82%8D-postgresql%E7%B7%A8/)によると、数十倍の差があります。
|
|
16
|
+
|
|
17
|
+
具体的には、テーブルへの登録処理を2段のパイプラインに分けます。1段目の処理でデータを加工処理せずに受信したままの形でデータベースに登録します。この登録処理では速度を最大にするために TSV ファイル形式からのCOPY命令を利用します。今回の質問の 1. の考え方に近いです。
|
|
18
|
+
|
|
19
|
+
0. データを受信したプログラムで TSVファイルを生成
|
|
20
|
+
0. 生成したファイルから COPY で生データテーブルにインポートする
|
|
21
|
+
|
|
22
|
+
このとき、後の処理のために生データテーブルには[シリアルデータ型](https://www.postgresql.jp/document/7.3/user/datatype.html#DATATYPE-SERIAL)で通番をふっておきます。また、インポートを高速化するため、生データテーブルにはインデックスをはらないほうが良いでしょう。
|
|
23
|
+
|
|
24
|
+
2段目の処理で、生テーブルに登録されているデータを完成形テーブルに転記します。今回の質問の2.の考え方に近いです。
|
|
25
|
+
|
|
26
|
+
0. 一定時間ごとのバッチ処理で生データテーブルの内容から完成形テーブルに INSERT 文で転記する（WHERE, JOIN,GROUP BY などの加工処理はこの転記時に実行する:参考 [表をSELECTして別の表へINSERTする](http://www.projectgroup.info/tips/SQLServer/SQL/SQL000004.html))
|
|
27
|
+
0. 転記時に生テーブルどこまで転記したかを別テーブルに記録しておく（前述の通番を参照）
|
|
28
|
+
0. 転記が終わった範囲のデータを生データテーブルから削除する（性能を上げるためにここを[テーブルパーティショニング](https://www.postgresql.jp/document/9.4/html/ddl-partitioning.html)を使って DELETE ではなく DROP で掃除するようにする必要があります）
|
|
29
|
+
|
|
19
30
|
で、上記の設計が可能であれば、今回の質問については、 TSV ファイルで生データを出力し、C++ のAPIで COPY 文を実行するというのが最適解になるかと思います。
|
|
20
31
|
|
|
32
|
+
この方法ですと、1段目の処理と2段めの処理は同期する必要がありませんので、以下のメリットが得られると思います。
|
|
21
|
-
|
|
33
|
+
- 生データテーブルの読み込みのほうはかなりの性能が出ますので、取りこぼしの心配の方がなくなる
|
|
34
|
+
- 完成形テーブルへの転記の INSERT 文の性能がボトルネックになってくると思いますが、受信スループットにピークがあるような場合、ピーク時に遅れが出ても後で暇になったときに追いつかせることができます（単純に一定時間ごとに一定量処理するのではなく、遅れが出ている場合は処理量が増やすようにロジックを組む必要があります）
|
|
22
35
|
|
|
23
36
|
あと、ステマみたいで恐縮ですが、[AWS Redshift](https://aws.amazon.com/jp/redshift/?sc_channel=PS&sc_campaign=acquisition_JP&sc_publisher=google&sc_medium=redshift_b&sc_content=redshift_e&sc_detail=aws%20redshift&sc_category=redshift&sc_segment=73850134825&sc_matchtype=e&sc_country=JP&sc_brand=brand&ef_id=VOGutAAAAQpcCiuk:20161115094316:s)はこういう要件に強いです。
|