Вот 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 не рекомендуется, поскольку это критически важно для работоспособности базы данных.
Но в целях тестирования он может быть выключен для конкретной таблицы для гарантии, что ничего не будет происходить автоматически в фоновом режиме.
Таблица MySQL information_schema.processlist предоставляет большой объем информации о текущем состоянии сервера MySQL. Она важна для администраторов и разработчиков баз данных, чтобы мониторить эти данные для обеспечения оптимальной производительности и диагностики потенциальных проблем. В этой статье мы познакомимся с несколькими запросами MySQL, предназначенными для извлечения полезных идей из списка процессов, и обсудим, насколько они могут помочь нам в мониторинге и оптимизации сервера MySQL.
1. Просмотр всех процессов
Чтобы получить исчерпывающий обзор всех текущих процессов на сервере MySQL, вы можете использовать следующий запрос:
Выбор правильного формата резервной копии может составлять разницу между 10-минутным восстановлением и целым днем мучений. Утилита pg_dump в PostgreSQL предоставляет множество форматов вывода, каждый из которых оптимизирован для различных сценариев - от быстрой разработки снимков до восстановления после сбоев производственных баз. Понимание этих форматов помогает построить такую стратегию восстановления, которая сбалансирует эффективность использования хранилища, скорость восстановления и операционную гибкость. В этом руководстве рассматриваются пять ключевых форматов резервных копий с указанием, когда использовать каждую из них.
У меня есть данные, пришедшие в мой SQL Server в формате JSON. Перед началом парсинга, который довольно интенсивный, необходимо проверить, присутствуют ли некоторые значения в этом JSON. Имеется ли функция, которую я могу использовать с этой целью? Давайте посмотрим, что может делать JSON_CONTAINS, новая функция в SQL Server 2025.
В одной из предыдущих статей я описывал, как работает обеспечение целостности внешних ключей в Postgres. Краткая версия такова: каждая команда INSERT или UPDATE в таблицу, ссылающуюся на другую, запускает триггер AFTER, который проверяет, существуют ли значения столбца внешнего ключа (FK) в ссылочной (PK) таблице. Эта проверка проходит через SPI (Server Programming Interface): строится запрос, он планируется, выполняется, а затем всё уничтожается — для каждой отдельной строки.
Это дорогостоящая операция. При массовой вставке (INSERT) миллиона строк в таблицу с внешним ключом вы выполняете миллион мини-запросов к индексу таблицы первичного ключа. Каждый из них открывает отношение первичного ключа, захватывает снимок (snapshot), выполняет проверку прав, делает поиск по индексу и закрывает всё. Стоимость одной строки в абсолютном выражении невелика, но она очень быстро накапливается.
Для Postgres 19 я применил два патча (соавтором обоих является Джунванг Жао), которые полностью обходят SPI для стандартного случая и выполняют пакетную (batch) проверку индекса. Вместе они ускоряют массовые вставки с внешними ключами примерно в 2.9 раза в используемом мною тесте (int первичный ключ, int внешний ключ, 1 миллион строк, таблица первичного ключа и её индекс в памяти).Что деаёт быстрый путь (fast path)
Continue reading "Postgres: более быстрые проверки внешних ключей"
