Алфавитный порядок выдал нам первую «жертву». archive_cleanup_command — это параметр резервного сервера (standby-server knob), который существует исключительно для того, чтобы прибираться после archive_command. Однако алфавит настаивает на том, чтобы отложить рассмотрение archive_command до следующей статьи. Поэтому мы опишем, как прибирать вечеринку, которую ещё не устраивали.
Кратчайшая предыстория: Первичный сервер PostgreSQL (primary) может архивировать свои сегменты WAL в некоторое место — каталог, корзину S3, общий ресурс NFS — выполняя команду оболочки для каждого заполненного сегмента. Резервные серверы (standbys) читают из этого места, чтобы догонять изменения, а инструменты резервного копирования читают из него для обеспечения восстановления на момент времени (point-in-time recovery, PITR). Файлы накапливаются. Кто-то должен их удалять.
Для хорошего бэкенд-разработчика существует определенное стремление к оптимизации запросов (скорости и памяти). Одним из полезных инструментов оптимизации баз данных являются подзапросы SQL.
Подзапрос - это запрос внутри другого запроса. Он позволяет вам вытащить связанные данные или результаты вычислений без написания нескольких запросов или тяжелых соединений. Он также позволяет динамически выполнять вычисления и агрегировать данные.
Большинство GUC в этой серии будут операционно не важны для большинства читателей. Этот — не такой. application_name — это самое дешёвое средство наблюдаемости (observability infrastructure), которое поставляет PostgreSQL, и поразительное количество производственных баз данных работают с неустановленным значением или со значением, застрявшим на значении по умолчанию клиентской библиотеки (psql, PostgreSQL JDBC Driver или, что я люблю больше всего, — пустая строка).
Это метка уровня сеанса (per-session label). Значение по умолчанию — пустая строка, контекст — user, поэтому любая роль может его установить. Установите его через SET application_name = 'order-service';, через параметр подключения application_name или через переменную окружения PGAPPNAME, которую libpq учитывает автоматически. Максимальная длина — NAMEDATALEN - 1 — 63 байта в стандартной сборке, а непечатаемые символы заменяются на ?.
Вот GUC, который поставляется с предупреждающей этикеткой. Документация, обычно сдержанная до степени пародии, прямо заявляет, что неправильная установка этого параметра может привести к «необратимой потере данных или серьёзному повреждению системы базы данных». Когда документация PostgreSQL так повышает голос — прислушайтесь.
Давайте начнем с базы данных Stack Overflow (будет работать версия любого размера), удалим все индексы на таблице Users и выполним DELETE:
SET STATISTICS IO ON;
GO
BEGIN TRAN
DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';
Я использую SET STATISTICS IO ON, о чем мы говорили в статье "Как думать подобно серверу SQL Server" для иллюстрации количества прочитанных данных, и я делаю это в транзакции, которую я могу периодически откатывать, каждый раз демонстрируя полученные эффекты. Вот действительный план выполнения:
allow_in_place_tablespaces существует для того, чтобы набор тестов PostgreSQL мог тестировать репликацию. Вот и всё. Если вы читаете это как администратор (оператор), вы никогда к нему не прикоснётесь. Но раз он есть в алфавите, вот мы здесь.
GUC — это аббревиатура от Grand Unified Configuration (Великая унифицированная конфигурация).
В контексте PostgreSQL это просто техническое название для всех параметров (настроек) сервера, которые можно менять.
Простыми словами, это переменные, которые определяют, как работает ваш экземпляр PostgreSQL.
Мы начинаем с allow_alter_system — параметра, который одновременно и новый, и политически взрывоопасный. Поэтому давайте начнём со спора.
Команда ALTER SYSTEM была добавлена в PostgreSQL 9.4 как улучшение качества жизни: возможность устанавливать GUC (Grand Unified Configuration — унифицированные параметры конфигурации) из SQL-приглашения, записывая значения в postgresql.auto.conf, без необходимости доступа к оболочке операционной системы. Однако она сразу же вызвала споры среди тех, кто управляет PostgreSQL с помощью систем управления конфигурацией. Если Ansible управляет postgresql.conf, но суперпользователь незаметно выполняет ALTER SYSTEM SET work_mem = '1GB', следующий запуск Ansible не трогает postgresql.auto.conf, и расхождение (дрейф) конфигурации сохраняется бесконечно. Никто не замечает проблемы, пока что-то не сломается.
Этот спор занял примерно десятилетие. В PostgreSQL 17 появился параметр allow_alter_system — булевый параметр, который, когда установлен в off, заставляет ALTER SYSTEM возвращать ошибку: ERROR: ALTER SYSTEM is not allowed in this environment. Значение по умолчанию — on, контекст — sighup, так что для его изменения требуется лишь перезагрузка конфигурации.
Каждый администратор баз данных держит в голове инструментарий готовых запросов. Некоторым потребовались годы, чтобы научиться. Некоторые были случайно обнаружены во время работы в 2 часа ночи. Сегодня я поделюсь 10-ю моими самыми любимыми короткими запросами T-SQL, теми, что копируешь, вставляешь и сразу чувствуешь себя гением. Некоторые из них - классические, некоторые - из недавнего пополнения, и все из них полезны. Ставьте закладку, я надеюсь, что вы сюда вернетесь.
1. Генерация числовой последовательности на лету
Нужно быстро получить последовательность чисел без создания таблицы подсчета? Если вы имеете SQL Server 2022 или более позднюю версию, GENERATE_SERIES - ваш новый лучший друг:
SELECT value FROM GENERATE_SERIES(1, 100);
Это все. Одна строка кода. 100 строк в последовательности. Никаких временных таблиц, никаких CTE, никаких перекрестных соединений. Аналогичный прием для диапазона дат. Используйте это для получения каждого дня 2025 года в одной строке:
SELECT DATEADD(DAY, value, '2025-01-01') AS dt FROM GENERATE_SERIES(0, 364);
Предыдущая статья о регрессии производительности pgbench на Linux 7.0 заканчивалась тем же указанием, которым заканчивается любая другая статья о производительности Postgres: включите huge pages. Эта статья — подробная версия. Если вы читали документацию Postgres о huge_pages, но всё ещё не совсем уверены, что вам говорит /proc/meminfo, какова связь между vm.nr_hugepages и Transparent Huge Pages, или почему huge_pages = try — неправильный выбор, то эта статья для вас.
Здесь не так много нового материала. Однако есть материал, о который люди продолжают спотыкаться, несмотря на то, что он описан в документации.
В мире баз данных существует устойчивое убеждение, что вертикальное масштабирование решает все проблемы. Нужна большая пропускная способность? Добавьте ядра ЦП. Заканчивается кэш? Добавьте оперативной памяти. Запросы обращаются к диску? Добавьте операций ввода-вывода в секунду (IOPS). Это утешительная философия, потому что она проста, и на удивление долгое время она работает. Один мощный экземпляр Postgres может выдержать колоссальную нагрузку, прежде чем рухнуть под её давлением.
Но этот потолок существует, и следует он не из аппаратного обеспечения. Postgres был спроектирован как однокомандный движок базы данных, и многие его внутренние структуры являются общими для всех баз данных, которые содержит экземпляр. Эти общие ресурсы редко вызывают беспокойство в одном скромном экземпляре. Но с двадцатью базами данных, работающими со смесью тяжёлых OLTP-нагрузок, аналитических запросов или даже в основном простаивающих, общая природа этих внутренних механизмов становится очень важной.
Давайте поговорим о барьерах, с которыми в конечном итоге сталкиваются такие перегруженные экземпляры, ссылаясь для убедительности на исходный код Postgres. Некоторые из них хорошо известны, другие — из тех, что внезапно поражают в 2 часа ночи, когда все панели мониторинга одновременно становятся красными.
PostgreSQL получил широкое распространение благодаря тому, что снимает лицензионные ограничения и даёт таким компаниям, как OpenAI, Lovable и Supabase, надёжную основу для масштабной эксплуатации производственных систем. Однако после развёртывания разговор о стоимости PostgreSQL смещается с лицензирования к тому, насколько эффективно база данных поддерживает выполняемую на ней рабочую нагрузку.
Начальные оптимизации, такие как индексы, дизайн запросов и пути доступа, часто остаются неизменными по мере развития систем. Со временем эти устаревшие решения приводят к неэффективности, вызывая замедления работы и истощение ресурсов. Вместо того чтобы устранять первопричину, команды часто масштабируют инфраструктуру и увеличивают операционные накладные расходы. В результате система продолжает функционировать, но с существенно более высокими затратами, чем необходимо. Если вы понимаете, откуда исходит неэффективность, вы можете исправить её должным образом, а не просто «забрасывать» проблему дополнительными ресурсами.
На практике это часто становится заметным по таким сигналам, как увеличение размеров инстансов с течением времени, повторяющиеся проблемы с производительностью, замедление работы отчётов по мере роста данных или увеличение времени, которое инженеры тратят на исследование поведения базы данных.
Модель хранения в PostgreSQL отличается от многих других баз данных. Вместо переписывания строки по месту, PostgreSQL создает новые версии строк при обновлении данных. Эта схема хороша для безопасности транзакций и параллелизма, но при этом влияет на размер таблицы и VACUUM приобретает важное значение для производительности.
Давайте пошагово пройдем тестовый пример, чтобы увидеть, как это работает на практике.
Отключение autovacuum (только для тестирования)
PostgreSQL обычно выполняет фоновый процесс, который называется autovacuum, для очистки мертвых кортежей и предотвращения раздувания таблиц.
Для реальных приложений выключение autovacuum не рекомендуется, поскольку это критически важно для работоспособности базы данных.
Но в целях тестирования он может быть выключен для конкретной таблицы для гарантии, что ничего не будет происходить автоматически в фоновом режиме.
