EXAMINING DATABASE CUMULATIVE I/O STATISTICS WITH PG_STAT_IO
pg_stat_io, which comes with PostgreSQL version 16, is a view that shows statistics related to I/O operations in databases cumulatively and for the entire cluster, and can be quite useful when used and interpreted correctly. I/O operations are shown in the view grouped according to backend process types, for example, statistics related to operations of users connecting to the database are shown together as “backend_type”=”client backend”. Another column “context” indicates the content of the I/O operation, in other words its type, and can take the values of “normal”, “vacuum”, “bulkread”, “bulkwrite“. You can review detailed information about view columns here. In our article, we will examine the statistics resulting from simple operations in order to better understand the content offered by the pg_stat_io view.
ANALYSIS OF PG_STAT_BGWRITER AND PG_STAT_CHECKPOINTER IN POSTGRESQL 17
In this article, where we will examine the features of the pg_stat_bgwriter and pg_stat_checkpointer views and the behaviors of the processes, let’s start by talking about the history of the subject. The “background write” and “checkpoint” processes, which were previously the tasks of the “Background Writer” (bgwriter) process, were divided into two separate processes in PostgreSQL 9.2 and updated so that bgwriter would do the “background write” process and the checkpoint process would do the checkpoint process. (There was no checkpointer process before version 9.2). One of the most important reasons for this change, as you can see in this link, was that checkpoint had to stop the “background write” operation in order to perform the last fsync, and factors like these had a negative performance effect. The change we mentioned was made in version 9.2 and the checkpointer process was activated, but this change made in background processes was not reflected in the statistics views and the information belonging to both processes continued to be kept in the pg_stat_bgwriter view. In PostgreSQL version 17, these statistics are now kept in different views.
FACTORS AFFECTING CRASH RECOVERY MECHANISM IN POSTGRESQL
Our expectation from OLTP databases is that the system will be reopened without data loss and consistently after a sudden/unexpected shutdown, that is, with committed transactions completed and uncommitted transactions rolled back. We call this process crash recovery, and WAL (Write Ahead Log) is at the center of the crash recovery mechanism in PostgreSQL. PostgreSQL has all the necessary capabilities to meet the expectations we mentioned thanks to the WAL system, but in order to be able to say that this process will be completed without problems in a PostgreSQL database, and that the database will be opened consistently and without data loss at every shutdown, it is necessary to examine some configurations. In this article, we will examine the parameters, reasons for existence, and configuration options that will affect the crash recovery mechanism.
POSTGRESQL MAJOR UPGRADE METHODS
There are 3 different methods to perform major version upgrades on your PostgreSQL databases. These are:
- Moving all data to a database cluster created in the target version with pg_dumpall
- Refreshing system tables and using user data as is with pg_upgrade
- Moving all data to the new version in a standby environment with logical replication and changing roles with switch-over.
Let’s quickly go over the advantages/disadvantages of these methods.