PostgreSQL - 高速でINSERTしたい君へ

大量インサートの性能をあげよう

PostgresSQLに大量インサートする場合には、
複数レコードまとめてインサート用のユーティリティを使用しましょう。
あるいは、commit実行頻度を見直して、不要な処理コストを減らしましょう

複数パターンで実測して性能を比較します。
PythonでPostgreSQLにアクセスするライブラリのひとつpsycopg2では
複数件まとめてインサート用のユーティリティとして
execute_valuesという関数を使用します。
また、commitの実行単位を変更してcommitの実行間隔を変えて実測します。

確認準備

テスト用テーブル

テスト用DDL

CREATE TABLE test01
(key serial PRIMARY KEY,
 text_val text);

テスト用プログラム

簡単のため、実行件数やcommit回数は対話的な入力が行えるようにします。
できるだけ同じ範囲の時間で比較できるようにパラメータを作る部分を含めます。
実行前に都度、テーブルリセットするため、TRUNCATEとSEQUENCEオブジェクトの値初期化を行います。

execute_valuesは、デフォルト100行まで。それ以上を入力するときは、引数のpage_sizeに入力数以上の値を渡す必要があります。

テスト用プログラム

import psycopg2
from psycopg2.extras import execute_values
from time import time

_text = 'test' * 300

conn = psycopg2.connect('dbname=dbname host=localhost user=username password=password')
cur = conn.cursor()

# テーブルリセット
reset_query_tbl = "TRUNCATE TABLE test01;"
cur.execute(reset_query_tbl)
reset_query_seq = "SELECT setval ('test01_key_seq', 1, false);"
cur.execute(reset_query_seq)
count_query = "SELECT count(key) FROM test01;"
cur.execute(count_query)
(count,) = cur.fetchone()
conn.commit()
print('■実行前状態')
print('　データ件数: ', count)
print()

# テスト設定
print('■INSERT方法')
print('INSERT方法を以下から選択してください(0/1/2/3)')
print('　0: 1件ずつINSERT&commit.')
print('　1: 1件ずつINSERT&複数まとめてcommit.')
print('　2: 1件ずつINSERT&全件まとめてcommit.')
print('　3: まとめてINSERT&commit.')
test_type = int(input('　INSERT方法 >'))
test_cnt  = int(input('　INSERT件数 >'))
if test_type == 1:
    commit_cnt  = int(input('　COMMIT間隔 >'))

# テスト実行＆結果
print()
print('■実行結果')
if test_type==0:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)
        conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==1:
    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for i in range(test_cnt):
        cur.execute(query)
        if (i + 1) % commit_cnt == 0:
            conn.commit()

    conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==2:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)

    conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==3:

    query = "INSERT INTO test01 (text_val) VALUES %s;"
    st = time()

    params = []
    for _ in range(test_cnt):
        params += [(_text,)]

    ret = execute_values(cur, query, params, page_size=test_cnt)
    conn.commit()
    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

cur.execute(count_query)
(count,) = cur.fetchone()
print('　データ件数: ', count)

cur.close()
conn.close()

性能結果

結果は参考まで。当然、環境、マシンスペックに依存します。

• Windows10 64bit/論理プロセッサ数4/メモリ8GB
• PostgreSQL11
• PostgreSQLのコンフィグはデフォルト値。

INSERT方法	INSERT件数	COMMIT回数	処理時間(S)
1件ずつINSERT(execute) & 1件ずつCOMMIT.	100,000	100,000	40.9
1件ずつINSERT(execute) & 10件ずつCOMMIT.	100,000	10,000	25.4
1件ずつINSERT(execute) & 100件ずつCOMMIT.	100,000	1,000	24.1
1件ずつINSERT(execute) & 1,000件ずつCOMMIT.	100,000	100	27.2
1件ずつINSERT(execute) & 10,000ずつCOMMIT.	100,000	10	25.9
1件ずつINSERT(execute) & 全件まとめてCOMMIT.	100,000	1	24.4
まとめてINSERT(execute_values) & 全件まとめてCOMMIT.	100,000	1	8.8

結論

execute_values、速い。
できれば使いましょう。