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

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

попробуйте вставлять не по одной строке

Нормального способа нет. Можно попробовать использовать предикатные индексы (т.е. если вы вставляете записи с каким-то таймстампом,то можно не включать их пока в индекс). Но не возможно "сдвинуть" это условие, добавив в индекс новые записи. Можно только создать новый индекс. Ещё можно поиграться с materialized view и индексами созданными для него. Но это тоже всё криво и не инктерментально.

Нет, нельзя. Иначе бы ровно также "откладывалось" получение правильных результатов запросов (по крайней мере в этой сессии), понимаете? IMHO, тут https://xyproblem.info/

в моем случае ничего страшного в этом нет — Иначе бы ровно также "откладывалось" получение правильных результатов запросов

В проекте PostgreSQL принципиально не занимаются такими "оптимизациями", которые помогут 0.00001% пользователей (а всем остальным, скорее всего, навредят). Так что даже не ждите. ;)

спасибо, гляну в этом направлении

Вот можете почитать если очень хочется - там длинная дискуссия о том как я пытался пропихнуть batсh index update в комьюнити: https://www.postgresql.org/message-id/flat/569F5346.1010005%40postgrespro.ru А в начале есть ссылка как этого можно добиться с помощью materiaized view.

благодарю:)

Это сомнительный аргумент. Можно делать seqscan на пул новых записей. Можно строить отдельные индэксы для них, потом мерджыть. Просто это куча работы для разработчиков ядра.

Вобще-то по моему опыту — это поможэт примерно 100% польщователей, у которых база не влезает в RAM. И минимум четверти из них — поможэт в чём-то существенном.

Это настолько "несомнительный" аргумент, что я вполне уверен, что в PostgreSQL этого никогда не будет. Более того, даже если Вы лично (или даже кто-то из long-time contributors) напишет такой код (красивый, корректный, с тестами и документацией) — дело кончится"Tom Lane rejected my patch and All I got was this stupid t-shirt. In short: -1 from me. regards, tom lane". ;)

Спасибо!

Ваш опыт не заменяет объективных измерений, извините. Хотите — попробуйте вышеописанное (или убедите кого-то), посмотрим, что получится.

Много раз пробовал добавлять индэксы по одному после массовой заливки – разница по сравнению с online индэксированием для данных в 10 и более раз большэ, чем RAM была существенной.

Суть запрашиваемого совсем не в этом, а в требовании "локального" нарушения ACID (технически — хотя бы в рамках запроса). Но, по-хорошему, тому, кто возвращает пользователю результат "всё выполнено" до завершения транзакции, нужна MongoDB и т.п., а не PostgreSQL. ;) Т.е. подумайте о последствиях, и этот вариант Вам сразу разонравится, мне кажется.

Хм. Сомневаюсь в такой трактовке. Локальное нарушэние цэлостности, конечно, потребуется — но оно у нас ужэ отлично работает в виде deferred constraints. По-моему человек хотел именно скорости.

Во-первых, вопрос-то был "можно ли отложить это индексирование?". Так вот если отложить, то без "делать seqscan на пул новых записей. Можно строить отдельные индэксы для них, потом мерджыть" следующий запрос в этой транзакции (и даже volatile functions в том же запросе) этих изменений не увидят. Далее, вот эта вся куча "интересных" действий/структур либо должна уйти в WAL (иначе Durability не будет), либо Вы создаёте feature, которая просто разбивает нагрузку так, что сам запрос-то завершается быстрее, но вот сама содержащая его транзакция — медленнее (потому что все эти данные надо-таки поместить туда, где они должны быть — в индексы), и что мы таким образом выиграли, я как-то не понимаю?

Мы выиграли то, что вместо миллиона random index access/insert на каждый индэкс у нас будет по одному index merge scan с insert на индэкс (что дажэ дешэвле, чем full index range scan). Дажэ на ssd я бы ожыдал роста производительности, на hdd рост будет колоссальный.

> будет по одному index merge scan с insert на индэкс И выигрыш-то где, подробнее (после "уплаты" накладных расходов, которые — чистый проигрыш)? > Дажэ на ssd я бы ожыдал роста производительности, на hdd рост будет колоссальный. А я бы не ожидал (хорошо, если не будет обратного), и как вообще с этим связан тип диска?! Вы понимаете, о каких "типичных" объёмах данных идёт речь?

Тип диска связан так, что разница между произвольным и последовательным доступом на hdd гораздо большэ. Типичные объёмы данных — 0.5-10 RAM.

Я это знаю, и, как мне кажется, это вообще не имеет отношения к обсуждаемому вопросу. > Типичные объёмы данных — 0.5-10 RAM. Каких данных? Вставляемых? Хранимых в индексе? Что это меняет? Вы, кстати, пробовали посчитать, сколько "средних" rows нужно вставить за раз, чтобы получилось хотя бы 100 MB?

Вставляемых. Данных в таблицэ (включая индэксы) при этом 5-100 RAM, тожэ типично. Меняет это то, что большынство операцый с индэксом идут через диск с соответствующей цэной доступа. И сэкономить иопы тут очень полезно. Сотни тысяч-миллион, а что.

> Вставляемых. Данных в таблицэ (включая индэксы) при этом 5-100 RAM, тожэ типично. Размер данных в таблицы и размер [b-tree] индексов — совсем разные вещи ("ширина" индексов обычно невелика — то число, которое ниже (или даже 80), почему-то часто используют для расчётов в среднем, хотя источника / обоснования я не нашёл). > Сотни тысяч-миллион, а что. А то, что у кого-то из нас что-то не так с арифметикой: > SELECT pg_size_pretty(1000000.0 * 100); 95 MB На один индекс. 100 — это размер (ключ + ctid). Я что-то не так посчитал? > Меняет это то, что большынство операцый с индэксом идут через диск с соответствующей цэной доступа. См. выше. Как-то непохоже, что идут, это во-первых. И, если добавить такие структуры, то их только может быть меньше (причём, [выполнив прикидки] процентов на 20 в идеальном случае). Где profit-то?

Откуда 100? 8 байт bigint, 6 байт ctid.

Я же написал — откуда я знаю?! ;) Оттуда же, откуда и 80 — средний размер ключа среди [взвешенного] множества используемых на практике индексов некоторые почему-то считают таким. Если хотите выбрать любое другое правдоподобное число — пожалуйста.