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

🛠️ Шаблон для обеспечения отказоустойчивости кластера PostgreSQL - Patroni (ч. 1).

Я думаю про Patroni слышали если не все, то очень многие. Давайте разберемся, что это такое. В одну заметку всю информацию о Patroni запихнуть невозможно, так что будет цикл заметок.

Начнем с того, почему сами разработчики Patroni называют его шаблоном. Все дело в том, что сам по себе Patroni не может работать из коробки, ему нужно дополнительное программное обеспечение. Именно в связке с ним Patroni сможет обеспечить высокодоступный кластер PostgreSQL.

Что же такое Patroni? Patroni - open-source шаблон для разворачивания отказоустойчивого, высокодоступного кластера PostgreSQL на базе потоковой репликации, а также для управления и мониторинга им. Patroni написан на Python. Под капотом Patroni для отказоустойчивости использует потоковую репликацию PostgreSQL, но позволяет автоматизировать переключение между серверами и избежать так называемого split-brain.

Основные возможности Patroni:

📌 Автоматическое переключение узлов в случае аварии на главном;

📌 Ручное переключение узлов или переключение по расписанию;

📌 Поддержка синхронной и асинхронной репликации PostgreSQL;

📌 Поддержка каскадной репликации;

📌 Поддержка автоматического запуска pg_rewind для возврата главного сервере в строй;

📌 Пользовательские скрипты;

📌 REST API для управления и мониторинга кластера Patroni;

📌 Интерфейс командной строки patronictl для управления кластером Patroni.

Теперь, когда мы поняли, что Patroni штука нужная и полезная, давайте разберёмся со схемой его работы и компонентами.

Для работы Patroni необходима распределенная база данных типа ключ-значение. В такой базе Patroni будет хранить состояния узлов и их статус, также нужен прокси-сервер для обеспечения единой точки входа клиента в кластер и переключения клиента на новый мастер сервер. Таким образом, краткое описание схемы работы Patroni получается следующим: агент Patroni устанавливается на всех узлах PostgreSQL, между которыми настроена потоковая репликация. С помощью РБД типа ключ-значение отслеживается состояние узлов, если случилась авария, то Patroni производит автоматическое переключение на сервер реплику и поднимает его роль до главного сервера, а прокси-сервер обеспечивает переключение соединений клиентов на нового мастера.

Если говорить про каждый компонент шаблона Patroni отдельно, то в качестве РБД ключ-значение он поддерживает следующие базы: etcd, Consul, ZooKeeper, и Kubernetes API. Наиболее популярной РБД в связке с Patroni является etcd, а Kubernetes API необходим в случае разворачивания Patroni в Kubernetes. В качестве прокси-сервера вы можете использовать вообще все что угодно, но наиболее популярным тут будет HAProxy.

Собственно РБД обеспечивает Patroni четкие инструкции о том какой сервер является в данный момент главным, а какой репликой. Patroni хранит в РБД ключ типа /leader, который имеет ограниченный TTL (time to live), или по русски срок действия и хранит в себе информацию о текущем мастер сервере. Если ключ не получил своевременного обновления, то Patroni посчитает, что мастер сервер вышел из строя и создаст новый /leader ключ, а также сделает новый мастер сервер из реплики. Все операции с этим ключём защищены работой РБД, которая производит его сравнение на всех узлах кластера, что исключает ситуации когда сразу несколько реплик могут стать мастером и случится split-brain базы данных.

В данной заметке мы привели очень краткое и основное описание работы Patroni. В будущих постах полезем в глубины работы Patroni, а также научимся его устанавливать и настраивать.

#pghi

Вебинар «Анализируем транзакции в реальном времени» 

Приходите на бесплатный вебинар и узнайте, как обрабатывать высокие транзакционные и аналитические нагрузки в гибридной in-memory СУБД. 

Дата и время: 28 ноября, 16:00.

Программа

🔹 Ускорение аналитических расчетов и аналитика на самых свежих данных с помощью HTAP-систем.

🔹 Основные архитектурные характеристики гибридных транзакционно-аналитических СУБД (HTAP). 

🔹 Повышение отказоустойчивости транзакционно-аналитических решений, настройка под разные профили нагрузки и интеграция с другими компонентами ИТ-ландшафта. 

Проведем демонстрацию возможностей продукта Tarantool Column Store в работе с объектами и данными.

Расскажем, как формировать отчетность в реальном времени и рассчитывать агрегаты в антифрод-системах с помощью продукта Tarantool Column Store.  

Вебинар будет полезен архитекторам, дата-инженерам, DevOps-инженерам и разработчикам аналитических систем. 
  
Регистрируйтесь, и вам придет ссылка на трансляцию в день мероприятия.

🔬 Сценарии при которых срабатывает AUTOVACUUM в PostgreSQL.

Опять про великий и ужасный AUTOVACUUM 👹 На этот раз поговорим о том при каких обстоятельствах он срабатывает в PostgreSQL.

