simple monitoring of PostgreSQL incremental statistics

Everybody should to know PostgreSQL's stats views: pg_stat_user_tables, pg_stat_database. There are lot of interesting values. For better interpretation we need a difference of these values in some time. It is impossible to get previous value with clean SQL, but in Postgres we can use PLpgSQL, and we can use custom configure variables as session variables. I wrote function buffer, that returns difference between current value and buffered, and save current value to buffer:

CREATE OR REPLACE FUNCTION public.buffer_dif(text, bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE r bigint;
BEGIN
  r := $2 - current_setting($1, true)::bigint;
  PERFORM set_config($1, $2::text, false);
  RETURN r;
END;
$function$

Now I can prepare query:

SELECT datname,
       buffer_dif('x.xact_commit_' || datname, xact_commit) AS commits, 
       buffer_dif('x.tup_inserted_' || datname, tup_inserted) AS inserted
  FROM pg_stat_database
 WHERE datname IS NOT NULL;

I can repeat this query every 1 second by psql command \watch:

^Cpostgres=# \watch 1
Čt 2. dubna 2020, 21:33:59 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:00 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:01 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Now I can see 1 sec differences of incremental metrics without any other tools.