Как стать автором
Обновить

PostgreSQL: как всего одно изменение привело к росту производительности в 9 раз

Время на прочтение 10 мин
Количество просмотров 23K
Всего голосов 62: ↑39 и ↓23 +16
Комментарии 46

Комментарии 46

Странно, почему даже не попытались использовать Prepared statements для изначального INSERT-а, что было бы стандартным приемом для такого типа задач? В итоге все выродилось объединение/разбиение SQL-в в более длинные/короткие формы, и проблему так и не устранили…
Ожидал увидеть что-нибудь похардкорнее, типа разбиение на партиции/подтаблицы, выключение индексов на время инсерта, анализ физического расположения записываемых данных с целью их максимального эффективного группирования, прединсертные сортировки и т.п.
В общем странная статья, зачем ее было переводить…

Из вашего комментария я выделил для себя больше полезного в виде направлений, над чем можно думать, чем из статьи:)

Не раз слышал про отключение индексов для ускорения вставки. Но вижу в этом следующие проблемы: при большом количестве записей в таблице, операция включения индексов будет занимать довольно значительное время. Что делать при параллельной вставке из нескольких потоков? Синхронизировать их?
Вопросы не риторические и без сарказма, хотелось бы узнать, как решают при таком подходе описанные мной проблемы.
Вставка в таблицу без индексов обычно мгновенна и не зависит от размеров таблицы. Построение индекса на миллионах записей всегда быстрее, чем миллионы вставок в упорядоченную структуру. Я когда-то давно делал эти замеры на MySQL с количеством записей порядка миллионов, точных цифр у меня сейчас нет, но помню что разница по времени была в разы.
Но тут могут повлиять внешние факторы. Например этих нескольких минут на построение индекса может просто не быть, так как блокировка недопустима. В этих случаях может помочь
— разбиение на подтаблицы,
— hot swap таблиц путем переименования,
— доступ к данным через VIEW, который тоже можно переопределить мгновенно в зависимости от того, в каких таблицах в данный момент данные и индексы актуальны.
Еще можно предварительно создавать записи-placeholder-ы в индексировнной таблице, задав тем самым значения индексов для непоступивших еще данных, а при поступлении уже апдейтить какие-надо неиндексированные поля. Такой прием позволяет физически группировать записи в тот же самый блоке на диске, и потом прочитать их горздо быстрее, чем если бы записи были созданы в разное время и в разных блоках.
В общем поле извращений тут большое.
Вставка в таблицу без индексов обычно мгновенна и не зависит от размеров таблицы

В постгре это точно не так и зависит от огромного количества факторов. В частности от интенсивности чтения, изменения и собственно самой записи.

Построение индекса на миллионах записей всегда быстрее, чем миллионы вставок в упорядоченную структуру

Миллион вставок всегда будут очень медленными. А если вставить батчем, то это утверждение может быть верным далеко не всегда.

Остальные советы тоже хороши в ограниченном количестве случаев. Например, если работаете с финансами так лучше не делать :)
Остальные советы тоже хороши в ограниченном количестве случаев. Например, если работаете с финансами так лучше не делать :)
«Остальные советы» реализовывались как раз в финансах в телеком-биллинге. Там вообще это стандартная практика — копить транзакции за разные месяцы в отдельных таблицах. Там же пробовали и записи-плейсхолдеры. Они во много раз увеличивали скорость формирования стейтментов в конце месяца, так как данные каждого клиента были уже сгруппированы на диске, но естественно пришлось заплатить цену: загрузка системы при записи возрасла в разы, ну и какое-то количество плейсхолдеров оставалось неиспользованными, и БД слегка увеличилась.
Поэтому да, надо смотреть весь цикл жизни данных, в каком порядке ожидается поступление данных, когда, как часто и по каким критериям будет происходить чтение, насколько каждая из этих операций критична по времени, по целостности, какое есть железо, какая нужна надежность, требуемое время восстановления, и т.д. и т.п. И тогда уж принимать решение.
У PG только один протокол, я в терминологии не силен — там передаются байты-комманды и си-строки с нулем в конце. Вероятно что бинарный.
А pg и pg-native отличаются тем, что первый полностью на js написан, а второй обращается к сишной библиотеке, второй чуть-чуть быстрее.
Если надо ещё ускорить попробуйте (мой) pg-adapter
Протокол один, а вот параметры запроса (например, вида INSERT… VALUES ($1, $2 ...)) могут передаваться в текстовом или бинарном виде.
Это как? Можно пример или ссылку?
Не знаю, как с этим дела в node.js. Как правило, любой pg драйвер написан на основе libpq, а там см. функции PQexecParams и PQexecPrepared:
paramFormats[]
Specifies whether parameters are text (put a zero in the array entry for the corresponding parameter) or binary (put a one in the array entry for the corresponding parameter). If the array pointer is null then all parameters are presumed to be text strings.

