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

🌐 Обновления PostgreSQL 18.1, 17.7, 16.11, 15.15, 14.20, и 13.23 Вчера, 13.11.2025 вышли обновления для всех основных версий PostgreSQL. ⚠️ Это последнее обновление безопасности для 13-й версии PostgreSQL. Больше она обновляться не будет. В обновлениях, как обычно закрыли несколько дыр в безопасности и исправили кучу багов, которые в основном качаются PostgreSQL версии 18. 🤖 Проблемы безопасности. CVE-2025-12817: Команда CREATE STATISTICS в PostgreSQL не проверяет привилегию CREATE для схемы. Отсутствие авторизации в команде CREATE STATISTICS PostgreSQL позволяет владельцу таблицы осуществить отказ в обслуживании для других пользователей этой команды, создавая объекты в любой схеме. Последующая команда CREATE STATISTICS с тем же именем от пользователя, имеющего привилегию CREATE, завершится неудачей. CVE-2025-12818: Недостаточное выделение памяти в libpq из-за целочисленного переполнения. Целочисленное переполнение в нескольких функциях клиентской библиотеки libpq PostgreSQL позволяет поставщику входных данных приложения или сетевому узлу вызвать недостаточное выделение памяти в libpq и запись за пределами выделенной области на сотни мегабайт. Это приводит к аварийному завершению (segmentation fault) приложения, использующего libpq. Это обновление исправляет более 50 ошибок в PostgreSQL 18, среди них: ✅ Устранено возвращение дублирующихся строк при использовании хэш-правых полусоединений (hash right semi-joins); ✅ Предотвращены возможные сбои из-за нехватки памяти во время параллельного построения GIN-индексов; ✅ Несколько исправлений для BRIN-индексов; ✅ Исправлены сбои, связанные с секционированными таблицами, включая сбой во время повторной проверки (recheck); ✅ Устранено дублирование ограничений хэш-секций во время операции DETACH CONCURRENTLY, которое ранее вызывало проблемы при дампировании/восстановлении или если родительская таблица удалялась после DETACH; ✅ Запрещено использование генерируемых столбцов (generated columns) в ключах секционирования и в предложениях COPY ... FROM ... WHERE; ✅ Исправлен некорректный отчет о запаздывании репликации в представлении pg_stat_replication; ✅ Предотвращены сбои, когда synchronized_standby_slots ссылается на несуществующие слоты репликации; ✅ Устранено нежелательное завершение работы приемника WAL при переключении с потоковой передачи на архивный источник WAL; ✅ Предотвращена ненужная инвалидация слотов логической репликации; ✅ Корректная обработка GROUP BY DISTINCT в операторах присваивания PL/pgSQL; ✅ Устранена утечка памяти при обработке SQL-ошибок в PL/Python; ✅ Исправлена обработка ошибок, связанных с сокетами, в логике GSSAPI библиотеки libpq в Windows; ✅ Исправлен дампинг ненаследуемых ограничений NOT NULL для столбцов унаследованных таблиц; ✅ Обеспечение согласованного порядка ограничений внешнего ключа в выводе pg_dump; ✅ Несколько исправлений для обработки и отчетов об ошибках в pgbench; ✅ Устранена утечка памяти в pg_combinebackup; ✅ Разрешено пользователям без прав суперпользователя, но с привилегией SELECT на таблицу, использовать pg_prewarm для предварительной загрузки индексов этой таблицы. Подробнее об обновлениях читаем на официальном сайте: ➡️ https://www.postgresql.org/about/news/postgresql-181-177-1611-1515-1420-and-1323-released-3171/ #pgnews

