max_locks_per_transaction — это ни «на транзакцию», ни «максимум». Это доля на процесс в одной общей для всего кластера таблице блокировок, и любая отдельная транзакция может взять гораздо больше своей доли, пока никто другой не использует свою. Имя вводит людей в заблуждение с 7.2, а описание в документации как «среднего числа объектных блокировок, используемых каждой транзакцией» ближе к истине, хотя вряд ли это то, что вы нанесёте на ручку настройки.
Три функции T-SQL, которые появились за последние несколько релизов, и тихо вышедшие из употребления шаблоны, которые многие из нас до сих пор используют по привычке. Каждая из них заменяет устаревший обходной путь решения задачи одной строкой кода, и в двух из трех случаев выполняется много быстрее. Посмотрите и попробуйте.
1. APPROX_COUNT_DISTINCT - когда COUNT(DISTINCT) слишком дорого обходится
Доступна, начиная с SQL Server 2019. COUNT(DISTINCT col) для столбца с высоким кардинальным числом требует от SQL Server отслеживать каждое уникальное значение, что означает выделение памяти, пропорциональное кардинальному числу, и хеш-агрегат, который любит сбрасываться в базу данных tempdb для больших таблиц. Вместо этого APPROX_COUNT_DISTINCT использует HyperLogLog, который небольшое снижение точности компенсирует значительно меньшим объемом памяти и более быстрой работой.
Продолжить чтение "Три трюка, которые вы, возможно, еще не используете"
max_index_keys считает вещи, которые не являются ключами, начиная с PostgreSQL 11. То, что он сообщает — 32, — это максимальное число столбцов в индексе, а с появлением INCLUDE столбец в индексе не обязательно является ключом: индекс с 31 ключом, несущий один INCLUDE-столбец, находится на пределе, как и индекс с одним ключом, несущий 31 такой столбец. Сообщение об ошибке говорит «столбцов» (cannot use more than 32 columns in an index), pg_controldata говорит Maximum columns in an index:, и только имя параметра всё ещё говорит о ключах.
Вопрос обычно приходит как вопрос безопасности: «Стоит ли использовать параметры привязки, или можно собрать строку SQL самому?» Ответ на этот вопрос таков: используйте параметры привязки, сборка строки — это путь к SQL-инъекции, и все мы можем спокойно жить дальше.
Вопрос производительности, скрывающийся под ним, куда интереснее, и народная мудрость о нём в основном неверна. «Подготовленные операторы пропускают планирование» — обычная версия. Это верно лишь в узком наборе обстоятельств, и когда это верно, это не всегда хорошая новость. Эта статья о том, что PostgreSQL делает с запросом, значения которого впечатаны в текст, что он делает с запросом, значения которого приходят отдельно, и как кэш планов, стоящий между этими двумя путями, принимает свои решения. Есть конкретный момент — шестое выполнение подготовленного оператора, — где два пути расходятся, и удивительно много случаев «мой запрос стал медленным, а я ничего не менял» восходит именно к нему.
max_identifier_length сообщает число 63, и это число — самое неинтересное в нём. Каждая реляционная база данных ограничивает длину имени. Необычно в PostgreSQL то, что происходит, когда вы его превышаете: MySQL, SQL Server и Oracle отвергают оператор, а PostgreSQL обрезает имя до 63 байт, упоминает об этом в NOTICE и продолжает так, будто это то, что вы имели в виду. Почти каждая реальная проблема, связанная с этим параметром, восходит к этому одному решению.
max_files_per_process — это не лимит в том смысле, в каком его понимает большинство людей, когда превышение даёт ошибку. Это размер кэша. Каждый серверный процесс держит пул открытых файловых дескрипторов ядра, и когда пул полон, а серверному процессу нужен ещё один, он закрывает наименее недавно использованный и продолжает. Ничего не ломается. Запрос просто тратит пару дополнительных системных вызовов на файл, который был открыт тридцать секунд назад.
Никто не смотрит max_function_args намеренно. Вы встречаете его, когда PostgreSQL прерывает что-то несвязанное сообщением cannot pass more than 100 arguments to a function, и вы идёте искать ручку. Её нет.
max_connections — это бюджет памяти и автоматический выключатель, носящий костюм ёмкости. Он не решает, сколько запросов ваш сервер может выполнять одновременно; это решают количество ядер и хранилище, и они были решены, когда вы покупали оборудование. max_connections решает, сколько клиентских серверных процессов PostgreSQL создаст, прежде чем начнёт говорить «нет», и, что менее очевидно, сколько разделяемой памяти он откладывает в предположении, что каждый из них может появиться.
max_active_replication_origins — это десятилетний комментарий XXX, который наконец был оплачен. Он появился в PostgreSQL 18, и он существует потому, что то, чем он управляет, с 9.5 занимало число у другого параметра. На 14–17 число источников репликации, которые может отслеживать подписчик, задаётся max_replication_slots, параметром, чья настоящая работа — на издателе; с 16 документация всё равно перечисляет его в разделе для подписчика с примечанием, что он «также применяется на отправляющем сервере, но с другим значением». Отдельный параметр был впервые предложен в 2021 году и никуда не пошёл. Он ждал 18.
Небольшая настройка, которую большинство команд либо игнорируют, либо угадывают. Вот быстрая версия, а затем полный разбор.
Ключевые моменты
Fillfactor задаёт, насколько полно Postgres упаковывает каждую страницу таблицы при записи, оставляя остальное как свободное пространство для будущих обновлений, которые попадут на место (HOT-обновления), что сокращает WAL и ввод-вывод.
Начальная формула: 100 − (средний размер строки ÷ 8 192 × 100). Получите средний размер строки из pg_relation_size() и n_live_tup, запускайте после недавнего ANALYZE.
Для таблиц с интенсивными обновлениями берите значение ниже начального числа, чтобы оставить больше места для повторных перезаписей на месте.
Не устанавливайте по умолчанию для каждой таблицы 70 или 80. Рассчитывайте из реального размера строки таблицы и характера обновлений, затем корректируйте на основе наблюдаемой доли HOT и раздувания.
maintenance_work_mem — это параметр памяти, который ведёт себя так, как люди ошибочно предполагают, что ведёт себя work_mem: одно выделение, для одной операции, в одном сеансе, и число, которое вы задаёте, — это число, которое используется. work_mem — это допуск на каждую сортировку, каждый хеш, каждого исполнителя, который один запрос может затребовать дюжину раз. maintenance_work_mem — это потолок. Документация говорит, что «безопасно устанавливать это значение значительно больше work_mem», и это одно из немногих мест, где вам дают разрешение быть щедрым, и в основном они правы. Эта статья о «в основном».
Ваша база данных работает как часы — пока простой DELETE не поставит её на колени. Что пошло не так? Хотя мы склонны сосредотачиваться на оптимизации операций SELECT и INSERT, мы часто упускаем из виду скрытую сложность DELETE. А ведь удаление ненужных данных столь же важно. Устаревшие или нерелевантные данные могут раздувать вашу базу данных, снижать производительность и делать обслуживание кошмаром. Хуже того, хранение некоторых типов данных без действительного обоснования может даже привести к проблемам соответствия.
maintenance_io_concurrency — это effective_io_concurrency для работы, которую документация описывает как выполняемую «от имени множества клиентских сеансов». Эта фраза — весь смысл дизайна: VACUUM выплачивает долг по вводу-выводу, который накопил каждый сеанс, поэтому он может разумно быть более агрессивным в упреждающем чтении, чем любой из них. Механически эти два параметра — одна и та же ручка, включая трёхэтапную историю того, что означает число, и я не буду повторять это здесь. Эта статья о том, какая работа управляется им, потому что список не таков, как подсказывает название.
logical_decoding_work_mem — это то, сколько чужой незафиксированной транзакции walsender будет держать в памяти, прежде чем начнёт записывать эту транзакцию на диск во второй раз.