📌 Количество "мертвых" строк превышает пороговое значение с момента последнего VACUUM. В PostgreSQL есть парочка параметров, которые влияют на это поведение AUTOVACUUM.

autovacuum_vacuum_threshold (значение по умолчанию 50) и autovacuum_vacuum_scale_factor (значение по умолчанию 0,2).

Формула будет такой:

autovacuum_threshold = autovacuum_vacuum_threshold + (Кол-во строк в таблице * autovacuum_vacuum_scale_factor)

Т.е, если у вас 100 строк в таблице, то автоочистка при значениях по умолчанию сработает, когда в таблице будет примерно 70+ мертвых строк.

📌 relfrozenxid больше, чем autovacuum_freeze_max_age транзакций. relfrozenxid - это маркер, который показывает самый старый ID транзакции в таблице, который не был еще заморожен. Напомним, заморозка - это процесс в PostgreSQL, который позволяет безопастно переиспользовать ID старых транзакций, чтобы предотвратить их зацикливание.

За лимит заморозки в PostgreSQL отвечает параметр autovacuum_freeze_max_age. Соответственно, как только этот лимит привысится, на таблице сработает AUTOVACUUM и начнет замораживать старые ID.

📌 Кол-во новых строк (INSERT) превысит лимит вставки с момента последнего запуска VACUUM. Здесь ситуация похожая с кол-вом "мертвых" строк, только для строк новых.

Формула будет такой:

insert_threshold = autovacuum_vacuum_threshold + (Кол-во строк в таблице* autovacuum_vacuum_scale_factor)

📌 Кол-во транзакций превысило разницу между vacuum_freeze_table_age и vacuum_freeze_min_age с последнего агрессивного VACUUM. PostgreSQL может агрессивно включать VACUUM, чтобы предотвратить зацикливание ID транзакций. Под агрессивным VACUUM понимается то, что PostgreSQL будет проводить его несмотря ни на какие ваши настройки как можно быстрее, чтобы предотвратить катастрофу. Так вот, такой VACUUM может включиться, если кол-во транзакций превысило разницу между параметрами
vacuum_freeze_table_age и vacuum_freeze_min_age с последнего такого VACUUM.

Такую ситуацию лучше не допускать, так как агрессивный VACUUM сильно замедлит работу базы.

📌 Если срок жизни самого старейшего multixact ID превысит порог autovacuum_multixact_freeze_max_age. multixact ID используется в PostgreSQL для сценариев, когда нескольким транзакциям нужно получить доступ к одной и той же строке. Это параметр отслеживается с помощью значения relminmxid. Если возраст самого старого multixact ID превысит значение параметра autovacuum_multixact_freeze_max_age, то сработает AUTOVACUUM.

Кстати, для хранения информации о multixact ID PostgreSQL использует специальную папку на диске. Если размер этой папки превысит 2Gb, то тоже сработает AUTOVACUUM.

📌 Если общее кол-во вставленных (INSERT), обновленных (UPDATE) или удаленных (DELETE) строк превысит пороговое значение с момента последней операции ANALYZE. VACUUM отвечает не только за очистку старых строк, но и за сбор статистики, поэтому в этом случает он тоже сработает.

Формула такая:

analyze_threshold = autovacuum_analyze_threshold + (Кол-во строк в таблице * autovacuum_analyze_scale_factor)

Как видите случаев срабатывания VACUUM немало. Мы перечислили только самые основные. VACUUM играет ключевую роль в работоспособности и обеспечения производительности вашей базы данных, так что не забывайте приглядывать за ним 😉.

#pgbase

#pgmeme

🌐 Обновления PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, и 12.21.

Всем привет! Вчера (14.11.2024) вышло обновление для основных версий PostgreSQL.

⚠️ Это последнее обновление, закрывающее проблемы с безопасностью для PostgreSQL версии 12. Больше эта версия обновляться не будет. Так что, стоит подумать о переходе на новую версию 😏.

Обновление закрыло целых 4е дырки в безопасности и исправило множество мелких багов и недочетов.

Патчи безопасности:

📌 Устранение уязвимости в безопасности на уровне строк. Эту уязвимость уже пытались исправить разработчики PostgreSQL, но пропустили случаи с подзапросами, WITH запросами, представлениями и функциями. Теперь пользователь, которому не положено видеть какие-либо строки согласно политике безопасности на уровне строк и не сможет их увидеть. Раньше была лазейка, которая потенциально могла привести к утечке данных.

📌 Использование ошибок libpq для перехвата данных. Использование клиентом сообщения об ошибке сервера в PostgreSQL позволяло серверу, не являющемуся доверенным в текущих настройках SSL или GSS, предоставлять произвольные ненулевые байты приложению libpq. Это исправление предотвращает так называемые атаки "man-in-the-middle", когда злоумышленник мог отправить длинное сообщение об ошибке, которое пользователь мог принять за допустимые результаты запроса.

