notes/pages/pg.txt

*****************************************
В PostgreSQL анализ статистики для мониторинга может быть выполнен с использованием различных инструментов и запросов к системным представлениям. Вот несколько шагов и примеров:

1. pg_stat_statements:

Включите расширение pg_stat_statements в вашей базе данных. Это расширение отслеживает выполнение SQL-запросов и предоставляет информацию о времени выполнения, количестве вызовов и т.д.:

Включение расширения (выполнить один раз)

CREATE EXTENSION pg_stat_statements;

Запрос для анализа

SELECT * FROM pg_stat_statements ORDER BY total_time DESC;

Этот запрос вернет список SQL-запросов, отсортированный по общему времени выполнения в порядке убывания.

📌 Посмотреть статистику времени выполнения запросов для конкретной базы данных:

SELECT * FROM pg_stat_statements WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'your_database_name');

Замените 'your_database_name' на имя вашей базы данных.

📌 Посмотреть статистику времени выполнения запросов для конкретного пользователя:

SELECT * FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = 'your_username');

Замените 'your_username' на имя пользователя.

📌 Посмотреть статистику времени выполнения запросов для конкретной сессии:

SELECT * FROM pg_stat_statements WHERE pg_backend_pid() = procpid;

Этот запрос вернет статистику выполнения запросов для текущей сессии. Обратите внимание, что pg_backend_pid() - это функция, возвращающая идентификатор процесса текущей сессии.

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

2. pg_stat_bgwriter:

Данное представление предоставляет статистику по работе фонового процесса записи:

SELECT * FROM pg_stat_bgwriter;

3. pg_stat_database:

Это представление предоставляет статистику по использованию баз данных:

SELECT * FROM pg_stat_database;

4. pg_stat_user_tables:

Позволяет получить статистику по таблицам:

SELECT * FROM pg_stat_user_tables;

5. pg_stat_user_indexes:

Статистика по индексам:

SELECT * FROM pg_stat_user_indexes;

6. pg_locks:

Позволяет отслеживать текущие блокировки в базе данных:

SELECT * FROM pg_locks;

7. pg_stat_activity:

Дает информацию о текущих активных сессиях:

SELECT * FROM pg_stat_activity;

8. pg_stat_replication:

Если вы используете репликацию, этот запрос предоставит статистику по репликации:

SELECT * FROM pg_stat_replication;

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

*****************************************
Смотрим контекст памяти в PostgreSQL.

Сначала давайте разберемся что вообще это такое - Контекст памяти. Как мы все прекрасно знаем, PostgreSQL написан на языке программирования C, а управление памятью в C непростая задача. Программа должна явно освободить всю динамически выделенную ей память, если она этого не сделает, то может случится утечка памяти. Это, в свою очередь, может привести к постоянно растущему потреблению памяти, что в конечном итоге окажется фатальным для вашего сервера PostgreSQL. Память просто закончится 😥

Чтобы снизить опасность утечек памяти, PostgreSQL внедрила собственную систему управления памятью: контексты памяти. Контексты памяти - это участки памяти, которые могут увеличиваться по требованию программы. PostgreSQL ни когда не будет использовать память напрямую, она будет ее запрашивать из этих самых контекстов. Если памяти будет недостаточно, PostgreSQL просто расширит контекст памяти.

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

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

Как правило, DBA и разработчикам, работающим с PostgreSQL вообще не нужно заботиться об этих контекстах памяти, но когда на сервере случиться утечка памяти расследовать ее все же придется 😣

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

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/utils/mmgr/README;hb=HEAD

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

До 14-й версии PostgreSQL для расследования утечек памяти нужно было использовать какой-то дебагер или профайлер или средства ОС. В 14-ю версию PostgreSQL добавили специальное представление pg_backend_memory_contexts, которое нам позволит заглянуть в контексты памяти PostgreSQL и выяснить кто всю ее съел 😡

Для начала давайте посмотрим сколько памяти употребила наша сессия:

SELECT pg_size_pretty(sum(used_bytes)) AS “TotalMem by session” FROM pg_backend_memory_contexts;

