Freshly baked PostgreSQL 12 and changes it brought to pgCenter

PostgreSQL 12 release is expected today and just by looking at the prerelease notes this release is impressive - features added in the previous versions, became more “tasty”.

As pgCenter developer I am mainly interested in features related to monitoring and activity statistics. Postgres 11 didn’t have prominent monitoring features, but the upcoming release has several of these. In this post I will review these novations and show how they were implemented in pgCenter.

As you can remember, in Postgres 10 a new progress tracking view was introduced - pg_stat_progress_vacuum - which helps track vacuum activity and get a clearer understanding of what (auto)vacuum workers do.

This feature is growing in the new release and in Postgres 12 we will get two new progress views:
pg_stat_progress_create_indexpg_stat_progress_cluster As you can see, from their names, the first view allows tracking progress of indexes creation (and reindexes too). The second view, allows tracking the progress of CLUS…

pgCenter’s wait events profiler

As you might know, in the last pgCenter release the new tool has been added - wait events profiler. In this post, I'd like to explore this tool and propose some use-cases for it.

First of all, what are “wait events”? PostgreSQL official documentation doesn't give an explanation for wait events (it just provides a list of all wait events). In short, wait events are points in time where backends have to wait until a particular event occurs. This may be waiting for obtaining locks, IO, inter-process communication, interacting with client or something else. Stats about wait events are provided by pg_stat_activity view in wait_event_type and wait_event columns.

Using EXPLAIN we always can understand what query does. But EXPLAIN is aimed to work with query planner and doesn't show time when query got stuck in waitings. For this, you can use pgCenter's wait events profiler.

How does it work? First, you need to know PID of profiled backend. It can be found using pg_stat_activity

pgCenter 0.6.0 released.

Great news for all pgCenter users - a new version 0.6.0 has been released with new features and few minor improvements.

Here are some major changes:
new wait events profiler - a new sub-command which allows to inspect long-running queries and understand what query spends its time on.goreleaser support - goreleaser helps to build binary packages for you, so you can find .rpm and .deb packages on the releases page.Goreport card A+ status - A+ status is the little step to make code better and align it to Golang code styleThis release also includes following minor improvements and fixes:
report tool now has full help list of supported stats, you can, at any time, get a descriptive explanation of stats provided by pgCenter. Check out the “--describe” flag of “pgcenter report”;“pgcenter top” now has been fixed and includes configurable aligning of columns, which make stats viewing more enjoyable (check out builtin help for new hotkeys);wrong handling of group mask has been fixed. It is used fo…

Keeping Postgres tidy with partitioning

If you are in doubt whether partitioning is a useful tool with this one example I'm hoping you won’t wonder any further.

Why avoid long transactions?

The majority of PostgreSQL community clearly understands why long and idle transactions are “bad”. But when you talk about it to the newcomers it’s always a good idea to backup your explanation with some real tests.

While preparing slides for my presentation about vacuum I have made a simple test case with long transaction using pgbench. Here are the results.

Global shortcuts and PostgreSQL queries.

Using your favorite hotkeys on queries in LinuxOne of my colleagues often talks about using hot keys for his favourite SQL queries and commands in iterm2 (e.g. for checking current activity or to view lists of largest tables). 
Usually, I listen to this with only half an ear, because iterm2 is available only for MacOS and I am a strong Linux user. Once this topic came up again I thought perhaps this function could be realised not only through iterm2 but through an alternative tool or settings in desktop environment.

pgCenter - stress free Postgres stats.

pgCenter has seen a new release with several significant changes. If you used pgCenter before, you might have noticed that I stopped issuing updates for a while. This is because I was working on this new release with several new features and overall improvements that will make the use of the tool easier and will allow additional functionality to make your life easier when working with Postgres stats.