Skip to content

12 Lesson 12: Administrative Tasks / Monitoring

Native Markdown source page: Administrative Tasks / Monitoring
Note

This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. HTML source: dba1_12_admin_monitoring.html Archive: DBA1-handouts-16-en.zip PDF pages: 30

Page 1: Administrative Tasks / Monitoring

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)

Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.

Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru

Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

Page 2: Topics

  • OS Tools
  • Сumulative Statistics
  • Server Message Log
  • External Monitoring Systems

Page 3: OS Tools

Processes

  • ps, pgrep…
  • update_process_title parameter for updating the status of processes
  • cluster_name parameter for setting the cluster name
  • Resource usage
    • iostat, vmstat, sar, top…
  • Disk space
    • df, du, quota…

Speaker notes

PostgreSQL runs on an operating system and to a certain extent depends on its configuration.

PostgreSQL process information is accessible via OS tools. The server parameter update_process_title (on by default) displays the state of each process next to its title. The cluster_name parameter specifies the instance name used to identify it among running processes.

Various tools are available to monitor the use of system resources (CPU, RAM, disks) in Unix: iostat, vmstat, sar, top, etc.

Disk space monitoring is also necessary. The space occupied by the database on disk can be viewed both from the database itself (see the Data Organization module) and from the OS (with the du command). The amount of disk space available is also displayed with the df command in the OS. If disk quotas are used, they must also be taken into account.

The tools and approaches to monitoring differ significantly between various OS and file systems, so we will not discuss them in detail.

https://postgrespro.com/docs/postgresql/16/monitoring-ps

https://postgrespro.com/docs/postgresql/16/diskusage

Page 4: Сumulative Statistics

Statistics Collection

  • Current System Activities
  • Command Execution Monitoring
  • Extensions

Speaker notes

There are two primary sources of information about the state of the system. The first one is statistical information collected by PostgreSQL and stored inside the cluster.

Page 5: Statistics Collection

Settings of cumulative statistics

  • parameter
  • action
  • track_activities
  • monitor current commands
  • track_counts
  • collect table and index access statistics
  • track_functions
  • monitor user function calls
  • off by default
  • track_io_timing
  • monitor block read and write timing statistics
  • off by default
  • track_wal_io_timing
  • monitor write timing of WAL operations
  • off by default

Speaker notes

The cumulative statistics system in PostgreSQL collects and provides data on server operations. Cumulative statistics track access to tables and indexes at both the disk block and row levels. Additionally, they record details such as the number of rows, vacuum and analyze operations for each table.

It is also possible to track the number of user function calls and their execution time.

The amount of information collected is controlled by several server parameters, since the more information is collected, the greater the overhead.

https://postgrespro.com/docs/postgresql/16/monitoring-stats

Page 6: Architecture

Source slide page 6: Architecture{#source-figure-006 caption=“Source slide page 6: Architecture”}

Speaker notes

Backends collect statistics in running transactions. The process stores this data in shared memory, updating it at most once per second (compile-time setting).

Cumulative statistics are stored in PGDATA/pg_stat/ during a normal server shutdown and reloaded upon startup. In case of a crash shutdown, all counters are reset.

Backend may cache statistical data. The caching level is controlled by the stats_fetch_consistency parameter.

● none — no caching; statistics reside only in shared memory.

● cache — statistics for a single object are cached.

● snapshot — statistics for the entire database are cached.

Cache is the default mode, which balances consistency and performance efficiency.

Cached statistics are not refreshed and are discarded at the end of a transaction, or when pg_stat_clear_snapshot() is called.

Due to latency and caching, the backend will not always have the latest statistics, but it is seldom necessary.

Page 7: Cumulative Statistics

=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".

Enable collection of I/O statistics first:

=> ALTER SYSTEM SET track_io_timing=on;
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Monitoring server activity only makes sense if there is any activity. We can imitate load with pgbench, a standard benchmarking utility. First, it creates a number of tables and fills them with data.

student$ pgbench -i admin_monitoring
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.65 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 0.31 s,
vacuum 0.13 s, primary keys 0.18 s).

Let’s reset the previously accumulated database statistics.

=> SELECT pg_stat_reset();
 pg_stat_reset
---------------
(1 row)

