Hi all, Regarding this archive log generation found one observation. A table named abc_table id found to be archived every 9'th and 39'th minute. We are able to find number of tuples deleted from the pg_stat_user_tables view. But to my wonder the number of tuple inserts are shown 0. How can there be any delete without any inserts. It was found that the table is having 2060 rows, where in which all rows are getting deleted in every 9'th and 39'th minute of an hour. It implies that those deleted should be inserted before the delete operation. Also performed vacuum freeze on that table before 9'th minute of an hour it generated 36 archive logs, and when I tried to do the same operation after 9'th minute(say 11'th minute of the same hour), it is generating the same number of archive logs. This is possible only if the entire table gets updated/recreated. Now my final doubt is why the tuple inserts in pg_stat_user_tables is showing 0, when corresponding deletes are existing? Please find the below outputs FYR. --Steps performed on production server:-- --1. Found Count Of Rows in Production --****************************************** prod_erp=# select count(*) from abc_table;; count ------- 2060 (1 row) --2. Issued 'Select pg_stat_reset();' --3. Before Delete Statements (Before JAN 31'st 2018 14:09 Hrs) --**************************************************************** Issued: select * from pg_stat_user_tables where relname ='abc_table'; -[ RECORD 1 ]-----+---------------------------- relid | 550314 schemaname | public relname | abc_table seq_scan | 2 seq_tup_read | 4120 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 --4. After Delete Statements (Before JAN 31'st 2018 14:09 Hrs) --**************************************************************** select * from pg_stat_user_tables where relname ='abc_table'; -[ RECORD 1 ]-----+---------------------------- relid | 550314 schemaname | public relname | abc_table seq_scan | 3 seq_tup_read | 6180 idx_scan | 2060 idx_tup_fetch | 2060 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 2060 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 --5. After Delete Statements (Before JAN 31'st 2018 14:39 Hrs) --**************************************************************** select * from pg_stat_user_tables where relname ='abc_table'; -[ RECORD 1 ]-----+---------------------------- relid | 550314 schemaname | public relname | abc_table seq_scan | 4 seq_tup_read | 8240 idx_scan | 4120 idx_tup_fetch | 4120 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 4120 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 --6. After Delete Statements (Before JAN 31'st 2018 15:09 Hrs) --**************************************************************** select * from pg_stat_user_tables where relname ='abc_table'; -[ RECORD 1 ]-----+---------------------------- relid | 550314 schemaname | public relname | abc_table seq_scan | 5 seq_tup_read | 10300 idx_scan | 6180 idx_tup_fetch | 6180 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 6180 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 As said above if we compare n_tup_del value in steps 4,5,6 it says us that entire table is getting deleted(correct me if I'm wrong), but n_tup_ins is 0. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
