ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [Python] psycopg2를 사용한 unnest insert와 execute_values 속도 비교
    언어/파이썬 & 장고 2021. 7. 10. 23:45

    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까지의 속도를 비교했습니다. 데이터 변환 부분은 빠져있으므로 실 사용을 할 때는 변환 비용까지 고려하여 사용하는 것이 좋습니다.

    댓글