Ускорение вставки значений в базу данных

Question

Делаю SQL запрос на вставку. Как его можно ускорить?

for i, row in enumerate(df.iterrows()):
    values = ','.join(transform_types(row[1].values.tolist()))
    try:
        cursor.execute(f'INSERT INTO telemetry ({columns}) VALUES({values});')
    except Exception as e:
        self.__conn.rollback()
        print(list(zip(columns.split(','), values.split(','))), e)
    self.__conn.commit()

Answer 1

Вот вариант

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Можно ещё пакетом

Пример из ссылки

cursor.executemany("""
            INSERT INTO staging_beers VALUES (
                %(id)s,
                %(name)s,
                %(tagline)s,
                %(first_brewed)s,
                %(description)s,
                %(image_url)s,
                %(abv)s,
                %(ibu)s,
                %(target_fg)s,
                %(target_og)s,
                %(ebc)s,
                %(srm)s,
                %(ph)s,
                %(attenuation_level)s,
                %(brewers_tips)s,
                %(contributed_by)s,
                %(volume)s
            );
        """, all_beers)

Или пример с батчем

psycopg2.extras.execute_batch(cursor, """
            INSERT INTO staging_beers VALUES (
                %(id)s,
                %(name)s,
                %(tagline)s,
                %(first_brewed)s,
                %(description)s,
                %(image_url)s,
                %(abv)s,
                %(ibu)s,
                %(target_fg)s,
                %(target_og)s,
                %(ebc)s,
                %(srm)s,
                %(ph)s,
                %(attenuation_level)s,
                %(brewers_tips)s,
                %(contributed_by)s,
                %(volume)s
            );
        """, all_beers)

Answer 2

У вас тут сразу несколько проблем.

Первое, это комит после каждой записи. На HDD диске с 7200 оборотами, даже теоретический максимум - 120 транзакций в секунду, поэтому комит после каждой вставки очень плохо для производительности.

Дальше, нельзя использовать конструирование параметров в виде строки, т.е. речь о переменной values и ее подстановке {values}. Это во-первых, уязвимо к SQL-injection, а, во-вторых, плохо для производительности, так как не используются prepared statements.

Самый быстрый способ вставки это использовать команду postgres COPY. psycopg2 выставляет ее через функции copy_from и copy_expert. Там код посложнее чем наивная вставка или даже execute_batch, но того стоит. Я наблюдал ускорение и до того правильного кода в 100 раз после перехода на COPY.

Смысл в том, что используется низкоуровневый протокол постгрес для вставки, который хорошо оптимизирован как раз для чтения/записи большого количества данных.

Answer 3

Воспользуйтесь методом DataFrame.to_sql():

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
df.to_sql("telemetry", engine, if_exists="append")

БЛОГ НА HUSL

Ускорение вставки значений в базу данных

Ответы (3 шт):