Showing posts from October, 2017

Deep dive into Postgres stats: pg_stat_progress_vacuum

Yet another post about vacuum...In today’s post I would like to talk about another stats enhancement that has been added in 9.6 and I hope will be expanded in the upcoming versions. The post is about pg_stat_progress_vacuum.

Everyone heard about vacuum (I will be referring to both the regular vacuum and autovacuum in this post), even those who are not familiar with Postgres very well. Vacuum is surrounded by many legends and horror stories, thousands discussions and topics about vacuum and its configurations have been discussed in the mailing lists, forums and blogs. But let’s leave this aside for a minute, since vacuum internals and vacuum configuration can be a subject for a separate post.

Новые диагностические тулзы на нашем сайте

Четыре небольших запроса которые помогут вам в работе с PostgreSQL
С выходом новой версии PotsgreSQL 10.0 мы решили предложить вам, нашим читателям, проверить насколько хорошо настроена ваша база данных.
Мы приглашаем Вас пройти на сайт и воспользоваться нашими скриптами для проверки состояния вашей базы. Проверка адресует ключевые проблемы с которыми к нам обращаются клиенты. Ниже мы предлагаем небольшой обзор основных ситуаций, которые вы сможете диагностировать нашими скриптами.
Запросы планируются медленно Одной из причин медленного ответа базы может быть длительное планирование запросов. В некоторых случаях время планирования может в десятки раз превышать время исполнения запроса. Если это наблюдается в вашей базе, то необходимо исследовать и устранить причину по которой планировщик долго работает.
Наш SQL запроспоможет оценить на сколько медленно работает тот или иной запрос, сравнив время его планирования и исполнения.
Время ответа базы периодически падает При неправильн…

Deep dive into postgres stats: pg_stat_activity and pg_locks

DBA's best friends: pg_stat_activity and pg_locks

Today, I would like to discuss another handy stats views - pg_stat_activity. This is really useful and powerful view the value of which is hard to measure. In short, the main goal of pg_stat_activity is to show the current activity in Postgres. Hence, if you would like to know what is going on in the database at any given moment, pg_stat_activity is the first place to check. Having worked with Postgres for several years now I’ve accumulated many useful queries. The majority of which I’ve already mentioned in the first article in these series. Though that post is two years old now, I’d recommend reading it to those who might have missed it. There, I talk about basic examples related to pg_stat_activity usage, such as checking current client activity, viewing clients' states, checking transactions and queries ages, etc. Of course, since then PostgreSQL moved on and pg_stat_activity was slightly changed in the newer versions. In …

PostgreSQL's transition relations.

Great news! PostgreSQL 10 is out. Release notes look amazing and there are a lot of new, wonderful changes and additions. In my personal list, the most desirable are improvements related to parallel queries which have been introduced in 9.6. In the new Postgres, more operations can be executed in parallel, such as B-tree index scan, bitmap heap scan, merge joins and non-correlated subqueries. Of course, new Postgres has other great features such as tables’ publication/subscription based on logical replication and declarative partitioning.

Here, I would like to shed some light on another novation related to the trigger functions - transition relations. What does the official documentation say about transition relations? These are sets of rows that include all of the rows inserted, deleted, or modified by the current SQL statement. Key point here is - all. With transition relations, triggers have a global overview of statement’s output, not just one row at a time. Note, that transition r…