[Python] psycopg2를 사용한 unnest insert와 execute_values 속도 비교

psycopg2 2.7 버전 이후부터 execute_values 함수가 추가되었습니다. https://www.psycopg.org/docs/extras.html#fast-execution-helpers

대량 insert를 하는 방법은 여러 방법이 있지만 copy문을 사용하지 않고 가장 빠른 방법은 https://brownbears.tistory.com/297 에서 설명한 것과 같이 unnest를 사용해 insert 하는 방법이 가장 빠릅니다.

그럼 여기서 추가된 execute_values 함수와 비교를 진행해 어떤 것이 가장 빠른지 비교를 해봅니다.

예시

테이블은 name::text, id::bigint 로 설정하였습니다.

100건 insert

import psycopg2
import psycopg2.extras
import timeit
import uuid

count = 100

conn = psycopg2.connect(host='127.0.0.1', dbname='postgres', user='postgres', port=5432, password='')
cur = conn.cursor()

bulk_data = [[str(uuid.uuid4()), i] for i in range(count)]
unnest_name = [str(uuid.uuid4()) for _ in range(count)]
unnest_id = [i for i in range(count)]

def bulk_insert():
    cur.executemany("INSERT INTO public.bulk_insert_test (name, id) VALUES (%s, %s)", bulk_data)
    conn.commit()

def execute_value():
    psycopg2.extras.execute_values(cur, "INSERT INTO public.execute_values_test (name, id) VALUES %s", bulk_data, page_size=100000)
    conn.commit()

def unnest():
    query = """
    INSERT INTO public.unnest_test (name, id) select unnest(%(unnest_name)s), unnest(%(unnest_id)s)
    """
    cur.execute(query, {'unnest_name': unnest_name, 'unnest_id': unnest_id})
    conn.commit()

t = timeit.repeat('bulk_insert()', setup='from __main__ import bulk_insert', number=1, repeat=10)
print('executemany: ', sum(t) / 10)
t1 = timeit.repeat('execute_value()', setup='from __main__ import execute_value', number=1, repeat=10)
print('execute_values: ', sum(t1) / 10)
t2 = timeit.repeat('unnest()', setup='from __main__ import unnest', number=1, repeat=10)
print('unnest insert: ', sum(t2) / 10)

executemany:  0.011797215000000003
execute_values:  0.0013568470999999916
unnest insert:  0.0010179770000000089

데이터 100건은 수가 적어서 크게 차이가 나지 않습니다.

10,000건 insert

위 코드에서 10000건으로 늘려 테스트를 해봅니다.

executemany:  0.9089289675999999
execute_values:  0.09989987799999991
unnest insert:  0.05811045869999969

여기서부터 executemany가 현저히 느려지는 것을 볼 수 있습니다. 케이스를 많이 돌려봐야 확실하겠지만 unnest insert가 execute_values보다 빠른 것을 볼 수 있습니다.

100,000건 insert

executemany는 언제 끝날지 모르니 빼고 테스트를 진행했습니다.

execute_values:  1.0240389441999995
unnest insert:  0.6452484961000001

1,000,000 insert

executemany는 언제 끝날지 모르니 빼고 테스트를 진행했습니다.

execute_values:  9.774052778900002
unnest insert:  6.444013160800007

결론

unnest list는 입력할 데이터를 리스트로 변환해야 합니다 (물론 execute_values에서 요구하는 형태가 아니라면 동일하게 변환해야 함). 위 테스트는 순수하게 insert - commit까지의 속도를 비교했습니다. 데이터 변환 부분은 빠져있으므로 실 사용을 할 때는 변환 비용까지 고려하여 사용하는 것이 좋습니다.