Быстрая массовая вставка с помощью on_conflict_do_nothing в sqlalchemy с использованием базы данных postgresql ⇐ Python

[Anonymous]

Версия sqlalchemy: 2.0.36
Версия postgresql из образа докера: postgres:15-alpine
Сначала я использовал psycopg2 в Python для создания вставьте вот так:

Код: Выделить всё

sql = """INSERT INTO ego_pose(token, translation, rotation, timestamp)
VALUES %s ON CONFLICT DO NOTHING;"""
with conn.cursor() as cur:
sql_args_batch = [
(
ego_pose["token"],
ego_pose["translation"],
ego_pose["rotation"],
ego_pose["timestamp"],
)
for ego_pose in ego_poses
]
execute_values(cur, sql, sql_args_batch)

Я хотел перейти на sqlalchemy и создал класс EgoPose и эту вставку из sqlalchemy.dialects.postgresql:

Код: Выделить всё

 session.execute(Insert(EgoPose).on_conflict_do_nothing(), dataset.ego_pose) # dataset.ego_pose is list of ego_pose dictionary

К сожалению, я хочу вставить тысячи строк, а второй вариант занимает гораздо больше времени. Что я делаю не так?
Обновление 1: дополнительная информация

• Количество строк для вставки: 2631083.
• Старая таблица создана:

Код: Выделить всё

CREATE TABLE IF NOT EXISTS ego_pose (
token UUID NOT NULL DEFAULT uuid_generate_v4(),
translation NUMERIC(15, 10) [3] NOT NULL,
rotation NUMERIC(15, 10) [4] NOT NULL,
timestamp BIGINT NOT NULL,
PRIMARY KEY (token)
);

• Это заняло 2 минуты 19 секунд
• С sqlalchemy это выглядит так:

Код: Выделить всё

class EgoPose(Base):
__tablename__ = "ego_pose"
token: Mapped[str] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, nullable=False)
translation: Mapped[List[float]] = mapped_column(ARRAY(Numeric(20, 10)), nullable=False)
rotation: Mapped[List[float]] = mapped_column(ARRAY(Numeric(20, 10)), nullable=False)
timestamp: Mapped[int] = mapped_column(BigInteger, nullable=False)

def __repr__(self) -> str:
return f""

• Это заняло 3 минуты 16 секунд
• некоторые журналы:

Код: Выделить всё

2024-11-28 09:21:55,262 INFO sqlalchemy.engine.Engine [no key 12.57689s (insertmanyvalues) 1/26311 (unordered)] {'token__0': 'bddd80ae33ec4e32b27fdb3c1160a30e', 'timestamp__0': 1531883530440378, 'rotation__0': [-0.7504501527141022, -0.0076295847961364415, 0.00847103369020136, -0.6608287216181199], 'translation__0': [1010.1273947164545, 610.7727090350685, 0.0], 'token__1': '90df03ad4710427aabb5f88fe049df2e', 'timestamp__1': 1531883530510442, 'rotation__1': [-0.743742073537717, -0.007726799292098994, 0.008375154888041067, -0.6683695694771852], 'translation__1': [1010.1677992044663, 611.0752573625425, 0.0], 'token__2': '28e6129458c745dbbcbdbb3329e91575', 'timestamp__2': 1531883530585295, 'rotation__2': [-0.7360977621773152, -0.007751210095172378, .....
2024-11-28 09:21:55,385 INFO sqlalchemy.engine.Engine INSERT INTO ego_pose (token, translation, rotation, timestamp) VALUES (%(token__0)s::UUID, %(translation__0)s::NUMERIC(20, 10)[], %(rotation__0)s::NUMERIC(20, 10)[], %(timestamp__0)s), (%(token__1)s::UUID, %(translation__1)s::NUMERIC(20, 10)[], %(rot ... 11601 characters truncated ... s::NUMERIC(20, 10)[], %(rotation__99)s::NUMERIC(20, 10)[], %(timestamp__99)s) ON CONFLICT DO NOTHING
2024-11-28 09:21:55,385 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/26311 (unordered)] {'...

• После запуска новых тестов я думаю, что все в порядке, как есть. Я просто разочаровался после 2 сеансов отладки по 10 часов, но в конечном итоге самое трудоемкое занятие было в разных таблицах и не имело никакого отношения к sqlalchemy.
• Я тестировал разные размеры параметра Insertmanyvalues_page_size, и количество значений Insert_many_values ​​менялось, но я не заметил разницы во времени.
• dataset.ego_pose просто предоставляет список словарей для вставки, как и в первом примере, за время O(1).
• Думаю, после всего этого я возьму сегодня отпуск.
• Хочу поблагодарить в комментариях людей, которые нашли время проводят свои эксперименты.
• Вопрос остается, если вы можно получить еще быстрее. Примерно на 40 % медленнее — это нормально, если это занимает несколько минут, но, возможно, я еще что-то могу сделать.

Подробнее здесь: https://stackoverflow.com/questions/792 ... resql-data