Postgres Guru | Базы данных 💐

🛠️ Мало известные параметры команды EXPLAIN. Про команду EXPLAIN мы писали в канале много раз. И как про нее писать, если это один из основных инструментов оптимизации запросов? Но у этой команды есть еще несколько полезных параметров, о которых не все знают, а один из параметров появился не так давно. 📌 MEMORY: сколько памяти использовал запрос Это новая возможность, появившаяся в версии 18. Она отличается от знакомого BUFFERS тем, что отслеживает объём памяти, потреблённый на этапе планирования запроса, а не его выполнения. Вывод появляется внизу результата EXPLAIN, например: EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM mytable WHERE id = 123; Planning: Buffers: shared hit=36 read=1 Memory: used=63kB allocated=64kB 📌 WAL: насколько активно логирование? Ещё одна полезная опция, которую многие упускают из виду - WAL: EXPLAIN (ANALYZE, WAL) INSERT INTO mytable SELECT * FROM staging_table; WAL: records=100, fpi=5, bytes=45000 В примере выше records - это количество сгенерированных записей WAL, fpi (full-page images) - количество записанных образов целых страниц (страницы, изменённые впервые после последней контрольной точки), а bytes - общий объём данных WAL, созданный запросом. Это может быть полезно при исследовании нагрузок, интенсивно работающих с записью: массовые вставки, большие обновления, создание индексов и высокая нагрузка репликации. 📌 SETTINGS: в каком окружении выполнялся запрос? Иногда один и тот же SQL-запрос ведёт себя по-разному на двух серверах, или вы могли временно изменить некоторые параметры локально (например, work_mem). Чтобы правильно понять, как на запрос влияют различия в окружении, может быть полезна опция SETTINGS: EXPLAIN (SETTINGS) SELECT * FROM mytable WHERE id = 123; Settings: effective_cache_size = '48GB', effective_io_concurrency = '200', enable_partitionwise_aggregate = 'on', enable_partitionwise_join = 'on', max_parallel_workers = '16', max_parallel_workers_per_gather = '4', temp_buffers = '1MB', search_path = 'public' 📌 VERBOSE: вся история планировщика. Ещё одна полезная опция - VERBOSE. Она выводит дополнительную информацию: внутренние ссылки на столбцы, развёрнутые списки целевых столбцов и объекты со схемами. Хотя вывод с этим параметром будет выглядеть немного перегруженным, VERBOSE помогает при диагностике: ✅ разворачивания представлений (view expansion); ✅ перезаписи правил (rule rewriting); ✅ сложных преобразований запросов. Ну и конечно, мы можем комбинировать в EXPLAIN все выше приведенные параметры, для вывода более детельной информации по интересующим нас вопросам. На этом все! До связи! #queries

🌐 PostgreSQL 19 - самые заметные нововведения (ч. 2) Всем привет! Продолжаем знакомить вас с новшествами и улучшениями, которые будут в PostgreSQL 19. 📊 Производительность: 1. Значительное ускорение работы функции jsonb_agg, особенно для типов integer и numeric; 2. Оптимизация LISTEN/NOTIFY: - Добавлена хеш-таблица для хранения соответствия каналов и получателей; - Увеличена производительность доставки уведомлений в несколько раз. 3. Оптимизация функций ICU для преобразования регистра: - Ускорение функций upper, lower для баз данных с кодировкой UTF-8 и правилами сортировки ICU. 🧾 Работа с секционированными таблицами: 1. Команды MERGE PARTITIONS и SPLIT PARTITION: - Позволяют объединять и разделять секции секционированных таблиц; - Ограничение: Получают исключительную блокировку на всё время выполнения. 2. Поддержка COPY ... TO для секционированных таблиц: - Теперь можно использовать команду COPY ... TO напрямую с секционированными таблицами; - Также используется для начальной синхронизации в логической репликации. 3. Сбор статистики для секционированных таблиц в vacuumdb: - Утилита vacuumdb с параметрами --analyze-only и --analyze-in-stages теперь собирает статистику и для секционированных таблиц. 🎚 Мониторинг и отладка: 1. Мониторинг задержек синхронизации слотов логической репликации: - Добавлены столбцы slotsync_skip_reason, slotsync_skip_count и slotsync_last_skip в представления pg_replication_slots и pg_stat_replication_slots. 2. Улучшения в мониторинге выполнения VACUUM и ANALYZE: - В pg_stat_progress_vacuum добавлены столбцы mode (normal/aggressive/failsafe) и started_by (manual/autovacuum/autovacuum_wraparound); - В pg_stat_progress_analyze добавлен столбец started_by. 3. Статистика использования мультитранзакций: - Новая функция pg_get_multixact_stats() возвращает агрегированную статистику по мультитранзакциям. 4. Мониторинг объема записанных в WAL полных образов страниц: - Добавлен столбец wal_full_images в представление pg_stat_wal. 5. Новый параметр log_autoanalyze_min_duration: • Позволяет журналировать длительные операции автоматического анализа таблиц. 🔃 Логическая репликация: 1. Синхронизация последовательностей: - Подписчики логической репликации теперь могут синхронизировать значения последовательностей с сервером публикации; - Управляется вручную с помощью команды ALTER SUBSCRIPTION ... REFRESH SEQUENCES. 2. Динамическое управление уровнем логического декодирования WAL: - Уровень записи в WAL (wal_level) теперь определяется динамически в зависимости от наличия слотов логической репликации; - Текущий фактический уровень показывает параметр effective_wal_level. 3. Команда WAIT FOR LSN: - Позволяет дождаться применения заданной позиции LSN на реплике; - Полезно для гарантии актуальности данных на реплике перед чтением. ⚒️ Утилиты и инструменты: 1. Параметр --dry-run в vacuumdb: - Показывает, какие команды очистки и анализа будут выполнены, не отправляя их на сервер. 2. Параметр FLUSH_UNLOGGED в CHECKPOINT: - Позволяет сбрасывать на диск изменённые буферы нежурналируемых объектов; - Ускоряет перезагрузку сервера с нежурналируемыми таблицами. 3. Параметр MODE в CHECKPOINT: - Поддерживает режимы FAST и SPREAD для управления нагрузкой на систему; - SPREAD позволяет выполнять контрольную точку растянуто, учитывая checkpoint_completion_target. ♻️ Буферный кеш и память: 1. Отказ от списка свободных буферов: - Для поиска свободных буферов теперь используется алгоритм clock-sweep; - Упрощает последующие оптимизации для поддержки NUMA. 2. Представление pg_buffercache: - Добавлен столбец os_page_num, показывающий распределение буферов по страницам ОС. 3. Мониторинг использования памяти в VACUUM: - Команда VACUUM (VERBOSE) теперь выводит информацию об использовании памяти для хранения идентификаторов мёртвых строк. #pgnews

