I am testing new features of PostgreSQL v16. I see two new columns are added to system tables:
- *pg_catalog.pg_stat_all_tables.last_seq_scan* - records last sequential scan
- *pg_catalog.pg_stat_all_indexes.last_idx_scan* - records last index scan
I would like to get last table access time, so when is the last time: select, update, delete or insert was executed on table and I am interested if something got easier for PostgreSQL v16.
I executed:
- create table public.tab1 (id integer not null primary key);
- insert into public.tab1 values (1);
- select id from public.tab1 where id = 1;
- update public.tab1 set id = 2 where id = 1;
- delete from public.tab1 values where id = 2;
After every above SQL I have executed bellow SQL to get last table access time:
select table_name, max(last_time) as last_access from
(
select schemaname || '.' || relname as table_name, last_seq_scan as last_access_time
from pg_catalog.pg_stat_all_tables
union all
select schemaname || '.' || relname as table_name, last_idx_scan as last_access_time
from pg_catalog.pg_stat_all_indexes
) as mytable where table_name = 'public.tab1' group by table_name
;
I get new time in last_access_time for every action above except "insert". If I do the
explain of above commands I see "sequential scan" or "index scan" is NOT performed for "insert" statement, but it is performed for: select, update or delete.
I already know for other pre-PostgreSQL v16 solutions like:
- create trigger on insert
- create table with column: *last_insert default now()*
- turn on "track_commit_timestamp" and access "pg_xact_commit_timestamp(xmin)"
My question, I am specially interested in two things:
- Is there any solution without changing any objects/settings in PostgreSQL just pure select statement **and**
- is there something new related to this in PostgreSQL v16.
Asked by folow
(523 rep)
Jan 15, 2024, 09:04 AM