Есть таблица с парой индексов.
В нее идет интенсивная вставка в среднем по 20 записей за раз (в ХП передается массив и потом Insert из select from unnset(array)).

Если индексы убрать, то 2млн записей вставляются за 25сек (на рабочей машине, не сервер).
С индексами - 40сек.
Разница ощутимая, но индексы нужны.

Вопрос. Как можно ускорить?

Есть ли в Postgre IOT? (нашел только CLUSTER, но так понимаю, что в БД будет все равно два объекта индекс и таблица и в общем скорость вставки в CLUSTER-ed table получается такая же, как и в варианте таблица + индексы).
Есть ли в Postgre хинты? (что-нибудь типа append)

Maxim Boguk , сравнил.
Insert 2млн записей
Наличие индексовInsert по 20 записей (сек)Insert по 200 записей (сек)Copy from fileС индексами552525Без индексов301817

В итоге получается, если программно вставлять не по 20, а по 200 записей, то получается чуть ли не быстрее чем copy from file (тк в программном варианте еще идет вставка в мастер-таблицу, генерация сиквенса и case-ы в каждом поле таблицы деталей, куда заливаем 2млн записей, а время то же самое).

То есть сам механизм работает так же по скорости, если брать пакеты большего размера.
Но засада в том, что вставлять надо все же по 20 записей, а чаще еще меньше - например по 10.

Может есть возможность распараллелить?
То есть разбить таблицу на патиции по хешу например или другие варианты и грузить параллельно.
В постгре это шардинг называется?
Можно ли при шардинге таблицу держать в одной БД, а ее патиции разнести на разные диски?
Или шардинг это когда таблицу кладут в разные ноды кластера?
При шардинге не поддерживаются глобальные уник индексы?
Надеюсь, что просто не умею готовить, но пока грустно со скоростями.
Попробую еще на машине с SSD.

Попробовал еще так
1. делаем копию таблицы с 2млн записей (кстати ctas - 7сек вот это нормальная скорость :)
2. truncate исх таблицы
3. insert select в исх таблицу с индексами 30сек, без индексов - те же 18сек

Swa111 , суть как предложил Алексей, но так будет дольше (инсерт в таблицу без индексов уже время то же самое, потом еще + переливка в таблицу с индексами).

Алексей Роза , да, хороший вариант по-моему.
Но возникают другие моменты (особенно 3):
1. Сиквенс придется генерить на стороне приложения (это конечно решаемо, не вопрос)
2. Если приложение падает, то накрываются все данные в буфере, но по бизнесу этим можно пожертвовать.
3. Сейчас в процедуру передается массив целых чисел (ID-шников) и кучка параметров, на основе которых генерятся значения для других полей в таблице через case-ы в самом запросе.
Если сначала буферизовать данные, для набора пакета большего объема, то придется передавать не просто массив целых чисел, а уже желательно массив записей, то есть объектов.
Поэтому вопрос, есть ли возможность в Postgres передавать из Java CallableStatement набор объектов (так-то в CallableStatement есть метод setObject, это поищу что за зверь).
Либо передавать массив строк с json, а в постгре json_to_recordset, но не охота парсить ибо тоже затраты времени.

Maxim Boguk , итак пишу через тредпул в 15 потоков, это конечно дает ощутимый прирост, но диск-то все равно один.

Памяти на машине 8GB, параметры:
synchronous_commit - on
max_wal_size - 1GB
checkpoint_timeout - 5min
shared_buffers - 128MB

Maxim Boguk , попробовал.
Да получается побыстрее.
Немного нелинейная зависимость получается, но и зависимость от размера пакета сохраняется, то есть вариант с буферизацией на стороне приложения пока актуален.
Да и изменение параметров возможно влечет другие риски в частности настораживает
synchronous_commit =off.

Вставка 2млн записей
Наличие индексовПакетами по 20 записей (сек)Пакетами по 200 записей (сек)Без индексов2620 С индексами3630

При этом в варианте без индексов по 200 записей время загрузки стало сильно нестабильным и колеблется от 11 до 35 секунд.

deleted

Алексей Роза , тоже так думал, но во-первых, буде не сильно быстрее (в файл же тоже надо записать), во-вторых, самое главное, к этим данным могут идти селекты, а из файла не поселектишь особо ), поэтому придется все равно на время пока данные не легли в БД держать их в памяти, то есть буфер на 200+- записей
Maxim Boguk , ок, попробую на SSD еще.
И еще все же интересно что там с шардингом )

Хотя селектить данные, которые еще не легли в БД тоже негуд, т.к. юзер заселектил из буфера приложения, дальше приложение вдруг упало и после восстановления пользователь уже не увидит данные, которые он видел до падения ибо они не записались в базу из буфера.

Maxim Boguk , спасибо за пояснения.
Попутно еще возник вопрос по деградации производительности с ростом объема.
То есть.
2млн записей вставляются в среднем за 25сек (условно 80тыс. записей/сек)
А 30млн записей - за 22.5 минуты (примерно 22тыс записей/сек)
То есть с ростом объема скорость падает практически в 4 раза в данном случае, что довольно ощутимо.
Как боремся с этим?

