Comments 38
Но нужно ли обновлять все строки?
Шоке. Это бессмысленный вопрос, так как нужно или нет определяется бизнес задачей исключительно.
С тем же успехом можно вообще убрать условие обновления, и посмотреть, пропадет ли scan/seek перед обновлением. Если не нужно будет искать записи для обновления, то это сильно ускорит выполнение.
Хорошо бы добавить в заголовок или теги, что почти все советы касаются PostgreSQL и не применимы к другим СУБД.
2. При больших загрузках отключайте ограничения и индексы
Отключили ограничения — лишились минимальной гарантии что данные в миграции правильно были переложены из одного места в другое.
Данные лучше проверить на ограничения ДО обновления, по-моему, это очевидно.
Почему лишились? При включении ограничений они все равно будут проверены.
Я рассматриваю ограничения как «минимальную гарантию» того что данные не повреждены. Если мы после миграции получили ошибку при включении ограничений, то это в большинстве случаев говорит о том что повредили данные в процессе миграции и нужно делать откат из бекапа, и все по новой и никакой экономии времени.
Миграции с миллионами данных которые занимают недели/сутки делают по другому. Через дополнительные таблицы, через дополнительные базы, через файлы, короче вообще по другому, и там тоже нет вопроса с отключением ограничений.
Если это OLTP там самое дешевое создать ограничения на уровне БД, и, почти всегда, этого достаточно. Хотя часто на обработку ошибок пользователей здесь работает клиентское приложение — меньше грузим сервер при массовом вводе данных.
Если это ETL из «чужого» нам источника, то использование ограничений это скорее всего малоэффективно, т.к. во первых тормозит, а во вторых работает только на отказ, и не рассматривает варианты — здесь лучше будет работать приложение работающее через промежуточную таблицу и способное скорректировать некоторые данные, хотя конечно бывает что нам достаточно получить отказ и сказать поставщику — исправляйте.
А при отлаженном ETL из знакомых нам источников данных в DWH, зачастую вообще нет никаких ограничений, т.к. они реально тормозят потоки, а вероятность случайной ошибки крайне низка. Индексы при этом действительно отключают и по завершению потока включают. т.к. это гораздо более эффективно. А если вы все таки получили ошибку по факту заливки — значит поток что-то не учитывает, но это редкая ситуация, или на источнике провели изменения о которых вы не знаете, всякое бывает, и для отлова таких ошибок создают DQC.
Эээ, Вы же когда обновляете знаете ЧТО обновляете и к какому виду хотите это привести, соответственно, что мешает в условие "бизнес-задачи" включить только те данные, которые НУЖНО обновить?
К другим БД тоже применимы, но есть ньюансы.
Именно. Задача прямо говорит, что надо обновить. В статье в примере говорится, что чем меньше обновить тем быстрее будет (если я правильно прочитал). Это совершенно корректно, но не применимо, так как в большинстве случаев нет выбора обновлять меньше или больше — надо обновлять столько, сколько требуется.
Нюансов там много. Например некоторые примеры задач решаются принципиально иначе в других СУБД. А в большинстве примеров статьи используются специфичные Постгре операторы и конструкции.
Опять же, как уже сказали про большие загрузки — если речь идёт про сервис базы или разработку, то конечно быстрее залить, а потом построить индекс онлайн. (Хотя опять же это делается редко, можно и подождать). Но если это рутина, то такое дергание индексов и ограничений может аукнуться производительностью.
Ну, так задача DBA, если архитектор просмотрел, именно в том и состоит, чтобы оптимизировать процесс, разве нет? Я видел и такие "бизнес-задачи", как логировать ВСЕ действия пользователя по триггеру, которые решались влоб, с гигабайтами логов за час, когда изменялось ОДНО поле, а логировались все пятьсот с неизменными данными.
Принципиально другие — согласен, но концепция даёт пищу для размышлений, вернее, для гугла и всё встаёт на свои места, заодно и теория подтягивается.
Спорный вопрос, это же не инструкция для применения опять же, это пища для размышлений и мотивация к оптимизации, как мне показалось после прочтения статьи.
Если не нужно будет искать записи для обновления, то это сильно ускорит выполнение.
Ускорит только этап поиска (и то не всегда), но замедлит этап обновления, из-за большего количества ненужной работы. И последнее намного перевесит первое.
почти все советы касаются PostgreSQL и не применимы к другим СУБД.
Что именно, по-вашему, не применимо к другим СУБД (за исключением синтаксиса)?
Что именно, по-вашему, не применимо к другим СУБД (за исключением синтаксиса)?
Для промежуточных данных используйте UNLOGGED-таблицы
WITH и RETURNING
BRIN
Делайте индексы «невидимыми»
Это не синтаксис, это другие механизмы.
Ускорит только этап поиска (и то не всегда), но замедлит этап обновления, из-за большего количества ненужной работыКонечно. Но обычно вы не можете выбирать сколько записей вы вы обновляете — вся работа «нужная». «Требуется сделать емейлы в таблице капитализированными. А давайте только половину из них сделаем, ради ускорения!» — это имеет мало смысла.
Для промежуточных данных используйте UNLOGGED-таблицы
Этот механизм есть в других СУБД, название может отличаться. Этот совет применим везде, где механизм доступен.
Делайте индексы «невидимыми»
Транзакционный DDL есть в других СУБД.
WITH и RETURNING
Тут согласен. Механизмы для возврата результата из DML есть в других СУБД, но они сильно отличаются.
BRIN
Наверное, совет должен звучать как "Изучите доступные типы индексов и используйте наиболее подходящий в конкретной ситуации.
Но обычно вы не можете выбирать сколько записей вы вы обновляете — вся работа «нужная». «Требуется сделать емейлы в таблице капитализированными. А давайте только половину из них сделаем, ради ускорения!» — это имеет мало смысла.
Не передергивайте. Задача "требуется сделать так, чтобы все емейлы в таблице были в нижнем регистре". Ее можно решить несколькими способами, более или менее эффективными. Выбирать более эффективный имеет смысл.
Либо я не понял пример с обновлением, либо вы. В примере сказано, что обновление это тяжёлая операция, поэтому если обновлять не все записи, а некоторые, то это производительнее, чем обновлять все. По мне это капитанщина без смысла, так как понятно, что меньше = быстрее, но это решение не удовлетворяет условию задачи.
Возможно автор имел ввиду, что надо избегать обновлений, но это невозможно, мягко говоря. Возможно автор хотел сказать, что надо обновлять порциями, но это опять же зависит от задачи и конкретное решение зависит от множества факторов, начиная от объемов данных, заканчивая сервисными интервалами базы, критичностью данных и так далее и тому подобное. Нельзя так просто взять и обновить только часть записей потому, что это быстрее.
Я думаю, все-таки вы не поняли. Всего в таблице миллион емейлов, из них только 10 тыс. не в нижнем регистре. Остальные менять нет необходимости. Но можно и поменять, хуже не будет, конечный результат тот же. Только медленнее.
Это как перерисовывать весь экран при перемещении одного окна.
Советы легко могут превратиться в антисоветы в зависимости от способа использования субд...
Для отключения индексов без блокировки всей таблицы, есть решение заключающееся в пометке индекса как невалидный в pg_catalog.
Исправьте заголовок — это советы только для СУБД Postgres. Либо пишите реальные советы, применимые для всех. А то слишком громкий заголовок, а пользы никакой для тех кто не работает с Postgres
Как человек, который работает с БД далеко не первый год, сильно плюсую. Лишь в середине статьи упоминается, что речь про PostgreSQL.
Большинство советов для применимы только для PostgreSQL и отчасти для Greenplum. Возьмите другую БД где индексы отсутствуют вовсе / работают по-другому; где нет такого понятия как UNLOGGED таблицы; где UPDATE отсутствует вовсе, либо работает очень быстро и т.д. Список можно продолжать. Во всех этих случаях статья станет бесполезна чуть менее, чем полностью.
Надеюсь во втором пункте была речь про временное снятие ограничений и их возврат после крупной вставки? Или все же речь про полный отказ от FK?
Чтение с диска — операция дорогая, поэтому вы захотите читать как можно меньше. Например, если таблица размером 10 Мб, а индекс размером 1 Мб, то для считывания всей таблица придётся считать с диска 10 Мб. А если добавить индекс, то получится 11 Мб. Это расточительно.
Жесть какая. Считать 1 МБ — это полная фигня по сравнению с тем, что нужно будет сделать ~900 000 лукапов, чтобы добраться от индекса к данным. Именно поэтому индекс в данном случае использоваться не будет.
P.S. И перевод ужасно корявый. IMHO.
db=# UPDATE users SET email = lower(email)
db-# WHERE email != lower(email);
Но так писать — закладывать потенциальные проблемы. Предпочитаю
db=# UPDATE users SET email = lower(email)
db-# WHERE (email) is distinct from (lower(email));
— зальешь 50 кк строк без индексов, а дальше что?
А дальше — включай все индексы = полная перестройка — и где профит по времени и производительности?
А если таблица 3 ярда строк — а ты влил 50 кк — перестраивать индексы будет дороже
— Ограничения и так всегда отключены на работающих БД, а там где не отключены сделано специально для проверки корректности данных
— Пометьте пожалуйста что это для Postgres
А если таблица 3 ярда строк
, то нужно секционировать данные и использовать локальные индексы, чтобы перестраивать индекс по ограниченному списку секций.
Это тоже нужно учитывать при проектировании.
Ограничения и так всегда отключены на работающих БД
ну знаете, это на ваших БД может и так, но для начинающего разработчика это не очевидно, и часто люди после проектирования OLTP с теми же подходами садятся за DWH.
Пометьте пожалуйста что это для Postgres
Как разраб Oracle могу утверждать, что не только для Postgres, а для продвинутых реляционных БД. Есть конечно «фичи» типа BRIN, но это частности.
А вот пометку что примеры приведены в синтаксисе Postgres можно.
Как разраб Oracle могу утверждать, что не только для Postgres, а для продвинутых реляционных БД.
скорее это показывает, что в своём развитии postgre больше всего ориентировался на oracle.
Ну тогда наверное и на MSSQL Server тоже? И наверное на этих конкурентов все ориентируются, кто хочет занять свою нишу в сегменте реляционных СУБД. Просто не все позиционируют себя на этот уровень, отбрасывая сложные и дорогие фичи, чтобы удешевить продукт. Но рекомендации в общей части применимы ко всем РСУБД, которые оперируют сотнями и более гигабайт информации.
Трюки с SQL от DBA. Небанальные советы для разработчиков БД