🌐 PostgreSQL 19 - самые заметные нововведения. Пока мы тут неизвестно чем занимались, рабочая группа PostgreSQL уже закончила четыре комитфеста и пятый в самом разгаре 😱 Мы собрали самые значительные (на наш взгляд) нововведения из этих комитфестов. Если вы хотите более подробно почитать о новинках PostgreSQL 19, то можете обратиться к замечательным статьям Павла Лузанова из компании Postgres Pro: 1️⃣https://habr.com/ru/companies/postgrespro/articles/984580/ 2️⃣https://habr.com/ru/companies/postgrespro/articles/986694/ 3️⃣https://habr.com/ru/companies/postgrespro/articles/992426/ 4️⃣https://habr.com/ru/companies/postgrespro/articles/996010/ Нововведений много, по этому в один пост все не поместиться, продолжаем в следующем. 🔧 Подключение и удобство работы 📌 servicefile в libpq и переменная :SERVICEFILE в psql - имя файла служб соединения можно указывать непосредственно в строке подключения; в psql переменная возвращает текущий файл службы; 📌 Тип‑псевдоним regdatabase – преобразует имя базы данных в её OID и обратно (как regclass, regtype и др.); 📌 Группировка GROUP BY ALL - ключевое слово ALL автоматически включает в GROUP BY все неагрегированные выражения из SELECT; добавляя новое выражение в SELECT больше не нужно править GROUP BY; 📌 Случайная дата/время в заданном диапазоне - функция random() теперь принимает два значения типа date, time, timestamp/timestamptz и возвращает равномерно распределённое значение в этом интервале; 📌 file_fdw: пропуск начальных строк - аналогично опции header в COPY, внешняя таблица file_fdw получила параметр header = N, указывающий сколько первых строк файла пропустить перед загрузкой данных. 📊 Мониторинг, статистика и отладка 📌 Расширенная статистика в pg_stat_statements • новые счётчики generic_plan_calls / custom_plan_calls - показывают, сколько раз использовался общий и индивидуальный план; • нормализация команды FETCH (размер выборки заменяется константой) - разные FETCH N попадают в одну строку статистики; • нормализация IN со списком параметров - запросы WHERE col IN ($1,$2,…) получают одинаковый идентификатор; 📌 Оценки планировщика для узла Memoize в EXPLAIN - строка Estimates (capacity, distinct keys, lookups, hit %) позволяет сравнить планировщику прогноз с фактическими показателями (Hits/Misses и т.д.) при EXPLAIN ANALYZE; 📌 Функция error_on_null - полиморфная функция, возвращающая свой агрумент, если он не равен NULL, иначе генерирующая ошибку «null value not allowed»; 📌 pg_dsm_registry_allocations - новое системное представление, показывающее содержимое динамической общей памяти (DSM): имя, тип, размер сегментов; 📌 pg_available_extensions (столбец location) - в представлениях pg_available_extensions и pg_available_extension_versions добавлен столбец, показывающий каталог установки каждого расширения (значение $system - каталог по умолчанию). До встречи в будущих постах ☺️ #pgnews

🌐 PostgreSQL 14 мая 2026 г.: обновления и исправления безопасности. Всем привет! Надеюсь вы как и мы соскучились 😁 14 мая 2026 года PostgreSQL Global Development Group выпустила экстренное обновление для всех поддерживаемых веток СУБД, а также напомнила о скором прекращении поддержки PostgreSQL версии 14. Поддержка этой версии закончится 12 ноября 2026 года. Что вошло в релиз: ⚠️ 11 уязвимостей (CVE), исправлено в этом обновлении. Четыре из них получили оценку CVSS 8.8 и допускают практическую эксплуатацию: 1️⃣ CVE‑2026‑6473 - сервер PostgreSQL занижает размер выделяемых ресурсов с помощью целочисленного обхода. Целочисленный обход в нескольких функциях сервера PostgreSQL позволяет поставщику входных данных приложения заставлять сервер занижать размер выделяемых ресурсов и выводить данные за пределы допустимых значений. 2️⃣ CVE‑2026‑6475 - PostgreSQL pg_base backup и pg_rewind могут перезаписывать несвязанные файлы по выбору суперпользователя. Символическая ссылка в PostgreSQL pg_basebackup в обычном формате и в pg_rewind позволяет суперпользователю перезаписывать локальные файлы, например /var/lib/postgres/.bashrc, которые перехватывают учетную запись операционной системы. 3️⃣ CVE‑2026‑6477 - функции PostgreSQL libpq lo_* позволяют суперпользователю сервера перезаписывать память клиентского стека. Использование потенциально опасной функции PQfn(..., result_is_int=0, ...) в функциях PostgreSQL libpq lo_export(), lo_read(), lo_lseek64() и lo_tell64() позволяет суперпользователю сервера перезаписывать клиентский буфер стека с помощью ответа произвольного размера. 4️⃣ CVE‑2026‑6477- PostgreSQL refint допускает переполнение стекового буфера и SQL-инъекцию. Переполнение стекового буфера в модуле PostgreSQL refint позволяет непривилегированному пользователю базы данных выполнять произвольный код в качестве пользователя операционной системы, запускающей базу данных. Оставшиеся 8 CVE охватывают: 📌 несколько случаев раскрытия информации (memory disclosure); 📌 уязвимости типа denial‑of‑service (DoS), связанные с некорректной обработкой сложных запросов и слотов репликации; 📌 проблемы с проверкой прав доступа при работе с логическими слотами репликации и временными таблицами. Помимо исправлений безопасности, релиз включает более 60 баг‑фиксов, затрагивающих: 📌 планировщик запросов (улучшена оценка стоимости для сложных JOIN‑ов); 📌 репликацию (исправлена проблема при переключении лидерa в потоковой репликации, когда реплика могла читать старые данные); 📌 VACUUM и autovacuum (повышена эффективность очистки больших таблиц); 📌 работу с JSONB и массивами (исправлены редкие случаи повреждения данных при конкурентном обновлении данных). Рекомендуется применить обновление как можно скорее, так как три из уязвимостей имеют высокую степень критичности и допускают удалённое выполнение кода без аутентификации. В итоге выпуск от 14 мая 2026 года является одним из самых крупных пакетов безопасности в истории PostgreSQL: кроме критических исправлений, он привносит значительные улучшения производительности и стабильности во всех поддерживаемых версиях. Обновление рекомендуется к незамедлительному применению на всех рабочих и тестовых кластерах. Более подробно об обновлении читаем на официальном сайте: ➡️ https://www.postgresql.org/about/news/postgresql-184-1710-1614-1518-and-1423-released-3297/ Постараемся выходить более регулярно, несмотря ни на что! 😁💪 #pgnews

Channel name was changed to «Postgres Guru | Базы данных 💐»

