PostgreSQL внедрил декларативное разделение с версии 10, чтобы заменить громоздкую модель разделения на основе наследования. Планировщик запросов выполняет статическое сокращение разделов на этапе планирования, сравнивая предикаты запросов с границами разделов, но требует, чтобы выражения были оценимыми до констант на этапе планирования для определения, какие разделы могут быть исключены.
Основное архитектурное ограничение заключается в том, что STABLE функции, такие как now() или current_timestamp, не оцениваются во время планирования, поскольку их результаты могут различаться между планированием и выполнением или даже во время выполнения запроса. Соответственно, планировщик рассматривает предикаты, содержащие эти функции, как черные ящики, не способные доказать, что определенные разделы не могут содержать совпадающие строки, что вынуждает сканировать все разделы.
Решение включает либо переписывание предиката с использованием IMMUTABLE функций или литеральных констант, либо использование сокращения разделов во время выполнения, доступного в PostgreSQL 11 и позже. Установив enable_partition_pruning в значение on, исполнитель оценивает результаты STABLE функций по сравнению с границами разделов на этапе выполнения, динамически пропуская нерелевантные разделы после начальной фазы планирования.
Финансовая аналитическая компания разделила таблицу сделок по TIMESTAMPTZ по столбцу execution_time, используя ежедневные диапазоны для управления терабайтами данных тиков. Аналитики часто запрашивали недавнюю активность с помощью WHERE execution_time >= now() - interval '1 hour', но заметили, что эти запросы страдали от катастрофического ухудшения производительности, сканируя все 365 ежедневных разделов вместо только последнего.
Первым подходом было поручить прикладному слою рассчитать границу временной метки и вставить ее в качестве литеральной константы. Это позволило немедленно выполнить статическое сокращение и уменьшило время запроса с 45 секунд до 80 миллисекунд. Однако это сломало существующие SQL-запросы, встроенные в сторонние инструменты BI, которые нельзя было легко изменить.
Второй подход состоял в создании пользовательской неизменяемой функции, которая возвращала фиксированную временную метку, основанную на текущей дате. Этот вариант был отвергнут, поскольку он мог бы дать некорректные результаты, если транзакция базы данных оставалась бы открытой за полночной границей, нарушая контракт STABLE, который предоставляет now() в рамках транзакции. Такое нарушение могло привести к невидимым некорректным результатам запроса, если планировщик закэшировал устаревший диапазон разделов.
Выбранное решение использовало возможности сокращения разделов во время выполнения в PostgreSQL 12. Администраторы баз данных убедились, что enable_partition_pruning включен, и изменили приложение, чтобы использовать подготовленные операторы с временной границей, переданной в качестве параметра, а не конкатенированной в SQL-строку. Это позволило исполнителю динамически сокращать разделы, используя фактическое значение параметра на этапе выполнения, достигая почти оптимальной производительности без необходимости изменения генерации текста SQL в устаревших отчетных инструментах.
Как механизм исключения ограничений PostgreSQL, применяемый к унаследованным таблицам, отличается от нативного сокращения разделов, и почему первый требует явной конфигурации GUC, а последний — нет?
Исключение ограничений было методом разделения, использованным до декларативного разделения, опирающимся на CHECK ограничения на дочерних таблицах, чтобы доказать, что таблицы не могут содержать соответствующие строки. Поскольку оценка этих ограничений ко всем унаследованным таблицам во время планирования дорогостоящая, когда существуют сотни таблиц, это контролируется параметром constraint_exclusion, который по умолчанию устанавливается в partition (проверка только при запросах через наследование). Нативное сокращение разделов использует специализированные структуры данных в планировщике, которые непосредственно понимают иерархию разделов, что делает его быстрее и всегда включенным, не требуя корректировки GUC для правильной функциональности.
Почему при обновлении строки, чтобы переместить ее между разделами, изменяя ключ раздела, PostgreSQL выполняет DELETE и INSERT вместо обновления на месте, и какие последствия это имеет для триггеров?
Поскольку каждый раздел является отдельной кучей, обладающей отдельным физическим хранилищем, изменение ключа раздела требует перемещения кортежа из одного файла в другой. PostgreSQL реализует этот переход, удаляя строку из исходного раздела и вставляя ее в целевой раздел. Это означает, что триггеры на уровне строк BEFORE DELETE, AFTER DELETE, BEFORE INSERT и AFTER INSERT срабатывают во время того, что выглядит как одна операция UPDATE. Кроме того, логическая репликация передает это как два отдельных журнала изменений WAL (удаление и вставка), что может вызвать конфликты у подписчиков, если репликационная идентичность не настроена должным образом.
Каковые конкретные накладные расходы на блокировку и проверку, когда вы прикрепляете новый раздел к таблице, которая имеет DEFAULT раздел, и как можно избежать полного сканирования раздела по умолчанию?
При прикреплении нового раздела к таблице с диапазоном или списком, содержащей DEFAULT раздел, PostgreSQL должен просканировать весь DEFAULT раздел, чтобы проверить, что нет строк, которые должны принадлежать новому, более специфическому разделу. Этот проверочный скан накладывает ACCESS EXCLUSIVE блокировку на разделенную таблицу и может занять часы для больших разделов по умолчанию. Чтобы избежать этого, следует отвязать DEFAULT раздел перед прикреплением нового раздела, затем повторно подключить DEFAULT раздел только после того, как будет проверено, что нет конфликтующих строк, или, альтернативно, использовать CREATE TABLE ... PARTITION OF для создания нового пустого раздела и миграции данных с помощью INSERT ... SELECT с WHERE условием, которое фильтрует для конкретного диапазона раздела, минуя проверочный скан содержимого раздела по умолчанию.