show all not empty tables in postgres
postgresql, sql
Solution
Checking for the number of rows could give you wrong results. Assume that a table is used as a staging table: rows get inserted (e.g. from a flat file), processed and deleted. If you check the number of rows in that table you could very well believe it's never used if you don't happen to run your query while the processing takes place.
Another way to detect "unused" tables would be to monitor the IO and changes that are done to the tables.
The statistic view pg_stat_user_tables records changes (deletes, inserts, updates) to each table in the system. The statistic view pg_statio_user_tables records IO done against the tables.
If you take snapshots of those tables in regular intervals you can calculate the difference in the values and see if a tables is used at all.
You can use `pg_stat_reset()` to reset all values to zero and then start from that.
Problem
Is there a simple PostgreSQL or even SQL way of listing empty/not empty tables? P.S.: I'm analyzing a database containing hundreds of tables and would like to detect "death code". I assume, when the table after some month is still empty, than it's not used. EDIT:Solved Thank you all! Finally this statement seems to output the statistics I can use: ``` select schemaname, relname, n_tup_ins from pg_stat_all_tables WHERE schemaname = 'public' ORDER BY n_tup_ins ```