PostgreSQL 14 New Features – Monitoring improvements

Database Monitoring, Database Availability, Data Observability, Data Lineage, Data Provenance… Wow! way too many things.

As a Database Engineer, database monitoring is the utmost important aspect for any database shop, it lays the foundation of the whole platform. So, I am gonna stick to that in this post.

Database monitoring is a critical part of any platform’s maintenance. Finding database issues in time can help the platform in many ways. Without solid monitoring in place, database outages can go unnoticed until it’s too late. Any monitoring system relies on:

• Data collection
• Granuality of logging
• Ease and detail of progress tracking
• Reliability and availability
  (not gonna discuss about the DBRE here, storing it for another day)

PostgreSQL 14 adds several new features to help with monitoring and observability. With this data and metrics, Database engineers are provided with all the information they need to identify and solve any database issues that impact the application performance which enables the team to set up high-performing databases.

I’d like to shed some light on a few of the interesting Monitoring improvements in PostgreSQL 14 with this post. Like:

pg_backend_memory_contexts

Displays all the memory contexts of the server process attached to the current session. The view contains one row for each memory context. The useful columns are:

I have always been curious to look at a certain postgres backend connection’s memory consumption. With this new pg_backend_memory_contexts view we can take a close look at what exactly is allocated for a given postgres backend process. Be it looking at the “user_bytes” column for calculating the total memory used or looking at the TOP 10 memory utilizing contenders.

Total Memory by session:

postgres=# SELECT pg_size_pretty(sum(used_bytes)) AS "TotalMem by session" FROM pg_backend_memory_contexts;
 TotalMem by session 
---------------------
 1112 kB
(1 row)

TOP 10 memory using entries:

postgres=# SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 10;
          name           | ident |      parent      | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
-------------------------+-------+------------------+-------+-------------+---------------+------------+-------------+------------
 CacheMemoryContext      |       | TopMemoryContext |     1 |     1048576 |             8 |     479592 |           0 |     568984
 Timezones               |       | TopMemoryContext |     1 |      104128 |             2 |       2584 |           0 |     101544
 TopMemoryContext        |       |                  |     0 |      127216 |             7 |      32816 |          16 |      94400
 MessageContext          |       | TopMemoryContext |     1 |       65536 |             4 |      17272 |           0 |      48264
 ExecutorState           |       | PortalContext    |     3 |       49216 |             4 |       5712 |           2 |      43504
 WAL record construction |       | TopMemoryContext |     1 |       49776 |             2 |       6344 |           0 |      43432
 TupleSort main          |       | ExecutorState    |     4 |       32832 |             2 |       6792 |           0 |      26040
 Type information cache  |       | TopMemoryContext |     1 |       24384 |             2 |       2584 |           0 |      21800
 smgr relation table     |       | TopMemoryContext |     1 |       32768 |             3 |      16768 |           8 |      16000
 Operator lookup cache   |       | TopMemoryContext |     1 |       24576 |             2 |      10712 |           4 |      13864
(10 rows)

or a little more granular (but complex to understand) way is to:

postgres=# SELECT name,pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts
postgres-# GROUP BY name;
           name           | pg_size_pretty 
--------------------------+----------------
 TableSpace cache         | 6144 bytes
 printtup                 | 264 bytes
 Relcache by OID          | 13 kB
 Record information cache | 6656 bytes
 WAL record construction  | 42 kB
 index info               | 119 kB
 LOCALLOCK hash           | 7680 bytes
 Portal hash              | 7680 bytes
 relation rules           | 7728 bytes
 TopTransactionContext    | 472 bytes
 TopPortalContext         | 544 bytes
 Operator lookup cache    | 14 kB
 TransactionAbortContext  | 264 bytes
 ErrorContext             | 264 bytes
 ExprContext              | 6616 bytes
 TopMemoryContext         | 92 kB
 Timezones                | 99 kB
 MessageContext           | 46 kB
 smgr relation table      | 16 kB
 Operator class cache     | 7680 bytes
 CacheMemoryContext       | 556 kB
 PortalContext            | 448 bytes
 Table function arguments | 320 bytes
 Type information cache   | 21 kB
 HashAgg meta context     | 7696 bytes
 RowDescriptionContext    | 1312 bytes
 MdSmgr                   | 776 bytes
 PrivateRefCount          | 5608 bytes
 ExecutorState            | 42 kB
