{"id":174,"date":"2024-05-28T11:16:33","date_gmt":"2024-05-28T11:16:33","guid":{"rendered":"https:\/\/stradata.com.tr\/en\/?page_id=174"},"modified":"2024-11-28T08:34:38","modified_gmt":"2024-11-28T08:34:38","slug":"blog-yazilari","status":"publish","type":"page","link":"https:\/\/stradata.com.tr\/en\/?page_id=174","title":{"rendered":"BLOG POSTS"},"content":{"rendered":"\n

EXAMINING DATABASE CUMULATIVE I\/O STATISTICS WITH PG_STAT_IO<\/a><\/h3>\n\n\n\n

pg_stat_io<\/strong>, which comes with PostgreSQL version 16, is a view that shows statistics related to I\/O operations in databases cumulatively <\/strong>and for the entire cluster<\/strong>, 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 \u201cbackend_type\u201d=\u201dclient backend\u201d<\/strong>. Another column \u201ccontext\u201d<\/strong> indicates the content of the I\/O operation, in other words its type, and can take the values \u200b\u200bof \u201cnormal\u201d, \u201cvacuum\u201d, \u201cbulkread\u201d, \u201cbulkwrite<\/strong>\u201c. You can review detailed information about view columns here<\/a>. 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.<\/p>\n\n\n\n

…<\/a><\/p>\n\n\n\n

ANALYSIS OF PG_STAT_BGWRITER AND PG_STAT_CHECKPOINTER IN POSTGRESQL 17<\/a><\/h3>\n\n\n\n

In this article, where we will examine the features of the\u00a0pg_stat_bgwriter\u00a0<\/strong>and\u00a0pg_stat_checkpointer\u00a0<\/strong>views and the behaviors of the processes, let\u2019s start by talking about the history of the subject. The\u00a0\u201cbackground write\u201d<\/strong>\u00a0and\u00a0\u201ccheckpoint\u201d<\/strong>\u00a0processes, which were previously the tasks of the\u00a0\u201cBackground Writer\u201d (bgwriter)<\/strong>\u00a0process, were divided into two separate processes in PostgreSQL 9.2 and updated so that bgwriter would do the \u201cbackground write\u201d 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\u00a0link<\/a>, was that checkpoint had to stop the \u201cbackground write\u201d 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.<\/p>\n\n\n\n

…<\/a><\/p>\n\n\n\n

FACTORS AFFECTING CRASH RECOVERY MECHANISM IN POSTGRESQL<\/a><\/h3>\n\n\n\n

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<\/strong>, and WAL (Write Ahead Log)<\/strong> 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.<\/p>\n\n\n\n

…<\/a><\/p>\n\n\n\n

POSTGRESQL MAJOR UPGRADE METHODS<\/a><\/h3>\n\n\n\n

There are 3 different methods to perform major version upgrades on your PostgreSQL databases. These are:<\/p>\n\n\n\n