И все-таки шардинг и патиционирование - разные вещи значит?
То есть таблица может быть и патиционирована и шардирована одновременно.
В общем надо еще курить документацию, хотя бы основы, то тут все немного по-другому похоже )

Maxim Boguk, спасибо, теперь с шардингом еще понятнее, думал, это в коробке)

Продолжаю эксперименты.
Пока самый быстрый вариант это передача массива и select from unnest пакетами в районе 200 значений в массиве.

Но ввиду обьединения многих вызовов по 10-20 записей в один через буфер на 200 записей, нужен многомерный массив или обьекты типа pojo(это не пробовал, не знаю есть ли это в постгре).

Поэтому.
Пробовал еще просто батч инсерты - unnest быстрее.
Пробовал засунуть все 200 записей в json и потом select from json_to_recordset - unnest быстрее.

Пробовал передавать в процедуру в каждый из параметров отдельный массив и массив индексов массива - мало того, что криво но unnest из одного массива все равно ощутимо быстрее.

Можно было бы попробовать передавать массив строк с разделителями и потом их парсить, но думаю, это будет примерно как с json_torecordset.

Есть другая мысль. Передаются только числа. То есть можно пофиксить формат, выделив для каждого параметра определенное кол-во байт. И тогда парсить можно будет быстрее, сразу задавая смещение. Поэтому вопрос есть ли в постгре возможность работы с бинарными данными?
То есть передавать одномерный массив, элементы которого двоичные данные. Хотя потом их все равно придется приводить к тому же bigint например.

Попробовал просто массив строк с фиксированным форматом. По скорости так же как вставка пакетами по 20 строк. То есть медленне чем json_to_recordset.
Но может криво написано с типами и преобразованием.

Сделал таблицу с со всеми чаровскими полями, убрал из функции преобразование типов в запросе - вставка 2млн записей без индексов - 13сек, с индексами - 1min.
Какая-то огромная разница, будто индексы по строкам строятся сильно дольше, чем по числам.

Вопрос с аналогичным тестом, но переложенным в бинарный формат пока актуален. Так же как и вариант передачи массива обьектов без сериализации и последующего парсинга)

Прошу пардона, на самом деле пара запусков была по минуте, позже пробовал еще раз - 35-40сек, то есть разница не такая большая с индексами по чарам.
Ну и кому-то может пригодится товарищ тоже шуршал в этом направлении .

Ну и на простеньком SSD, core i5-8400 2.8, 4Gb:
insert 2млн записей, текущий вариант (массив строк фикс формата) в несколько потоков.

Без индексов
пакетами по 20 записей - 12сек
пакетами по 200 записей - 5сек

С индексами
пакетами по 20 записей - 24сек
пакетами по 250 записей - 16сек

То есть все в 2 раза быстрее, хоть и ожидал большего, но 125тыс.записей/сек с индексами в целом нормально.
Ище бы сюда IOT, index skip scan (тогда хватило бы одного индекса), патишены с возможностью их разнесения по разным дискам и логи на отдельный диск, плюс передача массива объектов или массива бинарных данных.

Melkij , да, как оказалось IOT + индекс вместо двух индексов ничего не дает.
Насчет "объясните вручную" там что-то интересное, но если количество уникальных значений будет большим, эти рекурсивные запросы будут висеть очень долго. До соучастия очень далеко )

Нашел еще PgBulkInsert .
на SSD вставка 2млн записей одним пакетом без индексов - 3сек,
с индексами пакетами по 200 записей - 11сек
Вполне себе неплохо, попробую пока остановиться на этом варианте.

cvv, а есть возможность выложить тесткейс, прогнать у себя?
У меня правда на машине где ssd core i5 и 4гб.
Но копи так понимаю из файла инсертит.
Если бы можно было хотя бы из потока копи.

У меня просто копи с инднксами работает все равно небыстро.

cvv , и еще не понятно если 150к в секунду, то откуда 5млн, за 3.5сек?
Или скорость выше, или не 5млн, а 0.5млн?

cvv , да, интересный вариант по скорости и в целом круто.
А так у меня через один коннект к БД - 2млн - 20сек, но таблица 5 полей.
Если в пуле 15 коннектов, то 11сек, 30 коннектов - 9сек.
Не уверен, что у меня получится переложить С на Java, но в PgBulkInsert-е есть CopyManager , который работает через copy stdin, но примеры тоже из файла и то не проверял, как сделать обертку, чтобы читать из памяти как из файла... возможно сразу писать в OutputStream нужную информацию типа как в файл, а в CopyManager.copyIn передавать ридер для этого потока. Да и что писать на вход навроде String.GetBytes()..

Там же есть еще PgBinaryWriter , это уже ближе к искомому похоже, но как с ним работать пока не понял.

Так как пишут, дескать бинари формат может съезжать от версии постгрес, пока без него.