Values passed in binary format require knowledge of the internal representation expected by the backend. For example, integers must be passed in network byte order. Passing numeric values requires knowledge of the server storage format, as implemented in src/backend/utils/adt/numeric.c::numeric_send() and src/backend/utils/adt/numeric.c::numeric_recv().
Значит, все-таки, у PG один формат для передачи по сети, это библиотека libpq на стороне клиента может принимать в разном виде. Используется не в любом драйвере, в node по умолчанию pg без неё работает.
Значит, все-таки, у PG один формат для передачи по сети

Нет, именно что по сети гоняет по-разному.
Протоколов именно что два: версии 2 и версии 3.
согласен. статья написана чтобы похвалить штатную функцию библиотеки pg-promise, у которой этот метод вообще вписан в документацию. Где новости то?
Статья уровня школьника, который узнал, что в базу можно вставлять несколько значений одновременно.
+
как всего одно изменение привело к росту производительности в 9 раз

Ожидание — неожиданная конфигурация PSQL, интересный способ разбивки данных…
Реальность — оказывается можно объединить несколько вставок в одну.

Статья вполне ок — автор описал свой реальный опыт и грабли, на которые наступил.

Согласен. Вообще детские ошибки. А для таких множественных потоков и транзакций нужно использовать пуллер типа pgbouncer, т… к для postgres дорого создавать для каждого соединения отдельный процесс.
Откройте для себя COPY.

Хорошая штука, но сделать INSERT ... ON CONFLICT с её помощью не выйдет.
С её помощью зато очень хорошо заполнять миллионами записей временные таблицы, чтобы не думать об INSERT-ах с миллионами аргументов.

1. Как можно было изначально написать вставку единичными запросами, если на входе ожидалось тысячи сообщений? За такое джунам по рукам дают еще на стадии учебы.
2. Хранить метку времени в поле TEXT?? Почитайте про типы timestamp и timestamptz.
3. А для group_id зачем TEXT? Почти наверняка там строки вполне предсказуемой длины.
3. А для group_id зачем TEXT? Почти наверняка там строки вполне предсказуемой длины.

А это не BIGINT-FK должен быть вообще?
REFERENCES group(id), например

Этот товарищ, кроме своего пет-проекта actual еще и работает в stripe.
Но это не главное.
Он тот, кто придумал prettier.


Это позор автора.
Статья не стоила времени, затраченного на перевод.

Спасибо за комментарий. Хоть понял что это перевод, т.к. в приложении хабра не видно, кто автор оригинала. Сходил на его сайт… мда… «и это люди запрещают мне ковыряться в носу» (с)
James Long is a developer & designer with over a decade of experience building large-scale applications.


P.S. Еще теплилась надежда, что в stripe он пришел на позиции джуна. Но нет "I talked to Alex Sexton 5 years ago about joining Stripe".
Почитал его блог. Понятно, почему его взяли в stripe. Ему больше нравиться заниматься продуктами. Т.е. да, технически он видимо слабоват (в пересчете на годы проведенные в разработке), но любит заниматься развитием и созданием продуктов. Тем более у него продукт в виде actual который можно пощупать и оценить.
Он сделал большой вклад в индустрию, сделав prettier.

Но это — позор, как он есть.

Такие статьи встречаются регулярно, и довольно часто в корпоративных блогах. Видимо, у людей какой-то план по публикациям и получаются такие материалы. Но его никто не заставлял в свой блог писать, я полагаю…
Если это он придумал prettier, то я готов ему все простить
Широкой вы души человек :)

Можно было бы это простить, если бы это на review нашлось :)