🤖 Серверные транзакции с возможностью отката на уровне оператора в PostgreSQL (расширение pg_statement_rollback) pg_statement_rollback — это расширение PostgreSQL, которое добавляет серверные транзакции с возможностью отката на уровне оператора, как в Oracle или DB2. Официальный GitHub расширения: ➡️ https://github.com/HexaCluster/pg_statement_rollback В чем собственно проблема: В PostgreSQL транзакция не может продолжиться при возникновении ошибки хотя бы в одном SQL операторе, и вся работа, выполненная в транзакции, откатывается. В Oracle или DB2 существуют неявные точки сохранения перед выполнением каждого SQL оператора, что позволяет откатиться к состоянию непосредственно перед сбоем оператора. Если в любой момент выполнения оператор SQL вызывает ошибку, все эффекты от этого оператора откатываются. Результат отката такой, как если бы этот оператор никогда не выполнялся. Эта операция называется откатом на уровне оператора и имеет следующие характеристики: 📌 Неуспешный оператор SQL приводит к потере только той работы, которую он должен был выполнить сам. Неудачный оператор не вызывает потери любой предыдущей работы в текущей транзакции; 📌 Эффект отката такой, как если бы оператор никогда не запускался. Текущие реализации отката на уровне оператора для PostgreSQL выполняются на стороне клиента. В psql есть \set ON_ERROR_ROLLBACK on, в JDBC есть автооткат при исключении SQL при выполнении запроса, в psqlODBC тоже есть режим "statement level rollback". Проблема этих реализаций в том, что они добавляют дополнительную коммуникацию с сервером, отправляя SAVEPOINT autosave и RELEASE SAVEPOINT autosave, что может серьезно ограничить пропускную способность приложения. Расширение pg_statement_rollback выполняет автоматическую точку сохранения на стороне сервера, что добавляет очень незначительное снижение производительности. Конечно, вам все равно нужно управлять ROLLBACK TO SAVEPOINT на стороне клиента при возникновении ошибки. Установка расширения подробно рассмотрена в его документации. Пройдемся по параметрам расширения: pg_statement_rollback.enabled - включение/отключение расширения (по-умолчанию on) Вы можете отключать или включать расширение в любой момент на уровне сеанса. Например так: SET pg_statement_rollback.enabled TO off; pg_statement_rollback.savepoint_name - имя точки сохранения (по-умолчанию PgSLRAutoSvpt). Этот параметр может быть установлен только суперпользователем. pg_statement_rollback.enable_writeonly По умолчанию расширение не создает автоматические точки сохранения после операторов SELECT. Это сделано для ограничения количества точек сохранения, чтобы избежать переполнения кэша подтранзакций. Это поведение можно отключить без потери производительности, если у вас меньше 64 (PGPROC_MAX_CACHED_SUBXIDS) операторов в транзакции. В противном случае вы столкнетесь с потерей производительности из-за сканирования pg_subtrans на диске. Во всех сеансах, где вы хотите использовать транзакции с откатом на уровне оператора от pg_statement_rollback, вам нужно загрузить расширение с помощью: LOAD 'pg_statement_rollback.so'; SET pg_statement_rollback.enabled TO on; Затем в вашем приложении, когда возникает ошибка, вам нужно будет вызвать ROLLBACK TO SAVEPOINT "PgSLRAutoSvpt"; чтобы продолжить выполнение текущей транзакции с состояния непосредственно перед ошибкой. Если вы хотите использовать расширение на уровне всего кластера PostgreSQL, то добавьте его в postgresql.conf вот так: session_preload_libraries = 'pg_statement_rollback' и включите расширение pg_statement_rollback.enabled = on Совместно с этим расширением можно использовать расширение pg_dbms_errlog. Официальный GitHub: ➡️ https://github.com/HexaCluster/pg_dbms_errlog Это расширение позволяет логировать сбойные SQL операторы внутри транзакции. На этом все! До связи! #pgext