В ответ получим кол-во израсходованной памяти в байтах.

Следующим запросом мы увидим топ 10 контекстов-потребителей оперативки:

SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 10;

А таким запросом мы увидим все контексты памяти и размер ее потребления каждым:

SELECT name,pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts;

Пользоваться представлением может только роль с правами суперпользователя или с правом pg_read_all_stats.

*****************************************

16-я версия PostgreSQL принесла нам много нововведений и улучшений как в плане технической части, так и в плане мониторинга. Одним из таких нововведений в мониторинге стало представление pg_stat_io.

До появления этого представления нагрузку на дисковую подсистему, которую генерирует PostgreSQL можно было посмотреть с помощью средств и утилит операционной системы, профайлеров и т.д. Короче приходилось как-то извращаться 😪 Теперь же, в 16-й версии PostgreSQL все стало просто и понятно. Еще один повод обновиться 😉

Простейший запрос к этому представлению может выглядеть вот так:

SELECT * FROM pg_stat_io WHERE reads <> 0 OR writes <> 0;

Теперь давайте разбираться с полями в выводе запроса.

backend_type

Это поле показывает тип процесса PostgreSQL. Я думаю вы помните, что PostgreSQL использует так называемую процессную модель. Т.е. когда выделяется отдельный процесс в системе на каждое действие, в том числе и на подключение клиента. Вот имена этих процессов это поле и показывает.

object

В этом поле может быть одно из двух значений: relation и temp relation. Т.е. здесь мы увидим вид объекта базы данных: постоянная таблица, индекс или другой вид объекта или временная таблица.

context

В этом поле уже может быть четыре вида значений: normal, vacuum, bulkread или bulkwrite. Это поле показывает тип (контекст) нагрузки на файловую систему. 
normal - имеются в виду операции с общим буферным кэшем, тогда данные читаются или пишутся в него. Такие операции считаются "нормальными".
vacuum - как думаю понятно из названия, это операции, связанные с процессом VACUUM.
bulkread или bulkwrite - это операции, связанные с чтением и записью большого кол-ва данных за пределами буферного кэша. Т.е. это может быть, например, последовательное сканирование большой таблицы или загрузка большого объема данных с помощью операции COPY.

reads и writes

Кол-во операций чтения и записи. Здесь показывается именно кол-во, а не размер в байтах.

read_time и write_time

Время, затраченное процессом на чтение и запись. Эта колонка может быть помощником в выявлении проблем с производительностью. Чтобы значение в этой колонке заполнялось нужно включить параметр track_io_timing в конфигурационном файле postgresql.conf.

hits

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

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

➡️ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW

Вот еще парочка полезных запросов к этому представлению:

✅ Смотрим дисковую активность процесса AUTOVACUUM.

SELECT * FROM pg_stat_io WHERE backend_type = 'autovacuum worker';

✅ Смотрим большие операции чтения и записи.

SELECT * FROM pg_stat_io WHERE io_context IN ('bulkread', 'bulkwrite') AND (reads <> 0 OR writes <> 0);

В общем, вариантов запросов к представлению много, все зависит от вашей задачи. Расследование проблем с операциями ввода/вывода всегда было важной задачей для администратора PostgreSQL. С новым представлением pg_stat_io теперь очень легко и быстро можно получить как общую картину по операциям чтения и записи, так отдельно по определенному процессу или контексту.

*****************************************

