show all not empty tables in postgres

Question

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 

Answer 1

You could use PostgreSQL's system catalogs with e.g.

SELECT n.nspname, c.relname
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.reltuples = 0 AND c.relkind = 'r';

According to the documentation, the number of rows is an estimate, though.

If your tables have columns that take their default values from sequences, you could list them and check their values with nextval. (Unfortunately, currval returns a session-dependent value, so you'd have to ensure that no one else is using the database and use both nextval and setval.)

SELECT n.nspname, c.relname
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'S';

(Unfortunately I couldn't yet find any way to determine, which sequence belongs to which table. Obviously it would be very helpful. Anyway, you can use pg_class.relnamespace to narrow down the results.)

See http://www.postgresql.org/docs/9.3/interactive/catalogs-overview.html for details.

Answer 2

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.