📌 Ошибка в командах PostgreSQL SET ROLE, SET SESSION AUTHORIZATION.
Эта ошибка позволяла пользователю с меньшими привилегиями просматривать или изменять строки, которые ему собственно изменять или просматривать не положено, из-за того, что эти команды сбрасывали идентификатор пользователя на неверный.

📌 Изменения переменных среды PostgreSQL PL/Perl приводят к выполнению произвольного кода. Эта ошибка касается использования PostgreSQL совместно с PL/Perl. Неправильный контроль переменных среды позволял непривилегированному пользователю базы данных их изменять, что могло привести к выполнению произвольного кода.

Плюс исправили более 35 различных ошибок. Среди них есть такие:

✅ Исправление ошибки при присоединении или отсоединении разделов таблиц с ограничениями внешнего ключа;

✅ Несколько исправлений внесено в работу планировщика запросов;

✅ Исправлена ​​ошибка команды COMMIT PREPARED из-за которой после сбоя и восстановления могло потребоваться ручное удаление файлов;

✅ Уменьшено потребление памяти при логическом декодировании.

Более подробно со всеми обновлениями и исправлениями можно познакомиться на официальном сайте:

➡️ https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and-1221-released-2955/

Как видите, обновлений и исправлений приличное количество. Так что не запускаем свою PostgreSQL и поддерживаем ее в актуальном состоянии 😉.

#pgnews

🔬Волатильность (изменчивость) функций в PostgreSQL.

Давайте разбираться что это такое и зачем нам вообще об этом нужно знать 🤔.

Волатильной или изменчивой функцией будет считаться функция, значение которой будет постоянно меняться даже если она сканирует одну и ту же таблицу. Такие функции "опасны" тем, что планировщик запросов PostgreSQL не может их оптимизировать, так как значения все время разные. Поэтому планировщик даже не будет и пытаться их оптимизировать, параметризировать и т.д., а просто будет выполнять эту функцию как есть в каждой строке запроса. А это в свою очередь может привести к более медленному выполнению запроса.

В PostgreSQL существует несколько типов волатильности функций:

📌 VOLATILE. Те самые "нехорошие" функции, которые возвращают разные значения с одинаковыми аргументами даже в рамках сканирования одной таблицы. Такая функция может модифицировать данные в базе данных, а оптимизатор PostgreSQL не будет даже пытаться делать каких-то предположений о ее работе. Соответсвенно такая функция будет заново рассчитываться для каждой строки запроса. Данный тип волатильности для функции подразумевается PostgreSQL по умолчанию, но мы можем его переопределить (об этом ниже). К таким функциям относятся различные функции, которые работают со случайными значениями, или возвращают текущее время. Например: random(), currval() и timeofday() и т.д.;

📌 STABLE. Такие функции возвращают одинаковый результат в рамках одного запроса, но могут возвращать разные результаты в разных запросах. Такие функции не могут модифицировать базу данных. Благодаря такому поведению, планировщик может заменить множество вызовов функции одним. Такие функции можно безопасно использовать в условии поиска по индексу;

📌 IMMUTABLE. Такие функции будут возвращать одинаковые значения всегда. Они тоже не могут модифицировать базу данных и позволяют планировщику заранее вычислить их значение.

Указать тип волатильности функции мы можем в момент ее создания с помощью команды CREATE FUNCTION. Если этого не сделать, то по умолчанию будет подразумеваться VOLATILE.

Следует также помнить, что волатильность функции влияет на видимость ею данных, внесенных или изменных другими параллельно выполняющимися транзакциями. Функции с типом VOLATILE будут видеть такие данные, а вот STABLE и IMMUTABLE - нет. STABLE и IMMUTABLE функции используют снимок, полученный в начале вызывающего запроса, тогда как функции VOLATILE получают свежий снимок в начале каждого запроса внутри транзакции. Функции STABLE и IMMUTABLE не замечают изменений в базе данных, произведённых ими, поскольку они не попадают в моментальный снимок данных. Поэтому PostgreSQL требует, чтобы функции STABLE и IMMUTABLE не содержали никаких SQL-команд, кроме SELECT, чтобы избежать некорректной модификации данных.

Несмотря на такое требование мы можем использовать VOLATILE функции внутри STABLE и даже IMMUTABLE функций и модифицировать данные. Но если вы так делаете, то следует помнить, что STABLE и IMMUTABLE все равно не будут видеть изменения, внесённые VOLATILE функциями, что может привести к некорректной модификации данных в базе.

Волатильность функций может существенно повлиять на производительность запроса и даже на корректность данных, возвращаемых им. Так что, о существовании такой штуки очень полезно знать, она может пригодиться как при оптимизации, так и при отладке запросов.

На этом все! До связи!

#pl/pgsql #pgbase

📊 Представление pg_shmem_allocations в PostgreSQL.

Буферный кэш играет ключевую роль в PostgreSQL в обеспечении производительности работы базы данных. Но кроме буферного кэша, используемого для обработки данных, в общей памяти есть еще различные служебные структуры, которые используются всеми процессами PostgreSQL, как клиентскими, так и серверными.

