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_index
- pg_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 CLUSTER and VACUUM FULL operations. Don’t let VACUUM FULL confuse you, because internally its code is quite close to the code of CLUSTER command.
To summarize the above, the new Postgres brings additional tools for tracking maintenance operations which performed frequently by DBAs in their routines. Of course, this progress view is now supported by pgCenter and you can track the progress much easier.
However, to implement support of these views I had to accept some tradeoffs — one of them is the change of shortcuts for switching stats. Now, there is a new hotkey for watching progress statistics. Use ‘p’ hotkey for switching between vacuum, cluster or create indexes progress stats — it works in the same way as for pg_stat_statements stats. Also, you can press ‘P’ hotkey ang get the menu with progress stats views and select the appropriate one.
But that is not all, new Postgres brings another set of improvements related to activity statistics. A couple of them are related to pg_stat_database:
Two new columns have been added — checksum_failures and checksum_last_failure — which will be useful for users, who have checksums enabled in their databases. At this moment, pgCenter supports only checksum_failures column, and I hope you will only see zeroes in this column.
The second improvement is dedicated statistics for global objects in system catalog — these statistics are stored in row with datid = 0 and these stats don’t relate to particular databases. They also shown in pgCenter by default.
Among all changes, there are some which are not supported by pgCenter. One of the them is the pg_stat_ssl. It hasn’t been implemented since I don’t use it frequently enough, so simply don’t have good examples for pg_stat_ssl usage.
Another change is related to pg_stat_replication and shows last message received from standby hosts. And finally, there is an another new view — pg_stat_gssapi which shows information about GSSAPI authentication and encryption used by client connections.
For more information see:
Can’t wait to start using the brand new PostgreSQL 12!
