Does change in n_live_tup and n_dead_tup means that some new rows were inserted into a table

Question

I need to check if some rows were inserted into one specific table. I need to check it on a daily basis.

I've already tried to use pg_stat_all_tables.n_live_tup for that, but I am not sure if that works correct and was originally designed for that purposes.

Also do columns n_tup_ins, n_tup_upd, n_tup_del depict if any rows were inserted, updated or deleted?

Answer 1

These statistical data are collected and updated by the statistics collector process. Individual database backends send statistical information to the statistics collector via an UDP socket.

This has two consequences:

• The information is updated asynchronously, so there can be a delay between the completion of a database operation and the visibility of its effects in the statistics data.
• In UDP, messages can get lost.

The information is used internally to trigger autovacuum and autoanalyze, so it does not matter if the numbers are 100% accurate or not. If that is good enough for your purposes, then you can certainly use that information.