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.<\/p>\n
It is a popular and dangerous motto, especially in the IT sector: If it works, leave it alone!<\/em> 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.<\/p>\n Let's let it run for a certain amount of time, and then we'll go check what we created.\u00a0tmp_stat_user_tables<\/strong>\u00a0What is in its table?<\/p>\n pg_stat_user_tables<\/strong>\u00a0is 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.<\/p>\n Alternatively, telling PostgreSQL to reset all table statistics can also be a method:<\/p>\n<\/div>\n<\/div>\n<\/div>\n 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.<\/p>\n 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<\/strong> or PostgreSQL's scheduler pg_agent<\/strong> Available.<\/p>\n Let's first create a backup copy statistics table:<\/p>\n this that we created previously backup_stat_user_tables<\/strong> to its table, the time-stamped system (timestamped<\/em>) let's add a snapshot of:<\/p>\n If you add this insertion to this table as frequently as you like\u2014daily, monthly, or hourly\u2014in the manner we mentioned above, you will create a historical table usage statistics table.<\/p>","protected":false},"excerpt":{"rendered":" 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 used at all.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[76,75,45],"tags":[48,61,60],"class_list":["post-1967","post","type-post","status-publish","format-standard","hentry","category-linux","category-postgresql","category-yazi","tag-enterprisedb","tag-linux","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1967","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1967"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1967\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}create temp table tmp_stat_user_tables as select * from pg_stat_user_\n tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
select * from pg_stat_user_tables n\n join tmp_stat_user_tables t\n on n.relid=t.relid\n and (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) \n (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
select pg_stat_reset()\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
Generating Daily Table Usage Report<\/h2>\n
create table backup_stat_user_tables as\n select current_timestamp as snaptime, *\n from pg_stat_user_tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
INSERT into backup_stat_user_tables\n select current_timestamp as snaptime, *\n from pg_stat_user_tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n