🌐 Обновления PostgreSQL 18.3, 17.9, 16.13, 15.17 и 14.22. Вчера 26.02.2026 вышло обновление PostgreSQL для текущих, актуальных релизов. Само по себе обновление не закрывает каких-то критических дыр в безопасности, как это часто бывает, но устраняет много достаточно неприятных багов. ✅ Исправлена проблема, из-за которой резервный сервер (standby) останавливался с ошибкой "could not access status of transaction" (не удалось получить доступ к статусу транзакции); ✅ Исправлена ошибка, из-за которой функция substring() вызывала ошибку "invalid byte sequence for encoding" (недопустимая последовательность байтов для кодировки) для не-ASCII текстовых значений, если источником этого значения был столбец базы данных. Это было вызвано изменением, внесенным для исправления уязвимости CVE-2026-2006; ✅ Исправление для функции strict_word_similarity в модуле pg_trgm, которое могло приводить к неверным результатам или сбоям. Это произошло из-за упущения в исправлении для уязвимости CVE-2026-2007; ✅ Исправлена волатильность функций json_strip_nulls() и jsonb_strip_nulls() — теперь они снова являются неизменяемыми (immutable), как в предыдущих релизах, что позволяет использовать их в индексах. Если вы ранее обновились до версий PostgreSQL 18.0–18.2, ознакомьтесь с дополнительными действиями в разделе "Обновление"; ✅ Исправление для проверок NOT NULL в LATERAL подзапросе с UNION ALL, которое могло приводить к неверным результатам запроса; ✅ Предотвращение возникновения конфликтов имен между ограничениями NOT NULL, созданными системой, и ограничениями, созданными пользователем; ✅ Исправлена работа функций pg_stat_get_backend_wait_event() и pg_stat_get_backend_wait_event_type() — теперь они корректно отображают значения для вспомогательных процессов, аналогично pg_stat_activity; ✅ Исправлено приведение составного типа (composite-type) переменной к типу-домену (domain type) при возврате её значения из PL/pgSQL функции; ✅ Исправлена функция двоичного ввода (binary input) для типа hstore - предотвращены сбои при вводе данных с дублирующимися ключами. Остаёмся на связи! #pgnews

🤬 Утилита для восстановления данных PostgreSQL в критических ситуациях (PDU). Всем привет! Сегодня поговорим о замечательной утилите, которая может нас спасти в критической ситуации повреждения данных PostgreSQL и отсутствия бэкапов. PDU (PostgreSQL Data Unloader) — это профессиональный инструмент для аварийного восстановления и извлечения данных с открытым исходным кодом для баз данных PostgreSQL (версий 14-18). Он читает файлы данных PostgreSQL напрямую, без необходимости запущенного экземпляра базы данных, что делает его идеальным решением для аварийного восстановления и экстренного извлечения данных. Официальный GitHub утилиты: ➡️ https://github.com/wublabdubdub/PDU-PostgreSQLDataUnloader PDU поможет нам в следующих критических сценариях: ❌ Полное повреждение базы данных - не запускается, невозможно восстановить; ❌ Случайное DELETE/UPDATE - критически важные данные исчезают за секунды; ❌ Удаление файлов данных - повреждение файловой системы или ошибка оператора. Традиционные инструменты, такие как pg_filedump, pg_dirtyread и pg_waldump, решают каждую проблему по отдельности и имеют разные пороги вхождения. PDU объединяет все эти сценарии в одном интуитивно понятном инструменте. Преимущества PDU. ✅ Простота и унификация. Два файла (pdu + pdu.ini), ноль зависимостей. Один инструмент для решения проблем повреждения, удаления и обновления. ✅ Безопасность и мгновенность. 100% операций только для чтения. Ваши исходные файлы данных остаются нетронутыми. Восстанавливайте данные быстрее, чем традиционными методами PITR. ✅ Уникальность и совместимость. Мощные возможности прямого чтения файлов данных. Поддержка PostgreSQL 14-18. Возможности PDU. PDU справляется с тремя критическими сценариями аварий с помощью двух методов восстановления: ❌ Повреждение базы данных. PostgreSQL не запускается из-за повреждения каталога или файлов. ✅ Извлечение данных напрямую из поврежденных файлов. ❌ Случайное DELETE/UPDATE. ✅ Воспроизведение архивов WAL для восстановления исходных данных. ❌ Потеря файлов данных. ✅ Восстановление данных из оставшихся файлов. Два метода восстановления. 1️⃣ Прямое извлечение из файлов данных: · Прямой разбор файлов кучи (heap) и TOAST PostgreSQL; · Полный обход ядра базы данных; · Быстрый разбор данных TOAST; 2️⃣ Сканирование архивов WAL: · Анализ журналов упреждающей записи (WAL) для получения истории транзакций; · Извлечение удаленных строк или значений до обновления; · Фильтрация по времени и транзакциям. Полное руководство по установке и настройке PDY можно найти здесь: ➡️ pduzc.com/quickstart Примеры использования. Сценарий 1: Извлечение данных из поврежденной базы данных ❌ Проблема: PostgreSQL не запускается из-за поврежденного системного каталога. Решение: PDU> b; PDU> use mydb; PDU> set public; PDU> unload sch; ✅ Результат: CSV-файлы экспортированы в текущую директорию, даже если PostgreSQL не запускается. Сценарий 2: Восстановление удаленных записей ❌ Проблема: Был выполнен критический оператор DELETE, и вам нужны удаленные строки обратно. Решение: PDU> use production; PDU> set public; PDU> scan orders; PDU> restore del orders; ✅ Результат: Удаленные строки восстановлены из архивов WAL и экспортированы в CSV. Сценарий 3: Отмена случайного UPDATE ❌ Проблема: Оператор UPDATE изменил сотни строк на неверные значения. Решение: PDU> scan users; PDU> restore upd users; ✅ Результат: Исходные значения до выполнения оператора UPDATE восстановлены из WAL. Больше сценариев применения PDU : pduzc.com/docs/instant-recovery До использования этой утилиты лучше, конечно не доводить, но в критической ситуации она хотя бы позволит вам вытащить данные из поврежденной базы и загрузить их в новую. На этом все! До связи! #pgutils

🔬 Статистика в PostgreSQL (часть 3). Всем привет! В этой заметке мы уже наконец закончим разбираться с устройством статистики в PostgreSQL. В предыдущей заметке мы остановились на расширенной статистике и ее типах. Подходить к выбору типа расширенной статистики надо с умом, в зависимости от того какие операции используются в запросах. Выбор типа расширенной статистики Тип расширенной статистики, которую вы будете создавать, зависит от используемых операций: 📌 Если вы используете только оператор =, используйте dependencies (зависимости); 📌 Если у вас есть операторы GROUP BY, вам понадобится ndistinct (количество уникальных значений); 📌 Если вы используете скалярные операторы (<, >, <=, >=), вам нужен MCV list (список наиболее частых значений). Ограничения расширенной статистики Поскольку гистограммы не поддерживаются в расширенной статистике, она точна только: 1️⃣ Для значений из списка MCV (наиболее частые значения); 2️⃣ Если остальная часть вашего набора данных распределена равномерно. Если Postgres не может использовать расширенную статистику, она вернется к статистике по умолчанию. Это можно улучшить, увеличив параметр - цель сбора статистики (statistics target), либо для конкретного столбца, либо для всей базы данных. 📌 default_statistics_target: значение по умолчанию - 100, может варьироваться от 1 до 10000. Изменение целей сбора статистики может улучшить ситуацию, но для очень редких значений вы снова увидите расхождения между расчетными и фактическими количествами строк. Не рекомендуется просто выставлять максимальное значение цели, так как это сделает выполнение ANALYZE и VACUUM медленным и ресурсоемким. Бавыет, что мы запрашиваем более редкие значения из базы по сравнению с распространенными данными. Поэтому чуть-чуть медленных запросов в вашей базе данных, вполне допустимое явление. Если вы любите поковыряться во внутренностях, то вот где можно посмотреть в исходниках PostgreSQL различные вещи, касаемые статистики: 📌 Алгоритм для подсчета селективности каждого запроса: src/backend/utils/adt/selfuncs.c 📌 Как оптимизатор комбинирует селективность для AND/OR/JOIN: src/backend/optimizer/path/clausesel.c 📌 Алгоритм подсчета стоимости запроса: src/backend/optimizer/path/costsize.c На этом по статистике в PostgreSQL все! Надеемся, что теперь вы имеете полное представление о том, как она работает ☺️ До встречи в следующих заметках! #pgbase

