Каков самый быстрый способ сделать массовую вставку в Postgres?

существует альтернатива использованию COPY, которая является синтаксисом многорядных значений, который поддерживает Postgres. Из документация:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

приведенный выше код вставляет две строки, но вы можете расширить его произвольно, пока не достигнете максимального количества подготовленных токенов оператора (это может быть $999, но я не уверен на 100% в этом). Иногда нельзя использовать копию, и это достойная замена для тех ситуаций.

один из способов ускорить процесс-явно выполнить несколько вставок или копий в транзакции (скажем, 1000). Поведение Postgres по умолчанию заключается в фиксации после каждого оператора, поэтому путем пакетной фиксации вы можете избежать некоторых накладных расходов. Как говорится в руководстве в ответе Даниэля, вам, возможно, придется отключить autocommit, чтобы это работало. Также обратите внимание на комментарий внизу, который предлагает увеличить размер wal_buffers до 16 МБ, также может помочь.

UNNEST функция с массивами может использоваться вместе с синтаксисом многорядных значений. Я думаю, что этот метод медленнее, чем использование COPY но мне это пригодится в работе с psycopg и python (python list перешло к cursor.execute становится pg ARRAY):

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (
    UNNEST(ARRAY[1, 2, 3]), 
    UNNEST(ARRAY[100, 200, 300]), 
    UNNEST(ARRAY['a', 'b', 'c'])
);

без VALUES использование подзапроса с дополнительной проверкой существования:

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
SELECT * FROM (
    SELECT UNNEST(ARRAY[1, 2, 3]), 
           UNNEST(ARRAY[100, 200, 300]), 
           UNNEST(ARRAY['a', 'b', 'c'])
) AS temptable
WHERE NOT EXISTS (
    SELECT 1 FROM tablename tt
    WHERE tt.fieldname1=temptable.fieldname1
);

тот же синтаксис для массового обновления:

UPDATE tablename
SET fieldname1=temptable.data
FROM (
    SELECT UNNEST(ARRAY[1,2]) AS id,
           UNNEST(ARRAY['a', 'b']) AS data
) AS temptable
WHERE tablename.id=temptable.id;

Это в основном зависит от (другой) активности в базе данных. Такие операции эффективно замораживают всю базу данных для других сеансов. Другим соображением является модель данных и наличие ограничений, триггеров и т. д.

мой первый подход всегда: создайте таблицу (temp) со структурой, подобной целевой таблице (создайте таблицу tmp как select * from target где 1=0), и начните с чтения файла в временную таблицу. Затем я проверяю, что можно проверить: дубликаты, ключи, которые уже существуют в целевой и др.

затем я просто делаю "do insert into target select * from tmp" или аналогичный.

Если это не удается или занимает слишком много времени, я прерываю его и рассматриваю другие методы (временное удаление индексов/ограничений и т. д.)

можно использовать COPY table TO ... WITH BINARY Это "несколько быстрее, чем текстовые и CSV форматы."Только если у вас есть миллионы строк для вставки, и если вам удобно с двоичными данными.

здесь пример рецепта в Python, используя psycopg2 с двоичным входом.

Я только что столкнулся с этой проблемой и рекомендовал бы csvsql для оптового импорта в Postgres. Чтобы выполнить массовую вставку, вы просто createdb и затем использовать csvsql, который подключается к базе данных и создает отдельные таблицы для всей папки CSVs.

$ createdb test 
$ csvsql --db postgresql:///test --insert examples/*.csv

я реализовал очень быстрый загрузчик данных Postgresq с помощью собственных методов libpq. Попробуйте мой пакет https://www.nuget.org/packages/NpgsqlBulkCopy/