⚡Открытая трансляция главного зала HighLoad++ 2025! 🖐️ Подключайтесь и слушайте доклады от спикеров MTС Web Services (MWS), VK Tech, Яндекса, Т-Банка, СберТеха и других компаний. HighLoad++ 2025 — это конференция, которая определяет будущее высоконагруженных систем. 📍6 и 7 ноября все желающие могут бесплатно посмотреть онлайн-трансляцию главного зала. Открытую трансляцию мы организовали совместно с генеральным партнером конференции — МТС Web Services (MWS). МТС Web Services (MWS) — бигтех-компания, предоставляющая облачные и Al-сервисы, а также платформенные решения под задачи бизнеса: от работы с данными до разработки продуктов и оптимизации бизнес-процессов. Как всегда, в главном зале — топовые эксперты и самые актуальные темы. ✅ Подробности и расписание на сайте

Работаете с PostgreSQL и хотите забыть про простои и потери данных? 🤔 Не хотите оказаться в ситуации “руками переключать мастера” в 3 ночи? Этот практикум OTUS для вас! 6 ноября в 20:00 - бесплатно по записи. Разберем вместе с Senior Developer Unlimint: Как устроен Patroni и почему его выбирают для High Availability Как настроить кластеры Postgres, чтобы пережить любой фейловер без паники Демонстрация настройки кластера — всё покажем на живых примерах Как избежать split-brain, не “запилить” себе геморроя с Etcd/Consul Что мониторить, как обновлять, как спасать кластер после сбоев Только живой разбор, без воды и маркетингового бла-бла. Спикер: Андрей Поляков, Senior Developer в Unlimint 🎁Бонусы для участников: Скидка 7% на любой курс OTUS Видео-разбор: Мониторинг в highload-проектах Чек-лист: 5 частых ошибок в отказоустойчивых хранилищах Запишись сейчас — чтобы не искать решение в 3 часа ночи, когда что-то пойдет не так. Увидимся на эфире: https://tglink.io/73c66bb54511 Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963. erid: 2W5zFHDtWhe

🔬 SLRU и MultiXact в PostgreSQL (ч. 2). Всем привет! Надеюсь все хорошо отдохнули и готовы к новым знаниям ☺️. Продолжаем изучать SLRU и MultiXact в PostgreSQL. В этой заметке посмотрим как мы можем мониторить эти сущности и какие проблемы могут быть с ними связаны. Начиная с Postgres 13, у нас есть системное представление pg_stat_slru, которое можно использовать для получения кумулятивной статистики по SLRU. Чтобы перечислить только имена встроенных типов SLRU можно использовать такой запрос: SELECT name FROM pg_stat_slru; Чтобы понять, создает ли наша система SLRU для MultiXact, мы можем запросить все колонки представления pg_stat_slru. Мы увидим ненулевые числа в строках в результате запроса, когда система создает данные SLRU. SELECT * FROM pg_stat_slru; Hit и read относятся к чтениям из SLRU: когда нужные страницы уже находились в SLRU (hit) или нет (read). Когда новые страницы выделяются, мы видим это в blks_zeroed, так как они записываются, заполненные нулями. Когда новые страницы записываются (blks_written) в SLRU, это создает "грязные" (dirtied) страницы, которые в конечном итоге будут сброшены на диск (flushes). SLRU также могут усекаться (счетчик Truncates). Что может пойти не так с SLRU и Xact? SLRU используют 32-битные номера, и для высоконагруженной базы PostgreSQL они могут быстро закончится и случится Wrap-around 😱. Такое может случиться в двух случаях: 1️⃣ Переполнение подтранзакций (Subtransactions overflow): Использование подтранзакций создает идентификатор для отслеживания каждой из них. При достаточно высокой скорости создания возможно исчерпание доступных значений; 2️⃣ Исчерпание пространства членов MultiXact (MultiXact member space exhaustion). В Postgres 17 пространство для членов и смещений (member and offset) MultiXact теперь можно настраивать за пределами начального размера по умолчанию. Единица измерения — количество 8-килобайтных страниц. Для этого у нас появилось два новых параметра: multixact_member_buffers, по умолчанию 32 страницы по 8 КБ; multixact_offset_buffers, по умолчанию 16 страниц по 8 КБ. Какие выводы из всего этого можно сделать? Если у вас очень высоконагруженная база данных PostgreSQL, в которой много и часто порождаются MultiXact, вы просто обязаны о них знать и приглядывать за ними, чтобы избежать возможных проблем в будущем. Так что, мониторинг наше все! ☺️ На этом все! До связи! #pgbase #pgmonitor