🔬 Статистика в PostgreSQL (часть 2). Всем привет! Продолжаем углубляться в статистику PostgreSQL! В прошлой заметке мы остановились на селективности. Postgres собирает следующие элементы: 1️⃣ Сумму селективностей всех MCV (sumcommon); 2️⃣ Долю NULL-значений (nullfrac); 3️⃣ Селективность MCV (mcv_select): Это сумма частот MCV, соответствующих условию; 4️⃣ Селективность гистограммы (hist_select): Это процент корзин, соответствующих условию. Гистограмма состоит из "корзин" (buckets). Каждая корзина должна содержать примерно одинаковый процент строк. Чтобы получить это, Postgres перебирает гистограмму и подсчитывает количество подходящих корзин. Селективность гистограммы будет равна (количество_совпадений) / (количество_корзин). Затем Postgres использует все это для расчета селективности условия: 1️⃣ Инициализация селективности: selec = 1.0 - nullfrac - sumcommon; 2️⃣ Объединение с селективностью гистограммы: selec *= hist_selec; 3️⃣ Объединение с селективностью MCV: selec += mcv_select. Объединение условий. В большинстве запросов более одного условия WHERE. Планировщик оценивает селективность для каждого столбца отдельно и перемножает их. По умолчанию Postgres предполагает, что столбцы независимы при оценке результатов запроса. Это может привести к неточным оценкам. Расширенная статистика. Вы можете вручную указать Postgres связать два столбца. Postgres поддерживает расширенную статистику, включая: ✅ Функциональные зависимости; ✅ Многомерные подсчеты уникальных значений; ✅ Многомерные наиболее частые значения. Создание статистики работает в две части: 1️⃣ Добавление статистики по столбцам и таблице с помощью CREATE STATISTICS; 2️⃣ Запуск ANALYZE для таблицы. Зависимости (Dependencies). Функциональная зависимость описывает зависимость между двумя столбцами. Она может возникать из-за связи между ними (например, город и страна) или потому что значения двух столбцов изменяются вместе (столбец a = столбец b + 1). Ndistinct По умолчанию Postgres предполагает, что количество уникальных значений в группируемых столбцах независимо. Это может привести к некорректным оценкам мощности, когда столбцы фактически коррелируют. Коррелированные столбцы часто встречаются в наборах данных типа "родитель-потомок", как, например, category и title, где каждая категория имеет только ограниченный набор значений заголовков. Когда вы создаете статистику с использованием ndistinct, Postgres собирает и сохраняет информацию о том, сколько уникальных значений существует в комбинации столбцов. Это помогает улучшить оценки планировщика для запросов, включающих GROUP BY, DISTINCT или условия фильтрации по нескольким столбцам. Многомерные MCV (Most Common Values) Статистика MCV (наиболее частые значения) помогает Postgres оптимизировать планирование запросов, отслеживая наиболее часто встречающиеся значения в одном или нескольких столбцах. Эта статистика повышает точность оценки селективности, особенно для запросов с фильтрами вида WHERE column = value. По умолчанию Postgres автоматически собирает статистику MCV для отдельных столбцов, но вы можете вручную добавить статистику для коррелированных столбцов. MCV фиксирует наиболее часто встречающиеся пары (а не только отдельные значения). Это помогает планировщику точнее оценивать количество строк при фильтрации по этим столбцам. На этом пока все! Будет еще одна заметка из этой серии. До связи! #pgbase

🔬 Статистика в PostgreSQL (часть 1). Все мы прекрасно знаем зачем нужна статистика в PostgreSQL, но как работает статистика изнутри, что собирается и как рассчитывается нам может быть уже неизвестно. Работает, собирается, да и ладно ☺️ Но более глубокие знания работы статистики в PostgreSQL могут нам помочь как и в оптимизации запросов, так и в устранении неполадок. Поэтому решили выпустить цикл заметок по более углубленному изучению статистики в PostgreSQL. Погнали! Postgres отлично справляется с оптимизацией запросов. Собирая данные во внутренних статистических таблицах, Postgres перед выполнением запроса оценивает множество факторов: например, будет ли сканирование по индексу эффективнее последовательного сканирования, как выбирать данные для оператора WHERE и т.д. Что Postgres не знает - это то, как ваши столбцы связаны друг с другом. Postgres — не алгоритм машинного обучения. Он не будет со временем изучать взаимосвязи в данных на основе ваших запросов. Он использует одни и те же статистические вероятности, независимо от содержимого ваших столбцов. Но вы можете помочь планировщику. Вы можете добавить статистику по таблицам и рассказать Postgres о ваших данных, чтобы повысить производительность запросов. Об этом поговорим уже в следующей заметке. А пока давайте разберем, как работает статистика таблиц в PostgreSQL изнутри. Какая статистика собирается. Postgres собирает статистику по вашим таблицам при выполнении команды ANALYZE или при автоматическом запуске автоочистки (AUTOVACUUM). Для каждого столбца, в зависимости от его типа, собирается следующая статистика: ✅ Уникальные значения (Distinct values): Оценка количества уникальных значений в столбце; ✅ Средняя ширина данных (Average data width): Типичный размер значений в столбце; ✅ Доля NULL-значений (Null fraction): Доля значений NULL в столбце; ✅ Корреляция (Correlation): От -1 до 1. Описывает корреляцию между физическим порядком строк и порядком значений этого столбца. Например, если вы используете serial bigint для id, корреляция будет ближе к 1, чем при использовании uuid; ✅ Наиболее частые значения (Most common values, MCV) и их частоты (frequencies); ✅ Гистограммы (Histograms): Описывают распределение данных за пределами наиболее частых значений. У PostgreSQL есть представление pg_stats, которое дает нам удобную версию таблицы pg_statistics. Таблица pg_statistics оптимизирована под экономию места на диске и неудобна для прямого просмотра. Как Postgres использует статистику при планировании запроса. Postgres использует статистику для оценки стоимости запроса и выбора наиболее эффективного плана выполнения. Он учитывает: 📌Сколько строк будет возвращено; 📌 Общий объем данных; 📌 Количество страниц на диске, которые необходимо просканировать. Селективность (Selectivity) - это доля строк, которые вернет запрос. Планировщик запросов полагается на оценки селективности, чтобы определить, например, использовать индексное сканирование или последовательное сканирование. Если запрос фильтрует большую часть строк, предпочтительнее индексное сканирование. Если запрос возвращает большую часть строк, лучше последовательное сканирование. Селективность одного условия будет рассчитываться по следующему алгоритму: 📌 Для WHERE column = value. Если значение есть в списке MCV, Postgres использует сохраненную частоту. Если нет, он предполагает равномерное распределение не-MCV значений и оценивает селективность соответствующим образом; 📌 Для скалярных запросов (WHERE column < value или WHERE column > value). Оптимизатор использует как MCV, так и гистограммы для оценки селективности. На этом пока все! До встречи в следующих заметках! ☺️ #pgbase

