Re: pg_stat_all_tables data isnt accurate

Hello Mariel,

On Mon, Dec 3, 2018 at 8:50 AM Mariel Cherkassky <
mariel(dot)cherkassky(at)gmail(dot)com> 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 1
> postgres=# rollback;
> ROLLBACK
> postgres=# 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