Про расширение pg_buffercache мы уже писали, но оно показывает только данные об использовании общего кэша под таблицы и индексы. А вот чтобы посмотреть служебные структуры в общей памяти, нам и пригодится представление pg_shmem_allocations. Давайте разбираться!

Каждая строка представления pg_shmem_allocations представляет собой информацию о конкретной служебной структуре в общей памяти PostgreSQL. Запрос к представлению простой, так как само представление содержит совсем немного полей:

SELECT *
FROM pg_shmem_allocations
ORDER BY size DESC LIMIT 10;

Если посмотреть в вывод представления, то самой большой структурой по объему занимаемой памяти будет Buffer Blocks. Это и есть буферный кэш, который содержит пользовательские данные. Более подробно его можно исследовать с помощью того же расширения pg_buffercache.

Если не ограничивать представление в выводе, то можно увидеть большое кол-во служебных структур, содержащихся в памяти. Их количество может варьироваться в зависимости от версии PostgreSQL и подключённых дополнительных расширений и модулей.

Само представление pg_shmem_allocations содержит следующие поля:

📌 name - имя структуры, по которому можно примерно понять ее назначение. Если в списке присутствует имя NULL, то это сегмент неиспользуемой памяти, если есть имя <anonymous> - то это анонимный сегмент памяти, который может использоваться для хранения информации о блокировках;

📌 off - смещение в области общей памяти, с которого начинается сегмент структуры. Для анонимных сегментов значение смещения будет NULL;

📌 size - размер сегмента в байтах;

📌 allocated_size - размер сегмента с учетом выравнивания в памяти (padding). О выравнивании мы уже писали. Для свободной памяти и анонимных сегментов значения size и allocated_size будут всегда равны.

В итоге: представление pg_shmem_allocations будет не очень полезно в повседневном мониторинге производительности и работоспособности вашей СУБД. Но оно может очень сильно помочь в целях контроля расхода оперативной памяти, или отладки какого-либо кода.

На этом все! До связи!

#pg_monitor

#pgmeme

#pgmeme

🔬 Логика работы и стратегии TOAST в PostgreSQL.

Продолжаем разбираться с механизмом TOAST в PostgreSQL. В этой заметке посмотрим на логику его работы и стратегии, применяемые при сжатии или разбиении строк.

Логика работы алгоритма TOAST такая: если размер строки (кортежа) превышает 2Kb, то алгоритм проходит по всем атрибутам строки и сжимает ее до тех пор, пока она не будет удовлетворять этому условию. От куда взялся размер строки в 2Kb, если размер страницы 8Kb?

Расчет тут такой: должно влезть 4е строки в одну страницу (2Kb * 4). Соответсвенно, если строка больше 2Kb, то 4е строки не поместятся на одну страницу и придется разбивать строку на две страницы, что в свою очередь негативно скажется на производительности базы данных. Поэтому PostgreSQL будет пытаться впихнуть все 4е строки на одну страницу, применяя к ним механизм сжатия.

Если строки удастся сжать до требуемого размера, то они останутся в основной таблице. Если же сжатие не поможет, то строка будет разбита на более мелкие кусочки и помещена в отдельную тост-таблицу.

Для сжатия строк TOAST использует специальный алгоритм компрессии, называемый pglz (PostgreSQL Lempel-Ziv). Этот метод сжатия используется по умолчанию, но можно использовать еще и lz4, если PostgreSQL была изначально скомпилирована с параметром with-lz4. Устанавливать алгоритм компрессии для TOAST можно с помощью параметра default_toast_compression. Алгоритм сжатия можно установить для конкретной колонки таблицы, введя параметр COMPRESSION в команду CREATE TABLE или ALTER TABLE.

У TOAST существует четыре вида стратегии:

📌 PLAIN. Эта стратегия применяется только в том случае, если тип данных в столбце не совместим с механизмом TOAST. Т.е. в данном случае, строка не будет ни сжата, ни перемещена в отдельную тост-таблицу;

📌 EXTENDED. Эта стратегия используется по умолчанию. TOAST сначала пытается сжать значение, если не получилось, то перемещает его в тост-таблицу;

📌 EXTERNAL. При такой стратегии допускается только отдельное хранение больших данных в тост-таблицах, но не сжатие. Эта стратегия ускорит операции над большими столбцами с типами данных text и bytea, но и ценой такого ускорения будет больший расход места на дисках;

📌 MAIN. Здесь наоборот, допускается сжатие, но не отдельное хранение. Но если сжатие не поможет ни как, то такие данные все равно будут храниться в отдельных тост-таблицах.

Есть несколько конфигурационных параметров, с помощью которых мы можем повлиять на работу механизма TOAST:

📌 TOAST_TUPLE_THRESHOLD - размер строки, при котором срабатывает TOAST. По умолчанию обычно 2Kb;

📌 TOAST_TUPLE_TARGET - минимальный размер строки, до которого TOAST будет ее сжимать или выносить в отдельную тост-таблицу. Значение по умолчанию так же 2Kb.