Including instance I/O statistics:

=> SELECT pg_stat_reset_shared('io');
 pg_stat_reset_shared
----------------------
(1 row)

Start the TPC-B test and let it run for a few seconds:

student$ pgbench -T 10 admin_monitoring
pgbench (16.11 (Ubuntu 16.11-1.pgdg24.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 1119
number of failed transactions: 0 (0.000%)

Page 8: Page 8

latency average = 8.933 ms
initial connection time = 10.450 ms
tps = 111.944576 (without initial connection time)

Now, let’s check the statistics on table access in terms of rows:

=> SELECT *
FROM pg_stat_all_tables
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]-------+------------------------------
relid               | 16393
schemaname          | public
relname             | pgbench_accounts
seq_scan            | 0
last_seq_scan       |
seq_tup_read        | 0
idx_scan            | 2238
last_idx_scan       | 2025-11-27 13:51:06.378593+03
idx_tup_fetch       | 2238
n_tup_ins           | 0
n_tup_upd           | 1119
n_tup_del           | 0
n_tup_hot_upd       | 290
n_tup_newpage_upd   | 829
n_live_tup          | 0
n_dead_tup          | 1062
n_mod_since_analyze | 1119
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

And in terms of tables:

=> SELECT *
FROM pg_statio_all_tables
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]---+-----------------
relid           | 16393
schemaname      | public
relname         | pgbench_accounts
heap_blks_read  | 0
heap_blks_hit   | 7824
idx_blks_read   | 273
idx_blks_hit    | 5866
toast_blks_read |
toast_blks_hit  |
tidx_blks_read  |
tidx_blks_hit   |

There are similar views for indexes:

=> SELECT *
FROM pg_stat_all_indexes
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]-+------------------------------
relid         | 16393
indexrelid    | 16407
schemaname    | public
relname       | pgbench_accounts
indexrelname  | pgbench_accounts_pkey
idx_scan      | 2238
last_idx_scan | 2025-11-27 13:51:06.378593+03
idx_tup_read  | 3071
idx_tup_fetch | 2238
=> SELECT *
FROM pg_statio_all_indexes
WHERE relid = 'pgbench_accounts'::regclass \gx

Page 9: Page 9

-[ RECORD 1 ]-+----------------------
relid         | 16393
indexrelid    | 16407
schemaname    | public
relname       | pgbench_accounts
indexrelname  | pgbench_accounts_pkey
idx_blks_read | 273
idx_blks_hit  | 5866

These views can be used to pinpoint unused indexes. Such indexes not only occupy useful space on the disk, but also waste resources on updates every time data in the table changes. There are also views for user-defined and system objects (all, user, sys), current transaction statistics (pg_stat_xact*), and more. You can view global statistics across the whole database:

=> SELECT *
FROM pg_stat_database
WHERE datname = 'admin_monitoring' \gx
-[ RECORD 1 ]------------+-----------------------------
datid                    | 16386
datname                  | admin_monitoring
numbackends              | 1
xact_commit              | 1138
xact_rollback            | 0
blks_read                | 275
blks_hit                 | 23537
tup_returned             | 18052
tup_fetched              | 3371
tup_inserted             | 1119
tup_updated              | 3358
tup_deleted              | 0
conflicts                | 0
temp_files               | 0
temp_bytes               | 0
deadlocks                | 0
checksum_failures        |
checksum_last_failure    |
blk_read_time            | 10.404
blk_write_time           | 0.697
session_time             | 22702.047
active_time              | 9444.514
idle_in_transaction_time | 516.089
sessions                 | 2
sessions_abandoned       | 0
sessions_fatal           | 0
sessions_killed          | 0
stats_reset              | 2025-11-27 13:50:55.98728+03

It provides a lot of data on the number of deadlocks occurred, committed and cancelled transactions, utilization of temporary files, and checksum errors. It also maintains total session count and statistics on sessions terminated for various reasons. The numbackends column specifically indicates the current number of active backend processes connected to this database. For monitoring I/O operations at the server level, administrators can query the pg_stat_io view. Let’s first execute a checkpoint operation, then examine the resulting page read and write counts categorized by process type:

=> CHECKPOINT;
CHECKPOINT
=> SELECT backend_type, sum(hits) hits, sum(reads) reads, sum(writes) writes
FROM pg_stat_io
GROUP BY backend_type;

Page 10: Page 10

    backend_type     | hits  | reads | writes
---------------------+-------+-------+--------
 background worker   |     0 |     0 |      0
 client backend      | 23723 |   275 |      0
 walsender           |     0 |     0 |      0
 standalone backend  |     0 |     0 |      0
 autovacuum worker   |   559 |     0 |      0
 autovacuum launcher |     0 |     0 |      0
 background writer   |       |       |      0
 startup             |     0 |     0 |      0
 checkpointer        |       |       |   2874
(9 rows)

Page 11: Current Activities

Configuration

  • statistics
  • parameter
  • current activities and waits
  • track_activities
  • of backends
  • on by default
  • and background processes

Speaker notes

The current activities of all backends and background processes are displayed in the pg_stat_activity view. We will focus on it more in the demo.

This view depends on the track_activities parameter (enabled by default).

Page 12: Current Activities

Source slide page 12: Current Activities{#source-figure-012 caption=“Source slide page 12: Current Activities”}

Let’s imitate a scenario when one process blocks another, and then figure it out using system views. Create a table with one row:

=> CREATE TABLE t(n integer);
CREATE TABLE
=> INSERT INTO t VALUES(42);
INSERT 0 1

Start two sessions, one of which changes the table and does not complete the transaction:

student$ psql -d admin_monitoring
=> BEGIN;
BEGIN
=> UPDATE t SET n = n + 1;
UPDATE 1

And the other tries to change the same row and gets blocked:

student$ psql -d admin_monitoring
=> UPDATE t SET n = n + 2;

View data about backend processes:

=> SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend' \gx
-[ RECORD 1
]----+-----------------------------------------------------------------------------
pid              | 22152
query            | UPDATE t SET n = n + 1;
state            | idle in transaction
wait_event       | ClientRead
wait_event_type  | Client
pg_blocking_pids | {}
-[ RECORD 2
]----+-----------------------------------------------------------------------------
pid              | 21005
query            | SELECT pid, query, state, wait_event, wait_event_type,
pg_blocking_pids(pid)+
                 | FROM pg_stat_activity
     +
                 | WHERE backend_type = 'client backend'
state            | active
wait_event       |
wait_event_type  |
pg_blocking_pids | {}
-[ RECORD 3
]----+-----------------------------------------------------------------------------
pid              | 22245
query            | UPDATE t SET n = n + 2;
state            | active
wait_event       | transactionid
wait_event_type  | Lock
pg_blocking_pids | {22152}

The state “idle in transaction” means that the session has started a transaction, but isn’t doing anything at the moment, and the transaction isn’t closed. This could become a problem if the situation comes up regularly (for example, because of poor application code or driver errors), because an open transaction holds a data snapshot and prevents vacuuming. The administrator has a parameter idle_in_transaction_session_timeout at their disposal to force sessions to close after their transaction is idle for a certain period of time. The idle_session_timeout parameter forcibly terminates sessions that remain idle beyond the specified duration. You can also terminate a blocking session manually. First, you need the blocked process ID. The function pg_blocking_pids can help you with that:

Page 13: Page 13

=> SELECT pid AS blocked_pid
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND cardinality(pg_blocking_pids(pid)) > 0;
 blocked_pid
-------------
       22245
(1 row)

Instead, you can use a query on the locks table. It will return two rows in this case: one transaction has been granted the lock, while the other is waiting for it.

=> SELECT locktype, transactionid, pid, mode, granted
FROM pg_locks
WHERE transactionid IN (
  SELECT transactionid FROM pg_locks WHERE pid = 22245 AND NOT granted
);
   locktype    | transactionid |  pid  |     mode      | granted
---------------+---------------+-------+---------------+---------
 transactionid |          1871 | 22245 | ShareLock     | f
 transactionid |          1871 | 22152 | ExclusiveLock | t
(2 rows)

Generally, you have to keep the lock type in mind. Running query can be cancelled with the pg_cancel_backend function. The transaction is idle in our case, so we use the pg_terminate_backend to terminate the session:

=> SELECT pg_terminate_backend(b.pid)
FROM unnest(pg_blocking_pids(22245)) AS b(pid);
 pg_terminate_backend
----------------------
 t
(1 row)

The unnest function is necessary because pg_blocking_pids returns an array of process IDs that block the specified process. There is only one in our examples, but there can be multiple. Locks are discussed in more detail in the DBA2 course. Check the backends:

=> SELECT pid, query, state, wait_event, wait_event_type
FROM pg_stat_activity
WHERE backend_type = 'client backend' \gx
-[ RECORD 1 ]---+------------------------------------------------------
pid             | 21005
query           | SELECT pid, query, state, wait_event, wait_event_type+
                | FROM pg_stat_activity                                +
                | WHERE backend_type = 'client backend'
state           | active
wait_event      |
wait_event_type |
-[ RECORD 2 ]---+------------------------------------------------------
pid             | 22245
query           | UPDATE t SET n = n + 2;
state           | idle
wait_event      | ClientRead
wait_event_type | Client

Only two remain, and the blocked one has completed its transaction successfully. The pg_stat_activity view shows the information not only about backend processes, but also about the system background processes running on the instance:

=> SELECT pid, backend_type, backend_start, state
FROM pg_stat_activity;

Page 14: Page 14

  pid  |         backend_type         |         backend_start         | state
-------+------------------------------+-------------------------------+--------
 20908 | autovacuum launcher          | 2025-11-27 13:50:46.90002+03  |
 20909 | logical replication launcher | 2025-11-27 13:50:46.901748+03 |
 21005 | client backend               | 2025-11-27 13:50:54.68677+03  | active
 22245 | client backend               | 2025-11-27 13:51:14.373422+03 | idle
 20905 | background writer            | 2025-11-27 13:50:46.818366+03 |
 20904 | checkpointer                 | 2025-11-27 13:50:46.817735+03 |
 20907 | walwriter                    | 2025-11-27 13:50:46.898508+03 |
(7 rows)

Compare that to what the OS reports:

student$ sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid
20903
student$ ps -o pid,command --ppid 20903
    PID COMMAND
  20904 postgres: 16/main: checkpointer
  20905 postgres: 16/main: background writer
  20907 postgres: 16/main: walwriter
  20908 postgres: 16/main: autovacuum launcher
  20909 postgres: 16/main: logical replication launcher
  21005 postgres: 16/main: student admin_monitoring [local] idle
  22245 postgres: 16/main: student admin_monitoring [local] idle

Page 15: Command Execution

Views for monitoring command executions

  • command
  • execution
  • ANALYZE
  • pg_stat_progress_analyze
  • CREATE INDEX, REINDEX
  • pg_stat_progress_create_index
  • VACUUM
  • pg_stat_progress_vacuum
  • including autovacuuming
  • CLUSTER, VACUUM FULL
  • pg_stat_progress_cluster
  • Create base backup
  • pg_stat_progress_basebackup
  • COPY
  • pg_stat_progress_copy

Speaker notes

You can monitor the progress of some potentially long-running commands using the corresponding views.

The structures of the views are described in the documentation:

https://postgrespro.com/docs/postgresql/16/progress-reporting

Backup is discussed in the Backup module.

Page 16: Additional Statistics

Additional supplied extensions

  • pg_stat_statements
  • query statistics
  • pgstattuple
  • row versions statistics
  • pg_buffercache
  • buffer cache status
  • Other extensions
    • pg_wait_sampling
    • statistics for waits
    • pg_stat_kcache
    • CPU and I/O statistics
    • pg_qualstats
    • predicate statistics
    • …

Speaker notes

There are extensions, both additional supplied and third-party, that enable the collection of additional statistics.

For example, the pg_stat_statements extension collects information about queries executed by the system, pg_buffercache provides tools for monitoring the buffer cache, etc.

Many key extensions are discussed in more detail in the DBA2 and DEV2 courses.

Page 17: Server Message Log

Log Record Configuration

  • Log File Rotation
  • Log Analysis

Speaker notes

The other primary source of information about the state of the server is the message log.

Page 18: Server Message Log

Message receiver (log\_destination = list)

  • stderr
  • error stream
  • csvlog
  • CSV format (if the collector is enabled)
  • jsonlog
  • JSON format (if the collector is enabled)
  • syslog
  • the syslog daemon
  • eventlog
  • Windows event log
  • Message collector (logging_collector = on)
    • can provide additional info
    • never loses messages (unlike syslog)
    • writes stderr, csvlog and jsonlog to the log_directory/log_filename

Speaker notes

The server log can be output in various formats and forwarded to various destinations. The format and the destination are determined primarily by the log_destination parameter (you can list multiple receivers separated by a comma).

The stderr value (on by default) sends messages to the standard error stream as plain text. The syslog value forwards messages to the syslog daemon (for Unix systems), and the eventlog value does the same for the Windows event log.

The message collector is an auxiliary process that collects additional information from all PostgreSQL processes to supplement the basic log messages. It is designed to keep track of every message, therefore it can become the bottleneck in high-load environments.

The message collector is switched on and off by the logging_collector parameter. When stderr is enabled, the messages are written into the file defined by the log_filename parameter, which is located in the directory defined by the log_directory parameter.

When the collector is on and csvlog is selected as a receiver, the info will also be output into a CSV file log_filename.csv. With the jsonlog output enabled, log files are written in JSON format and use the .json file extension.

Page 19: What to Log?

Settings

  • information
  • parameter
  • level of messages
  • log_min_messages
  • long command execution time
  • log_min_duration_statement
  • command execution time
  • log_duration
  • application name
  • application_name
  • checkpoints
  • log_checkpoints
  • connections and disconnections
  • log_(dis)connections
  • long lock waits
  • log_lock_waits
  • command executed
  • log_statement
  • temporary file usage
  • log_temp_files
  • …

Speaker notes

A lot of useful information can be output to the server message log. By default, almost all output is disabled so as not to turn logging into the bottleneck for the I/O subsystem. The administrator should decide what information is important, provide the necessary disk space to store it, and evaluate the impact of the log output on the overall system performance.

Page 20: Log File Rotation

By the message collector

  • setting
  • parameter
  • file name pattern
  • log_filename
  • rotation time, minutes
  • log_rotation_age
  • rotation file size, KB
  • log_rotation_size
  • overwrite file
  • log_truncate_on_rotation = on
  • combining file name patterns and rotation times allow for different rotation
  • schemes:
  • ‘postgresql-%H.log’, ‘1h’
  • 24 files a day
  • ‘postgresql-%a.log’, ‘1d’
  • 7 files a week
  • External tools
    • logrotate system utility

Speaker notes

If all the log output goes into a single file, sooner or later the file will grow to an unmanageable size, making administration and analysis highly inconvenient. Therefore, a log rotation scheme is usually employed.

https://postgrespro.com/docs/postgresql/16/logfile-maintenance

The message collector has its own rotation tools. Some of the parameters that configure them are listed on the slide.

The log_filename parameter can specify not just a name, but a file name pattern using designated date and time characters.

The log_rotation_age parameter determines how long a file is used before the output switches to a new one (and log_rotation_size is the file size at which to switch to the next one).

The log_truncate_on_rotation flag determines if PostgreSQL should overwrite existing files or append messages to them.

Different rotation schemes can be defined by using various file name patterns and switch time combinations.

https://postgrespro.com/docs/postgresql/16/runtime-config-logging#RUNTIM E-CONFIG-LOGGING-WHERE

Alternatively, rotation can be managed by external tools. For example, Ubuntu package uses logrotate system utility (it is configured through the /etc/logrotate.d/postgresql-common file).

Page 21: Log Analysis

OS tools

  • grep, awk…
  • Special analysis tools
    • pgBadger — requires a certain log configuration

Speaker notes

There are different ways to analyze logs.

You can search for certain information using OS tools or specially designed scripts.

The de facto standard for log analysis is the PgBadger application https://github.com/darold/pgbadger, but it imposes certain restrictions on the contents of the log. In particular, only messages in English are allowed.

Page 22: Log Analysis

Let’s consider a simple case. For example, display all messages of the FATAL level:

student$ sudo grep FATAL /var/log/postgresql/postgresql-16-main.log | tail -n 10
2025-11-27 13:45:53.443 MSK [2670] student@student FATAL:  terminating connection due to
administrator command
2025-11-27 13:51:16.330 MSK [22152] student@admin_monitoring FATAL:  terminating
connection due to administrator command

The “terminating connection” message is caused by us terminating the blocking process. Logs are usually used to analyse the queries that execute the longest. We can make the log display all executed commands and their execution times:

=> ALTER SYSTEM SET log_min_duration_statement=0;
ALTER SYSTEM
=> SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Now, run a command:

=> SELECT sum(random()) FROM generate_series(1,1_000_000);
        sum
--------------------
 500117.78944922757
(1 row)

Check the log:

student$ sudo tail -n 1 /var/log/postgresql/postgresql-16-main.log
2025-11-27 13:51:18.277 MSK [21005] student@admin_monitoring LOG:  duration: 405.591 ms
statement: SELECT sum(random()) FROM generate_series(1,1_000_000);

Page 23: External Monitoring

Universal monitoring systems

  • Zabbix, Munin, Cacti…
  • cloud-based: Okmeter, NewRelic, Datadog…
  • PostgreSQL monitoring systems
    • pg_profile, pgpro_pwr
    • PGObserver
    • PostgreSQL Workload Analyzer (PoWA)
    • Open PostgreSQL Monitoring (OPM)
    • …

Speaker notes

In practice, you need a full-fledged monitoring system that collects various metrics from both PostgreSQL and the operating system, stores the history of these metrics, displays them as readable graphs, notifies when certain metrics exceed certain limits, etc.

PostgreSQL does not come with such a system by itself, it only provides the means by which such information can be acquired. We have gone over them already. Therefore, for full-scale monitoring, an external system is required. There are quite a few such systems on the market. Some are universal and come with PostgreSQL plugins or agents. These include Zabbix, Munin, Cacti, cloud services such as Okmeter, NewRelic, Datadog, and others.

There are also systems specifically designed for PostgreSQL: PGObserver, PoWA, OPM, etc. The pg_profile extension allows you to build snapshots of static data and compare them, identifying resource-intensive operations and their dynamics. pgpro_pwr is its extended, commercially available version.

https://postgrespro.com/docs/enterprise/16/pgpro-pwr

An incomplete but representative list of monitoring systems can be viewed here: https://wiki.postgresql.org/wiki/Monitoring

Page 24: Takeaways

Monitoring collects data on server operations both from the operating system and from the database points of view

  • PostgreSQL provides cumulative statistics and the server message log
  • Full-scale monitoring requires an external system

Page 25: Practice

  1. In a new database, create a table, insert several rows, and then delete all rows.
  • Look at the table access statistics and reference the values (n_tup_ins, n_tup_del, n_live_tup, n_dead_tup) against your activity. Perform a vacuum, check the statistics again and compare with the previous figures. 2. Create a deadlock with two transactions.
    • See what information is recorded in the server message log.

Speaker notes

  1. Deadlock is a situation when two (or more) transactions are waiting for each other to complete first. Unlike a normal lock, transactions have no way to get out of deadlock, and the DBMS has to resolve it by forcibly interrupting one of the transactions.

The easiest way to reproduce a deadlock is on a table with two rows. The first transaction changes (and locks) the first row, and the second one locks the second row. Then the first transaction tries to change the second row, discovers that it is locked, and starts waiting. And then the second transaction tries to change the first row, and also waits for the lock to be released.

Page 26: Table Access Statistics

Create a database and a table:

=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
=> CREATE TABLE t(n numeric);
CREATE TABLE
=> INSERT INTO t SELECT 1 FROM generate_series(1,1000);
INSERT 0 1000
=> DELETE FROM t;
DELETE 1000

Check access statistics.

=> SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx
-[ RECORD 1 ]-------+------------------------------
relid               | 16387
schemaname          | public
relname             | t
seq_scan            | 1
last_seq_scan       | 2025-11-27 14:00:50.589242+03
seq_tup_read        | 1000
idx_scan            |
last_idx_scan       |
idx_tup_fetch       |
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 1000
n_tup_hot_upd       | 0
n_tup_newpage_upd   | 0
n_live_tup          | 0
n_dead_tup          | 1000
n_mod_since_analyze | 2000
n_ins_since_vacuum  | 1000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

We inserted 1000 rows (n_tup_ins = 1000), then removed 1000 rows (n_tup_del = 1000). No live row versions remain (n_live_tup = 0), all 1000 rows are dead (n_dead_tup = 1000). Run vacuuming.

=> VACUUM;
VACUUM
=> SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx

Page 27: Page 27

-[ RECORD 1 ]-------+------------------------------
relid               | 16387
schemaname          | public
relname             | t
seq_scan            | 1
last_seq_scan       | 2025-11-27 14:00:50.589242+03
seq_tup_read        | 1000
idx_scan            |
last_idx_scan       |
idx_tup_fetch       |
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 1000
n_tup_hot_upd       | 0
n_tup_newpage_upd   | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 2000
n_ins_since_vacuum  | 0
last_vacuum         | 2025-11-27 14:00:52.37659+03
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Dead row versions vacuumed (n_dead_tup = 0), vacuuming performed one time (vacuum_count = 1).

2. Deadlocks

=> INSERT INTO t VALUES (1),(2);
INSERT 0 2

One transaction locks the first row of the table…

student$ psql
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
=> BEGIN;
BEGIN
=> UPDATE t SET n = 10 WHERE n = 1;
UPDATE 1

The other locks the second row…

student$ psql
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
=> BEGIN;
BEGIN
=> UPDATE t SET n = 200 WHERE n = 2;
UPDATE 1

Now, the first transaction tries to change the second row and waits for it to release…

=> UPDATE t SET n = 20 WHERE n = 2;

While the second transaction waits for the first row to release…

=> UPDATE t SET n = 100 WHERE n = 1;

…and so a deadlock occurs. The server terminates one of the transactions:

ERROR:  deadlock detected
DETAIL:  Process 43139 waits for ShareLock on transaction 739; blocked by process 43007.
Process 43007 waits for ShareLock on transaction 740; blocked by process 43139.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "t"

Page 28: Page 28

The other transaction gets unblocked:

UPDATE 1

Check the message log:

student$ sudo tail -n 8 /var/log/postgresql/postgresql-16-main.log
2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring ERROR:  deadlock detected
2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring DETAIL:  Process 43139 waits
for ShareLock on transaction 739; blocked by process 43007.
Process 43007 waits for ShareLock on transaction 740; blocked by process 43139.
Process 43139: UPDATE t SET n = 100 WHERE n = 1;
Process 43007: UPDATE t SET n = 20 WHERE n = 2;
2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring HINT:  See server log for
query details.
2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring CONTEXT:  while updating
tuple (0,1) in relation "t"
2025-11-27 14:00:56.806 MSK [43139] student@admin_monitoring STATEMENT:  UPDATE t SET n =
100 WHERE n = 1;

Page 29: Practice+

  1. Install the pg_stat_statements extension.
  • Execute several queries.
  • See what information gets into the pg_stat_statements view.

Speaker notes

  1. To install the extension, before executing the CREATE EXTENSION command, change the value of the shared_preload_libraries parameter, and restart the server.

https://postgrespro.com/docs/postgresql/16/pgstatstatements

Page 30: 1. The pg_stat_statements Extension

The extension collects planning and execution statistics for all queries. For the extension to work, a module with the same name has to be loaded. To do that, add the module name to shared_preload_libraries and restart the server. This is usually done through postgresql.conf, but for the purpose of the demo we will set it using the ALTER SYSTEM command.

=> ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM
=> \q
student$ sudo pg_ctlcluster 16 main restart
student$ psql
=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Now, run some queries:

=> CREATE TABLE t(n numeric);
CREATE TABLE
=> SELECT format('INSERT INTO t VALUES (%L)', x)
FROM generate_series(1,5) AS x \gexec
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
=> DELETE FROM t;
DELETE 5
=> DROP TABLE t;
DROP TABLE

Check the statistics for the most frequently executed query.

=> SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 1;
           query           | calls | total_exec_time
---------------------------+-------+-----------------
 INSERT INTO t VALUES ($1) |     5 |         0.18377
(1 row)

The shared library is no longer required — restoring the original parameter value:

=> ALTER SYSTEM RESET shared_preload_libraries;
ALTER SYSTEM
=> \q
student$ sudo pg_ctlcluster 16 main restart