This is about another feature which will most likely show up in PostgreSQL 18 later this year. The statistic system is something which gets more and more details with almost every release of PostgreSQL, and PostgreSQL 18 will be no exception to that.
When you take a look at pg_stat_all_tables (or pg_stat_user_tables) in PostgreSQL 17, it looks like this:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1 20250110 (Red Hat 14.2.1-7), 64-bit
(1 row)
postgres=# \d pg_stat_all_tables
View "pg_catalog.pg_stat_all_tables"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
relid | oid | | |
schemaname | name | | |
relname | name | | |
seq_scan | bigint | | |
last_seq_scan | timestamp with time zone | | |
seq_tup_read | bigint | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_fetch | bigint | | |
n_tup_ins | bigint | | |
n_tup_upd | bigint | | |
n_tup_del | bigint | | |
n_tup_hot_upd | bigint | | |
n_tup_newpage_upd | bigint | | |
n_live_tup | bigint | | |
n_dead_tup | bigint | | |
n_mod_since_analyze | bigint | | |
n_ins_since_vacuum | bigint | | |
last_vacuum | timestamp with time zone | | |
last_autovacuum | timestamp with time zone | | |
last_analyze | timestamp with time zone | | |
last_autoanalyze | timestamp with time zone | | |
vacuum_count | bigint | | |
autovacuum_count | bigint | | |
analyze_count | bigint | | |
autoanalyze_count | bigint | | |
There already are statistics for [auto]vacuum and [auto]analyze but there is no information about how much time the system spend in total for vacuum and analyze operations. This is now available:
postgres=# select version();
version
--------------------------------------------------------------------
PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit
(1 row)
postgres=# \d pg_stat_all_tables
View "pg_catalog.pg_stat_all_tables"
Column | Type | Collation | Nullable | Default
------------------------+--------------------------+-----------+----------+---------
relid | oid | | |
schemaname | name | | |
relname | name | | |
seq_scan | bigint | | |
last_seq_scan | timestamp with time zone | | |
seq_tup_read | bigint | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_fetch | bigint | | |
n_tup_ins | bigint | | |
n_tup_upd | bigint | | |
n_tup_del | bigint | | |
n_tup_hot_upd | bigint | | |
n_tup_newpage_upd | bigint | | |
n_live_tup | bigint | | |
n_dead_tup | bigint | | |
n_mod_since_analyze | bigint | | |
n_ins_since_vacuum | bigint | | |
last_vacuum | timestamp with time zone | | |
last_autovacuum | timestamp with time zone | | |
last_analyze | timestamp with time zone | | |
last_autoanalyze | timestamp with time zone | | |
vacuum_count | bigint | | |
autovacuum_count | bigint | | |
analyze_count | bigint | | |
autoanalyze_count | bigint | | |
total_vacuum_time | double precision | | |
total_autovacuum_time | double precision | | |
total_analyze_time | double precision | | |
total_autoanalyze_time | double precision | | |
To see that in action, lets create a small table and populate it:
postgres=# create table t ( a int, b text);
CREATE TABLE
postgres=# insert into t select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
This triggers autovacuum (you’ll have to wait up to a minute before you see something, because of autovacuum_naptime) and you get the total time in ms spend for auto vacuum and auto analyze:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
postgres=# select last_autovacuum
, last_autoanalyze
, total_autovacuum_time
, total_autoanalyze_time
from pg_stat_all_tables
where relname = 't';
last_autovacuum | last_autoanalyze | total_autovacuum_time | total_autoanalyze_time
-------------------------------+-------------------------------+-----------------------+------------------------
2025-01-31 11:12:09.809252+01 | 2025-01-31 11:12:09.942748+01 | 187 | 134
(1 row)
The same happens if you manually trigger either vacuum or analyze:
postgres=# select last_vacuum
, last_analyze
, total_vacuum_time
, total_analyze_time
from pg_stat_all_tables
where relname = 't';
last_vacuum | last_analyze | total_vacuum_time | total_analyze_time
-------------+--------------+-------------------+--------------------
| | 0 | 0
(1 row)
postgres=# analyze t;
ANALYZE
postgres=# select last_vacuum
, last_analyze
, total_vacuum_time
, total_analyze_time
from pg_stat_all_tables
where relname = 't';
last_vacuum | last_analyze | total_vacuum_time | total_analyze_time
-------------+-------------------------------+-------------------+--------------------
| 2025-01-31 11:23:07.102182+01 | 0 | 52
(1 row)
postgres=# vacuum t;
VACUUM
postgres=# select last_vacuum
, last_analyze
, total_vacuum_time
, total_analyze_time
from pg_stat_all_tables
where relname = 't';
last_vacuum | last_analyze | total_vacuum_time | total_analyze_time
-------------------------------+-------------------------------+-------------------+--------------------
2025-01-31 11:23:12.286613+01 | 2025-01-31 11:23:07.102182+01 | 1 | 52
Nice, this really helps in identifying the relations where [auto]vacuum and [auto]anaylze have spent most of the time on. Thanks to all involved.