🔬 SLRU и MultiXact в PostgreSQL (ч. 1). В этой заметке мы рассмотрим два важных внутренних компонента PostgreSQL, о которых знать очень полезно. Тема объемная, поэтому будет несколько постов по ней. Начнем с SLRU. Аббревиатура расшифровывается как "Simple Least Recently Used" (Простой алгоритм наименьшего давнего использования). SLRU — это небольшие хранилища данных в оперативной памяти. Поскольку они должны сохраняться после перезапуска службы PostgreSQL, они также записываются в файлы на диске. О них стоит знать, потому что с ними может быть связано несколько потенциальных точек сбоя из-за их фиксированного размера. Основная цель SLRU— отслеживание метаданных о транзакциях, их состояния и идентификаторов. Смысл "наименьшего давнего использования" (LRU) означает, что самые старые элементы вытесняются из кеша, когда он заполнен, и их место занимают новые элементы. Это происходит потому, что кеш имеет фиксированный объем (измеряемый в 8-килобайтных страницах по-умолчанию) и, следовательно, может хранить только ограниченное количество элементов. Старые элементы кеша SLRU периодически очищаются процессом VACUUM. Буферный кеш (его размер задается параметром shared_buffers) - это другая форма кеширования, предназначенная для других целей. В SLRU хранится информация о метаданных транзакций (xact), т.е. их состояния и внутренние идентификаторы. Помимо обычных транзакций, существуют их разновидности. Транзакции могут создаваться внутри других транзакций, они называются "вложенными транзакциями" (nested transactions). Будь то родительские или вложенные транзакции, каждая из них получает свой собственный 32-битный целочисленный идентификатор, как только начинает что-то изменять, и все они отслеживаются пока используются. Еще одна разновидность транзакции - это "мультитранзакция" (группа транзакций) или на языке PostgreSQL - MultiXact. MultiXact получает отдельный номер, отличный от идентификатора транзакции - MultiXact ID. MultiXact ID — это вторичная структура данных, которая отслеживает несколько транзакций, удерживающих блокировки на одной и той же строке. Когда MultiXact создаются, их идентификатор сохраняется в заголовке версии строки (tuple), заменяя идентификатор транзакции. Идентификаторы транзакций (Transaction IDs) и идентификаторы MultiXact (MultiXact IDs) представлены в виде 32-битного целого числа, что означает возможность хранить максимум около ~4 миллиардов значений. Мы можем получить текущее значение идентификатора транзакции, выполнив: SELECT pg_current_xact_id();. MultiXact создаются только для определенных типов операций DML и для отдельных определений схемы. Другими словами, возможно, что ваша конкретная рабочая нагрузка в базе вообще не создаст MultiXact, или наоборот, они могут интенсивно использоваться. К созданию MultiXact приводит: ✅ Принудительное соблюдение ограничений внешнего ключа (Foreign key constraint enforcement); ✅ SELECT FOR SHARE. Если вы не используете ограничения внешних ключей или ваше приложение никогда не создает блокировки SELECT FOR SHARE, то в вашей базе данных Postgres может не быть MultiXact вообще. SLRU имеют фиксированный размер (до версии Postgres 17), измеряемый в страницах. Когда элементы вытесняются из кеша SLRU, происходит замена страницы. Заменяемая страница называется "жертвенной" (victim page), и Postgres должен выполнить небольшую работу, чтобы найти такую страницу. Поскольку SLRU сохраняются при перезапуске службы PostgreSQL, они сохраняются в файлах в каталоге PGDATA. Имя каталога зависит от типа SLRU. Например, для MultiXact имя каталога — pg_multixact. Страницы буферов SLRU записываются в WAL и на диск. Каждый экземпляр SLRU реализует циклический буфер страниц в общей памяти, вытесняя наименее недавно использовавшиеся страницы. С теоретической частью все. В следующих постах рассмотрим возможные сбои, связанные с MultiXact и средства мониторинга. На этом все! До связи! #pgbase

