Какой самый быстрый способ сделать массовую вставку в Postgres?
Мне нужно программно вставить десятки миллионов записей в базу данных postgres. В настоящее время я выполняю тысячи операторов вставки в одном "запросе".
Есть ли лучший способ сделать это, какое-то массовое выражение вставки, о котором я не знаю?
12 ответов
PostgreSQL имеет руководство о том, как лучше всего заполнить базу данных изначально, и они предлагают использовать команду COPY для массовой загрузки строк. В руководстве есть и другие полезные советы по ускорению процесса, такие как удаление индексов и внешних ключей перед загрузкой данных (и последующее добавление их обратно).
Существует альтернатива использованию 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% в этом). Иногда нельзя использовать COPY, и это достойная замена для таких ситуаций.
Одним из способов ускорить процесс является явное выполнение нескольких вставок или копий в транзакции (скажем, 1000). Поведение Postgres по умолчанию - коммит после каждого оператора, так что пакетируя коммиты, вы можете избежать некоторых накладных расходов. Как сказано в ответе Даниэля, возможно, вам придется отключить автокоммит, чтобы это работало. Также обратите внимание на комментарий внизу, который предлагает увеличить размер wal_buffers до 16 МБ, также может помочь.
UNNEST Функция с массивами может использоваться вместе с многорядным синтаксисом VALUES. Я думаю, что этот метод медленнее, чем при использовании COPY но мне это пригодится в работе с psycopg и python (python list перешел к cursor.execute становится пг 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;
Внешний файл - лучший и типичный массив данных
Термин "объемные данные" относится к "большому количеству данных", поэтому естественно использовать исходные необработанные данные без необходимости преобразовывать их в SQL. Типичными файлами необработанных данных для "массовой вставки" являются форматы CSV и JSON.
Массовая вставка с некоторым преобразованием
В приложениях ETL и процессах приема нам необходимо изменить данные перед их вставкой. Временная таблица занимает (много) дискового пространства, и это не самый быстрый способ сделать это. Обертка внешних данных PostgreSQL (ИДП) является лучшим выбором.
Пример CSV. Предположим, чтоtablename (x, y, z) на SQL и файл CSV, например
fieldname1,fieldname2,fieldname3
etc,etc,etc
... million lines ...
Вы можете использовать классический SQL COPYзагрузить (как исходные данные) вtmp_tablename, они вставляют отфильтрованные данные в tablename... Но, чтобы избежать потребления диска, лучше всего принимать его напрямую
INSERT INTO tablename (x, y, z)
SELECT f1(fieldname1), f2(fieldname2), f3(fieldname3) -- the transforms
FROM tmp_tablename_fdw
-- WHERE condictions
;
Вам нужно подготовить базу данных для FDW, а вместо статической tmp_tablename_fdwвы можете использовать функцию, которая его генерирует:
CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE tmp_tablename_fdw(
...
) SERVER import OPTIONS ( filename '/tmp/pg_io/file.csv', format 'csv');
Пример JSON. Набор из двух файлов,myRawData1.json а также Ranger_Policies2.json может быть проглочен:
INSERT INTO tablename (fname, metadata, content)
SELECT fname, meta, j -- do any data transformation here
FROM jsonb_read_files('myRawData%.json')
-- WHERE any_condiction_here
;
где функция jsonb_read_files() читает все файлы в папке, определенной маской:
CREATE or replace FUNCTION jsonb_read_files(
p_flike text, p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, fname text, fmeta jsonb, j jsonb) AS $f$
WITH t AS (
SELECT (row_number() OVER ())::int id,
f as fname,
p_fpath ||'/'|| f as f
FROM pg_ls_dir(p_fpath) t(f)
WHERE f like p_flike
) SELECT id, fname,
to_jsonb( pg_stat_file(f) ) || jsonb_build_object('fpath',p_fpath),
pg_read_file(f)::jsonb
FROM t
$f$ LANGUAGE SQL IMMUTABLE;
Отсутствие потоковой передачи gzip
Наиболее частый метод "загрузки файлов" (в основном в Big Data) - это сохранение исходного файла в формате gzip и передача его с помощью алгоритма потоковой передачи, все, что может работать быстро и без использования диска в каналах unix:
gunzip remote_or_local_file.csv.gz | convert_to_sql | psql
Так что идеальным (будущим) является вариант сервера для форматирования .csv.gz.
Ты можешь использовать COPY table TO ... WITH BINARY что " несколько быстрее, чем текст и форматы CSV." Делайте это только в том случае, если у вас есть миллионы строк для вставки и если вы знакомы с двоичными данными.
Вот пример рецепта на Python с использованием psycopg2 с двоичным вводом.
В основном это зависит от (другой) активности в базе данных. Такие операции эффективно замораживают всю базу данных для других сеансов. Еще одним соображением является модель данных и наличие ограничений, триггеров и т. Д.
Мой первый подход всегда: создать (временную) таблицу со структурой, аналогичной целевой таблице (создать таблицу tmp AS select * from target, где 1=0), и начать с чтения файла в временную таблицу. Затем я проверяю, что можно проверить: дубликаты, ключи, которые уже существуют в цели, и т. Д.
Затем я просто делаю "сделать вставку в target select * from tmp" или подобное.
Если это не удается или занимает слишком много времени, я прерываю его и рассматриваю другие методы (временное удаление индексов / ограничений и т. Д.)
Я только что столкнулся с этой проблемой и рекомендую csvsql для массового импорта в Postgres. Чтобы выполнить массовую вставку, вы просто createdb а затем использовать csvsql, который подключается к вашей базе данных и создает отдельные таблицы для всей папки CSV.
$ createdb test
$ csvsql --db postgresql:///test --insert examples/*.csv
Я реализовал очень быстрый загрузчик данных Postgresq с помощью собственных методов libpq. Попробуйте мой пакет https://www.nuget.org/packages/NpgsqlBulkCopy/
Может я уже опоздала. Но есть библиотека Java под названием
pgbulkinsertпользователя Bytefish. Я и моя команда смогли массово вставить 1 миллион записей за 15 секунд. Конечно, были и другие операции, которые мы выполнили, например, чтение 1M + записей из файла, находящегося на Minio, выполнение нескольких обработок поверх 1M + записей, фильтрация записей, если они дублируются, и, наконец, вставка 1M записей в базу данных Postgres. . И все эти процессы были выполнены за 15 секунд. Я не помню точно, сколько времени потребовалось для выполнения операции с БД, но я думаю, что это было менее 5 секунд. Дополнительные сведения см. На https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html.
Как отмечали другие, при импорте данных в Postgres все будет замедляться из-за проверок, которые Postgres предназначен для вас. Кроме того, вам часто нужно манипулировать данными тем или иным образом, чтобы они были пригодны для использования. Все, что можно сделать вне процесса Postgres, будет означать, что вы можете импортировать с использованием протокола COPY.
Для себя я регулярно импортирую данные из проекта httparchive.org с помощью pgloader. Поскольку исходные файлы создаются MySQL, вам нужно уметь справляться с некоторыми странностями MySQL, такими как использование
\Nдля пустого значения и вместе с проблемами кодирования. Файлы также настолько велики, что, по крайней мере, на моей машине при использовании FDW не хватает памяти. pgloader упрощает создание конвейера, который позволяет вам выбирать нужные поля, приводить к соответствующим типам данных и выполнять любую дополнительную работу, прежде чем она попадет в вашу основную базу данных, чтобы обновления индекса и т. д. были минимальными.
Запрос ниже может создать таблицу с
generate_seriesстолбец , который имеет 10000 строк . * Обычно я создаю такую таблицу для проверки производительности запроса, и вы можете проверить generate_series():
CREATE TABLE test AS SELECT generate_series(1, 10000);
postgres=# SELECT count(*) FROM test;
count
-------
10000
(1 row)
postgres=# SELECT * FROM test;
generate_series
-----------------
1
2
3
4
5
6
-- More --
И запустите запрос ниже, чтобы вставить 10000 строк , если у вас уже есть
testтаблица :
INSERT INTO test (generate_series) SELECT generate_series(1, 10000);