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

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

Развернуть PostgreSQL в MWS Cloud Platform ⬜️ — быстрее, чем вспомнить пароль от pgAdmin. И точно быстрее, чем объяснить DevOps'у, зачем ещё одна база. Всего несколько минут и у вас:

⏺️готовая база на сетевых или локальных дисках ⏺️постоянный primary endpoint ⏺️безопасное подключение через Private Link ⏺️автоматические бэкапы и обслуживания по твоему расписанию

🎄🎁 И грант до 10 000 ₽ на запуск — чтобы точно не пришлось вспоминать, как настраивать failover вручную. ➡️Развернуть кластер

🤖 Используем ИИ в 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

📕Базовые принципы шардирования и репликации в ClickHouse 👤Вебинар для: Data-инженеров, архитекторов и аналитиков, администраторам баз данных и DevOps-инженеров, разработчиков высоконагруженных приложений На открытом уроке 18 декабря в 20:00 мск мы разберемся в двух ключевых механизмоах для работы с большими данными в ClickHouse. 📗 На вебинаре разберем: • Что такое шардирование и репликация и зачем они нужны. • Как эти механизмы устроены внутри ClickHouse. 📘 В результате на практике изучите основные понятия (шард, реплика, Distributed-таблицы), поймете, как распределяются данные и обеспечивается отказоустойчивость, и узнаете, с чего начать проектирование кластера ClickHouse. 👉 Регистрация на урок и подробности о курсе ClickHouse для инженеров и архитекторов БД: https://tglink.io/747d4e96ddd5?erid=2W5zFJaKpJC Все участники открытого урока получат скидку на курс "ClickHouse для инженеров и архитекторов БД" Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963.

🔬 Контрольная сумма в 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

В условиях повышенных требований к отказоустойчивости и импортозамещению инфраструктуры вопросы бесперебойной работы систем управления базами данных (СУБД) выходят на первый план ⚡️Но как не потерять базу данных и обеспечить высокую доступность – в том числе на импортозамещенном ПО?Для этого есть решение, которое обеспечивает комплексную настройку и оптимизацию IT- инфраструктуры (серверы, виртуализация, ОС, СУБД и др.) под прикладные системы, чтобы обеспечить им высокую производительность, отказоустойчивость и стабильную работу, в том числе на импортонезависимом стеке, — и это Digital Q.SystemIntegration 🎯Ему посвящен вебинар, который пройдет 18 декабря в 14:00!Эксперты Диасофт также разберут:▪️практические подходы к построению высокодоступных кластеров на базе PostgreSQL и его форков.▪️реальные кейсы и проблемы (VACUUM, влияние горизонта транзакций) + их решения.Регистрируйтесь по ссылке!Реклама. ООО "ДИАСОФТ". ИНН 7715560268.

🐘 Открыт прием заявок на выступления для специалистов по PostgreSQL Мероприятие: PG BootCamp Russia — официальное российское комьюнити-мероприятие PostgreSQL Когда: весна 2026 г. (дата уточняется) Где: г. Москва Больше о мероприятиях PG BootCamp В отличие от коммерческих конференций, предметом докладов выступает «ванильная» версия этой СУБД. Темы выступлений, связанные с коммерческими продуктами, не принимаются. Доклады (их обычно до 16 в два трека) делятся по темам разработки и эксплуатации. Формат предполагает камерную атмосферу, максимальную практическую пользу и содержательное профессиональное общение. Открыт прием заявок на выступления:

🔹Исследование внутренней архитектуры PostgreSQL 🔹 Оптимизация производительности в высоконагруженных системах 🔹Анализ сложных задач и методов их решения 🔹 Инструменты и методологии для DBA 🔹 R&D-исследования, связанные с Postgres

Если у вас есть материалы, которым вы хотите поделиться с сообществом, — пожалуйста, присылайте тезисы. Это возможность не только представить свою работу, но и получить содержательную обратную связь от ведущих специалистов. 🎙Подать заявку на выступление

🔬 Типы сканирований в 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

Полиглот – человек, знающий много языков 😛 IT-полиглот – функциональность для ускоренного перехода с Oracle и MS SQL 🤖 Но обо всем по порядку: Основная сложность перехода на российскую СУБД — не поиск аналога, а обеспечение комплексного перехода с единой ответственностью, гарантиями регуляторов и прозрачной экономикой проекта. Digital Q.DataBase от Диасофт решает эту задачу кардинально. Система обеспечивает переход с зарубежных СУБД без переписывания кода приложений. Новая функциональность "Полиглот" в Digital Q.DataBase обеспечивает непосредственное исполнение запросов, написанных на диалектах SQL ведущих зарубежных СУБД: Oracle (PL/SQL), Microsoft SQL Server (T-SQL), а также PostgreSQL (PL/pgSQL). 🆓 Диасофт запустил бесплатную версию СУБД Digital Q.DataBase. Решение подходит для всех категорий пользователей, включая коммерческие организации и госучреждения. Скачивайте дистрибутив бесплатно на сайте! Реклама. ООО "ДИАСОФТ ЭКОСИСТЕМА". ИНН 9715403607.

🔬 Как бороться с раздуванием таблиц (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