PostgreSQL 16: More statistics in pg_stat_*_tables

PostgreSQL 16 is already in feature freeze and Beta1 has been released on May 25th. This is the time you should start testing and report any issues back to the community. There are plenty of new features, as usual, and in this post we’ll take a look a additional statistics in the pg_stat_*_tables catalog views.

Let’s create a small simple table and an index:

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 select i, i::text from generate_series(1,500000) i;
INSERT 0 500000
postgres=# create index i1 on t1 (a);
CREATE INDEX

Some of the statistics which are already available in versions before PostgreSQL 16 are these:

postgres=# select seq_tup_read,n_tup_ins,n_live_tup from pg_stat_all_tables where relname = 't1';
 seq_tup_read | n_tup_ins | n_live_tup 
--------------+-----------+------------
       500000 |    500000 |     500000
(1 row)

This tells is that 500’000 tuples have been read, inserted and are active, which of course is not a big surprise as we’ve just inserted them. One additional statistic which has been added to PostgreSQL 16 is the time of the last sequential scan for a given table:

postgres=# select last_seq_scan from pg_stat_all_tables where relname = 't1';
         last_seq_scan         
-------------------------------
 2023-06-05 09:50:11.252371+02
(1 row)

This is already populated and corresponds to the time we’ve created the tuples. Another statistics is the time of the last index scan:

postgres=# select last_idx_scan from pg_stat_all_tables where relname = 't1';
 last_idx_scan 
---------------
 
(1 row)

This is empty as of now, as we did not read via the index, but will be populated once we do so:

postgres=# select * from t1 where a = 5;
 a | b 
---+---
 5 | 5
(1 row)

postgres=# select last_idx_scan from pg_stat_all_tables where relname = 't1';
         last_idx_scan         
-------------------------------
 2023-06-05 09:58:49.721493+02
(1 row)

This is already nice for tracking access times but there is another very interesting statistic which made it into PostgreSQL 16. When PostgreSQL is updating a tuple and this tuple cannot be placed into the same block, the new version of the tuple is added to another block. This is usually nothing you want to happen too often as it avoids HOT updates. The new statistic “n_tup_newpage_upd” tells you how often a tuple needed to be created in a another block instead of the current block of the tuple. As we did not specify the fill factor when we created the table above we have the default of one hundred. This means that an update of a tuple in all blocks except the last one will move the tuple to a another block:

postgres=# update t1 set b = 'xxx' where a = 5;
UPDATE 1
postgres=# select n_tup_newpage_upd from pg_stat_all_tables where relname = 't1';
 n_tup_newpage_upd 
-------------------
                 1
(1 row)

Really nice.