На этом пока что все! До связи!

#pgbase #pgsettings

❓Как научиться извлекать максимальную пользу из данных?

Приходите на курс "Основы Data Governance" с Александром Бараковым от BI Consult и получите актуальные навыки в трендовой теме управления данными! Data Governance позволит вам навести порядок в данных, ускорить разработку в ИТ-системах, повысить надежность и качество данных, аналитики и отчетности.

📆Даты: 4-13 декабря 2024 года.

Формат курса: 5 встреч по 3 часа интенсива в онлайн формате для CDO, CIO, Data stewards, BI-менеджеров, Операционных директоров и всех кому тема близка.

Автор курса: Александр Бараков - эксперт с 10-летним опытом ведения Data Governance проектов.

Курс позволит сформировать глубокое понимание темы Data Governance и 'примерить' все это на бизнес реалии вашей организации. По итогам курса у каждого слушателя будет сформирована готовая стратегия по внедрению Data Governance в свою компанию и понимание того, как настроить управление данными в любом бизнесе.

🗂На курсе мы погрузимся в темы:

• Information Management (IM)
• Data Quality (DQ)
• Data Governance (DG) (основные элементы, технологии и практики)
• Master Data Management (MDM)
• Управление и оценка эффективности DG

Каждый участник будет заполнять excel-гайд своего проекта Data Governance, применяя разделы курса на контекст своей компании.

📌Почему стоит выбрать этот курс?

• Сильная база: Вы поймете почему данные - это важно, в чем их реальная ценность и как 'продавать' проект менеджменту.
• Практическая направленность: Курс включает реальные кейсы и задачи из бизнеса.
• Нетворкинг: Участвуйте в активном обмене опытом с другими участниками курса и расширьте свою профессиональную сеть. На нашем курсе обучаются CDO и CIO таких компаний, как Максидом, Сбербанк, ВК, Sunlight и KFC.

🔝Записаться на курс

Реклама: ИП Громов С.Л. ИНН: 781133862981 Erid: 2VtzqvpDKKs

🔬 Недостатки TOAST в PostgreSQL и как с ними жить.

Про технологию TOAST в PostgreSQL, я думаю, вы все прекрасно знаете. Кратко напомним, что это такое.

TOAST (The Oversized Attribute Storage Technique) - это механизм хранения больших колонок в PostgreSQL. Все дело в том, что в PostgreSQL размер одной записи в таблице не может превышать размер страницы (по умолчанию 8Kb). Как только запись превысит размер страницы, в дело вступает TOAST. TOAST разбивает значение на кусочки, или сжимает его (зависит от выбранной стратегии) и хранит в специальной таблице.

При этом оригинальное значение в основной таблице подменяется на ссылку (TOAST Pointer), которая хранит служебную информацию, такую как: ID тост-таблицы, ID записи в тост-таблице, длина исходного и фактически хранимого значения.

К плюсам TOAST можно отнести:

📌 Технология разработана уже давно и работает стабильно и без нареканий;

📌 TOAST работает абсолютно незаметно для пользователей и приложений, работающих с базой данных.

Но, к сожалению и недостатков у TOAST хватает:

📌 Ограничение на 2^32 уникальных ID значений в тост-таблице. То есть, больше чем 2^32 значений просто не может попасть в тост-таблицу. При достижении этого значения больше невозможно сделать операции INSERT или UPDATE в основную таблицу, если хотя бы одна колонка при этом попадет в TOAST;

📌 В тост-таблицах нет операций UPDATE. Каждая старая строка помечается как "мертвая", а на ее место вставляется новая. Место под новые строки освободиться только тогда когда в тост-таблицу придет VACUUM, как следствие, это может вести к распуханию тост-таблиц;

📌 Тост-таблица имеет точно такое же ограничение на объем данных, как и простая таблица в PostgreSQL на 32Tb. На сильно нагруженных базах данных такой объем уже не кажется очень большим. А учитывая, что в тост-таблицы попадают все значения больше 8Кб, такая таблица может достигнуть ограничения быстрее чем основная, что опять же приведет к невозможности операций INSERT и UPDATE основной таблицы;

📌 TOAST является частью ядра PostgreSQL, что сильно затрудняет его модификацию.

В основном, недостатки механизма TOAST связаны с несколькими устареванием данного механизма под современные реалии увеличившегося в разы объема обрабатываемых данных, по сравнению с тем объемом когда TOAST только появился.

Учитывая ограничения TOAST, нужно следить за разрастаниями тост-таблиц и месте на дисках, проверять как часто в такие таблицы приходит AUTOVACUUM и не распухают ли они.

В будущих постах еще более подробно поговорим о механизме TOAST. До связи!

#pgbase

#pgmeme

🛠️ Пишем в лог таблицы, используемые в запросах (расширение pg_relusage).

Сегодня у нас для вас свежее расширение, которое позволит узнать какие таблицы реально используются в вашей базе данных, а какие просто занимают место. Речь пойдет про расширение pg_relusage. Официальный GitHub расширения находится здесь:

➡️ https://github.com/adept/pg_relusage

Расширение поддерживает версии PostgreSQL с 9.5 до 17.

Зачем нам может это понадобиться? Например, если у нас есть какая-то рукотворная база данных и мы хотим ее оптимизировать и удалить неиспользуемые таблицы. Конечно, такой трюк не пройдет с базами данных, которые используются каким-то проприетарным приложением, типо 1с. Удаление таблиц из таких баз чревато отказом в работе приложения и полной катастрофой 🤬🤕. Или, если мы хотим переместить неиспользуемые таблицы на более медленный диск, чтобы освободить место на быстром диске.

pg_relusage запишет в лог PostgreSQL таблицы, которые используются в запросах. Таким образом мы можем отсеять такие таблицы от неиспользуемых. Конечно, чтобы получить реальную картину, нужно чтобы расширение поработало какое-то время (желательно не в выходные 😁).

Расширение использует так называемые хуки (hooks), которые внедряются в работу планировщика запросов PostgreSQL и позволяют узнать информацию о таблицах, используемых в запросах. В связи с этим расширение должно быть собрано под конкретную версию PostgreSQL. Делается это с помощью команды

make install

После сборки и установки расширения его нужно прописать в shared_preload_libraries и перезапустить службу PostgreSQL.

После этого расширение начнет писать в логи информацию вида:

relations used: имя таблиц, участвующих в запросах

У расширения есть парочка конфигурационных параметров:

📌 pg_relusage.log_level (по умолчанию LOG) - уровень сообщения. Значения этого параметра аналогичны параметру PostgreSQL - client_min_messages. Этим параметром вы можете повысить уровень сообщения до предупреждения или ошибки;

📌 pg_relusage.rel_kinds (по умолчанию 'riSvmfp') - вид отношений, о которых будет собираться информация об использовании. Значение этого параметра представляет собой код. Такие же коды используются в колонке relkind в системном каталоге pg_class. Со списком все кодов можно ознакомиться в документации:

➡️ https://www.postgresql.org/docs/current/catalog-pg-class.html

На этом все! До связи!

#pgext

🤬 Ошибка "Relation ‘table_name’ Does Not Exist" в PostgreSQL.

Ошибка достаточно распространённая и причин ее появления много: от простых ошибок в запросах, до серьезного повреждения базы данных. Давайте разбираться!

1️⃣ Ошибки в запросах. Это на самом деле самая банальная и распространённая причина данной ошибки. Так что, прежде чем бросаться восстанавливать базу данных из резервной копии, стоит повнимательнее посмотреть на ваш запрос.

Самое частое о чем забывают - это то, что имена таблиц в PostgreSQL регистрозависимые. Т.е. если ваша таблица называется, например MyTable, то при запросе к ней, ее имя нужно заключать в кавычки, вот так:

SELECT * FROM "MyTable";

Ну и конечно, нужно удостовериться, что вы вообще пишете правильное имя таблицы в запросе. Посмотреть какие таблицы у вас существуют в базе данных можно с помощью команды psql - \dt.

2️⃣ Путь поиска. Путь поиска в PostgreSQL определяет в том числе в какой схеме базы данных по умолчанию искать таблицы. Если у вас есть схема, созданная вами и в ней хранятся какие-то таблицы, то вполне возможно, что эта схема просто не включена в путь поиска PostgreSQL.

Варианта решения тут два. Обращаться к таблице в запросе с указанием имени схемы, вот так;

SELECT * FROM shema_name.table_name;

Или включить вашу схему в путь поиска PostgreSQL. Посмотреть что включено в путь поиска можно с помощью вот такой команды:

SHOW search_path;

Добавить вашу схему в путь поиска можно вот так:

SET search_path TO myschema, public;

Не забывайте добавить схему public, это схема, в которой по умолчанию хранятся таблицы в PostgreSQL.

3️⃣ Материализованные представления. Про них мы уже писали. Здесь может случиться, так что у вас появилась новая таблица, которую вы включили в материлизованное представление, а само представление обновить забыли.

Чтобы обновить материлизованное представление, можно воспользоваться следующей командой:

REFRESH MATERIALIZED VIEW view_name;

4️⃣ Отсутствие прав на таблицу. Тоже частая причина данной ошибки, о которой забывают.

Чтобы проверить права на таблицу можно воспользоваться следующей командой:

\dp table_name

А чтобы выдать права на чтение, можно выполнить вот такую команду:

GRANT SELECT ON table_name TO user_name;

5️⃣ Повреждение базы данных. Ну и самое страшное, что может вызвать данную ошибку - повреждение базы данных. Причин тут может быть очень много: от отказа оборудования, до случайных действий пользователей или админа, которые привели к удалению целевой таблицы.

Универсальный рецепт в такой ситуации - восстановление из резервной копии либо всей базы, либо отдельно взятой таблицы. Если копии нет - то беда. Можно пытаться восстанавливать файлы базы различными способами, проверять жесткие диски на ошибки и т.д. В любом случае лучше до такого не доводить, иметь свежие резервные копии и мониторить оборудование.

