PostgreSQL 16 (currently in beta 2) will come with a new view for tracking I/O related statistics. I’ve written a very basic blog about pg_stat_io in the past. In this post and some follow up posts we want to dig a bit deeper into the various statistics this view provides.

The first statistic we want to look at is “extends”. Before we do that we need to understand what is meant by “extends”. The idea behind that is pretty simple. Consider an empty table like this:

postgres=# create table t ( a int );
CREATE TABLE
postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          |

How much disk space does this table currently allocate on disk? One way to get to this information is to ask PostgreSQL:

postgres=# select pg_relation_size('t');
 pg_relation_size 
------------------
                0
(1 row)

This means, currently, the table does not consume any disk space. This can easily be verified like this:

postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/5/16438
(1 row)

postgres=# \! ls -l $PGDATA/base/5/16438
-rw------- 1 postgres postgres 0 Jul 27 15:55 /u02/pgdata/PGDEV/base/5/16438
postgres=#

Both, PostgreSQL and the file system report the same size. What happens if we add one row?

postgres=# insert into t values (1);
INSERT 0 1
postgres=# select pg_relation_size('t');
 pg_relation_size 
------------------
             8192
(1 row)

postgres=# \! ls -l $PGDATA/base/5/16438
-rw------- 1 postgres postgres 8192 Jul 27 15:59 /u02/pgdata/PGDEV/base/5/16438
postgres=#

What we see here is 8k, and this is the block size of the cluster:

postgres=# show block_size;
 block_size 
------------
 8192
(1 row)

Coming back to question: What is an “extend”? When PostgreSQL needs to add additional rows into a relation, and there is no free space left in any of the existing blocks, a new block needs to be added. In the example above there was one “extend”, which corresponds to 8k, or one block.

These “extends” are now tracked in the new pg_stat_io view. Lets do some simple examples to understand those numbers. First of all we start from scratch and truncate the table we already have:

postgres=# truncate t;
TRUNCATE TABLE
postgres=# \! ls -l $PGDATA/base/5/16438
-rw------- 1 postgres postgres 0 Jul 27 16:09 /u02/pgdata/PGDEV/base/5/16438
postgres=#

This brings us back to size zero on disk. The pg_stat_io view comes with these columns:

postgres=# \d pg_stat_io
                        View "pg_catalog.pg_stat_io"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 backend_type   | text                     |           |          | 
 object         | text                     |           |          | 
 context        | text                     |           |          | 
 reads          | bigint                   |           |          | 
 read_time      | double precision         |           |          | 
 writes         | bigint                   |           |          | 
 write_time     | double precision         |           |          | 
 writebacks     | bigint                   |           |          | 
 writeback_time | double precision         |           |          | 
 extends        | bigint                   |           |          | 
 extend_time    | double precision         |           |          | 
 op_bytes       | bigint                   |           |          | 
 hits           | bigint                   |           |          | 
 evictions      | bigint                   |           |          | 
 reuses         | bigint                   |           |          | 
 fsyncs         | bigint                   |           |          | 
 fsync_time     | double precision         |           |          | 
 stats_reset    | timestamp with time zone |           |          | 

postgres=#

As we are only interested in our session (no other client sessions are connected to this instance), and we only want to see the statistics about “extends”, and we are only interested in statistics for objects of type “relation”, this is the query we can use to see the extends we’ve triggered:

postgres=# select backend_type,extends,op_bytes 
             from pg_stat_io 
            where backend_type = 'client backend' 
              and context = 'normal' 
              and object ='relation';
  backend_type  | extends | op_bytes 
----------------+---------+----------
 client backend |       9 |     8192
(1 row)

As we already have some statistics let’s reset the counters:

postgres=# select pg_stat_reset_shared('io');
 pg_stat_reset_shared 
----------------------
 
(1 row)

postgres=# select backend_type,extends,op_bytes 
             from pg_stat_io 
            where backend_type = 'client backend' 
              and context = 'normal' 
              and object ='relation';
  backend_type  | extends | op_bytes 
----------------+---------+----------
 client backend |       0 |     8192
(1 row)

postgres=#

“op_bytes” means the number of bytes per unit of I/O, which can be either “read”, “write”, or “extend”. This again comes down to the block size of 8k.

What happens once we generate 1000 rows in our tiny table “t”?

postgres=# insert into t select * from generate_series(1,1000);
INSERT 0 1000
postgres=# select backend_type,extends,op_bytes 
             from pg_stat_io 
            where backend_type = 'client backend' 
              and context = 'normal' 
              and object ='relation';
  backend_type  | extends | op_bytes 
  backend_type  | extends | op_bytes 
----------------+---------+----------
 client backend |       8 |     8192
(1 row)

This gives us 8 extends. When we do the math (8*8192) this results in 65536 bytes. PostgreSQL should report the same size if we ask for the size of the relation:

postgres=# select pg_relation_size('t');
 pg_relation_size 
------------------
            40960
(1 row)

It does not. The size reported by PostgreSQL is not as large as the product of block size and extends we’ve calculated above. What does the file system report?

postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/5/16441
(1 row)

postgres=# \! ls -l $PGDATA/base/5/16441
-rw------- 1 postgres postgres 40960 Jul 28 07:11 /u02/pgdata/PGDEV/base/5/16441
postgres=#

This again is consistent with PostgreSQL’s answer, but we still see a difference to what we’ve calculated out of the statistics from pg_stat_io. Where does this difference come from? The answer is the free space map:

postgres=# \! ls -l $PGDATA/base/5/16441*
-rw------- 1 postgres postgres 40960 Jul 28 07:11 /u02/pgdata/PGDEV/base/5/16441
-rw------- 1 postgres postgres 24576 Jul 28 07:11 /u02/pgdata/PGDEV/base/5/16441_fsm

Summing that up (40960+24576) gives exactly the result we want to see: 65536 bytes. Not only the table’s files need to be extended, but also the free space map, so the total of extends is the sum of both.

To finalize this post, there is an additional statistic when it comes to extends: “extend_time”. This is the time in milliseconds that was spend in extending relation files. By default this will always be zero:

postgres=# select backend_type,extends,extend_time,op_bytes 
             from pg_stat_io 
            where backend_type = 'client backend' 
              and context = 'normal' 
              and object ='relation';
  backend_type  | extends | extend_time | op_bytes 
----------------+---------+-------------+----------
 client backend |       8 |           0 |     8192
(1 row)

For getting those statistics you need to enable track_io_timing. That’s it for now, in the next post we’ll look at the “evictions” statistic.

Post Views: 1,273
Thumbnail [60x60]
by
Daniel Westermann