(29 rows)

Tracking WAL activity using pg_stat_wal

Last year, PostgreSQL 13 version introduced a few Write-ahead log (WAL) monitoring capabilities like:

• WAL statistics in EXPLAIN plan
• Postgres log file entries of autovacuum to specifically point the WAL usage
• WAL statitics gathered via pg_stat_statements

In continuation to that, the new PostgreSQL 14 release brings a new server-wide catalog view for WAL information, called pg_stat_wal.

The view helps to monitor WAL writes over time:

postgres=# SELECT * FROM pg_stat_wal;
-[ RECORD 1 ]----+------------------------------
wal_records      | 367627
wal_fpi          | 27636
wal_bytes        | 1283718067
wal_buffers_full | 8162
wal_write        | 13486
wal_sync         | 5321
wal_write_time   | 0
wal_sync_time    | 0
stats_reset      | 2021-10-06 10:42:37.246554+00

In situations like how often processes wait to write WAL because WAL buffer is full, it is important to provide the metrics for tuning the size of WAL buffers. DBAs would now love to tune the WAL buffer size for performance improvement using this Summary view.

With this summary information, you can track how many Full Page Images (FPI) were written to the WAL, insights into when Postgres generated a lot of WAL records due to a checkpoint. We can use the new wal_buffers_full counter to quickly see when the wal_buffers configuration parameter setting is set too low, which can cause unnecessary I/O that can be prevented by raising wal_buffers to a higher value.

I see this as a great compliment for DBAs constantly monitoring pg_stat_bgwriter view.

As mentioned in our earlier blog about new PG 14 configuration parameters – the new parameter track_wal_io_timing enables monitoring of WAL write times.

Monitor queries using pg_stat_activity.query_id

Most of the Database engineers would look at the pg_stat_activity catalog view every now and then, a new column query_id is added in the PG 14 release.

The calculation of the query_id, which uniquely identifies a query. If the same query is executed again it will have the same query_id, which enables us to identify workload patterns on the database. This information was only available with pg_stat_statements, which shows aggregate statistics about queries that have finished executing, but now this is available with pg_stat_activity as well as in log files by configuring the logging parameter log_line_prefix – %Q

The parameter default value is auto.

To put things into perspective, let’s look at the following example:

--session #1 for pgbench initialize command
pgbench -i -s 30

--session #2

mypgbench=# SELECT pid,substring(query,1,30) AS query_part,query_id FROM pg_stat_activity WHERE backend_type = 'client backend';
 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(1 row)

mypgbench=# \watch 1
             Mon Oct 11 05:47:54 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(1 row)

--pgbench writing data

             Mon Oct 11 05:47:55 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | copy pgbench_accounts from std |  3002094969003007272
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)
...
           Mon Oct 11 05:48:01 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | copy pgbench_accounts from std |  3002094969003007272
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)

--pgbench vacuum process

             Mon Oct 11 05:48:02 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | vacuum analyze pgbench_account | -1697985474390439145
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)
...
             Mon Oct 11 05:48:05 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | vacuum analyze pgbench_account | -1697985474390439145
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)

--pgbench creating primary keys

             Mon Oct 11 05:48:06 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | alter table pgbench_accounts a | -7684589253409855891
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)

             Mon Oct 11 05:48:07 2021 (every 1s)

 pid  |           query_part           |       query_id       