🔬 PostgreSQL 18 и поддержка NUMA (libnuma). Всем привет! Надеюсь все пришли в себя после длинных выходных и готовы воспринимать информацию ☺️ В PostgreSQL версии 18 была представлена начальная поддержка архитектуры NUMA (Non-Uniform Memory Access, неоднородный доступ к памяти), которая реализована с использованием библиотеки libnuma. Чтобы эта поддержка работала, PostgreSQL должна быть собрана с опциец конфигурации --with-libnuma. Эта функция помогает оптимизировать производительность на многопроцессорных серверах, управляя распределением памяти между различными узлами NUMA. Ключевые моменты поддержки PostgreSQL libnuma: 1️⃣ До версии 18 управление производительностью PostgreSQL в системах с NUMA в значительной степени зависело от конфигурации на уровне операционной системы (например, использование numactl с политикой чередования interleave и включение огромных страниц(huge pages)). PostgreSQL 18 интегрировал явную поддержку NUMA на уровне приложения, что позволяет осуществлять более детальный контроль и потенциально приводит к значительному повышению производительности, особенно на серверах с несколькими процессорными сокетами; 2️⃣ Чтобы включить эту поддержку, PostgreSQL должен быть скомпилирован из исходного кода с использованием флага --with-libnuma. Если сервер собран с этой поддержкой, SQL-функция pg_numa_available() будет возвращать true; 3️⃣ Поддержка libnuma вводит новые системные представления для наблюдения: 📌 pg_shmem_allocations_numa: Показывает, как распределены аллокации общей памяти по узлам NUMA; 📌 pg_buffercache_numa: Предоставляет информацию о NUMA, связанную с кешем общих буферов; 📌 pg_numa_available(): Функция для проверки, была ли поддержка NUMA включена при компиляции; 4️⃣ Цель интеграции libnuma — обеспечить, чтобы процессы обращались к памяти, которая физически "ближе" к ядру процессора, на котором они выполняются. Это уменьшает задержки, связанные с доступом к удалённой памяти через межсоединение (основное узкое место в архитектурах NUMA); 5️⃣ Даже с новой поддержкой libnuma общие рекомендации по запуску PostgreSQL в системах NUMA по-прежнему включают включение огромных страниц (huge pages) в ОС и конфигурации PostgreSQL (huge_pages = on) для эффективного управления памятью. Однако новая встроенная поддержка меняет некоторые предыдущие лучшие практики, касающиеся политик NUMA на уровне ОС, таких как чередование (interleave). В общем и целом новая поддержка libnuma в PostgreSQL должна упростить настройку производительности на многопроцессорных серверах, так что можно тестировать и начинать пользоваться! Всем легкой рабочей недели! До связи! #pgbase

Сейчас в тренде подводить итоги года канала. Вот и мы решили не отставать. Наши итоги года не сильно выдающиеся, но мы и не гнались за цифрами. Все чего мы хотели - это приносить реальную пользу нашими постами всем DBA, кто нас читает. И надеемся, что нам это удалось! ☺️ Всех с наступающим Новым Годом! Успехов и стабильной работы ваших СУБД в новом году. Мы в свою очередь постараемся приносить вам еще больше пользы и интересных постов в новом году!

Channel name was changed to «Postgres Guru | Базы данных 🎅»

🤖 Храним большие базы данных PostgreSQL правильно. Базы данных PostgreSQL имеют свойство незаметно расти. Однажды вы замечаете, что запросы замедлились, резервное копирование длится вечность, и никто уже не помнит, что хранится в той таблице на 500 ГБ. В заметке рассмотрим как бороться с разрастанием базы данных, какие у нас для этого есть методы и возможности. 📌 Партиционирование. Если в вашей базе есть одна гигантская таблица, которая продолжает расти, партиционирование может стать спасением. С 10-й версии PostgreSQL поддерживает нативное декларативное партиционирование, а в последних версиях (13 и выше) работать с ним стало намного проще. Вам не нужны никакие расширения - вы можете определить родительскую таблицу и разделить её по диапазону, списку или хешу. Например, для данных, зависящих от времени: CREATE TABLE events ( id serial, created_at timestamptz, payload jsonb ) PARTITION BY RANGE (created_at); Полезность партиционирования заключается в том, что оно улучшает планирование запросов и делает удаление старых данных простым (удаление партиции происходит быстрее и чище, чем операция DELETE). Чтобы существующую таблицу превратить в партицированную, можно сделать так: 1️⃣ Создать партиционированную версию таблицы; 2️⃣ Перенести данные партиями с помощью скриптов; 3️⃣ Добавить триггеры для новых вставок в партиционированную версию; 4️⃣ Поменять местами старую и новую таблицы после завершения миграции. ⚠️ Важно: Создавайте индексы на самой партиционированной таблице - PostgreSQL автоматически создаст соответствующие индексы на всех партициях, включая те, что будут добавлены в будущем. 📌 Сжатие. PostgreSQL использует механизм TOAST (The Oversized-Attribute Storage Technique) для обработки больших значений полей, таких как text, jsonb и bytea. По умолчанию он сжимает их с помощью алгоритма pglz. Начиная с PostgreSQL 14, вы можете явно выбирать алгоритм сжатия для каждого столбца - если ваша сборка включает поддержку LZ4 (--with-lz4), вы можете использовать более быстрый и часто более эффективный алгоритм lz4: CREATE TABLE logs_lz4 ( id serial PRIMARY KEY, message text COMPRESSION lz4 ); 📌 Сжатие резервных копий. Хотя это не относится к хранению таблиц, помните, что pg_dump также поддерживает сжатие. Самый современный метод сжатия - это zstd, а базовые - gzip и lz4: # pg_dump --format=custom --compress=zstd:3 > dump.zstd Это отличный вариант для архивирования "холодных" данных за пределами базы данных с экономией дискового пространства. 📌 Архивация старых данных вне PostgreSQL. Вам не обязательно хранить всё в основном экземпляре PostgreSQL вечно. "Холодные" данные можно переместить в более дешёвые и медленные системы хранения - и PostgreSQL всё ещё сможет их запрашивать. Самый распространённый подход - использование обёрток для сторонних данных (FDW), например: ✅ postgres_fdw для выгрузки старых партиций в другой экземпляр PostgreSQL; ✅ file_fdw для доступа к данным в CSV; ✅ pg_parquet для запросов к файлам Parquet в локальном или облачном хранилище. Мораль сей заметки такова: за вашей базой данных нужно обязательно приглядывать, чтобы со временем не столкнуться с большими проблемами и медленными запросами. Если у вас в базе есть быстро растущие таблицы, то партицирование будет вашим первым помощником. С наступающим Новым Годом!!! 🎄 🎉 #pgmonitor

