CODEWITHSUNDEEP
databasepostgresqlcountpostgresql-9.4
Takito Isumoro
Takito Isumoro

Reputation: 184

Postgres check if any new rows were inserted

I have numerous quite large tables (300-400 tables, ~30 million rows each). Everyday (once a day) I have to check if any new rows were inserted into any of these tables. Possible number of rows inserted may vary from 0 to 30 million rows. Rows are not going to be deleted.

At the moment, I check if any new rows were inserted using approximate count. And then compare it with previous (yesterday) result.

SELECT reltuples FROM pg_class WHERE oid='tablename'::regclass;

The main thing I doubt: how soon reltuples will be updated if, for example, 3000 rows will be inserted (or 5 rows inserted)? And is approximate count a good solution for that case?

My config parameters are: 

autovacuum_analyze_threshold: 50
autovacuum_analyze_scale_factor: 0.1

Upvotes: 0

Views: 1263

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246568

reltuples will be updated whenever VACUUM (ir autovacuum) runs, so this number normally has an error margin of 20%.

You'll get a better estimate for the number of rows in the table from the table statistics view:

SELECT n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'myschema' AND relname = 'mytable';

This number is updated by the statistics collector, so it is not guaranteed to be 100% accurate (there is a UDP socket involved), and it may take a little while for the effects of a data modification to be visible there.

Still it is often a more accurate estimate than reltuples.

Upvotes: 1

Related Questions