⚡️Всем, кто работает с контентом и большими данными: Selectel дает месяц бесплатного использования S3 по акции «Миграционные каникулы». S3 от Selectel: 📦 Подходит для хранения бэкапов, данных приложений, ML-датасетов и работы с аналитикой; 💸Помогает сэкономить до 30% бюджета благодаря разным классам хранилища; 📍Катастрофоустойчиво засчет работы на базе дата-центров в Москве и Санкт-Петербурге. Создавайте заявку и пользуйтесь: https://slc.tl/obf58 Реклама. АО "Селектел".erid:2W5zFJuFU64

🤖 Обращение к предыдущим (OLD) и к текущим (NEW) значениям в предложении RETURNING в PostgreSQL 18. Всем привет! Помимо таких ключевых функций в PostgreSQL 18, как асинхронный ввод-вывод и поддержка UUIDv7, мы получили вот такое небольшое, но приятное улучшение. В этой версии добавлена возможность обращаться как к предыдущим (OLD), так и к текущим (NEW) значениям в предложении RETURNING для команд INSERT, UPDATE, DELETE и MERGE. Суть в том, что теперь доступны два синонима OLD и NEW, с помощью которых мы можем возвращать старые и новые значения в предложении RETURNING. Это не революционное изменение, как асинхронный ввод-вывод, но это одна из тех небольших функций, которая оказывается просто неоценимой в определённых ситуациях. Простая демонстрация с UPDATE для получения всех старых и новых значений: UPDATE fruit SET quantity = 300 WHERE item = 'Apples' RETURNING OLD.*, NEW.*; Вывод: id|item|quantity|id|item|quantity ——+—-——+————————+——+———+—————— 5|Apples| 200 | 5| Apples |300 (1 row) Получается очень просто, удобно и наглядно! Все как мы любим! ☺️ Вот еще пример. Допустим, мы выполняем операцию UPSERT (INSERT ... ON CONFLICT ... DO UPDATE) и хотим определить, была ли строка, возвращённая RETURNING, новой вставленной строкой или существующей обновлённой строкой. Это было возможно и раньше, но полагалось на неинтуитивную проверку xmax = 0: INSERT INTO webhook ( id, data ) VALUES ( @id, @data ) ON CONFLICT (id) DO UPDATE SET id = webhook.id RETURNING webhook.*, (xmax = 0) AS is_new; Этот оператор полагается на то, что xmax устанавливается в ноль для новой вставки, что является особенностью реализации блокировок в PostgreSQL. Это работает, но не является гарантированной частью API и потенциально может измениться в любой момент. В PostgreSQL 18 мы можем переписать приведённый выше код так, чтобы он был более понятным и не зависел от деталей реализации. Это также просто - достаточно проверить, является ли OLD значением NULL в предложении RETURNING: INSERT INTO webhook ( id, data ) VALUES ( @id, @data ) ON CONFLICT (id) DO UPDATE SET id = webhook.id RETURNING webhook.*, (OLD IS NULL)::boolean AS is_new; Доступ к OLD и NEW, без сомнения, найдёт множество других полезных применений, но этот пример позволяет нам сразу же улучшить код, написанный до 18-й версии. Переходим на PostgreSQL 18 и пользуемся! ☺️ #pgnews #pgbase