🤖 Используем ИИ в PostgreSQL (расширение pg_ai_query). На фоне бума развития искусственного интеллекта, неудивительно, что ИИ появляется и в нашей любимой PostgreSQL. Недавно появилась стабильная версия расширения pg_ai_query, которое позволяет использовать ИИ прямо в внутри PostgreSQL. Официальный GitHub расширения: ➡️ https://github.com/benodiwal/pg_ai_query Основные возможности расширения: ✅ Описание SQL запросов на естественном языке (пока только на английском): преобразование описаний на естественном языке в корректные SQL-запросы PostgreSQL; ✅ ИИ-анализ запросов: анализ производительности запросов с помощью EXPLAIN ANALYZE и получение рекомендаций по оптимизации; ✅ Автоматическое обнаружение схемы: расширение автоматически анализирует структуру базы данных для понимания таблиц, связей и ограничений; ✅ Поддержка нескольких ИИ-провайдеров: работа с моделями OpenAI (GPT-4, GPT-3.5), Anthropic (Claude) и Google (Gemini); ✅ Интеллектуальная генерация запросов: создание оптимизированных запросов с правильными JOIN, WHERE и LIMIT условиями; ✅ Оптимизация производительности: получение ИИ-рекомендаций по улучшению запросов и предложений по индексам; ✅ Безопасность: встроенная защита от опасных операций и несанкционированного доступа к системным таблицам; ✅ Настраиваемость: гибкая система конфигурации с поддержкой API-ключей, выбора моделей и логирования; ✅ Нативная интеграция: работает непосредственно внутри PostgreSQL как родное расширение. Расширение поддерживает работу с тремя моделями от OpenAI (GPT-4o, GPT-4 и GOT 3.5 Turbo), с тремя моделями от Google (Gemini 2.5 Pro, Gemini 2.5 Flash и Gemini 2.0 Flash) и Claude 3.5) и Claude 3.5 Sonnet. Для работы расширения нужно PostgreSQL 14 + и ОС Linux, Windows или MacOS. Расширение придется устанавливать из исходников, установку из пакетов пока не завезли, но обещают в ближайшем будущем. Подробные инструкции по установке есть в документации: ➡️ https://benodiwal.github.io/pg_ai_query/installation.html После установки расширения вам будет необходимо получить API ключ от ИИ модели, с которой вы собираетесь работать и прописать его в настройках расширения. Примеры работы с расширением: — простой запрос: SELECT generate_query('show me all users created in the last 7 days'); — анализ запроса: SELECT explain_query('SELECT * FROM users WHERE active = true'); Нейросети похоже плотно входят в нашу жизнь, так что не отстаем от прогресса и пользуемся! ☺️ На этом все! До связи! #pgext

🔬 Контрольная сумма в PostgreSQL. Контрольная сумма данных - это простой, но мощный метод проверки целостности страниц данных, хранящихся на диске. Это как цифровой отпечаток для каждого 8-килобайтового блока данных («страницы») в вашей базе данных. Когда PostgreSQL записывает страницу данных (таблицы или индекса) на диск, она запускает алгоритм для содержимого страницы, чтобы вычислить небольшое производное значение - контрольную сумму. Эта контрольная сумма сохраняется в заголовке страницы вместе с данными. Каждый раз, когда PostgreSQL считывает эту страницу с диска, она немедленно пересчитывает контрольную сумму из данных и сравнивает её с сохранённым значением. Если значения не совпадают, это означает, что страница данных была изменена или повреждена с момента последней записи. Это важно, потому что повреждение данных может происходить незаметно. Обнаружив несоответствие, PostgreSQL может немедленно вызвать ошибку и предупредить вас о потенциальной проблеме. Команда initdb в PostgreSQL - это утилита, используемая для создания нового кластера баз данных PostgreSQL. Она инициализирует каталог данных, где PostgreSQL хранит все постоянные данные. При запуске initdb она выполняет такие действия, как: ✅ Создание структуры каталогов; ✅ Создание шаблонных баз данных, таких как template1, template0 и postgres; ✅ Заполнение первоначальных системных каталогов-таблиц; ✅ Создание начальной версии файлов конфигурации сервера; ✅ Включение и начало отслеживания контрольных сумм. Раньше администраторам баз данных приходилось вручную добавлять флаг --data-checksums при запуске initdb, чтобы включить эту функцию. Если вы забывали или не знали об этой функции, новый кластер создавался без этих встроенных проверок целостности. В PostgreSQL 18 поведение по умолчанию для initdb - всегда включать контрольные суммы данных при инициализации кластера. И это хорошо ☺️. Каждый новый кластер баз данных теперь автоматически оснащён защитой от повреждений, не требуя дополнительных усилий. У вас может быть очень специфическая причина отключить контрольные суммы, и вы можете явно отказаться от них, используя новый флаг: # initdb --no-data-checksums -D /data/pg18 Хотя новое значение по умолчанию - это отлично, оно может создать проблему совместимости для тех, кто выполняет обновление основной версии с помощью утилиты pg_upgrade. pg_upgrade работает, соединяя старый каталог данных с новым, и фундаментальное требование заключается в том, что в обоих кластерах должен быть одинаковый параметр контрольных сумм - либо оба ВКЛЮЧЕНЫ, либо оба ВЫКЛЮЧЕНЫ. Если вы обновляете старый кластер PostgreSQL, созданный до этого изменения, велика вероятность, что в нём контрольные суммы отключены, и pg_upgrade завершится ошибкой из-за несоответствия настроек. В случае такой проблемы при обновлении, чтобы обновить кластер без контрольных сумм, вы можете использовать новый флаг --no-data-checksums при инициализации нового кластера, чтобы привести настройки в соответствие. Вместо того чтобы продолжать работать без контрольных сумм вечно, лучшее долгосрочное решение - добавить контрольные суммы в вашу базу данных перед следующим обновлением. К сожалению, сделать это без некоторого простоя и перезапуска службы PostgreSQL невозможно. Добавление контрольных сумм в существующую базу данных может быть медленным процессом для больших баз. Для этого существует хорошо документированная утилита pg_checksums. Общая рекомендация: всегда включайте контрольные суммы в своем кластере, это может уберечь от срытого повреждения данных. На общую производительность PostgreSQL это повлияет незначительно. На этом все! До связи! #pgbase

