Как быстрее записать в базу PosgresQl?

Question

[kaxa3201]

Имеется 700 000 записей. есть скрипт который парсит csv файл и записывает данные в базу.
Запись идет очень долго примерно 200 записей в секунду. Подскажите можно ли как то это дело ускорить?

foreach ($ipPool as $ip) {
                    $date = new \DateTime($csv[5]);
                     Resource::updateOrCreate([
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                    ], [
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                        'version_date' => $updatedDate
                    ]);
                }

Comments

[JhaoDa]

Не хочешь показать, как ты это делаешь сейчас, чтобы нам не гадать на кофейной гуще?

[kaxa3201]

JhaoDa,

foreach ($ipPool as $ip) {
                    $date = new \DateTime($csv[5]);
                     Resource::updateOrCreate([
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                    ], [
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                        'version_date' => $updatedDate
                    ]);
                }

[JhaoDa]

kaxa3201, тебе не пришла в голову простая мысль добавить это в вопрос, чтобы все видели, а не персонально мне в каментах показывать?

[kaxa3201]

JhaoDa, у тебя есть варианты ответа на мой вопрос?

[JhaoDa]

kaxa3201, тебе уже дали два абсолютно правильных ответа.

Answer 1

[Melkij]

Быстрее всего - COPY запросом.
При том, сразу из csv файла, локального к СУБД.

Через приложение - сильно лучше тоже COPY. В PDO интерфейс для COPY, впрочем, ужасен.
Хуже - относительно большими insert
ещё хуже - кучей запросов в транзакции
кучей запросов вне общей общей транзакции - очевидно будет очень медленно.

Answer 2

[Konata Izumi]

Надеюсь, не циклом по одной модели за раз пишется.
Делайте batch insert штук по 1000-10000 (оптимальное кол-во подбирается опытным путем).

Comments

[kaxa3201]

Konata Izumi подскажите как правильно

foreach ($ipPool as $ip) {
                    $date = new \DateTime($csv[5]);
                     Resource::updateOrCreate([
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                    ], [
                        'ip'           => trim($ip),
                        'domain'       => $csv[1],
                        'url'          => $csv[2],
                        'organization' => $csv[3],
                        'number'       => $csv[4],
                        'date'         => $date,
                        'version_date' => $updatedDate
                    ]);
                }

[Konata Izumi]

kaxa3201, То, о чем я говорил:
По одной записи это будет происходить очень долго.
Гуглите batch insert.

[kaxa3201]

Konata Izumi, с insert все понятно, а как быть с updateOrCreate ?

[Konata Izumi]

kaxa3201, работу с batch update в postgres не трогал - тут смотрите сами в доку/примеры.
В целом план тот же: за каждый запрос к бд обновлять как можно больше данных (слишком большие запросы тоже не хорошо - будут тормозить). А как эти данные подготовить - зависит от реализации.
Как минимум разделить update и create в приведенном коде. Всё, что create - в batch insert. Всё, что update - в batch update.

В этом коде вы обновляете только дату - такое можно и через sql методы реализовать (там есть метод получения текущего времени).

[Melkij]

insert into ... values (...),... on conflict ... do update set field1 = excluded.field1
https://www.postgresql.org/docs/current/sql-insert.html
Если вы на 9.4 или вовсе на EOL чём-то - обновляйтесь до поддерживаемых версий. Последняя версия postgresql не умеющая on conflict (т.е. 9.4) снимается с поддержки чуть больше чем через полгода.