Эффективная загрузка данных в PostgreSQL с помощью Python

В этот раз на курсе ученица столкнулась с задачей загрузки своего датасета в базу данных PostgreSQL. Все бы ничего, это типичная задача, через которую проходят ученики, но здесь повлиял объем исходных данных. На входе был json файл объемом 150Мб, который если преобразовать в формат таблицы, то получится 8.9 млн строк. Не Big Data, но и не маленькая экселька на самом деле.

Мы сравнили разные методы загрузки такого датасета в одну и ту же базу данных в пустые таблицы и вот что получилось:

Самый быстрый метод - COPY (выполнился за 25 секунд). Если вкратце описать его особенность, то этот механизм специально предназначен для массовой вставки данных внутри одного запроса к серверу базы данных, в отличие от INSERT команды, где каждая вставка будет обрабатываться сервером как отдельный запрос (или группы запросов) и проходить дополнительно разбор sql команды через планировщик базы данных, что также ест ресурсы.

Для COPY предварительно пришлось преобразовать датафрейм в .csv файл, чего например не нужно делать с удобным методом to_sql. Кстати, для всех остальных методов вставки которые помню нужно отдельно сначала создать таблицу с помощью CREATE TABLE, потом уже выполнять вставку. Метод to_sql сам создает таблицу, если ее в базе данных не существует.

Но часто без INSERT не обойтись, когда например важно вставлять данные часто и небольшими частями, не сохраняя их при этом предварительно в виде файла.

Результаты разных способом по длительности выполнения можете оценить на скрине. 👍 если возьмете себе на заметку