В PostgreSQL, операция Seq Scan (https://t.me/pg_guru/544) (последовательное сканирование) и Index Scan (https://t.me/pg_guru/146) (сканирование индекса) - это два основных метода доступа к данным, которые используются для выполнения запросов.

✅ Seq Scan (Последовательное сканирование):

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

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

✅ Index Scan (Сканирование индекса):

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

📌 Однако использование индексов не всегда эффективно, особенно если выбираемые данные включают большую часть таблицы или если индекс имеет низкую кардинальность (т.е. много строк с одинаковыми значениями в индексе).

Иногда PostgreSQL может выбрать Sequential Scan, даже если индекс существует, если он считает, что Sequential Scan будет более эффективным.

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


Помимо Seq Scan и Index Scan, PostgreSQL поддерживает и другие методы доступа к данным, которые могут использоваться для выполнения запросов. Некоторые из них включают:

✅ Bitmap Index Scan (Сканирование битовой карты индекса):

Этот метод используется, когда несколько индексов объединяются для выполнения запроса с использованием операции битовой карты.

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

✅ Bitmap Heap Scan (Сканирование битовой карты кучи):

Этот метод используется после Bitmap Index Scan для получения фактических строк из таблицы.

PostgreSQL использует битовую карту, чтобы определить, какие строки получить из таблицы.

✅ TID Scan (Сканирование по идентификатору строки):

Этот метод используется, когда PostgreSQL уже знает идентификаторы (TID) строк, которые нужно получить.
Обычно это происходит в результате других операций сканирования или операций слияния.

✅ Index Only Scan (Только сканирование индекса):

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

📌 Это более эффективно, чем обычное сканирование индекса, так как PostgreSQL может избежать обращения к данным таблицы.

✅ Nested Loop (Вложенный цикл):

Этот метод используется для выполнения соединений между таблицами.

PostgreSQL сканирует одну таблицу и использует результаты сканирования для поиска соответствующих строк в другой таблице.

✅ Hash Join (Хэш-соединение):

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

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

✅ Эти методы используются в различных сценариях выполнения запросов и могут быть комбинированы PostgreSQL в различные способы для оптимизации выполнения запросов. Вывод команды EXPLAIN позволяет увидеть, какие методы использовались PostgreSQL для выполнения конкретного запроса.

*****************************************

Рассмотрим более подробно метод доступа к данным Index Only Scan (только сканирование индекса).

✅ Другие методы доступа к данным в PostgreSQL которые используются для выполнения запросов мы рассмотрели ранее (https://t.me/pg_guru/697). 

✅ Index Only Scan (только сканирование индекса) - это оптимизация запроса в PostgreSQL, которая позволяет выполнять запросы, используя только индексы, без необходимости обращаться к фактическим данным таблицы. Это особенно полезно в случаях, когда все необходимые столбцы для выполнения запроса уже содержатся в индексе.

Вот как это работает:

📌 Предпосылки: 
Для того чтобы использовать Index Only Scan, PostgreSQL должна иметь доступ к индексу, который покрывает все столбцы, используемые в запросе, включая столбцы, которые возвращаются в результирующем наборе данных.

📌 План выполнения запроса: 
Планировщик запросов PostgreSQL решает, следует ли использовать Index Only Scan или нет, основываясь на статистике и стоимости доступа к данным. Если PostgreSQL решает, что Index Only Scan более эффективен, он будет использован.

📌 Использование индекса: 
Когда выполняется Index Only Scan, PostgreSQL анализирует предикаты запроса и определяет, какие строки данных нужно извлечь. Затем он использует индекс, чтобы найти соответствующие строки данных, не обращаясь к самим данным в таблице.

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

📌 Ограничения: 
Важно отметить, что Index Only Scan не всегда возможен. Например, если запрос включает столбцы, которые не были включены в индекс, или если таблица содержит большое количество обновлений, которые могут привести к несоответствиям индекса и данных, то PostgreSQL может решить, что Index Only Scan не является подходящим.

✅ В целом, Index Only Scan является мощным инструментом оптимизации запросов в PostgreSQL, который может значительно улучшить производительность при правильном использовании.

Для примера предположим, у нас есть таблица users с колонками id, name и age, и у нас есть индекс по колонке age.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INTEGER
);

CREATE INDEX idx_age ON users(age);

Теперь допустим, мы хотим выбрать имена пользователей и их возраст, которые старше 30 лет. Мы можем написать запрос следующим образом:


SELECT name, age
FROM users
WHERE age > 30;

Если PostgreSQL решает использовать Index Only Scan, то он может извлечь имена и возраст пользователей, используя только индекс idx_age, не обращаясь к данным в таблице users.

✅ Важно помнить, что использование Index Only Scan зависит от множества факторов, включая статистику данных, размер таблицы, а также текущие нагрузки на базу данных.

✅ PostgreSQL автоматически принимает решение о том, следует ли использовать метод Index Only Scan для выполнения запроса или нет, на основе статистики данных и стоимости доступа к ним. Это решение принимается оптимизатором запросов PostgreSQL в процессе составления плана выполнения запроса.

✅ Оптимизатор запросов PostgreSQL анализирует доступные индексы, статистику данных и другие факторы, чтобы решить, какие методы выполнения запроса будут наиболее эффективными. Если оптимизатор определяет, что использование Index Only Scan будет более эффективным, чем другие методы, то он будет выбран.

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

*****************************************

🔬 Правила видимости строк в PostgreSQL.

В этом посте рассмотрим на основании чего PostgreSQL принимает решения какая строка является видимой для транзакций, а какая является устаревшей.

Если описать механизм MVCC в PostgreSQL по-простому, то он работает примерно так:

1️⃣ Есть у нас в базе строка со значением А. Пользователь меняет ее значение А на Б (операция UPDATE);

2️⃣ PostgreSQL под капотом фактически не обновляет данные в строке, а вставляет новую строку со значением Б, а старую строку со значением А помечает как устаревшую. Таким образом у нас получается две версии одной строки;

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

Это, конечно, сильно упрощенный пример, но примерно так и работает MVCC в PostgreSQL. Важно помнить, что любая операция UPDATE в PostgreSQL, это фактически вставка новой строки и пометка старой строки неактуальной.

В PostgreSQL есть ряд механизмов и индикаторов , которые помогают ей разобраться с актуальностью версий строк. А именно:

✅ Transaction ID;
✅ xmin;
✅ xmax;
✅ CLOG (Commit Log);
✅ cid;
✅ transaction snapshot;
✅ hintbit.

Рассмотрим кратко каждый из них.

📌 Transaction ID

Здесь все просто: каждая транзакция в PostgreSQL имеет свой номер (txid). Это 32-битное целое число без знака, позволяющее использовать примерно 4,2 миллиарда идентификаторов транзакций.

📌 xmin

Это значение находится в заголовке каждой строки и содержит ID транзакции, которая вставила данную конкретную строку. В нашем примере выше, это строка со значением А.

📌 xmax

Это значение тоже находится в заголовке каждой строки и содержит ID транзакции, которая удалила данную конкретную версию строки. Если транзакция завершилась успешно (COMMIT), то это значение сигнал для PostgreSQL, что данная версия строки является устаревшей. В нашем примере, xmax появится у строки со значением А, а у новой строки появится xmin с номером транзакции, которая ее вставила (сделала UPDATE).

📌 CLOG (Commit Log)

CLOG (Журнал фиксации), о нем мы более подробно уже говорили (https://t.me/pg_guru/611).
CLOG — это структура данных, хранящаяся в общей памяти и сохраняющаяся в каталоге $PGDATA/pg_xact. Целью CLOG является запись статуса транзакций.

📌 cid

Идентификатор команды внутри транзакции. Также имеет свои системные поля cmin и cmax. Cmin - идентификатор команды, которая вставила строку внутри текущей транзакции, cmax - индентификатор команды, которая удалила строку внутри транзакции. Cid используется в основном для определения видимости строки внутри работающей транзакции. В общем, смысл такой же как для идентификаторов xmin и xmax, только внутри транзакции.

📌 Transaction snapshot

В PostgreSQL используется изоляция транзакций на основе снимков. Каждая транзакция делает свой снимок данных и работает только с ним. Какая версия строки попадет в снимок определяется на основании значений полей xmin и xmax. Если очень сильно упростить, то в снимке видны изменения сделанные транзакцией xmin и не видны изменения, сделанные транзакцией xmax. Т.е. видны те версии строк, которые уже появились в базе, но еще не удалились.

📌 hintbit

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

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

*****************************************

В PostgreSQL, операция Seq Scan (https://t.me/pg_guru/544) (последовательное сканирование) и Index Scan (https://t.me/pg_guru/146) (сканирование индекса) - это два основных метода доступа к данным, которые используются для выполнения запросов.

✅ Seq Scan (Последовательное сканирование):

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

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

✅ Index Scan (Сканирование индекса):

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

📌 Однако использование индексов не всегда эффективно, особенно если выбираемые данные включают большую часть таблицы или если индекс имеет низкую кардинальность (т.е. много строк с одинаковыми значениями в индексе).

Иногда PostgreSQL может выбрать Sequential Scan, даже если индекс существует, если он считает, что Sequential Scan будет более эффективным.

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


Помимо Seq Scan и Index Scan, PostgreSQL поддерживает и другие методы доступа к данным, которые могут использоваться для выполнения запросов. Некоторые из них включают:

✅ Bitmap Index Scan (Сканирование битовой карты индекса):

Этот метод используется, когда несколько индексов объединяются для выполнения запроса с использованием операции битовой карты.

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

✅ Bitmap Heap Scan (Сканирование битовой карты кучи):

Этот метод используется после Bitmap Index Scan для получения фактических строк из таблицы.

PostgreSQL использует битовую карту, чтобы определить, какие строки получить из таблицы.

✅ TID Scan (Сканирование по идентификатору строки):

Этот метод используется, когда PostgreSQL уже знает идентификаторы (TID) строк, которые нужно получить.
Обычно это происходит в результате других операций сканирования или операций слияния.

✅ Index Only Scan (Только сканирование индекса):

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

📌 Это более эффективно, чем обычное сканирование индекса, так как PostgreSQL может избежать обращения к данным таблицы.

✅ Nested Loop (Вложенный цикл):

Этот метод используется для выполнения соединений между таблицами.

PostgreSQL сканирует одну таблицу и использует результаты сканирования для поиска соответствующих строк в другой таблице.

✅ Hash Join (Хэш-соединение):

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

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

✅ Эти методы используются в различных сценариях выполнения запросов и могут быть комбинированы PostgreSQL в различные способы для оптимизации выполнения запросов. Вывод команды EXPLAIN позволяет увидеть, какие методы использовались PostgreSQL для выполнения конкретного запроса.

*****************************************

Рассмотрим различные способы отслеживания блокировок в PostgreSQL, начиная с более старых и заканчивая более новыми:

✅ pg_locks - это системное представление в PostgreSQL, даёт доступ к информации о блокировках, удерживаемых активными процессами на сервере баз данных. 

Тип: Системное представление.

Возможности: Предоставляет информацию о текущих блокировках в базе данных, включая тип блокировки, режим, PID процесса и другие.

Ограничения: Может быть сложно интерпретировать результаты, особенно в случае большого количества блокировок.

Старый метод: Используется в более старых версиях PostgreSQL.

✅ pg_blocking_pids():

Тип: Встроенная функция.

Возможности: Позволяет определить PID-ы процессов, которые блокируют заданный процесс.

Преимущества: Проще в использовании по сравнению с прямым анализом pg_locks.

Ограничения: Показывает только PID-ы блокирующих процессов, не предоставляет полной информации о блокировках.

✅ pg_stat_activity:

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

Тип: Системное представление.

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

Преимущества: Хорошо подходит для общего мониторинга активности базы данных.

Ограничения: Требует дополнительного анализа для определения блокировок.

✅ pg_locks_view:

В отличие от pg_locks,   может содержать дополнительные агрегированные данные или дополнительные столбцы для удобства анализа

Тип: Пользовательское представление.

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

Преимущества: Удобство использования по сравнению с pg_locks.

Ограничения: Может требовать дополнительной настройки.

✅ pganalyze:

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

pganalyze включает новое оповещение "Блокировка запросов", которое будет уведомлять вас, когда запрос блокирует другие запросы на время, превышающее указанный порог времени.

Чтобы получить информацию о блокировке, сборщик pganalyze использует функцию pg_blocking_pids(). 

Тип: Сервис мониторинга и анализа производительности.

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

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

Ограничения: Требует интеграции и может иметь платную модель подписки.

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

*****************************************