Из текста следует что параметры запроса интерполируются в строку запроса.Если бы это не делалось и использовался бы параметрический запрос то можно было бы и на окна не разбивать.

Автор какой-то агалтелый джун. Не понятно, как статья набрала +16.

К правильным комментариям выше я бы добавил UUID в качестве первичного ключа. И генерация его на клиенте. Ждать ответа от бека при этом не нужно. Можно на сервере реализовать вставку через очередь.
Ждем от него цикл статей под общим заголовком «Читаем документацию к PostgreSQL.»
Хорошая статья, к тому же сагрила людей на полезные комментарии.
А одному мне кажется, что использовать составной ключ с датой это не очень разумно?
С технической точки зрения структура базы в целом паршивенькая. А первичным там должен быть UUID с генерацией на клиенте.
честно говоря, никогда не сталкивался с генерацией uuid на клиенте, но уж что стараюсь всегда вбить в голову студентам, что первичный ключ ни в коем случае не должен быть составным.
Почему?
На моём небольшом опыте тут чаще всего возникает ситуация, что составные первичные ключи в какой-то момент могут по факту перестать быть первичными ключами (возможны задвоения). Используя же первичные ключи на основе автоинкремента или uuid всё таки исключаем такую возможность и если необходима функция, то её можно отдельно реализовать, а при необходимости отключить.
При этом мы в целом уменьшаем размеры индекса по первичному ключу. Чем больше столбцов и чем более объёмные там типы данных, тем больше получается индекс, но это спорное утверждение, местами может быть и меньше.
Ну если составной ключ перестает быть первичным, значит изначально автор неправильно понял предметную область, т.е. допустил ошибку на этапе проектирования. В самом составном ключе ни чего криминального нет, особенно когда он естественный.
Другой вопрос, что суррогатные ключ в виде автоинкрементных идентификаторов или UUID-ов удобным на практике в том числе и не разрабам. Второй мотив использования суррогатных ключей — зачастую сложность в выделении естественного ключа, а иногда и невозможности это сделать.
На мой взгляд всё таки не всегда это происходит из-за ошибки. Иногда меняется область. При этом это может добавить некоторые трудности как раз разработчикам при изменении области, а суррогатный ключ минимизирует подобные проблемы.
Другая же проблема на мой взгляд, которая как раз перекликается с невозможностью выделения естественного ключа — требование практического анализа большого объёма данных для проверки высказываний экспертов. Я очень часто сталкиваюсь с ситуацией, когда вроде бы эксперты говорят, что дублирования быть не может, но при анализе на предмет поиска как раз ситуаций, которых быть не может несколько таких не найдётся.
Поэтому на мой взгляд суррогатный ключ это прям обязательная вещь, ведь нельзя спроектировать прям идеальную систему.
В составных первичных ключах есть еще один недостаток. Зачастую PK делаются не только для того чтобы обеспечить уникальность, но и для того чтобы сослаться на таблицу из дочерней foreign key-м. В таком случае в дочернюю таблицу придется тащить не одно, а два поля, что означает перерасход места под таблицу.
Ну и как заметил GooG2e, бывают ситуации когда естественные ключи перестают быть уникальными. Имхо, достаточно столкнуться с одним таким случаем на своей практике в проде на большой базе, чтоб перестать даже думать использовать естественные ключи.
Зато такие случае сразу позволяют выявить проблемы в бизнес процессах предметной области. Что очень полезно. Поэтому я тоже лично за использование суррогатных ключей (в том числе из-за FK), но при этом за уникальный индекс на естественном PK именна для отлова вышеприведенной ситуации.
Кто возьмется написать расширение для браузера чтобы скрывать бесполезные статьи от ruvds? :)
Мне кажется, тут же был вроде черный личный список авторов.
НЛО прилетело и опубликовало эту надпись здесь

Вообще больше похоже на статью, как я делал и как делать не стоит.
Если приложение предполагает высокую нагрузку то всё сделано не верно.
На наших проектах мы прокачивам по несколько сотен тысяч записей в бд на обновление и вставку.
При этом постгресс не испытывает видимых усилий.
Любой проект и задача начинаеться с анализов механизмов и возможностей инструментов, а не со статьи на хабре как я открыл для себя америку.

Зарегистрируйтесь на Хабре , чтобы оставить комментарий