Hello Mariel,
On Mon, Dec 3, 2018 at 8:50 AM Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:
Hi,We are trying to use the info in pg_Stat_all_tables to debug our applcation(see how much inserts/updates/deletes since the last reset). However we found some scenarios where the data in the view isnt accurate :1)When you preform a rollback all the inserts/updates/deletes you did are added to the n_tup_ins/upd/del column :
[..]
postgres=# insert into test values(7);INSERT 0 1postgres=# rollback;ROLLBACKpostgres=# select relname, n_live_tup,n_tup_ins,n_tup_del,n_tup_upd from pg_Stat_all_tables where relname='test';relname | n_live_tup | n_tup_ins | n_tup_del | n_tup_upd---------+------------+-----------+-----------+-----------test | 0 | 3 | 0 | 0(1 row)
These counters reflect what is going on "under hood" -- physically, job
was done, tuples were inserted, but then just marked as dead. So
n_tup_ins has absolutely correct value here.
2)sometimes the n_tup_ins isnt accurate and it takes some time until it is updated. Does someone has an explanation for it ? During that time analyze isnt running so it seems might be something else.
Citation from the documentation https://www.postgresql.org/docs/current/monitoring-stats.html:
"When using the statistics to monitor collected data, it is
important to realize that the information does not update
instantaneously. Each individual server process transmits new
statistical counts to the collector just before going idle; so
a query or transaction still in progress does not affect the
displayed totals. Also, the collector itself emits a new report
at most once per
PGSTAT_STAT_INTERVAL milliseconds (500 ms
unless altered while building the server). So the displayed
information lags behind actual activity. However, current-query
information collected by track_activities is always up-to-date."Nik