На этом все! До связи!

#pgerrors

📊 Обнаружение блокировок с помощью представления pg_locks.

Тут в комментариях к одному из постов про pg_stat_statements появился запрос на заметку про представление pg_locks. Так что сегодняшний пост будет именно про него 😊.

С помощью представления pg_locks можно получить информацию об удерживаемых блокировках. Запросами только к этому одному представлению можно получить детальную информацию по блокировкам, но на практике данное представление хорошо соединять с полями представления pg_stat_activity, чтобы получить еще более развернутую информацию.

Каждая строка представления pg_locks содержит информацию об объекте базы данных на который установленная блокировка, или для которого требуется установка блокировки, а также информация о том кто затребовал данную блокировку.

Когда несколько процессов требуют блокировку одного и того же объекта базы данных, то этот объект в выводе представления будет показан несколько раз.Объектами блокировки могут быть как таблицы, так и строки, или даже отдельные страницы таблицы. Соответсвенно, если блокировка снята, то информация о ней из представления убирается.

Представление pg_locks покажет нам информацию о рекомендательных блокировках (advisory lock), эти блокировки устанавливает само приложение, но не покажет информацию о легких блокировках (lightweight lock, LWLock), эти блокировки работают на более низком уровне и контролируют доступ к структурам памяти PostgreSQL.

Следует помнить о том, что представление pg_lock собирает информацию только на данный момент времени (это касается и pg_stat_activity) и не имеет кумулятивной статистики. Чтобы увидеть всю структуру и поля pg_lock можно воспользоваться командой:

\d+ pg_locks

Остановимся на наиболее важных (наш взгляд) полях представления более подробно:

📌 relation, page, tuple - отношение, номер страницы и номер строки внутри страницы;

📌 virtualxid, transactionid - виртуальный и фактический номер транзакции;

📌 locktype - тип блокировки;

📌 database - OID базы, в которой возникла блокировка;

📌 pid - идентификатор процесса. Это поле чаще все используется для объединения с представлением pg_stat_activity, чтобы можно было получить дополнительную информацию, такую как: имя пользователя, текст запроса и т.д.;

📌 granted - этот флаг указывает удерживается блокировка (true), или ожидает удержания (false);

📌 waitstart - время перехода в ожидание блокировки.

Примеры запросов к представлению pg_locks можно посмотреть здесь:

➡️ https://wiki.postgresql.org/wiki/Lock_Monitoring

И здесь:

➡️ https://wiki.postgresql.org/wiki/Lock_dependency_information

Запросы достаточно объемные и в пост не влезут. Да и вообще, сайт сам по себе полезен, рекомендуем ознакомиться 😊.

На этом на сегодня все! До связи!

#pgmonitor

#pgmeme

#pgmeme

📊 Глубокое погружение в pg_stat_statements, часть 6.

Сегодня у нас 6-я, она же заключительная часть постов про pg_stat_statements. Мы рассмотрели с вами наиболее важные нюансы работы расширения. В этой части пробежимся по основным настройкам pg_stat_statements и приведем примеры наиболее полезных и важных на наш взгляд запросов.

Основные настройки pg_stat_statements, которые влияют на его работу и получаемые данные:

📌 pg_stat_statements.max - максимально кол-во отслеживаемых типов запросов, т.е. кол-во строк, выводимых в представлении. Значение по умолчанию 5000;

📌 pg_stat_statements.track — уровень отслеживаемых запросов. Возможные значения:
top- отслеживать вызовы верхнего уровня,то есть запрошенные клиентом (по умолчанию);

all - отслеживать все вызовы, включая функции и процедуры;

none - не отслеживать ничего.

📌 pg_stat_statements.track_utility - отслеживать статистику для служебных команд, отличных от SELECT, INSERT, UPDATE и DELETE;

📌 pg_stat_statements.track_planning - сбор статистики о планировании запросов;

📌 pg_stat_statements.save - сохранение статистики при отключении службы PostgreSQL.

Переходим к запросам!

Запросы с наибольшим количеством вызовов:

SELECT
sum(calls) AS total_calls,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(calls) DESC
LIMIT 10;

Запросы с наибольшим количеством возвращаемых строк:

SELECT
sum(rows) AS total_rows,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(rows) DESC
LIMIT 10;

Запросы с наибольшим временем выполнения:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time), 'HH24:MI:SS'
) AS exec_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query ORDER BY sum(total_exec_time) DESC LIMIT 10;

Запросы с наибольшим временем ввода-вывода:

SELECT
to_char(
interval '1 millisecond' * sum(
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time
), 'HH24:MI:SS.MS'
) AS io_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
WHERE blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time > 0
GROUP BY query
ORDER BY sum(blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time) DESC
LIMIT 5;

Запросы, с наибольшем временем работы CPU:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time - (
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time)),
'HH24:MI:SS') AS cpu_time,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(total_exec_time - (
blk_read_time + blk_write_time + temp_blk_read_time + temp_blk_write_time)
) DESC LIMIT 10;

