PostgreSQL 101: Who is Using This Table?
After years of development and use, changing developers and database administrators, added/deleted tables, fields, and packages: Before you know it, you are faced with a database whose schema is now full of tables where nobody knows who uses them, or even if they are used at all.
If it ain't broke, don't fix it!
It is a popular and dangerous motto, especially in the IT sector: If it works, leave it alone! However, as a responsible database administrator or application developer, let's take the initiative and see who is using these tables and which tables are no longer in use at all.
create temp table tmp_stat_user_tables as select * from pg_stat_user_ tables;
Let's let it run for a certain amount of time, and then we'll go check what we created. tmp_stat_user_tables What is in its table?
select * from pg_stat_user_tables n
join tmp_stat_user_tables t
on n.relid=t.relid
and (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
(t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);
pg_stat_user_tables is a special view that keeps track of table usage statistics. When we check the usage count of tables in the statistics, we can see that the number of used tables has changed. If we leave the temporary table running for a longer period, we can observe table usage over the long term and generate a good candidate list for tables that are no longer used at all. I say candidate because the tables in this list might be used in reports rather than general application or user operations, so you will need to perform one more check.
Alternatively, telling PostgreSQL to reset all table statistics can also be a method:
select pg_stat_reset()
In this case, all table usage statistics will be reset. Therefore, you can find the used tables by specifying that their usage is not zero.
Generating Daily Table Usage Report
It is good to have historical table usage statistics. By looking at usage changes over time, you can make decisions about the schema and apply different optimizations. It is possible to generate hourly reports to find usage by daily, monthly, yearly, or load status. For this, the scheduling task tool of the Linux operating system CRON or PostgreSQL's scheduler pg_agent Available.
Let's first create a backup copy statistics table:
create table backup_stat_user_tables as select current_timestamp as snaptime, * from pg_stat_user_tables;
this that we created previously backup_stat_user_tables to its table, the time-stamped system (timestamped) let's add a snapshot of:
INSERT into backup_stat_user_tables select current_timestamp as snaptime, * from pg_stat_user_tables;
If you add this insertion to this table as frequently as you like—daily, monthly, or hourly—in the manner we mentioned above, you will create a historical table usage statistics table.