🤬 Чиним поврежденный уникальный индекс в PostgreSQL. Если вы увидели вот такую картину с первичными ключами в таблице, то это следствие повреждения уникального индекса: SELECT id FROM mytable ORDER BY id LIMIT 5; id ---- 1 2 3 3 4 (5 rows) Надо чинить! ⚠️ Перед дальнейшими операциями обязательно сделайте копию таблицы с повреждённым индексом, а лучше всей базы. Уникальный индекс невозможно создать (или пересоздать), пока в таблице есть дублирующиеся строки. Postgres просто откажется это делать, так как это нарушает уникальность. Поэтому мы должны удалить дублирующиеся строки из таблицы. Первое, что нам нужно сделать - отключить использование индексов: set enable_indexscan = 0; set enable_bitmapscan = 0; set enable_indexonlyscan = 0; Повреждённые индексы - основной способ, через который такие плохие строки попадают в базу данных, мы не можем им доверять. Теперь PostgreSQL будет обращаться напрямую к таблице, а не искать данные через индексы. Можем сначала все проверить на тестовой таблице: CREATE TABLE test_mytable AS SELECT * FROM mytable WHERE id < 30; CREATE TABLE test_mytable_duperows (LIKE mytable); В нашем случае - на уменьшенной версии реальной таблицы. Поскольку мы знаем, что проблемные строки находятся при id = 3, мы создали новую таблицу, содержащую эти строки. Мы также создаём новую пустую таблицу test_mytable_duperows, которая будет хранить удаляемые дублирующиеся строки. Чтобы нам не мешали удалять строки ни какие триггеры, или внешние ключи нам нужно установить session_replication_role в значение replica - это расширенная (и опасная) команда, которая отключает все триггеры и правила. Кроме того, мы сделаем это как SET LOCAL вместо просто SET, что гарантирует возврат настройки в норму при следующем COMMIT или ROLLBACK. Для тестовой таблицы это не актуально, но на рабочей может пригодиться. BEGIN; SET LOCAL session_replication_role = 'replica'; WITH goodrows AS ( SELECT min(ctid) from TEST_mytable GROUP BY id) ,mydelete AS ( DELETE FROM TEST_mytable WHERE NOT EXISTS (SELECT 1 FROM goodrows WHERE min=ctid) RETURNING *) INSERT INTO TEST_mytable_duperows SELECT * FROM mydelete; RESET session_replication_role; Здесь: SELECT min(ctid) FROM TEST_mytable GROUP BY id - каждая строка в Postgres имеет скрытый столбец ctid (column tuple identifier), который является указателем на фактическое физическое местоположение строки и поэтому всегда уникален. Если мы сгруппируем по столбцу id, мы можем получить один ctid для каждого уникального id, запросив «наименьший» ctid (не имеет значения, используем ли мы min() или max() или что-то ещё, главное - выбрать только один). Промежуточный результат сохраняем в список goodrows; DELETE FROM TEST_mytable WHERE NOT EXISTS (SELECT 1 FROM goodrows WHERE min=ctid) RETURNING * - удаляем все дублирующиеся строки, которые НЕ входят в наш список goodrows. Таким образом, каждая из дублирующихся строк будет иметь разные ctid, и мы удалим все, кроме одной, для каждого id. Финальная часть RETURNING * указывает DELETE вернуть полную информацию о каждой удалённой строке; INSERT INTO test_mytable_duperows_20250317 SELECT * FROM mydelete; - наконец, мы берём вывод удаления и сохраняем его в нашей таблице. Таким образом, строки удаляются, но у нас остаётся полный список того, какие строки были удалены, для отладки и анализа. На этом этапе дублирующиеся строки должны быть удалены и находиться в таблице «duperows». Возможно, лучше проверить обе таблицы - и test_mytable, и таблицу с дубликатами, чтобы убедиться, что всё работало как ожидалось. Ну и наконец, перестраиваем индексы таблицы: REINDEX TABLE mytable; Если все ок, то можем провернуть на рабочей таблице. В конце надо не забыть вернуть Postgres к обычным настройкам планирования, установив enable_indexscan, enable_bitmapscan и enable_indexonlyscan обратно в 1. На этом все! До связи! #pgerror

🔬 Типы сканирований в PostgreSQL. Погнали! Последовательное сканирование (Sequential Scan, Seq Scan) Этот тип сканирования читает всю таблицу целиком, строка за строкой, проверяя соответствие условиям запроса. Если есть WHERE или FILTER, Postgres просто сканирует каждую строку в поиске совпадений. Это основа для выполнения сканирований, и для многих запросов Postgres будет использовать именно его. Для очень больших наборов данных или тех, к которым часто обращаются, последовательные сканирования не идеальны, и сканирование по индексу может быть быстрее. Сканирование по индексу (Index Scan) Postgres может использовать индекс как карту для данных, хранящихся в таблице. Базовое сканирование по индексу использует B-дерево для быстрого поиска точного местоположения данных в два этапа: сначала Postgres находит запись в индексе, использует ссылку, а затем извлекает остальные данные строки из таблицы. Первичные ключи автоматически индексируются с помощью B-дерева, поэтому запросы с участием первичного ключа могут использовать сканирование по индексу. Сканирование по индексу обычно быстрее последовательного сканирования. Однако в некоторых ситуациях планировщик запросов Postgres правильно выберет последовательное сканирование. Обычно это происходит, когда сканируемая таблица мала или процент возвращаемых строк перевешивает преимущества использования индекса. Если запрос возвращает ~10% строк, последовательное сканирование, вероятно, будет быстрее. Битовое сканирование по индексу (Bitmap Index Scan) Если сканирование по индексу или последовательное сканирование - не идеальный вариант, Postgres может использовать битовое сканирование по индексу как своего рода гибридный подход. Обычно оно выбирается, когда запросу соответствует слишком много строк для обычного сканирования по индексу, но не настолько много, чтобы последовательное сканирование было лучшим вариантом. В плане EXPLAIN это отображается как двухэтапный подход: 1️⃣ Bitmap Index Scan: Сначала Postgres сканирует один или несколько индексов, чтобы создать "битовую карту" в памяти - простую карту всех страниц таблицы, которые могут содержать нужные строки; 2️⃣ Bitmap Heap Scan: Битовую карту используют для обращения к основной таблице. Битовые сканирования по индексу распространены, когда в запросе есть несколько условий фильтрации, для каждого из которых существует отдельный индекс. Вы увидите этот тип сканирования в запросах с условиями WHERE, объединенными операторами AND или OR. Параллельное последовательное сканирование (Parallel Sequential Scan) Вы увидите параллельное последовательное сканирование, когда Postgres использует несколько фоновых процессов (workers) для выполнения более чем одного последовательного сканирования одной большой таблицы одновременно. Таблица разбивается на части, каждый процесс получает свою часть для сканирования, а результаты объединяются в конце в процессе сбора (Gather). В зависимости от вашего запроса у вас также могут быть этапы агрегации (Aggregate) или сортировки (Sort) после параллельных запросов и перед финальным сбором. Параллельное сканирование по индексу (Parallel Index Scan) Параллельное сканирование по индексу использует те же параллельные процессы для одновременного сканирования индекса. Оно применяет ту же методологию, что и обычное сканирование по индексу, за исключением того, что несколько процессов выполняют его одновременно. Только-индексное сканирование (Index-Only Scan) Только-индексное сканирование - это суперзвезда среди сканирований, которая отвечает на весь запрос, используя только информацию, хранящуюся в самом индексе. Ему даже не нужно обращаться к основной таблице. Такие сканирования - это огромный выигрыш в производительности, потому что не требуется извлекать информацию из основной таблицы. Они также обычно используют меньше ресурсов ввода-вывода, потому что индексы очень дружелюбны к кэшу и часто находятся в shared buffers, а это значит, что данные не нужно читать с диска. До связи! #pgbase