Здесь мы из общего времени выполнения запроса вычитаем, время затраченное запросом на операции ввода-вывода. Оставшее время - это время затраченное CPU на выполнение запроса.

Агрегированная таблица по времени выполнения запроса и процентам операций ввода-вывода и работы CPU:

SELECT
to_char(
interval '1 millisecond' * sum(total_exec_time),
'HH24:MI:SS'
) AS exec_time,
(100 * sum(
blk_read_time + blk_write_time +
temp_blk_read_time + temp_blk_write_time
) / sum(total_exec_time))::numeric(5,2)::text || ' / ' ||
(100 * sum(total_exec_time - (
blk_read_time + blk_write_time +
temp_blk_read_time + temp_blk_write_time)
) / sum(total_exec_time))::numeric(5,2) AS "io / cpu, %",
left(query, 48) AS query_trunc
FROM pg_stat_statements
GROUP BY query ORDER BY sum(total_exec_time) DESC LIMIT 10;

Такая таблица будет полезна тем, что можно будет понять на что больше тратит запрос время при выполнении.

Запросы, которые больше всех пишут данные в журнал WAL:

SELECT
pg_size_pretty(sum(wal_bytes)) AS wal_volume,
left(query, 64) AS query_trunc
FROM pg_stat_statements
GROUP BY query
ORDER BY sum(wal_bytes) DESC
LIMIT 10;

На этом все! Если у вас есть какие-то еще полезные запросы для pg_stat_statements, оставляйте их в комментариях 👇 . До связи!

#pgmonitor #pgext

📊 Глубокое погружение в pg_stat_statements, часть 5.

В прошлой заметке мы с вами смотрели как считать кумулятивные метрики pg_stat_statements на основе дифференциации по временным интервалам. В этой заметке посмотрим еще два варианта расчета кумулятивных метрик.

Начнем с дифференциации по количеству вызовов (calls). Расчет таких метрик достаточно прост и понятен, берем два значение метрики, собранные в разные промежутки, находим разницу между ними и делим на разницу между вызовами за эти же промежутки. Формула будет такой:

dM/dc

где M дифференцированное значение метрики, а c - дифференцированное значение кол-ва вызовов.

Вместо M мы будем подставлять все теже метрики из предыдущей заметки. Кроме, собственно, calls, так как в данном случае всегда будем получать единицу 😊. Что нам дадут посчитанные таким образом метрики? Они нам покажут усредненные характеристики группы запросов и позволят выявить аномалии в них.

Перечислять все метрики мы не будем, учитывая что они такие же как и в предыдущей заметке. Подсветим только наиболее важные:

M– total_plan_time + total_exec_time. Получим среднее время выполнения запроса в конкретной группе. Очень важная метрика для мониторинга производительности запросов. Если ее применить вообще для всех агрегированных запросов на сервере, то получим среднюю задержку выполнения запросов на всем сервере PostgreSQL. Эта информация ценна тем, что система комсулятивной статистики самой PostgreSQL не предоставляет пока что таких данных.

Если переходить к трактовке полученных данных, то к примеру, если у вас среднее время выполнения запросов равно 2мс, а в какой-то группе запросов оно подскочило до 10, можно переходить к поиску виновника и оптимизации запроса. Также данную метрику можно использовать в качестве тестирования нагрузки на ваш сервер PostgreSQL.

3й тип расчета метрик, полученных через pg_stat_statements - процентное отношение. Рассчитывается так: сначала рассчитываем временную дифференциацию для выбранной группы запросов, а потом делим полученное значение на сумму значений всех остальных групп запросов 🤯 Если предыдущие способы расчета метрик нам дадут абсолютные значения типо вызовы в секунду или гигабайты, то данный тип покажет нам относительные значения. Т.е. с помощью процентных отношений мы можем выявить основных игроков в нашей рабочей нагрузке в различных аспектах, типо частоты, времени, кол-ве вызовов и т.д.

Процентные отношения хороши тем, что помогут нам понять насколько метрики, полученные предыдущими способами приемлемые на фоне общей нагрузки или нет. Т.е. если мы увидим, например, кол-во запросов в секунду равное 1000 в какой-то группе запросов и подумаем, что это много и что-то тут не так, но увидим процентное отношение во всей нагрузке, например в 3%, то поймем, что оптимизация этой группы запросов нам не даст большого прироста производительности. Другое дело, если процентное отношение покажет, например 50%, тогда стоит уже задуматься над оптимизацией.

На этом будем заканчивать марафон постом по pg_stat_statements. Конечно данная серия постов не претендует на полное описание всех нюансов использования pg_stat_statements, но даст более глубокие знания по теме. Если вам нужно еще более глубоко погрузиться в нюансы использования pg_stat_statements, то можете почитать документацию.

Финальным постом этой серии собираем полезные запросы к pg_stat_statements. До связи!

#pgmonitor #pgext