------+--------------------------------+----------------------
 1728 | alter table pgbench_accounts a | -7684589253409855891
 1324 | SELECT pid,substring(query,1,3 | -8357952155587440210
(2 rows)

The queries – COPY; VACUUM; ALTER TABLE is the same from the pgbench utility perspective, but the exact commands are different, making it hard to find patterns in the workload. With the query_id, we can clearly identify the number of certain kinds of queries and assess performance problems more easily, by just using a GROUP BY clause.

pg_stat_progress_copy

PostgreSQL has the ability to report the progress of certain commands during command execution. Currently, the only commands which support progress reporting are ANALYZE, CLUSTER, CREATE INDEX, VACUUM, COPY, and BASE_BACKUP. CLUSTER and pg_basebackup progress were added in PG13, and COPY progress is the new addition in PG 14.

COPY can be long, very long depending on the amount of data to load with users having no idea how the operation is going to last. It can be tracked in the new view that returns one row per backend running a COPY. Several fields are tracked in that:

• pid of the backend running COPY
• command: COPY FROM, TO
• bytes_processed: amount of data processed
• bytes_total: total amount of data from the original file of a COPY FROM
• and a few more columns

Let’s take a look at the following example:

--session #1 for pgbench initialize command
pgbench -i -s 30

-session #2
postgres=# SELECT * FROM pg_stat_progress_copy ;
(0 rows)

postgres=# \watch 1
Mon Oct 11 05:58:43 2021 (every 1s)

-[ RECORD 1 ]----+----------
pid              | 1874
datid            | 12972
datname          | postgres
relid            | 24691
command          | COPY FROM
type             | PIPE
bytes_processed  | 4129743
bytes_total      | 0
tuples_processed | 353403
tuples_excluded  | 0
...
Mon Oct 11 05:58:48 2021 (every 1s)

-[ RECORD 1 ]----+----------
pid              | 1874
datid            | 12972
datname          | postgres
relid            | 24691
command          | COPY FROM
type             | PIPE
bytes_processed  | 35648896
bytes_total      | 0
tuples_processed | 2689999
tuples_excluded  | 0

maintenance_io_concurrency

Similar to effective_io_concurrency, but used for maintenance work that is done on behalf of many client sessions. Useful for configuring concurrent disk I/O operations that PostgreSQL has at its disposal that can be executed simultaneously during maintenance operations. The default is 10.

The pg_settings.context is set to user for this parameter. So, testing the value shouldn’t be a tough job. Starting with 20 should be good. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter. Like:

--create a new tablespace
CREATE TABLESPACE mytbs2 location '/<location>' WITH (maintenance_io_concurrency=12);

--or ALTER an existing tablespace
ALTER TABLESPACE mytbs SET (maintenance_io_concurrency=12);

pg_stat_replication_slots

A statistics view showing statistics about logical replication slot usage, specifically about transactions spilled to disk from the ReorderBuffer.

One of the PG13 parameters we were very happy with around PostgreSQL Native Logical replication is logical_decoding_work_mem. Now, with the new catalog view pg_stat_replication_slots, it is going to make the DBA life easier to monitor logical replication slot usage. Statistics for a particular logical replication slot can be reset by calling the function pg_stat_reset_replication_slot(slot_name).

postgres=# \d pg_stat_replication_slots
               View "pg_catalog.pg_stat_replication_slots"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 slot_name    | text                     |           |          | 
 spill_txns   | bigint                   |           |          | 
 spill_count  | bigint                   |           |          | 
 spill_bytes  | bigint                   |           |          | 
 stream_txns  | bigint                   |           |          | 
 stream_count | bigint                   |           |          | 
 stream_bytes | bigint                   |           |          | 
 total_txns   | bigint                   |           |          | 
 total_bytes  | bigint                   |           |          | 
 stats_reset  | timestamp with time zone |           |          | 

pg_amcheck

We have used amcheck contrib module in a few scenarios – amcheck is implemented as an extension providing functions to verify the logical consistency of BTree indexes. It has been there since PG10.

pg_amcheck is a new command-line utility, effectively a command-line wrapper for the amcheck contrib module. It can be used to run amcheck on all of your databases, tables, and BTree indexes in a convenient way. DBAs can also use its various options – –heapallindexed; –rootdescend; –exclude-toast-pointers to name a few, to select just the targets and kinds of checks you want to perform. The utility can run parallel too, use the -j option.

Note: To leverage the utility, make sure to CREATE EXTENSION amcheck before using.

postgres@pg14:~ $ pg_amcheck -d postgres -v
pg_amcheck: including database "postgres"
pg_amcheck: in database "postgres": using amcheck version "1.3" in schema "public"
pg_amcheck: checking heap table "postgres.public.pgbench_accounts"
pg_amcheck: checking btree index "postgres.public.pgbench_accounts_pkey"
...
pg_amcheck: checking heap table "postgres.public.foo_blog_post"
pg_amcheck: checking heap table "postgres.public.pgbench_history"

There are many more super cool monitoring improvements available in PG14. For the complete list, take a look at the release note here.

Stay tuned for the PG 14 New features – Security tomorrow.