🔬 Как бороться с раздуванием таблиц (bloat)? Раздувание (bloat) - это свободное пространство внутри страниц базы данных, которое образуется, когда AUTOVACUUM удаляет большое количество кортежей (tuples). Когда строка в таблице обновляется, Postgres не перезаписывает старые данные. Вместо этого он помечает старую версию строки (кортеж) как "мёртвую" и создаёт новую версию строки. Со временем, по мере обновления или удаления строк, пространство, занимаемое этими мёртвыми кортежами, может накапливаться. В какой-то момент AUTOVACUUM (или ручной VACUUM) удаляет мёртвые кортежи, оставляя свободное пространство внутри страниц, доступное для повторного использования. Но если накапливается большое количество мёртвых кортежей, могут оставаться большие объёмы свободного пространства - в худших случаях оно может занимать 99% всего пространства таблицы или индекса, или даже больше. Низкие уровни раздувания (скажем, ниже 40%) не следует считать проблемой, в то время как высокие уровни определённо являются проблемой, так как приводят к негативным последствиям: ❌ Повышенное использование дискового пространства; ❌ Больше операций ввода-вывода для запросов на чтение и запись; ❌ Снижение эффективности кеширования (как буферного пула, так и файлового кеша ОС); ❌ Как следствие - ухудшение производительности запросов. Уровень раздувания индексов и таблиц следует проверять регулярно. Рекомендации для использования в системах мониторинга: ✅ Нет смысла запускать проверочные запросы для определения размера таблиц часто (например, каждую минуту), так как уровень раздувания меняется не так быстро; ✅ Для больших баз данных выполнение запроса может занять много времени, до нескольких секунд, поэтому может потребоваться настроить частоту проверок и параметр statement_timeout. Подходы для более точного определения уровня раздувания: ✅ Запросы на основе расширения pgstattuple (требует установки этого расширения); ✅ Проверка размеров объектов БД на клоне базы. Запускать VACUUM FULL ресурсоёмко и он блокирует запросы, поэтому не для продакшена. Делаем это на клоне и сравниваем "до/после". К сожалению, в базах данных, где происходит много операций UPDATE и DELETE, состояние индексов неизбежно ухудшается со временем. Это означает, что индексы необходимо регулярно перестраивать. Рекомендации: ✅ Используйте REINDEX CONCURRENTLY для неблокирующего перестроения раздутого индекса; ✅ Помните, что REINDEX CONCURRENTLY удерживает горизонт xmin во время выполнения. Это влияет на способность AUTOVACUUM очищать недавно умершие кортежи во всех таблицах и индексах; ✅ Предпочтительно использовать версии Postgres 14+, поскольку в PG14 btree-индексы были значительно оптимизированы, чтобы деградировать гораздо медленнее при нагрузках на запись. Некоторый уровень раздувания таблиц можно считать не таким уж плохим явлением, потому что он увеличивает шансы на оптимизированные UPDATE - HOT - обновления (Heap-Only Tuples). Однако, если уровень вызывает беспокойство, рассмотрите возможность использования pg_repack для перестроения таблицы без длительных эксклюзивных блокировок. Альтернатива pg_repack - pg_squeeze. Проактивное предотвращение раздувания: ✅ Настройте AUTOVACUUM; ✅ Не допускайте ненужных длительных транзакций (например, > 1 часа), ни на основном сервере, ни на репликах с включённым параметром hot_standby_feedback; ✅ Если используется Postgres 13, то рассмотрите возможность обновления до 14+, чтобы воспользоваться оптимизациями btree-индексов; ✅ Партиционируйте большие таблицы (100+ ГБ); ✅ Не используйте массовые UPDATE и DELETE, всегда работайте пакетами (длительностью не более 1-2 секунд); ✅ Убедитесь, что AUTOVACUUM оперативно очищает мёртвые кортежи, или выполняйте VACUUM вручную, когда необходимы массовые изменения данных. На этом все! До связи! #pgmonitor

🔬 Системный каталог PostgreSQL (ч. 3). Всем привет! Продолжаем разбираться с системным каталогом PostgreSQL. Это будет заключительная заметка из этой серии. Фактически, те метакоманды psql, которые мы изучали в первой заметке из этой серии соответствуют конкретным таблицам системного каталога. Вот их примерное соответствие: \d (таблицы и объекты) - pg_class \di (индексы) - pg_class, pg_index \dx (установленные расширения) - pg_extension \dp (таблицы и привилегии) - pg_class, pg_roles, pg_attribute \l (базы данных) - pg_database \df (доступные функции) - pg_proc Давайте рассмотрим эти таблицы подробнее и посмотрим как их можно использовать. pg_stats Таблица pg_stats собирает всю статистику о ваших столбцах - такие вещи как кардинальность - много ли элементов в этом столбце или мало? Postgres использует много информации из pg_stats для принятия решений планировщиком запросов. В некоторых случаях предоставление pg_stats дополнительной информации может ускорить ваши запросы. Этот запрос покажет информацию по столбцам конкретной таблицы: SELECT * FROM pg_stats WHERE tablename = 'имя_таблицы' AND attname = 'имя_столбца'; pg_class pg_class содержит строку для каждой таблицы, индекса, последовательности, представления, материализованного представления и других объектов в базе данных. SELECT c.relname, pg_get_userbyid(c.relowner) AS owner FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relkind = 'r' ORDER BY c.relname; pg_type Эта таблица хранит все существующие типы данных. В Postgres каждая таблица имеет связанный составной тип, который определяет структуру ее строк. Так что если вы сделаете SELECT *, вы увидите здесь все имена таблиц и все типы данных. Если немного отфильтровать, вы можете увидеть все свои пользовательские типы данных, домены и перечисления. Этот запрос покажет пользовательские типы данных в Postgres: SELECT t.typname AS type_name, n.nspname AS schema_name, t.typtype AS type_class FROM pg_type AS t JOIN pg_namespace AS n ON t.typnamespace = n.oid LEFT JOIN pg_class c ON typrelid = c.oid WHERE t.typtype IN ('e', 'd', 'c') AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND (t.typtype <> 'c' OR c.relkind = 'c') ORDER BY schema_name, type_name; pg_proc Это каталог всех функций и хранимых процедур, которые может использовать Postgres. Создали функцию на прошлой неделе, но не можете найти сейчас? Просто просмотрите их все. Этот запрос находит все функции, триггеры и хранимые процедуры; SELECT proname AS function_name, proargnames AS argument_names, pg_catalog.format_type(prorettype, NULL) AS return_type FROM pg_proc ORDER BY proname; pg_attribute Эта таблица хранит информацию о столбцах таблиц. pg_attribute покажет одна строку для каждого столбца в каждой таблице. Запрос столбцов и типов данных для любой таблицы: SELECT a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_catalog.pg_attribute a WHERE a.attrelid = 'orders'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; Схема pg_catalog pg_catalog- это схема, содержащая системные таблицы. Иногда навигация по этим таблицам и представлениям может быть запутанной и требовать просмотра смеси документации и исходного кода. Если вы хотите исследовать, как каталог связан, вы можете подключиться к своей базе данных с аргументом -E для psql (или выполнить \set ECHO_HIDDEN on, если вы уже подключены). Postgres будет показывать SQL-команду, которая выполняется для каждой метакоманды psql. Очень полезно для образовательных целей и понимания внутреннего устройства PostgreSQL. На этом все! До связи! #pgbase #queries