Written with AI assistance from the source video. The video is the authority.
Log in to leave a comment
No posts yet
Рекурсивные CTE, используемые для обхода отношений между родителями и потомками или множественных путей в устаревших системах, ломаются по мере увеличения глубины данных. В тот момент, когда веер индекса (index fan-out) начинает сбоить, движок создает временную таблицу на каждом шаге и выполняет повторные самосоединения (self-joins). Результирующий набор заполняет память и переливается на диск, вызывая spillover. Ввод-вывод блокируется, а ЦП истощается. Встроенный синтаксис CYCLE, появившийся в PostgreSQL 14, снижает затраты на поиск по массиву, но по мере увеличения глубины обхода затраты на повторные поиски по вторичным индексам и копирование памяти больших кортежей остаются прежними.
Чтобы увеличить скорость запросов и сэкономить ЦП, следует перейти на встроенный синтаксис графов на базе SQL/PGQ. Во-первых, используйте синтаксис CREATE PROPERTY GRAPH, оставляя физическую структуру существующих реляционных таблиц без изменений. Укажите таблицы отдельных сущностей как VERTEX TABLES, а таблицы пересечений «многие ко многим» — как EDGE TABLES. во-вторых, поскольку колонки, указанные в качестве ключей, не попадают в свойства графа автоматически, добавьте поля в предложение PROPERTIES, чтобы обеспечить права фильтрации GRAPH_TABLE. В-третьих, для запросов, у которых глубина обхода превышает 3 уровня, средний коэффициент ветвления составляет 10 или более, а количество кортежей превышает 10 миллионов, замените их на паттерн GRAPH_TABLE MATCH. Сложный рекурсивный код сократится до одной строки паттерна, а использование буфера памяти заметно уменьшится.
Ошибки дублирования вставки, возникающие, когда несколько потоков одновременно передают данные в распределенной среде, невозможно устранить с помощью блокировок приложения. Возникают накладные расходы на сетевой RTT, и гонка данных (race condition) срабатывает в зазоре непосредственно перед фиксацией транзакции. Необходимо инкорпорировать блокировки на уровне строк с помощью конструкций INSERT ON CONFLICT и MERGE. Конструкция ON CONFLICT использует спекулятивную технику вставки для блокировки страницы уникального индекса и попытки вставки. Если происходит конфликт, она немедленно разветвляется в DO UPDATE или DO NOTHING, снижая частоту сеансов дедлоков.
Изменение уровня изоляции транзакций при использовании атомарных конструкций оказывает большой побочный эффект. В READ COMMITTED последующая транзакция ожидает фиксации предыдущей транзакции, а затем повторно считывает последние кортежи для безопасной обработки. Однако при повышении до REPEATABLE READ или SERIALIZABLE при возникновении конфликта с предыдущей транзакцией она без ожидания выдает ошибку сбоя сериализации и выполняет откат. Чтобы сократить затраты на откат и предотвратить исчерпание пула соединений, цикл повторных попыток следует применять только к кодам ошибок 40001 и 40P01. Добавьте случайное значение джиттера к базовому времени ожидания, чтобы разбросать моменты попыток, и ограничьте максимальное количество повторных попыток от 3 до 5 раз, прежде чем передать ошибку на верхний бизнес-уровень.
Определение момента, когда фрагментация блоков и накопившиеся мертвые кортежи снижают эффективность сканирования индекса, является отправной точкой управления хранилищем. Из-за особенностей архитектуры MVCC мертвые кортежи, созданные с помощью UPDATE или DELETE, не возвращаются ОС сразу, а остаются в виде фрагментации. При совместном рассмотрении метрики n_dead_tup в представлении pg_stat_user_tables и расширения pgstattuple, если коэффициент dead_tuple_ratio превышает 20 процентов или доля free_space составляет 30 процентов, необходимо освободить дисковое пространство. Если оставить это без внимания, чтение блоков ввода-вывода увеличится, а эффективность буферного пула будет нарушена.
Чтобы освободить хранилище в фоновом режиме без остановки сервиса, используйте утилиту pg_repack. Во-первых, создайте теневую таблицу (shadow table) целевой исходной таблицы и добавьте триггер логирования для отслеживания изменений. Во-вторых, массово скопируйте действительные кортежи в теневую таблицу, асинхронно и параллельно пересоздайте индексы, а затем замените системный каталог. В-третьих, выполните команду фонового процесса напрямую из терминала.
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
Чтобы предотвратить состязание за блокировки (lock contention), установите SET lock_timeout = '3s'; на уровне сеанса, чтобы в случае невозможности захвата блокировки в течение 3 секунд запрос не задерживался в очереди, а сразу выдавал ошибку. Добавьте опцию --wait-timeout=10, чтобы прервать ситуацию, когда последующие запросы блокируются каскадом.
Если цикл сбора статистики нарушен и план запроса внезапно меняется, это приводит к сбою в рабочей среде. В местах скопления тяжелых операций статистика и фактическое распределение данных расходятся, что приводит к переключению планов (plan flip). Если оптимизатор выбирает соединение Nested Loop вместо сканирования индекса, происходят скачки ЦП и возникают узкие места ввода-вывода. Диагностика возможна только в том случае, если добавить модуль auto_explain в shared_preload_libraries и установить log_min_duration равным 500 миллисекундам для записи фактического плана выполнения медленных запросов в системный журнал сервера.
Чтобы принудительно зафиксировать путь выполнения определенного запроса, используются модули подсказок (hints). После обнаружения проблемного запроса в логах установите расширение pg_hint_plan и добавьте комментарий перед инструкцией SQL или зарегистрируйте запрос в таблице каталога hint_plan.hints. Если исходный код сложно развернуть, можно напрямую внедрить строку статического хинта на уровне каталога, чтобы немедленно зафиксировать план без повторного развертывания. Это самый быстрый способ защитить скорость ответа на запросы в экстренных ситуациях, когда повторный сбор статистики или пересоздание индекса занимают много времени.
